【DB系列】Jooq之常用函数使用姿势

文章目录
  1. I. 项目搭建
    1. 1. 项目依赖
    2. 2. 数据准备
  2. II. 使用姿势
    1. 0. 基本准备
    2. 1. abs
    3. 2. sqrt
    4. 3. pow
    5. 4. mod
    6. 5. ceil, floor, round
    7. 6. rand
    8. 7. length
    9. 8. concat
    10. 9. replace
    11. 10. lower, upper
    12. 11. left, right
    13. 12. trim
    14. 13. reverse
    15. 14. 日期相关
    16. 15. 日期加减
  3. II. 其他
    1. 0. 项目
    2. 1. 一灰灰Blog

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

I. 项目搭建

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

1. 项目依赖

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<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. 基本准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@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()

1
2
3
4
5
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

开方

1
2
3
4
5
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次方

1
2
3
4
5
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

求余

1
2
3
4
5
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

浮点数的取整处理

1
2
3
4
5
6
7
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

随机数

1
2
3
4
5
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

字符串长度

1
2
3
4
5
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

字符串拼接

1
2
3
4
5
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

字符串替换

1
2
3
4
5
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

字符串大小写转换

1
2
3
4
5
6
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

字符串左右截取

1
2
3
4
5
6
7
8
9
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

去掉收尾空白字符

1
2
3
4
5
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

字符串反转

1
2
3
4
5
6
7
8
9
10
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. 日期相关

1
2
3
4
5
6
7
8
9
10
11
12
13
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, 增量,单位)

1
2
3
4
5
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. 项目

系列博文

项目源码

1. 一灰灰Blog

尽信书则不如,以上内容,纯属一家之言,因个人能力有限,难免有疏漏和错误之处,如发现bug或者有更好的建议,欢迎批评指正,不吝感激

下面一灰灰的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

一灰灰blog


打赏 如果觉得我的文章对您有帮助,请随意打赏。
分享到