JOOQ用法和实例

jooq使用和实例

BLKNjy

10449人浏览 · 2022-07-15 14:01:28

BLKNjy · 2022-07-15 14:01:28 发布

这里写目录标题

JOOQ简介

jOOQ，是一个ORM框架，利用其生成的Java代码和流畅的API，可以快速构建有类型约束的安全的SQL语句。

优点：

jOOQ的核心优势是可以将数据库表结构映射为Java类，包含表的基本描述和所有表字段。通过jOOQ提供的API，配合生成的Java代码，可以很方便的进行数据库操作

生成的Java代码字段类型是根据数据库映射成的Java类型，在进行设置和查询操作时，因为是Java代码，都会有强类型校验，所以对于数据的输入，是天然安全的，极大的减少了SQL注入的风险

jOOQ的代码生成策略是根据配置全量生成，任何对于数据库的改动，如果会影响到业务代码，在编译期间就会被发现，可以及时进行修复

CRUD

所有的操作jooq都提供两种方式，第一种是使用 DSLContext API 以类SQL的语法进行调用，第二种是利用 Record API 进行调用。这里面只记录第一种，了解第二种请点击这里

了解：

dslContext 代表DSLContext实例
S1_USER 由jOOQ插件生成的表描述常量
S1_USER.* 由jOOQ插件生成的表内字段常量

了解三个接口：

org.jooq.Result 结果集接口，此接口实现了List接口，可以当做一个集合来操作，是一个数据库查询结果集的包装类，除了集合的相关方法，该接口还提供了一些结果集转换，格式化，提取字段等方法。通常我们查询出来的结果都是此接口的实现类，掌握好此接口是jOOQ的基础接口，基本所有的SQL查询操作，都会碰到这个接口
org.jooq.Record 此接口再使用关系型数据库时，主要用于定义数据库表记录，储存的内容是一条表记录的字段和值，每个值会储存对应字段的类型，可以通过通用的 getValue(Field field) 方法，取到对应字段的值，也可以将这个接口看做是一条记录的字段/值映射
org.jooq.DSLContext jOOQ的核心接口之一，可以理解为一个SQL执行器，通过静态方法 DSL.using，可以获取一个 DSLContext 实例，此实例抽象了所有对于SQL的操作API，可以通过其提供的API方便的进行SQL操作

INSERT

// 类SQL语法 insertInto 方法第一个参数通常是表常量
dslContext.insertInto(S1_USER, S1_USER.USERNAME, S1_USER.ADDRESS, S1_USER.EMAIL)
        .values("username1", "demo-address1", "diamondfsd@gmail.com")
        .values("username2", "demo-address2", "diamondfsd@gmail.com")
        .execute();
 //批量插入
 List<S1UserRecord> recordList = IntStream.range(0, 10).mapToObj(i -> {
    S1UserRecord s1UserRecord = new S1UserRecord();
    s1UserRecord.setUsername("usernameBatchInsert" + i);
    s1UserRecord.setEmail("diamondfsd@gmail.com");
    return s1UserRecord;
}).collect(Collectors.toList());
dslContext.batchInsert(recordList).execute();
//插入后获取主键
//通过此方法插入数据，可以通过 returning API读取想要返回的数据，此语法支持返回多个值，通过fetchOne()方法可以取到一个Record对象
Integer userId = dslContext.insertInto(S1_USER,
    S1_USER.USERNAME, S1_USER.ADDRESS, S1_USER.EMAIL)
    .values("username1", "demo-address1", "diamondfsd@gmail.com")
    .returning(S1_USER.ID)
    .fetchOne().getId();
//插入时主键重复的处理办法
// 第一种 ：这里执行完，返回affecteRow影响行数为0，即不生效
// 生成的SQL: insert ignore into `learn-jooq`.`s1_user` (`id`, `username`) values (1, 'username-1')
int affecteRow = dslContext.insertInto(S1_USER,
    S1_USER.ID, S1_USER.USERNAME)
    .values(1, "username-1")
    .onDuplicateKeyIgnore()
    .execute();
// 第二种:更新主键所在列
//生成SQL: insert into `learn-jooq`.`s1_user` (`id`, `username`, `address`) values (1, 'duplicateKey-update', 'hello world') on duplicate key update `learn-jooq`.`s1_user`.`username` = 'duplicateKey-update', `learn-jooq`.`s1_user`.`address` = 'update'
dslContext.insertInto(S1_USER)
    .set(S1_USER.ID, 1)
    .set(S1_USER.USERNAME, "duplicateKey-insert")
    .set(S1_USER.ADDRESS, "hello world")
    .onDuplicateKeyUpdate()
    .set(S1_USER.USERNAME, "duplicateKey-update")
    .set(S1_USER.ADDRESS, "update")
    .execute();

