f0rb

V1

2022/02/14阅读:56主题:默认主题

基于查询对象字段的后缀推导

基于查询对象字段的后缀推导

接前篇《SpringDataJPA之从入门到弃用》

新的问题

前篇讲了如何在Query类的字段上加@QueryField注解以将字段映射为SQL语句的查询条件,但当查询条件越来越多时,我们的UserQuery类大概会变成这样:

public class UserQuery extends PageQuery {

    @QueryField(and = "username = ?")
    private String username;

    @QueryField(and = "nickname = ?")
    private String nickname;

    @QueryField(and = "email = ?")
    private String email;

    @QueryField(and = "valid = ?")
    private Boolean valid;

    @QueryField(and = "nickname LIKE CONCAT('%', ?, '%')")
    private String nicknameLike;

    @QueryField(and = "email LIKE CONCAT('%', ?, '%')")
    private String emailLike;

}

这时候就能发现点问题了:每个字段都需要加一个@QueryField注解,看上去写了很多重复代码。那么能不能简化一下呢?

那我们就试着继续重构一下。

继续重构

仔细查看UserQuery类的定义会发现部分字段的@QueryField注解的and变量定义的值格式类似,都是<column> = ?,而这里的<column>又和字段变量的名字是一样的,所以我们可以从private String <field>;中的变量名称直接推导出对应的查询语句,比如private String username,取出username再加上= ?即得username = ?

那么对应的处理逻辑就演变如下:

  1. 遍历Query对象的所有字段,对于所有不为null的字段:
  2. 如果定义了@QueryField注解,就添加and定义的查询语句;
  3. 如果没定义@QueryField注解,就是添加查询语句<feild> = ?

核心代码大致如下:

    private void appendAnd(List<String> whereList, Field field) {
        QueryField queryField = field.getAnnotation(QueryField.class);
-       whereList.add(queryField.and());
+       if (queryField != null) {
+           whereList.add(queryField.and());
+       } else {
+           whereList.add(field.getName() + " = ?");
+       }
    }

而我们的UserQuery类就可以简化如下,

public class UserQuery extends PageQuery {

    private String username;

    private String nickname;

    private String email;

    private Boolean valid;

    @QueryField(and = "nickname LIKE CONCAT('%', ?, '%')")
    private String nicknameLike;

    @QueryField(and = "email LIKE CONCAT('%', ?, '%')")
    private String emailLike;

}

那么问题又来了,LIKE查询可不可以也优化一下呢?

分析一下就会发现这种LIKE语句也可以通过字段名emailLike拆分得到查询email LIKE ?,再通过代码将字符串拼接成%value%的格式,替代掉CONCAT函数。这样LIKE查询也不需要添加@QueryField注解了。

于是我们就有了这样一个思路,就是把字段命名为列名加特定后缀的形式,当字段值不为null时,通过字段名和字段值来推导出对应的查询语句和查询参数。

后缀推导

通过前面的分析,我们可以从字段定义private String username;中推导出username = ?,从private String emailLike中推导出email LIKE ?,这是SQL语句里常用的两个查询条件,那么:

IN查询怎么处理呢?

IN查询的特点是参数是一个列表,长度不固定,所以需要根据入参的个数来确定占位符的个数。

对于IN查询,也是先将字段定义为列名加后缀的形式,比如idIn,假设传入了一个长度为3的列表,那么从idIn 推出id IN,从参数推出占位符为?, ?, ?,加上括号再拼接到一起,即得id IN (?, ?, ?)

当传入的列表长度为0时,需要将查询语句定义为id IN (null),与字段为null时的处理方式不同。

IS NULL如何处理?

IS NULL查询的特点是没有参数,不适合给字段定义具体的类型,也就不适合通过字段值是否为null来判断要不要为该字段生成查询语句。

一个解决方案就是将其定义为boolean类型,默认为false,不进行处理,当值被设为true时,才为其生成查询语句,即idNull赋值为true时,推导出对应的查询条件为id IS NULL

那还有NOT LIKE, NOT IN, >, <这些呢?

这些查询条件处理起来大同小异,这里汇总出一个表格,列出查询条件对应的后缀及处理方式。

后缀名称 操作符 占位符 类型限制 值处理
- =
Not != ?
NotLike NOT LIKE ? String %value%
Like LIKE ? String %value%
Start LIKE ? String %value
End LIKE ? String value%
NotIn NOT IN

集合非空时:(?[, ?])
集合为空时:忽略

Collection
In IN

集合非空时:(?[, ?])
集合为空时:(null)

Collection
NotNull IS NOT NULL - boolean
Null IS NULL - boolean
Gt > ?
Ge >= ?
Lt < ?
Le <= ?
Eq = ?

这样我们的UserQuery类就可以定义成这样来映射查询语句:

public class UserQuery extends PageQuery {
    private Integer id;
    private Integer idGt;
    private Integer idGe;
    private Integer idLt;
    private Integer idLe;
    private List<Integer> idIn;
    private List<Integer> idNotIn;
    private String username;
    private String usernameLike;
    private String usernameStart;
    private boolean emailNull;
    private boolean emailNotNull;
}

完美。

继续提问

以上通过对UserQuery类的优化,确立了SQL查询条件的后缀推导方案,大大简化了数据库动态查询代码的开发。

但是,问题又来了,对于下面这种复杂的嵌套查询又该如何处理呢?

SELECT * FROM t_perm WHERE id IN (
    SELECT permId FROM t_role_and_perm WHERE roleId IN (
        SELECT roleId FROM t_user_and_role WHERE userId IN (
            SELECT id FROM t_user WHERE username = ?
)))

下篇再解

分类:

后端

标签:

后端

作者介绍

f0rb
V1