0 概述

jooq经验汇总

为什么JPA的CriteriaQuery不适合做查询：

返回数据只能映射到实体，映射到POJO真的好麻烦，映射到实体的话又会受到一个事务中只能对应一个对象的限制。
只能使用JPQL这种受限的SQL语法，对于分析业务真是弱爆了
接口设计有问题，对比jooq可以随意链式和组合的接口，CriteriaQuery的代码很难看

Hibernate对于处理在线事务处理的业务相当在行，但是对于分析型业务，批量数据更改的业务，Hibernate的机制就会不太适合了。这种情况下，我们应该尽可能用SQL来直接表达业务，但是SQL代码属于字符串操作，在Java中开发并不顺手，另外还要考虑SQL注入的安全问题，这个时候，jooq就很合适了。

jooq的特点是：

优雅与灵活，尽可能以SQL的思想来表达SQL代码，同时以Java的方式表达出来
强类型安全性，带有代码生成工具，可以从数据库或者Entity中生成代码，以保证SQL操作的类型安全性

如果项目不大，直接全部用jooq作为ORM层也是一个不错的选择，个人认为比MyBatis更加省事

参考资料：

1 与SpringBoot整合

代码在这里

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <scope>provided</scope>
</dependency>

SpringBoot的整合相当简单

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.14.9</version>
    <configuration>
        <jdbc>
        <driver>com.mysql.cj.jdbc.Driver</driver>
        <url>jdbc:mysql://127.0.0.1:3306/library?serverTimezone=GMT%2B8</url>
        <user>root</user>
        <password></password>
        </jdbc>
        <generator>
        <database>
            <inputSchema>library</inputSchema>
        </database>
        <target>
            <packageName>com.jooq_test.app.codegen</packageName>
            <directory>src/main/java</directory>
        </target>
        </generator>
    </configuration>
</plugin>

在pom.xml中指定以上的配置，以上的配置为从数据库中生成代码

在Maven中，指定jooq-codegen:generate就能生成代码了

@Autowired
private DSLContext context;

@PostConstruct
public void init(){
    Result<Record> result = context.select().from(Author.AUTHOR).fetch();
    log.info("result {}",result);

    context.insertInto(Tables.AUTHOR,Tables.AUTHOR.ID,Tables.AUTHOR.FIRST_NAME,Author.AUTHOR.LAST_NAME)
            .values(1,"f1","l1")
            .values(2,"f2","l2")
            .execute();


    Result<Record> result2 = context.select().from(Author.AUTHOR).fetch();
    log.info("result2 {}",result2);
}

使用jooq也很简单，直接用引用DSLContext，然后就可以了

2 与JPA整合

代码在这里

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <scope>provided</scope>
</dependency>

加多一个data-jpa的依赖就可以了

@Autowired
private EntityManager em;

@Autowired
private DSLContext dsl;

public void jooqTest(){
    //事务状态下，要将JPA脏数据落地
    if( em.isJoinedToTransaction() ){
        //所有实体都落地
        em.flush();
    }
    List<CountrySummary> countrySummaryList = dsl.select(field("name"),sum(field("man_count",Integer.class)).as("count"))
            .from(table("country"))
            .groupBy(field("name"))
            .fetch().into(CountrySummary.class);

    log.info("summary {}",countrySummaryList);
}

查询之前，为了与JPA代码协同工作，需要先调用em.flush()，否则JPA的脏检查不落地，查询的数据都是旧数据。

Demo中还有JPA与jooq的不同协同方式，可以看看，避免一下坑。

3 JPA的代码自动生成

代码在这里

<plugin>
    <dependencies>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-meta-extensions-hibernate</artifactId>
            <version>3.14.13</version>
            </dependency>
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>javax.persistence-api</artifactId>
            <version>2.2</version>
        </dependency>
    </dependencies>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.14.13</version>
    <configuration>
    <generator>
        <database>
            <name>org.jooq.meta.extensions.jpa.JPADatabase</name>
            <properties>

            <!-- A comma separated list of Java packages, that contain your entities -->
            <property>
                <key>packages</key>
                <value>com.jooq_test.app.business</value>
            </property>

            <!-- Whether JPA 2.1 AttributeConverters should be auto-mapped to jOOQ Converters.
                    Custom <forcedType/> configurations will have a higher priority than these auto-mapped converters.
                    This defaults to true. -->
            <property>
                <key>useAttributeConverters</key>
                <value>true</value>
            </property>

            <!-- The default schema for unqualified objects:

                    - public: all unqualified objects are located in the PUBLIC (upper case) schema
                    - none: all unqualified objects are located in the default schema (default)

                    This configuration can be overridden with the schema mapping feature -->
            <property>
                <key>unqualifiedSchema</key>
                <value>none</value>
            </property>
            <property>
                <key>hibernate.physical_naming_strategy</key>
                <value>org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy</value>
            </property>
            </properties>
        </database>
        <target>
            <packageName>com.jooq_test.app.codegen</packageName>
            <directory>src/main/java</directory>
        </target>
        </generator>
    </configuration>