update

dslContext.update(S1_USER)
    .set(S1_USER.USERNAME, "apiUsername-1")
    .set(S1_USER.ADDRESS, "update-address")
    .where(S1_USER.ID.eq(1))
    .execute()
    
//批量更新
  S1UserRecord record1 = new S1UserRecord();
record1.setId(1);
record1.setUsername("batchUsername-1");
S1UserRecord record2 = new S1UserRecord();
record2.setId(2);
record2.setUsername("batchUsername-2");

List<S1UserRecord> userRecordList = new ArrayList<>();
userRecordList.add(record1);
userRecordList.add(record2);
dslContext.batchUpdate(userRecordList).execute();

select

基本查询方法，默认查询指定表的所有字段，返回一个结果集的包装，通过Result.into方法，可以将结果集转换为任意指定类型集合，当然也可以通过 Record.getValue 方法取得任意字段值，值类型依赖于字段类型

// select `learn-jooq`.`s1_user`.`id`, `learn-jooq`.`s1_user`.`username`, `learn-jooq`.`s1_user`.`email`, `learn-jooq`.`s1_user`.`address`, `learn-jooq`.`s1_user`.`create_time`, `learn-jooq`.`s1_user`.`update_time` from `learn-jooq`.`s1_user`
Result<Record> fetchResult = dslContext.select().from(S1_USER).fetch();
List<S1UserRecord> result = fetch.into(S1UserRecord.class);

// select `learn-jooq`.`s1_user`.`id`, `learn-jooq`.`s1_user`.`username`, `learn-jooq`.`s1_user`.`email`, `learn-jooq`.`s1_user`.`address`, `learn-jooq`.`s1_user`.`create_time`, `learn-jooq`.`s1_user`.`update_time` from `learn-jooq`.`s1_user` where `learn-jooq`.`s1_user`.`id` in (1, 2)
Result<Record> fetchAll = dslContext.select().from(S1_USER)
                .where(S1_USER.ID.in(1, 2)).fetch();
fetchAll.forEach(record -> {
    Integer id = record.getValue(S1_USER.ID);
    String username = record.getValue(S1_USER.USERNAME);
    String address = record.getValue(S1_USER.ADDRESS);
    Timestamp createTime = record.getValue(S1_USER.CREATE_TIME);
    Timestamp updateTime = record.getValue(S1_USER.UPDATE_TIME);
});

jooq也支持关联查询

//UserMessagePojo为新建的Pojo类，用于存储查询结果，可以忽略具体内容
Result<Record3<String, String, String>> record3Result =
        dslContext.select(S1_USER.USERNAME,
        S2_USER_MESSAGE.MESSAGE_TITLE,
        S2_USER_MESSAGE.MESSAGE_CONTENT)
        .from(S2_USER_MESSAGE)
        .leftJoin(S1_USER).on(S1_USER.ID.eq(S2_USER_MESSAGE.USER_ID))
        .fetch();
List<UserMessagePojo> userMessagePojoList = record3Result.into(UserMessagePojo.class)

Condition动态查询

public void query(String name,String age) {
		Condition condition=DSL.trueCondition();//真实条件
		if(name!=null) {
			condition=condition.and(Tables.STUDENT.NAME.eq(name));
		}
		if(age!=null) {
			condition=condition.and(Tables.STUDENT.AGE.eq(Integer.parseInt(age)));
		}
		List<Student> list=context.select().from(Tables.STUDENT).where(condition).fetch().into(Student.class);
	}

Delete

dslContext.delete(S1_USER).where(S1_USER.USERNAME.eq("demo1")).execute();

//批量删除
S1UserRecord record1 = new S1UserRecord();
record1.setId(1);
S1UserRecord record2 = new S1UserRecord();
record2.setId(2);
dslContext.batchDelete(record1, record2).execute();
// 
List<S1UserRecord> recordList = new ArrayList<>();
recordList.add(record1);
recordList.add(record2);
dslContext.batchDelete(recordList).execute();

结果处理

查询操作通常以fetch API 作为结束API，例如常用的有，所有的读取类方法都差不多，掌握一个就能很快的举一反三

读取多条
fetch 读取集合
fetchSet 读取并返回一个Set集合，常用于去重
fetchArray 读取并返回一个数组

读取单条
fetchOne 读取单条记录，如果记录超过一条会报错
fetchAny 读取单条记录，如果有多条，会取第一条数据
fetchSingle 读取单条记录，如果记录为空或者记录超过一条会报错

读取并返回Map
fetchMap 读取并返回一个Map
fetchGroups 读取并返回一个分组Map

fetch

