VOOZH about

URL: https://dev.to/javaer/jooq-util-jooqtemplate-quickstart-4kl5

⇱ Jooq util JooqTemplate Quickstart - DEV Community

1. Start a new Spring Boot project

Use start.spring.io to create a project with jooq

2. Add dependency

gradle:

implementation 'net.sf.sprtool:sprtool-jooq:1.2.0'

maven:

<dependency>
 <groupId>net.sf.sprtool</groupId>
 <artifactId>sprtool-jooq</artifactId>
 <version>1.2.0</version>
</dependency>

3. Inject JooqTemplate 

@Autowired
private JooqTemplate jt;

4. Use JooqTemplate

insert:

Map values=JooqMaps.toSnakeCase(someBean);
jt.insert("some_table",values);

update:

Map values=JooqMaps.toSnakeCase(someBean);
jt.updatev("some_table",values,"id",someBean.getId());

delete:

jt.deletev("some_table","id",idParam);

query single:

SomeBean someBean=jt.loadv("some_table",SomeBean.class,"id",id);

query multiple:
Final sql will not include "name" or "gender" when nameParam is null or genderParam is null.
The supported operators include: %,%L,%R,>,>=,==,<,<=,isnull,isnotnull,in,notin,between,notbetween,asc,ascnf,ascnl,desc,descnf,descnl.

Condition otherCondition=DSL...;
List<SomeBean> beanList=jt.queryv("some_table",SomeBean.class,"name%",nameParam,"gender",genderParam,"bidrthday:between",beignDate,endDate,otherCondition,"id:desc");

pagination query

List conditions=jt.conditions("id",idParam,"name%",nameParam,"birthday>=",birthDayParam,...);
LimitSelect limitSelect=new LimitSelect() {
 public SelectOrderByStep from(SelectSelectStep select) {
 return select.from(T("some_table")).where(conditions);
 }
 public List<OrderField> orderBy(){
 return Arrays.asList(F("birthday").desc());
 }
};
LimitResult result=jt.query(SomeBean.class,limitSelect,LimitRange.of(limitParam,offsetParam));