Springboot 2.x 整合jOOQ实现CRUD

最近在翻阅springboot官方文档时，看见官方文档中有推荐一款ORM框架——jOOQ,可能很多朋友和我一样都没有用过这款框架，于是百度了一下，发现用过的朋友的都说它在代码层面比Mybatis简洁得多，而且性能也非常优异，抱着学习的态度，通过查询相关资料，在本地写了一个demo工程，体验了一下，在此记录一下。demo基于springboot 2.2.0,jooq相关组件版本为3.12.1

创建springboot工程，引入jooq的starter

pom.xml

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

引入jooq插件依赖

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
 </dependency>

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

plugin配置

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${jooq.version}</version>
    <executions>
         <execution>
            <goals>
               <goal>generate</goal>
            </goals>
         </execution>
     </executions>
        <dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                 <version>${mysql.version}</version>
                 </dependency>
             </dependencies>
      <configuration>
                            <configurationFile>src/main/resources/JooqConfig.xml</configurationFile>
       </configuration>
</plugin>

在工程的src/main/resources目录下创建JooqConfig.xml文件，内容如下:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <jdbc>
        <driver>com.mysql.jdbc.Driver</driver>
        <!-- 配置数据库地址 -->
        <url>jdbc:mysql://127.0.0.1:3306/students?characterEncoding=UTF-8</url>
        <!-- 配置数据库用户名-->
        <user>root</user>
        <!-- 配置数据库密码-->
        <password>1234</password>
    </jdbc>
    <generator>
        <!-- 代码生成器 -->
        <!--   <name>org.jooq.meta.mysql.MySQLDatabase</name>-->
        <database>
            <!--强制为scheme1模式下所有的含有id域生成id-->
            <!--是否重写主键-->
            <name>org.jooq.meta.mysql.MySQLDatabase</name>
            <!--include和exclude用于控制为数据库中哪些表生成代码-->
            <includes>.*</includes>
            <excludes></excludes>
            <!--数据库名称-->
            <inputSchema>students</inputSchema>
        </database>

        <generate>
            <!--是否生成dao和pojo-->
            <daos>true</daos>
            <pojos>true</pojos>
            <!--是否把数据库时间类型映射到java 8时间类型-->
            <javaTimeTypes>true</javaTimeTypes>
            <!--<interfaces>true</interfaces>-->
            <!--是否在生成的代码中添加spring注释，比如@Repository-->
            <springAnnotations>false</springAnnotations>
        </generate>

        <target>
            <!--生成代码文件的包名及放置目录-->
            <packageName>com.twp.spring.jooq</packageName>
            <directory>src/main/java</directory>
        </target>
    </generator>
</configuration>

在数据库创建表，完成之后，启动jooq-codegen插件

CREATE TABLE celebrity (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  birth_place VARCHAR(50),
  PRIMARY KEY (`id`)
)ENGINE=INNODB CHARSET=utf8;

执行之后，会在项目目录中生成如下代码，其中会有两个以表名命名的类，一个代表数据的表，一个代表实体类

代码生成后，下面尝试实现简单的CRUD功能，其他的复杂查询后续再摸索

public interface CelebrityService {

    int createCelebrity(Celebrity celebrity);

    List<Celebrity> findAll();

    int updateCelebrity(Celebrity celebrity);

    int deleteCelebrity(int  id);
}

@Service
public class CelebrityServiceImpl implements CelebrityService {

    private final DSLContext dslContext;

    @Autowired
    public CelebrityServiceImpl(DSLContext dslContext){
        this.dslContext = dslContext;
    }

    @Override
    public int createCelebrity(Celebrity celebrity) {
        com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
        int execute = dslContext.insertInto(celeTbl)
                .columns(celeTbl.ID, celeTbl.FIRST_NAME, celeTbl.LAST_NAME, celeTbl.DATE_OF_BIRTH, celeTbl.BIRTH_PLACE)
                .values(celebrity.getId(), celebrity.getFirstName(), celebrity.getLastName(), celebrity.getDateOfBirth(), celebrity.getBirthPlace())
                .execute();
        return execute;
    }

    @Override
    public List<Celebrity> findAll() {
        com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
        List<Celebrity> celebrities = dslContext.select().from(celeTbl).fetchInto(Celebrity.class);
        return celebrities;
    }

    @Override
    public int updateCelebrity(Celebrity celebrity) {
        com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
        int execute = dslContext.update(celeTbl)
                .set(celeTbl.FIRST_NAME,celebrity.getFirstName())
                .set(celeTbl.LAST_NAME,celebrity.getLastName())
                .set(celeTbl.DATE_OF_BIRTH,celebrity.getDateOfBirth())
                .set(celeTbl.BIRTH_PLACE,celebrity.getBirthPlace())
                .set(celeTbl.BIRTH_PLACE, celebrity.getBirthPlace())
                .where(celeTbl.ID.equal(celebrity.getId()))
                .execute();
        return execute;
    }

    @Override
    public int deleteCelebrity(int id) {
        com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
        int execute = dslContext.delete(celeTbl).where(celeTbl.ID.equal(id)).execute();
        return execute;
    }
}

模拟数据进行测试

@SpringBootTest
@RunWith(SpringRunner.class)
public class SpringJooqApplicationTests {

    @Autowired
    private CelebrityService service;

    @Test
    public void createTest(){
        Celebrity celebrity1 = new Celebrity(1,"张","爱玲", LocalDate.of(1920,9,30),"上海");
        Celebrity celebrity2 = new Celebrity(2,"鲁","迅", LocalDate.of(1881,9,25),"浙江绍兴");
        Celebrity celebrity3 = new Celebrity(3,"老","舍", LocalDate.of(1899,2,3),"北京");
        Celebrity celebrity4 = new Celebrity(4,"巴","金", LocalDate.of(1904,11,25),"四川成都");
        List<Celebrity> celebrityList = new ArrayList<Celebrity>(){{
           add(celebrity1);
           add(celebrity2);
           add(celebrity3);
           add(celebrity4);
        }};
        celebrityList.forEach((cele)->{
            int create = service.createCelebrity(cele);
            System.out.println("create:"+create);
        });
    }

    @Test
    public void retrievallTest(){
        service.findAll().forEach((cele)->{
            System.out.println("celebrity:" + cele.toString());
        });
    }

    @Test
    public void updateTest(){
        Celebrity celebrity = new Celebrity(2,"鲁","迅", LocalDate.of(1881,9,25),"浙江省绍兴府会稽县");
        int update = service.updateCelebrity(celebrity);
        System.out.println("update:" + update);
    }

    @Test
    public void deleteTest(){
        int i = service.deleteCelebrity(1);
        System.out.println("delete:" + i);
    }
}

新增：

查询：

更新：

删除：

jOOQ支持很多的复杂查询功能，在编写代码时只需以面向对象编程的语法组装查询条件，代码可读性更高，操作也比较简单，配置较少，很大程度上可以提高开发效率。