当前位置: 首页 > 工具软件 > jOOQ > 使用案例 >

JOOQ用法和实例

瞿健
2023-12-01

JOOQ简介

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

优点:

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

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

  3. 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中文文档教程

更多用法参考

 类似资料: