JOOQ入门

简述

　　JOOQ，全称是Java Object Oriented Querying面向java对象查询，是一个ORM框架。利用其生成的Java代码和流畅的API，可以快速构建有类型约束的安全的SQL语句。

　　本质是在java语言层面，用java以流的方式生成对应sql语句。JOOQ会将数据库的数据映射成java类，包含表的基本描述和所有表字段，通过JOOQ的api生成你想要的sql语句

demo工程

　　https://github.com/Layton-sy/JooqPractice

引入依赖

gradle

implementation 'org.jooq:jooq:3.17.4'
implementation 'org.jooq:jooq-codegen:3.17.6'
implementation 'org.jooq:jooq-meta:3.17.6'

maven

<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq</artifactId>
  <version>3.17.8</version>
</dependency>

<!-- These may not be required, unless you use the GenerationTool manually for code generation -->
<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>3.17.8</version>
</dependency>
<dependency>
  <groupId>org.jooq.pro-java-8</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>3.17.8</version>
</dependency>

代码生成

　　jooq会根据数据库的元数据，生成Java代码，并生成指定的文件，存放到配置好的指定目录 

使用maven生成代码 

# 通过此命令里可以调用 jooq-codegen-maven 插件进行代码生成
mvn jooq-codegen:generate 

　　上面命令需配置maven插件

<build>
    <plugins>
        <plugin>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>

        <!-- 代码生成器插件 -->
        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>${jooq.version}</version>
            <configuration>
                <jdbc>
                    <driver>com.mysql.cj.jdbc.Driver</driver>
                    <url>jdbc:mysql://127.0.0.1:3306/learn-jooq?serverTimezone=GMT%2B8</url>
                    <user>root</user>
                    <password>root</password>
                </jdbc>
                <generator>
                    <database>
                        <includes>s1_.*</includes>
                        <inputSchema>learn-jooq</inputSchema>
                    </database>
                    <target>
                        <packageName>com.diamondfsd.jooq.learn.codegen</packageName>
                        <directory>/src/main/java</directory>
                    </target>
                </generator>
            </configuration>
        </plugin>
    </plugins>

使用gradle生成代码

　　这里把生成代码弄成了一个task，鼠标点一下即可

　　详细请参考https://blog.csdn.net/u013066244/article/details/102783372

　　官网的文档不是很友好：https://www.jooq.org/doc/3.12/manual/code-generation/codegen-gradle/ 

import groovy.xml.MarkupBuilder
import org.jooq.codegen.GenerationTool
apply plugin: 'java'
group 'com.misssad'
version '1.0-SNAPSHOT'
sourceCompatibility = 1.8
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.jooq:jooq:3.12.2'
    implementation 'org.jooq:jooq-meta:3.12.2'
    implementation 'org.jooq:jooq-codegen:3.12.2'
    implementation 'mysql:mysql-connector-java:8.0.15'
}
buildscript {
    repositories {
        mavenLocal()
        mavenCentral()
    }
    dependencies {
        classpath 'org.jooq:jooq-codegen:3.12.2'
        classpath('mysql:mysql-connector-java:8.0.15')
    }
}
task jooqCodeGenerate {
    def writer = new StringWriter()
    def xml = new MarkupBuilder(writer).configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd'){
        jdbc() {
            driver('com.mysql.cj.jdbc.Driver ')
            url('jdbc:mysql://localhost:3306/Student?useSSL=false&useUnicode=true&characterEncoding=UTF-8')
            user('root')
            password('pass')
        }
        generator() {
            database() { //不配这一项会生成所有数据库对应的代码
                name('org.jooq.meta.mysql.MySQLDatabase')
                inputSchema('Student') //指定的数据库
                includes('.*')
                excludes()
            }
            generate([:]) {
                pojos true
                daos true
            }
            target() {
                packageName('jooq') //生成的具体目录
                directory('src/main/java') //生成的根目录
            }
        }
    }
    GenerationTool.generate(writer.toString())
}

生成的代码说明

　　新建数据库school，新建表teacher和teacher_details

　　建表代码：

CREATE TABLE `teacher` (
  `name` varchar(50) NOT NULL,
  `age` int DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

create table if not exists Student.teacher_details
(
    teacher_id int auto_increment
        primary key,
    address    varchar(50) null,
    salary     int         null
);

　　然后我们执行gradle的生成代码的task，可以看到在我们指定的目录生成了jooq的代码

CRUD前准备

　　在jooq体系中，DSLContext是核心接口之一，我们可以把它理解为一个sql执行器，通过静态方法 DSL.using，可以获取一个 DSLContext 实例，此实例抽象了所有对于SQL的操作API，可以通过其提供的API方便的进行SQL操作

String jdbcUrl = "jdbc:mysql://localhost:3306/learn-jooq?serverTimezone=GMT%2B8&useSSL=false";
String jdbcUsername = "root";
String jdbcPassword = "pass";

// 获取 JDBC 链接
try (Connection connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)) {
    // 获取 jOOQ 执行器
    DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
} catch (SQLException e) {
    e.printStackTrace();
}

　　这里使用的是最原始的jdbc形式拿到DSLContext，拿到DSLContext之后我们就可以进行CRUD了

查询

普通查询

　　通过DSLContext实例的select、from、featch函数即可拿到返回的Record结果集，这里的select、where等函数和sql里的含义一样，from函数里填的是自动生成的表常量

Result<Record> recordResult = dslContext.select().from(TEACHER).fetch();
recordResult.forEach(record -> {
    Integer id = record.getValue(TEACHER.ID);
    Integer age = record.getValue(TEACHER.AGE);
    String name = record.getValue(TEACHER.NAME);
    System.out.println("id: "+ id + " , age: " + age + " , name: " + name);
});

　　最后通过record对象的getValue函数配合表常量的属性即可拿到结果对应的字段值

　　我们也可以通过Record.into方法将默认的Record对象转换为表的Record对象，所有的xxxRecord都是实现了Record对象的子类，表的record就有对应的属性了，通过get方法就能拿到返回值　　

List<TeacherRecord> userRecordResult = dslContext.select().from(TEACHER).fetch().into(TEACHER);
userRecordResult.forEach(record -> {
    Integer id = record.getId();
    Integer age = record.getAge();
    String name = record.getName();
    System.out.println("id: "+ id + " , age: " + age + " , name: " + name);
});

　　我们也可以通过fetchInto函数一步到位转化成实体类的record对象

List<TeacherRecord> fetchIntoClassResultList = dslContext.select().from(TEACHER).fetchInto(TeacherRecord.class);
Result<TeacherRecord> fetchIntoTableResultList = dslContext.select().from(TEACHER).fetchInto(TEACHER);

关联查询

　　对于一对一，一对多的关联查询，其实现的本质就是通过关联查询的结果构造出record类，然后通过反射的方式将record的查询结果转换成我们希望的类

　　对于查询时的record类，jooq提供了Record+数字的方式，表示该Record有多少个字段

 

　　此时我们想进行Teacher表和Classroom表的关联查询，可以创建对应的pojo

public class TeacherDetailsPojo {
    private String name;
    private Integer age;
    private String address;
    private Integer salary;
    //getter and setter
}

　　然后使用连接查询等sql方式，拿到汇总表的result

　　通过result的into方法即可转化成我们的pojo

Result<Record5<Integer,String,Integer,String,Integer>> record5Result = dslContext.select(
        TEACHER.ID,
        TEACHER.NAME,
        TEACHER.AGE,
        TEACHER_DETAILS.ADDRESS,
        TEACHER_DETAILS.SALARY
).from(TEACHER)
        .leftJoin(TEACHER_DETAILS)
        .on(TEACHER.ID.eq(TEACHER_DETAILS.TEACHER_ID))
        .fetch();

