Mybatis @Select注解动态组合SQL语句
apicescn 人气:0一、背景说明
由于以前在项目中一直使用sqlmap.xml进行mybatis语句的编写和实现，其xml实现动态更新和查询较为方便，而目前由于技术框架所定，采用@Select、@Insert等注解方式来实现对应的持久化操作（MyBatis提供了简单的Java注解，使得我们可以不配置XML格式的Mapper文件，也能方便的编写简单的数据库操作代码）
对于简单的数据库操作基本能够满足日常需要，但注解对动态SQL的支持一直差强人意，即使MyBatis提供了InsertProvider等*Provider注解来支持注解的Dynamic SQL，也没有降低SQL的编写难度，甚至比XML格式的SQL语句更难编写和维护，实现较为复杂的语句时还是不那么方便，团队成员一直通过类来实现SQL语句的硬拼接
这样的硬语句编写的SQL语句很长又冗余，给维护和修改带来一定的成本且易读性差，为了提高效率，一次编写重复使用的原则，Mybatis在3.2版本之后，其实提供了LanguageDriver接口，就是便于使用该接口自定义SQL的解析方式。
故在这里将研究的MyBatis如何在注解模式下简化SQL语句的硬拼接实现动态组合版SQL的方案进行分享。
二、实现方案
我们先来看下LanguageDriver接口中的3个方法：
public interface LanguageDriver {
ParameterHandler createParameterHandler(MappedStatement var1, Object var2, BoundSql var3);
SqlSource createSqlSource(Configuration var1, XNode var2, Class<?> var3);
SqlSource createSqlSource(Configuration var1, String var2, Class<?> var3);
}- createParameterHandler方法为创建一个ParameterHandler对象，用于将实际参数赋值到JDBC语句中
- 将XML中读入的语句解析并返回一个sqlSource对象
- 将注解中读入的语句解析并返回一个sqlSource对象
一旦实现了LanguageDriver，我们即可指定该实现类作为SQL的解析器，在不使用XML Mapper的形式下，我们可以使用@Lang注解
@Mapper
public interface RoleDAO {
/**
* 查询角色信息列表
*
* @param roleParam 查询参数
* @return 角色列表
*/
@Select("select id,name,description,enabled,deleted,date_created as dateCreated,last_modified as lastModified"
+ " from admin_role (#{roleParam})")
@Lang(SimpleSelectLangDriver.class)
List<RoleDO> findListRoleByPage(ListRoleParam roleParam);LanguageDriver的默认实现类为XMLLanguageDriver和RawLanguageDriver；
分别为XML和Raw，Mybatis默认是XML语言，所以我们来看看XMLLanguageDriver中是怎么实现的：
public class XMLLanguageDriver implements LanguageDriver {
public XMLLanguageDriver() {
}
public ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql) {
return new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
}
public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) {
XMLScriptBuilder builder = new XMLScriptBuilder(configuration, script, parameterType);
return builder.parseScriptNode();
}
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
if(script.startsWith("<script>")) {
XPathParser textSqlNode1 = new XPathParser(script, false, configuration.getVariables(),
new XMLMapperEntityResolver());
return this.createSqlSource(configuration, textSqlNode1.evalNode("/script"), parameterType);
} else {
script = PropertyParser.parse(script, configuration.getVariables());
TextSqlNode textSqlNode = new TextSqlNode(script);
return (SqlSource)(textSqlNode.isDynamic()?new DynamicSqlSource(configuration, textSqlNode)
:new RawSqlSource(configuration, script, parameterType));
}
}
}发现其实mybatis已经帮忙写好了解析逻辑，而且发现如果是以开头的字符串传入后，会被以XML的格式进行解析。那么方案就可以确认了，我们继承XMLLanguageDriver这个类，并且重写其createSqlSource方法，按照自己编写逻辑解析好sql后，再调用父类的方法即可。
三、 实现自定义注解
本例中将给出一些常见的自定义注解的实现和使用方式。
1、自定义Select注解
在本例中的业务场景下，我们需要根据对象中的字段进行查询，就会写出硬SQL语句拼接类，如下代码：
/**
* 查询
*
* @param userParam 查询条件
* @return 用户信息列表
*/
@SelectProvider(type = UserSql.class, method = "listByPage")
List<UserDO> listByPage(@Param(value = "userParam") ListUserParam userParam);public class UserSql {
/**
* 拼接查询语句
*
* @param params 查询条件
* @return 查询语句
*/
public static String listByPage(Map params) {
ListUserParam userParam = (ListUserParam)params.get("userParam");
if (userParam == null) {
return "";
}
long begin = (userParam.getPi() - 1) * userParam.getPs();
long end = userParam.getPi() * userParam.getPs();
String condition = "";
StringBuffer sb = new StringBuffer(" with query as ( ");
sb.append(
" select row_number() over(order by user.last_modified desc, user.date_created desc) as row_nr, user.* ");
sb.append(" from ( ");
sb.append(" select * from admin_user where 1=1 ");
condition = " and username like '%#{userParam.username}%'";
sb.append(StringUtils.isBlank(userParam.getUsername()) ? "" : condition);
condition = " and name like '%#{userParam.name}%'";
sb.append(StringUtils.isBlank(userParam.getName()) ? "" : condition);
condition = " and mobile like '%#{userParam.mobile}%'";
sb.append(StringUtils.isBlank(userParam.getMobile()) ? "" : condition);
condition = " and authorities like '%#{userParam.authorities}%'";
sb.append(StringUtils.isBlank(userParam.getAuthorities()) ? "" : condition);
condition = " and enabled = #{userParam.enabled}";
sb.append(StringUtils.isBlank(userParam.getEnabled()) ? "" : condition);
sb.append(" ) ");
sb.append(" user) ");
sb.append(" ");
sb.append(" select ");
sb.append(" id, username, password, name, mobile, authorities, enabled, deleted, ");
sb.append(" creator_id as creatorId, creator, date_created as dateCreated, ");
sb.append(" modifier_id as modifierId, modifier, last_modified as lastModified ");
sb.append(" from query where row_nr > ");
sb.append(begin);
sb.append(" and row_nr <= ");
sb.append(end);
sb.append(" order by last_modified desc, date_created desc ");
log.info("====UserSql.query====sb:{}", sb.toString());
return sb.toString();
}
}对于这样硬拼接的SQL语句可读性较差，也不利用日常维护，我们可以通过实现LanguageDriver将where子句抽象化，以此来简化Select查询语句。
简化后代码如下( 在上述实现方案中已贴过代码)：
/**
* 查询角色信息列表
*
* @param roleParam 查询参数
* @return 角色列表
*/
@Select("select id,name,description,enabled,deleted,date_created as dateCreated,last_modified as lastModified"
+ " from admin_role (#{roleParam})")
@Lang(SimpleSelectLangDriver.class)
List<RoleDO> findListRoleByPage(ListRoleParam roleParam);其SimpleSelectLangDriver的实现代码如下：
package com.szss.admin.common;
import java.lang.reflect.Field;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;
import com.google.common.base.CaseFormat;
/**
* @author Allen
* @date 2018/3/9
*
* 自定义Select注解,用于动态生成Select语句
*/
public class SimpleSelectLangDriver extends XMLLanguageDriver implements LanguageDriver {
/**
* Pattern静态申明
*/
private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
/**
* 实现自定义Select注解
* @param configuration 配置参数
* @param script 入参
* @param parameterType 参数类型
* @return 转换后的SqlSource
*/
@Override
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
Matcher matcher = inPattern.matcher(script);
if (matcher.find()) {
StringBuilder sb = new StringBuilder();
sb.append("<where>");
for (Field field : parameterType.getDeclaredFields()) {
String tmp = "<if test=\"_field != null\"> AND _column=#{_field}</if>";
sb.append(tmp.replaceAll("_field", field.getName()).replaceAll("_column",
CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field.getName())));
}
sb.append("</where>");
script = matcher.replaceAll(sb.toString());
script = "<script>" + script + "</script>";
}
return super.createSqlSource(configuration, script, parameterType);
}
}上述代码实现了动态生成SQL语句的功能，但由于在VO实体类中可能有部分参数是我们不想加入到动态组合里面的或部分字段在数据库中并不存在相应的列（比如自动 生成的serialVersionUID等其他字段），这时我们就需要排除VO实体类的一些多余的不匹配的字段进行逻辑隐藏；我们增加一个自定义的注解，并且对Language的实现稍作修改即可。
新建一个注解，其代码如下：
package com.szss.admin.common;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author Allen
* @date 2018/3/9
*
* 自定义的注解,用于排除多余的变量（自定义注解，过滤多余字段）
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Invisible {
}然后在VO实体类中不需要加入的字段可进行引用该注解
package com.szss.admin.model.param;
import com.szss.admin.common.Invisible;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* 角色查询参数
*
* @author Allen
* @date 2018/3/8
*/
@Data
public class ListRoleParam {
/**
* 角色名称
*/
@ApiModelProperty(value = "角色名称", example = "管理员", position = 1)
private String name;
/**
* 是否启用：0-不可用，1-可用
*/
@ApiModelProperty(value = "是否启用", example = "0", position = 2)
private Boolean enabled;
/**
* 删除标示：0-未删除，1-已删除
*/
@ApiModelProperty(value = "删除标示", example = "0", position = 3)
private Boolean deleted;
/**
* 当前页码
*/
@ApiModelProperty(value = "当前页码", example = "1", position = 4)
@Invisible
private long pi;
/**
* 当前页面大小
*/
@ApiModelProperty(value = "当前页面大小", example = "10", position = 5)
@Invisible
private long ps;
}最后需要对上述中的SimpleSelectLangDriver实现类中将被该注解声明过的字段排除操作，代码如下：
for (Field field : parameterType.getDeclaredFields()) {
// 排除被Invisble修饰的变量
if (!field.isAnnotationPresent(Invisible.class)) {
String tmp = "<if test=\"_field != null\"> AND _column=#{_field}</if>";
sb.append(tmp.replaceAll("_field", field.getName()).replaceAll("_column",
CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field.getName())));
}
}如上所示，只是对SimpleSelectLangDriver类增加了if (!field.isAnnotationPresent(Invisible.class)) 这样的判断，已过滤多余的变量。
需要注意的是在使用Select的时候，传入的参数前无需加入@Param注解，否则会导致Mybatis找不到参数而抛出异常，如需加入 就需要绑定对象属性(如在语句中就需要使用param.name)。
2、自定义Select in注解
在使用Mybatis注解的时候，发现其对Select In格式的查询支持不是很友好，在字符串中输入十分繁琐，可以通过将自定义的标签转成格式；下面便通过我们自己实现的LanguageDriver来实现SQL的动态解析：
DAO接口层中代码如下：
@Select("SELECT * FROM admin_role WHERE id IN (#{roleIdList})")
@Lang(SimpleSelectInLangDriver.class)
List<RoleDO> selectRolesByRoleId(List<Integer> roleIdList);LanguageDriver实现类如下：
package com.szss.admin.common;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;
/**
* @author Allen
* @date 2018/3/9
*
* 自定义Select in 注解,用于动态生成Select in 语句
*/
public class SimpleSelectInLangDriver extends XMLLanguageDriver implements LanguageDriver {
/**
* Pattern静态申明
*/
private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
/**
* 实现自定义Select in 注解
* @param configuration 配置参数
* @param script 入参
* @param parameterType 参数类型
* @return 转换后的SqlSource
*/
@Override
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
Matcher matcher = inPattern.matcher(script);
if (matcher.find()) {
script = matcher.replaceAll("<foreach collection=\"$1\" item=\"_item\" open=\"(\" "
+ "separator=\",\" close=\")\" >#{_item}</foreach>");
}
script = "<script>" + script + "</script>";
return super.createSqlSource(configuration, script, parameterType);
}
}通过自己实现LanguageDriver，在服务器启动的时候，就会将我们自定义的标签解析为动态SQL语句，其等同于：
@Select("SELECT * " +
"FROM admin_role " +
"WHERE id IN " +
"<foreach item='item' index='index' collection='list'open='(' separator=',' close=')'>" +
"#{item}" +
"</foreach>")
List<RoleDO> selectRolesByRoleId(List<Integer> roleIdList);通过实现LanguageDriver，剥离了冗长的动态拼接SQL语句，简化了Select In的注解代码。
需要注意的是在使用Select In的时候，则与上述相反需务必在传入的参数前加@Param注解，否则会导致Mybatis找不到参数而抛出异常。
3、自定义Update的注解
在扩展update注解时，数据库每张表的字段和实体类的字段必须遵循一个约定（数据库中采用下划线命名法，实体类中采用驼峰命名法）。
当我们update的时候，会根据每个字段的映射关系，写出如下代码：
/**
* 更新
*
* @param roleDO 角色信息
* @return 影响行数
*/
@Update("update admin_role set role_name = #{roleDO.roleName}, "
+ " enabled = #{roleDO.enabled}, deleted = #{roleDO.deleted}, modifierId = #{roleDO.modifierId},"
+ " modifier = #{roleDO.modifier}, last_modified = #{roleDO.lastModified} where id = #{roleDO.id}")
int update(@Param(value = "roleDO") RoleDO roleDO);上述的代码我们可以将实体类中的驼峰式代码转换为下划线式命名方式，这样就可以将这种映射规律自动化，但此代码存在一定的问题，就是当你在更新部分字段时其余所有字段原来的值必须传入，否则可能会将原有数据更新为null或空，亦或在更新时先查询原数据后将变更的数据进行操作，这样不仅增加了数据库查询操作且会造成代码冗余，而经过实现LanguageDriver后，注解代码如下:
/**
* 更新
*
* @param roleParam 角色信息
*/
@Update("update admin_role (#{roleDO}) where id=#{id}")
@Lang(SimpleUpdateLangDriver.class)
void update(RoleParam roleParam);相对于原始的代码量有很大的减少，尤其是对于一个类中字段越多，改善也就越明显。实现方式为：
package com.szss.admin.common;
import java.lang.reflect.Field;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;
import com.google.common.base.CaseFormat;
/**
* @author Allen
* @date 2018/3/9
*
* 自定义Update注解,用于动态生成Update语句
*/
public class SimpleUpdateLangDriver extends XMLLanguageDriver implements LanguageDriver {
/**
* Pattern静态申明
*/
private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
/**
* 实现自定义Update注解
* @param configuration 配置参数
* @param script 入参
* @param parameterType 参数类型
* @return 转换后的SqlSource
*/
@Override
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
Matcher matcher = inPattern.matcher(script);
if (matcher.find()) {
StringBuilder sb = new StringBuilder();
sb.append("<set>");
for (Field field : parameterType.getDeclaredFields()) {
// 排除被Invisble修饰的变量
if (!field.isAnnotationPresent(Invisible.class)) {
String tmp = "<if test=\"_field != null\">_column=#{_field},</if>";
sb.append(tmp.replaceAll("_field", field.getName()).replaceAll("_column",
CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field.getName())));
}
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append("</set>");
script = matcher.replaceAll(sb.toString());
script = "<script>" + script + "</script>";
}
return super.createSqlSource(configuration, script, parameterType);
}
}注意此处在传入的参数前无需加入@Param注解。
4、自定义Insert的注解 同理
我们可以抽象化Insert操作，简化后的Insert注解为：
/**
* 插入
*
* @param roleParam 角色信息
*/
@Insert("insert into admin_role (#{roleDO})")
@Lang(SimpleInsertLangDriver.class)
void insert(RoleParam roleParam);SimpleInsertLanguageDriver实现类代码如下:
package com.szss.admin.common;
import java.lang.reflect.Field;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;
import com.google.common.base.CaseFormat;
/**
* @author Allen
* @date 2018/3/9
*
* 自定义Insert注解,用于动态生成Insert语句
*/
public class SimpleInsertLangDriver extends XMLLanguageDriver implements LanguageDriver {
/**
* Pattern静态申明
*/
private final Pattern inPattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");
/**
* 实现自定义Insert注解
* @param configuration 配置参数
* @param script 入参
* @param parameterType 参数类型
* @return 转换后的SqlSource
*/
@Override
public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
Matcher matcher = inPattern.matcher(script);
if (matcher.find()) {
StringBuilder sb = new StringBuilder();
StringBuilder tmp = new StringBuilder();
sb.append("(");
for (Field field : parameterType.getDeclaredFields()) {
if (!field.isAnnotationPresent(Invisible.class)) {
sb.append(
CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field.getName())
+ ",");
tmp.append("#{" + field.getName() + "},");
}
}
sb.deleteCharAt(sb.lastIndexOf(","));
tmp.deleteCharAt(tmp.lastIndexOf(","));
sb.append(") values ("
+ tmp.toString() + ")");
script = matcher.replaceAll(sb.toString());
script = "<script>" + script + "</script>";
}
return super.createSqlSource(configuration, script, parameterType);
}
}至此我们完成了基本的@Select、@Update、@Insert自定义注解，简单化繁杂的拼接SQL语句的尴尬，但以上代码在SimpleSelectLangDriver中还有一定的局限性，比如对于一些字段我们需要使用like来进行查询，这时就需要对上述自定义Seleect注解进行完善以实现各种业务的场景。
四、注意事项&遇到的一些坑
- 务必确保数据库中列名和实体类中字段能一一对应。
- 在使用自定义SQL解析器的时候，只能传入一个参数，即相应的对象参数即可；传入多个参数会导致解析器中获得到的class对象改变，使得sql解析异常。
- Update的实现能满足大部分的业务，但有些业务场景可以会遇到根据查询条件来更新查询参数的情况，比如Update user SET uesr_name = ‘王雷’ WHERE user_name = ‘小王’; 在这样的场景中请不要使用自定义的SQL解析器。
- 请使用Mybatis 3.3以上版本。3.2以下版本会存在一些Bug，在本例中使用的为mybatis-spring-boot-starter1.3.1，其Mybatis为3.4.5。
五、总结
通过实现Language Driver，我们可以很方便的自定义自己的注解。在遵循一些约定的情况下（数据库下划线命名，实体驼峰命名），我们可以大幅度的减少SQL的编写量，并且可以完全的屏蔽掉麻烦的XML编写方式，再也不用再编写复杂的拼接动态SQL的烦恼，简化工作，提高开发效率。
//简洁的数据库操作
/**
* 查询角色信息列表
*
* @param roleParam 查询参数
* @return 角色列表
*/
@Select("select id,name,description,enabled,deleted,date_created as dateCreated,last_modified as lastModified"
+ " from admin_role (#{roleParam})")
@Lang(SimpleSelectLangDriver.class)
List<RoleDO> findListRoleByPage(ListRoleParam roleParam);
/**
* 插入
*
* @param roleParam 角色信息
*/
@Insert("insert into admin_role (#{roleDO})")
@Lang(SimpleInsertLangDriver.class)
void insert(RoleParam roleParam);
/**
* 更新
*
* @param roleParam 角色信息
*/
@Update("update admin_role (#{roleDO}) where id=#{id}")
@Lang(SimpleUpdateLangDriver.class)
void update(RoleParam roleParam);通过@Lang注解以及自定义LanguageDriver类实现来简化数据库操作，不仅代码减少便于可读的同时，还避免了在更新时需要获取原数据的操作。
注：上述通过@Lang及实现LanguageDriver类的方法目前已基本不建议采用了，可采取Mybatis Plus来进行取代更为方便和快捷，具体可参加本博客的另一篇文章<Spring Boot环境下Mybatis Plus的快速应用>
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
加载全部内容