接前篇《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 = ?
。
那么对应的处理逻辑就演变如下:
- 遍历Query对象的所有字段,对于所有不为null的字段:
- 如果定义了
@QueryField
注解,就添加and
定义的查询语句; - 如果没定义
@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 | 集合非空时:(?[, ?]) |
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 = ?
)))