List<TeacherWithDetailsPojo> teacherWithDetailsPojoList = record5Result.into(TeacherWithDetailsPojo.class);

插入

单次插入

　　使用dslContext的insertInto，指定表和列，然后设置插入值，即可完成插入

dslContext.insertInto(TEACHER,TEACHER.AGE,TEACHER.NAME)
        .values(18,"test insert")
        .values(19,"test insert2")
        .execute();

　　也可以使用record的api进行插入 

TeacherRecord record = new TeacherRecord();
record.setName("new teacher");
record.setAge(11);
record.insert();

批量插入

　　对于一个record的list，我们可以使用dslContext的batchInsert方法进行批量插入

dslContext.batchInsert(Collection<? extends TableRecord<?>> records)

插入后获取自增的主键

　　通过returning指定你想返回的主键信息，然后通过fetchOne拿到那最后一条记录，就能知道最后的自增主键了

Integer id = dslContext.insertInto(TEACHER,TEACHER.AGE,TEACHER.NAME)
        .values(18,"test insert")
        .values(19,"test insert2")
        .returning(TEACHER.ID)
        .fetchOne().getId();

插入时主键重复

　　我们可以用onDuplicateKeyIgnore设置主键重复时忽略插入

dslContext.insertInto(TEACHER,TEACHER.ID,TEACHER.AGE,TEACHER.NAME)
        .values(1,18,"test insert")
        .values(2,19,"test insert2")
        .onDuplicateKeyIgnore()
        .execute()

　　也可以对主键存在的记录进行更新处理

dslContext.insertInto(TEACHER)
        .set(TEACHER.ID,1)
        .set(TEACHER.NAME,"teacher insert")
        .set(TEACHER.AGE,19)
        .onDuplicateKeyUpdate()
        .set(TEACHER.NAME,"teacher insert")
        .set(TEACHER.AGE,19)
        .execute();

更新

单次更新

　　sql的方式进行更新，和写sql语句类似

dslContext.update(TEACHER)
        .set(TEACHER.NAME, "updated name")
        .set(TEACHER.AGE, 30)
        .where(TEACHER.ID.eq(2))
        .execute();

　　也可以通过record的方式进行更新，默认使用的是主键进行作为where的条件

TeacherRecord record = dslContext.newRecord(TEACHER);
record.setId(1);
record.setName("teacher name 2");
record.setAge(33);
record.update();

批量更新

　　批量更新用的是record的api，调用dslContext的batchUpdate方法进行records的批量更新

TeacherRecord record1 = dslContext.newRecord(TEACHER);
record1.setId(1);
record1.setName("teacher name 1");
record1.setAge(33);

TeacherRecord record2 = dslContext.newRecord(TEACHER);
record2.setId(2);
record2.setName("teacher name 2");
record2.setAge(33);

List<TeacherRecord> records = new ArrayList<>();
records.add(record1);
records.add(record2);

dslContext.batchUpdate(records).execute();

删除

单次删除

　　sql的方式进行删除

dslContext.delete(TEACHER)
        .where(TEACHER.NAME.eq("teacher1"))
        .execute();

　　record的方式进行删除

TeacherRecord record = new TeacherRecord();
record.setId(2);
record.delete();

批量删除 

　　和update类似，通过batchDelete对records进行批量删除 

TeacherRecord record1 = new TeacherRecord();
record1.setId(1);

TeacherRecord record2 = new TeacherRecord();
record2.setId(2);

List<TeacherRecord> recordList = new ArrayList<>();
recordList.add(record1);
recordList.add(record2);
dslContext.batchDelete(recordList);

references

https://jooq.diamondfsd.com/learn/section-1-how-to-start.html

https://juejin.cn/post/6844903907219472397

https://blog.csdn.net/u013066244/article/details/102783372

https://juejin.cn/post/7126366746696482852