</plugin>

更改Maven的plugin选项，改为从实体中生成代码

import static com.jooq_test.app.codegen.tables.Country.COUNTRY;

@Autowired
private EntityManager em;

@Autowired
private DSLContext dsl;

public void jooqTest(){
    //事务状态下，要将JPA脏数据落地
    if( em.isJoinedToTransaction() ){
        //所有实体都落地
        em.flush();
    }

    List<CountrySummary> countrySummaryList = dsl.select(COUNTRY.NAME,sum(field(COUNTRY.MAN_COUNT)).as("count"))
            .from(COUNTRY)
            .groupBy(COUNTRY.NAME)
            .fetch().into(CountrySummary.class);

    log.info("summary {}",countrySummaryList);
}

这个时候，查询就能可以以引入类的方式查询，有强类型的支持了

4 自由拼接sql

代码在这里

4.1 实体

package com.jooq_test.app.business;

import lombok.Getter;
import lombok.ToString;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
@ToString
@Getter
public class Country {

    private static Long globalId = 10001L;

    @Id
    private Long id;

    private String name;

    private Integer manCount;

    protected Country(){

    }

    public Country(String name,Integer manCount){
        this.id = globalId++;
        this.name = name;
        this.manCount = manCount;
    }

    public void modName(String name){
        this.name = name;
    }

    public void modCount(int count){
        this.manCount = count;
    }
}

Country实体

package com.jooq_test.app.business;

import lombok.Getter;
import lombok.ToString;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.GetMapping;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@ToString
@Getter
public class People {

    private static Long globalId = 10001L;

    @Id
    private Long id;

    private String name;

    private Long countryId;

    protected People(){

    }

    public People(String name,Long countryId){
        this.id = globalId++;
        this.name = name;
        this.countryId = countryId;
    }
}

People实体

package com.jooq_test.app;

import com.jooq_test.app.business.Country;
import com.jooq_test.app.business.CountryRepository;
import com.jooq_test.app.business.People;
import com.jooq_test.app.business.PeopleRepository;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.jooq.DSLContext;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.transaction.Transactional;
import java.util.List;

import static org.jooq.impl.DSL.*;

@Component
@Slf4j
public class CountryTest {

    @Autowired
    private CountryRepository countryRepository;

    @Autowired
    private PeopleRepository peopleRepository;

    @Transactional
    public void initData(){
        countryRepository.add(new Country("CHINA",100));
        countryRepository.add(new Country("US",10));
        countryRepository.add(new Country("JAPAN",10));

        peopleRepository.add( new People("CHINA_1",10001L));
        peopleRepository.add( new People("CHINA_2",10001L));
        peopleRepository.add( new People("US_1",10002L));
        peopleRepository.add( new People("US_2",10002L));
        peopleRepository.add( new People("JAPAN_1",10003L));
        peopleRepository.add( new People("JAPAN_2",10003L));
    }

    @Transactional
    public void clearAll(){
        List<Country> countryList = this.countryRepository.getAll();
        for( Country country : countryList){
            this.countryRepository.del(country);
        }
        List<People> peopleList = this.peopleRepository.getAll();
        for( People people : peopleList){
            this.peopleRepository.del(people);
        }
    }

    public void showAll(){
        List<Country> countryList = this.countryRepository.getAll();
        log.info("{}",countryList);
        List<People> peopleList = this.peopleRepository.getAll();
        log.info("{}",peopleList);
    }

    public void go(){
        CountryTest app = (CountryTest) AopContext.currentProxy();

        app.clearAll();
        app.initData();
        app.showAll();
    }
}

注入初始数据，没啥好说的

4.2 工具

package com.jooq_test.app;

import org.jooq.DSLContext;
import org.jooq.Query;
import org.jooq.ResultQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import java.util.List;

import static org.jooq.impl.DSL.*;

@Component
public class JooqRepository {

    @FunctionalInterface
    public interface JooqFetcher{
        ResultQuery apply(DSLContext dsl);
    }

    @Autowired
    private EntityManager em;

    @Autowired
    private DSLContext dslContext;

