8.Jooq之常用函数

Tags:
Categories:

  3 分钟阅读    

在我们的实际业务开发中，某些场景下会借助一些数据库支持的函数来做一些简单的操作，如针对浮点数的取整（ceil, floor）,字符串的长度获取(length)等，本文将介绍一下jooq中一些常见的函数使用姿势

I. 项目搭建

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

1. 项目依赖

关于如何创建一个SpringBoot的项目工程，不再本文的描述范围内，如有兴趣可以到文末的个人站点获取

在这个示例工程中，我们的选用h2dabase作为数据库（方便有兴趣的小伙伴直接获取工程源码之后，直接测试体验），因此对应的pom核心依赖如下

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

2. 数据准备

本文对应的项目工程，和前面介绍增加删除的一致，所以这里直接使用之前新增的数据

II. 使用姿势

0. 基本准备

@Repository
public class PoetryFunctionQueryRepository {

    private static final PoetryTB poetryTable = PoetryTB.POETRY;

    @Autowired
    private DSLContext dsl;

    private RecordMapper<PoetryPO, PoetryBO> poetryMapper;

    @PostConstruct
    public void init() {
        // 转换
        poetryMapper = dsl.configuration().recordMapperProvider().provide(poetryTable.recordType(), PoetryBO.class);
    }
}

1. abs

取绝对值，对于值可能为负数的场景下适用，除了使用下面的DSL.abs(Field)之外，也可以直接使用poetryTable.POET_ID.abs()

public void abs(int id) {
    // 绝对值 select abs(poet_id) from poetry where id = xxx
    Record1<Integer> ans = dsl.select(DSL.abs(poetryTable.POET_ID)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

2. sqrt

开方

public void sqrt(int id) {
    // 开方  select sqrt(poet_id) from poetry where id = xxx
    Record1<BigDecimal> ans = dsl.select(DSL.sqrt(poetryTable.POET_ID)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

3. pow

n次方

public void pow(int id) {
    // n次方  select pow(poet_id, 2) from poetry where id = xxx
    Record1<BigDecimal> ans = dsl.select(poetryTable.POET_ID.pow(2)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

4. mod

求余

public void mode(int id) {
    // 求余数，select mod(poet_id, 2) from poetry where id = xxx
    Record1<Integer> ans = dsl.select(poetryTable.POET_ID.mod(2)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

5. ceil, floor, round

浮点数的取整处理

public void ceil(int id) {
    // 向上取整，select ceil(poet_id) from poetry where id = xxx
    // floor 向下取整
    // round 四舍五入
    Record1<Integer> ans = dsl.select(DSL.ceil(poetryTable.POET_ID)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

6. rand

随机数

public void rand(int id) {
    // 随机数 select rand() from poetry where id = xxx
    Record1<BigDecimal> ans = dsl.select(DSL.rand()).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

7. length

字符串长度

public void strLen(int id) {
    // 字符串长度  select length(`content`) from poetry where id = xxx
    Record1<Integer> ans = dsl.select(DSL.length(poetryTable.CONTENT)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

8. concat

字符串拼接

public void strConcat(int id) {
    // 拼接 select concat(title, '--后缀') from poetry where id = xxx
    Record1<String> ans = dsl.select(DSL.concat(poetryTable.TITLE, "--后缀")).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

9. replace

字符串替换

public void replace(int id) {
    // 替换字符串  select replace(title, '落日', '一灰灰') from poetry where id = xxx
    Record1<String> ans = dsl.select(DSL.replace(poetryTable.TITLE, "落日", "一灰灰")).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

10. lower, upper

字符串大小写转换

public void lowerUpper(int id) {
    // 转小写  select lower(title) from poetry where id = xxx
    // 转大写  select upper(title) from poetry where id = xxx
    Record1<String> ans = dsl.select(DSL.lower(poetryTable.TITLE)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

11. left, right

字符串左右截取

public void leftRight(int id) {
    // 左侧截取n个字符  select left(title, n) from poetry where id = xxx
    // 右侧截取n个字符  select right(title, n) from poetry where id = xxx
    Record1<String> ans = dsl.select(DSL.left(poetryTable.TITLE, 2)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());

    ans = dsl.select(DSL.right(poetryTable.TITLE, 2)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

12. trim

去掉收尾空白字符

public void trim(int id) {
    // 去掉两端空格  select rim(title) from poetry where id = xxx
    Record1<String> ans = dsl.select(DSL.trim(poetryTable.TITLE)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
    System.out.println(ans.component1());
}

13. reverse

字符串反转

public void reverse(int id) {
    try {
        // fixme 请注意 h2database 不支持reverse 函数； mysql可以
        // 字符串反转 select reverse(title) from poetry where id = xxx
        Record1<String> ans = dsl.select(DSL.reverse(poetryTable.TITLE)).from(poetryTable).where(poetryTable.ID.eq(id)).fetchOne();
        System.out.println(ans.component1());
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

14. 日期相关

public void curDate() {
    // fixme h2database 不支持下面的函数；mysql支持
    // 获取当前日期、时间
    // curdate(), current_date() --> 日期，默认 'YYYY-MM-DD' 格式
    // curtime(), current_time() --> 时间，默认 'HH:MM:SS' 格式
    // now(), sysdate() --> 日期时间 YYYY-MM-DD HH:MM:SS
    try {
        Record4<Date, LocalDate, LocalTime, LocalDateTime> ans = dsl.select(DSL.currentDate(), DSL.currentLocalDate(), DSL.currentLocalTime(), DSL.currentLocalDateTime()).fetchOne();
        System.out.println(ans);
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

15. 日期加减

时间戳的加减 DSL.timestampAdd(field, 增量，单位)， 日期的加减可以使用 DSL.dateAdd(field, 增量，单位)

public void timeAdd() {
    // 日期运算，添加一天
    Record1<Timestamp> ans = dsl.select(DSL.timestampAdd(poetryTable.CREATE_AT, 1, DatePart.DAY)).from(poetryTable).limit(1).fetchOne();
    System.out.println(ans.component1());
}

II. 其他

0. 项目

系列博文

项目源码