fetch()
无参调用此方法，返回的是一个Result结果集对象

Result<Record> records = dslContext.select().from(S1_USER).fetch();

fetch(RecordMapper mapper)
RecordMapper接口的提供map方法，用于来返回数据。map 方法传入一个 Record 对象。可以使用lambda表达式将 Record 对象转换成一个指定类型的POJO

List<S1UserPojo> userPojoList = dslContext.select()
            .from(S1_USER)
            .where(S1_USER.ID.eq(1))
            .fetch(r -> r.into(S1UserPojo.class));

多表查询，字段相同时，直接用into方法将结果集转换为POJO时，相同字段名称的方法会以最后一个字段值为准。这时候，我们可以现将结果集通过 into(Table table) 方法将结果集转换为指定表的Record对象，然后再into进指定的POJO类中

// 多表关联查询，查询s2_user_message.id = 2的数据，直接into的结果getId()却是1
// 这是因为同时关联查询了s1_user表，该表的id字段值为1
List<S2UserMessage> userMessage = dslContext.select().from(S2_USER_MESSAGE)
        .leftJoin(S1_USER).on(S1_USER.ID.eq(S2_USER_MESSAGE.USER_ID))
        .where(S2_USER_MESSAGE.ID.eq(2))
        .fetch(r -> r.into(S2UserMessage.class));
// userMessage.getId() == 1

// 将结果集into进指定的表描述中，然后在into至指定的POJO类
List<S2UserMessage> userMessage2 = dslContext.select().from(S2_USER_MESSAGE)
        .leftJoin(S1_USER).on(S1_USER.ID.eq(S2_USER_MESSAGE.USER_ID))
        .where(S2_USER_MESSAGE.ID.eq(2))
        .fetch(r -> {
            S2UserMessage fetchUserMessage = r.into(S2_USER_MESSAGE).into(S2UserMessage.class);
            fetchUserMessage.setUsername(r.get(S1_USER.USERNAME));
            return fetchUserMessage;
        });
// userMessage.getId() == 2

fetch(Field field)
Field是一个接口，代码生成器生成的表字段常量例如 S1_USER.ID, 都实现了 Field 接口，这个重载可以直接取出指定表字段，会自动根据传入的字段推测其类型
```
List<Integer> id = dslContext.select().from(S1_USER).where(S1_USER.ID.eq(1))
        .fetch(S1_USER.ID);
```
fetch(String fieldName, Class type)
可以直接通过字段名称字符串获取指定字段值，可以通过第二个参数指定返回值，如果不指定，返回Object
```
List<Integer> idList = dslContext.select().from(S1_USER).where(S1_USER.ID.eq(1))
        .fetch("id", Integer.class);
```
fetch(int fieldIndex, Class type)
可以通过查询字段下标顺序进行查询指定字段，可以通过第二个参数指定返回值，如果不指定，返回Object
```
List<Integer> idList = dslContext.select(S1_USER.ID, S1_USER.USERNAME)
        .from(S1_USER).where(S1_USER.ID.eq(1)).fetch(0, Integer.class);
```

fetch

此方法可以将结果集处理为一个Map格式，此方法有很多重载，这里介绍几个常用的，注意，此方法作为key的字段必须确定是在当前结果集中是唯一的，如果出现重复key，此方法会抛出异常

fetchMap(Field field, Class type)
以表字段值为key，返回一个 K:V 的Map对象

Map<Integer, S1UserPojo> idUserPojoMap = dslContext.select().from(S1_USER)
                .fetchMap(S1_USER.ID, S1UserPojo.class);

fetchMap(Feild field, Field field)
以表字段值为key，返回一个 K:V 的Map对象

Map<Integer, String> idUserNameMap = dslContext.select().from(S1_USER)
                .fetchMap(S1_USER.ID, S1_USER.USERNAME);

fetchgroup

此方法可以将结果集处理为一个Map格式，和fetchMap类似，只不过这里的值为一个指定类型的集合，通常在处理一对多数据时会用到

fetchGroups(Field field, Class type)
以表字段值为Key，返回一个K:List 的Map对象

Map<Integer, List<S2UserMessage>> userIdUserMessageMap = dslContext.select().from(S2_USER_MESSAGE)
                .fetchGroups(S2_USER_MESSAGE.USER_ID, S2UserMessage.class);

fetchGroups(Field keyField, Field valueField)

以表字段值为Key，返回一个K:List的Map对象

Map<Integer, List<Integer>> userIdUserMessageIdMap = dslContext.select().from(S2_USER_MESSAGE)
                .fetchGroups(S2_USER_MESSAGE.USER_ID, S2_USER_MESSAGE.ID);

参考JOOQ中文文档教程

更多用法参考