    public <T> List<T> fetch(JooqFetcher fetcher,Class<T> clazz){
        if( em.isJoinedToTransaction()){
            //将所有实体刷新到数据库中
            em.flush();
        }
        ResultQuery query = fetcher.apply(this.dslContext);
        return query.fetchInto(clazz);
    }
}

创建一个JooqRepository，也没啥好说的

4.3 按需查询与聚合

package com.jooq_test.app;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.jooq.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static org.jooq.impl.DSL.*;

@Component
public class QueryTest {

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class DTO{
        private Long id;

        private String name;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Filter{
        private String countryName;

        private String peopleName;

        private Integer pageIndex;

        private Integer pageSize;
    }

    @Autowired
    private JooqRepository jooqRepository;

    public List<DTO> get(Filter filter){
        return jooqRepository.fetch((jooq)->{
            SelectSelectStep<?> selectStep =  jooq.select(field("country.name").as("name"),field("country.id").as("id"));

            //按需join
            SelectJoinStep<?> joinStep;
            if( filter.peopleName != null ){
                joinStep = selectStep.from(table("country").innerJoin(table("people")).on("country.id = people.country_id"));
            }else{
                joinStep = selectStep.from(table("country"));
            }

            //按需where
            List<Condition> condition = new ArrayList<>();
            if( filter.countryName != null ){
                condition.add(field("country.name").equal(filter.countryName));
            }
            if(filter.peopleName != null ){
                condition.add(field("people.name").equal(filter.peopleName));
            }

            SelectGroupByStep<?> conditionStep;
            if( condition.size() != 0 ){
                conditionStep = joinStep.where(condition);
            }else{
                conditionStep = joinStep;
            }

            //按需分页
            if( filter.pageIndex != null && filter.pageSize != null ){
                return conditionStep.limit(filter.pageSize).offset(filter.pageIndex);
            }else{
                return conditionStep;
            }
        },DTO.class);
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class DTO2{
        private Long countryId;

        private Long sum_id;
        private Integer count;

    }


    public List<DTO2> group(){
        return jooqRepository.fetch((jooq)->{
            return jooq.select(count().as("count"),sum(field("id",Integer.class)).as("sum_id"),field("country_id"))
                    .from("people")
                    .groupBy(field("country_id"));
        },DTO2.class);
    }
}

这是自由拼接sql的方式，好处在于直接映射到sql，也没有sql注入的问题，缺点就是丢失强类型特性了。

4.4 测试代码

package com.jooq_test.app;

import lombok.extern.slf4j.Slf4j;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.PostConstruct;
import java.util.List;

/**
 * Hello world!
 *
 */
@SpringBootApplication
@EnableTransactionManagement(proxyTargetClass = true)
@EnableAspectJAutoProxy(exposeProxy = true)
@Slf4j
public class App 
{
    public static void main( String[] args )
    {
        SpringApplication.run(App.class,args);
    }

    //非事务下的jooq测试
    @Autowired
    private CountryTest countryTest;

    @Autowired
    private QueryTest queryTest;

    @PostConstruct
    public void init(){
        countryTest.go();

        //普通query测试
        QueryTest.Filter filter = new QueryTest.Filter();
        filter.setPageIndex(0);
        filter.setPageSize(10);
        filter.setCountryName("CHINA");

        List<QueryTest.DTO> result = queryTest.get(filter);
        log.info("query1 {}",result);

        //join测试
        QueryTest.Filter filter2 = new QueryTest.Filter();
        filter2.setPeopleName("US_2");
        List<QueryTest.DTO> result2 = queryTest.get(filter2);
        log.info("query2 {}",result2);

        //group测试
        List<QueryTest.DTO2> result3 = queryTest.group();
        log.info("group {}",result3);
    }
}

测试代码也比较简单

5 总结

对于与JPA整合的jooq项目，我建议是：

不需要使用类型安全性，因为总是需要在编译前点多一下，没有必要。
绕开JPA的nativeQuery查询，直接用flush + jooq的fetch操作就可以了。JPA的nativeQuery的设计真是一言难尽，难以转换为POJO，转换为实体又会带来以外的问题。
JOOQ不要做修改操作，只做查询操作。JOOQ做修改操作的时候，会与JPA的脏检查机制有冲突。

JOOQ相比MyBatis的优势在于：

更容易支持按需join，按需where，按需limit
支持获取JPA实体

本文作者： fishedee

版权声明：本博客所有文章均采用 CC BY-NC-SA 3.0 CN 许可协议，转载必须注明出处！

jooq经验汇总

0 概述

1 与SpringBoot整合

2 与JPA整合

3 JPA的代码自动生成

4 自由拼接sql

4.1 实体

4.2 工具

4.3 按需查询与聚合

4.4 测试代码

5 总结

相关文章