JOOQ——The easiest way to write SQL in Java

最近在看springboot官方文档的时候，在数据库连接章节看到了JOOQ，于是了解了一下。

一. 什么是JOOQ

JOOQ（Java Object Oriented Querying ）是Data Geekery的一个流行产品，它从数据库中生成Java代码，并允许您通过它的fluent API构建类型安全的SQL查询。JOOQ 既吸取了传统ORM操作数据的简单性和安全性，又保留了原生sql的灵活性，它更像是介于 ORMS和JDBC的中间层。

官网上说使用jOOQ DSL, SQL看起来几乎是由Java本地支持的，那么就直接看那看看例子吧：

sql:

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
AND BOOK.PUBLISHED > DATE '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
LIMIT 2
OFFSET 1

对应java代码：

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED.gt(date("2008-01-01")))
      .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .having(count().gt(5))
      .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
      .limit(2)
      .offset(1)

可以看出java代码很接近sql的风格，所以写起来还是很流畅。

二. springboot + jOOQ

1. pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.justme</groupId>
    <artifactId>jooq-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>jooq-demo</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--h2的 browser-based console 自动配置需要这个jar-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!--jooq代码生成 -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>com.h2database</groupId>
                        <artifactId>h2</artifactId>
                        <version>${h2.version}</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <jdbc>
                        <driver>org.h2.Driver</driver>
                        <url>jdbc:h2:~/test</url>
                        <user>sa</user>
                        <password></password>
                    </jdbc>
                    <generator>
                        <name>org.jooq.util.DefaultGenerator</name>
                        <database>
                            <name>org.jooq.util.h2.H2Database</name>
                            <includes>.*</includes>
                            <excludes />
                            <inputSchema>PUBLIC</inputSchema>
                        </database>
                        <target>
                            <packageName>justme.cn.jooqdemo.domain</packageName>
                            <directory>${basedir}/src/main/java</directory>
                        </target>
                        <generate>
                            <pojos>true</pojos>
                            <!--<records>false</records>-->
                        </generate>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2. 准备sql

schema.sql

CREATE TABLE author (
  id              NUMBER(7)     NOT NULL PRIMARY KEY,
  first_name      VARCHAR2(50),
  last_name       VARCHAR2(50)  NOT NULL,
  date_of_birth   DATE,
  year_of_birth   NUMBER(7),
  distinguished   NUMBER(1)
);

data.sql

INSERT INTO author VALUES (1, 'Greg', 'Turnquest', '1804-09-17', 1804, 1);
INSERT INTO author VALUES (2, 'Craig', 'Walls', '1804-09-18', 1804, 1);

这两个放src/main/resources目录下，应用启动后会自动执行。

3. 生成代码

如图

4. 编写代码

AuthorService.java

@Service
public class AuthorService {

    @Autowired
    private DSLContext create;

    public void getById(Integer id) {
        Result<Record> fetch = create.select().from(AUTHOR).where(AUTHOR.ID.eq(id)).fetch();
        System.out.println("输出结果");
        System.out.println(fetch);

//        List<Author> authors = create.select().from(AUTHOR).where(AUTHOR.ID.eq(id)).fetchInto(Author.class);
//        System.out.println(authors);
    }

    public void insert(Author author) {
        create.insertInto(AUTHOR)
                .set(AUTHOR.ID, author.getId())
                .set(AUTHOR.FIRST_NAME, author.getFirstName())
                .set(AUTHOR.LAST_NAME, author.getLastName())
                .execute();

        Result<Record> fetch = create.select().from(AUTHOR).fetch();
        System.out.println("输出结果");
        System.out.println(fetch);
    }

    public void insert2(Author author) {
        AuthorRecord authorRecord = create.newRecord(AUTHOR, author);
        authorRecord.store();
        create.insertInto(AUTHOR).set(authorRecord).execute();
        Result<Record> fetch = create.select().from(AUTHOR).fetch();
        System.out.println("输出结果");
        System.out.println(fetch);
    }

}

测试

package cn.justme.jooqdemo;

import cn.justme.jooqdemo.domain.tables.pojos.Author;
import cn.justme.jooqdemo.service.AuthorService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;

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

    @Autowired
    private AuthorService authorService;

    @Test
    public void contextLoads() {
    }

    @Test
    public void testGetById() {
        authorService.getById(1);
    }

    /*
     * 输出结果
     * +----+----------+---------+-------------+-------------+-------------+
     * |  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|YEAR_OF_BIRTH|DISTINGUISHED|
     * +----+----------+---------+-------------+-------------+-------------+
     * |   1|Greg      |Turnquest|1804-09-17   |         1804|            1|
     * +----+----------+---------+-------------+-------------+-------------+
     */

    @Test
    public void testInsert() {
        Author author = new Author();
        author.setId(3);
        author.setFirstName("shi");
        author.setLastName("wei");
        authorService.insert(author);
    }

    @Test
    public void testInsert2() throws IOException {
        Author author = new Author();
        author.setId(3);
        author.setFirstName("shi");
        author.setLastName("wei");
        authorService.insert2(author);
    }

    /*
     * 输出结果
     * +----+----------+---------+-------------+-------------+-------------+
     * |  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|YEAR_OF_BIRTH|DISTINGUISHED|
     * +----+----------+---------+-------------+-------------+-------------+
     * |   1|Greg      |Turnquest|1804-09-17   |         1804|            1|
     * |   2|Craig     |Walls    |1804-09-18   |         1804|            1|
     * |   3|shi       |wei      |{null}       |       {null}|       {null}|
     * +----+----------+---------+-------------+-------------+-------------+
     */
}

三. 总结

初步体验，写java代写起来确实有种写sql的感觉，上手还是挺容易的，后面再继续看看文档，有什么小项目，感觉可以尝试尝试。 jooq还支持主流的RDMS和更多的特性，如self-joins，union，存储过程，复杂的子查询等等。