再谈动态SQL

专栏精选

引入Mybatis

Mybatis的快速入门

Mybatis的增删改查扩展功能说明

mapper映射的参数和结果

Mybatis复杂类型的结果映射

Mybatis基于注解的结果映射

Mybatis枚举类型处理和类型处理器

文章目录

专栏精选
摘要
引言
正文
- 动态sql标签
- - if
  - choose...when...otherwise
  - where、set
  - trim
  - foreach
  - script
- 动态sql API
- - 通过类名和方法名定位SQL
  - 通过方法名定位SQL
  - 自动定位SQL
总结

摘要

在这篇文章中，我们将深入Mybatis动态SQL的世界，了解动态SQL标签和动态sqlAPI的基本方法，其中的很多观点或内容都能在一定程度上让我们的开发之旅更加轻松方便，这是一个菜鸟提升技术能力，老鸟巩固基础知识的好机会。准备好开启今天的神奇之旅了吗？

引言

大家好，我是奇迹老李，一个专注于分享开发经验和基础教程的博主。欢迎来到我的频道，这里汇聚了汇集编程技巧、代码示例和技术教程，欢迎广大朋友们点赞评论提出意见，重要的是点击关注喔 🙆，期待在这里与你共同度过美好的时光🕹️，今天要和大家分享的内容是再谈动态SQL。做好准备，Let’s go🚎🚀

正文

在引入Mybatis一文中我们提到，jdbc对于过长，过复杂，多条件查询的无力感，Mybatis提供动态SQL这一特性解决拼接SQL语句的痛点。在之前的文章中我们已经简单介绍过动态SQL的一些特性，如

条件语句if、choose…when…otherwise
循环语句foreach
sql条件语句where、set

除此之外，动态SQL还包含以下几种特性

扩展语句trim、bind、script
动态sql API

动态sql标签

if

<if test="null != appName and ''.toString() != appName">and app_name like concat('%',#{appName},'%')</if>  
<if test="null != authType and ''.toString() != authType">and auth_type = #{authType}</if>  
<if test="null != startDate and '' != startDate">and create_date >= #{startDate}</if>  
<if test="null != endDate and '' != endDate">and create_date &lt;= #{endDate}</if>

choose…when…otherwise

mybatis映射文件中的 if…else

<choose>  
    <when test="id != null and id > 0">id=#{id}</when>  
    <when test="id <= 0">is_del='0'</when>
    <otherwise>id='1'</otherwise>  
</choose>

java代码的同义改写

Intege id=...;
if(id != null && id >0){
	//id=#{id}
}else if(id <=0){
	//is_del='0'
}else{
	//id='1'
}

where、set

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
—— Mybatis官方文档

<!--如果满足if条件，and auth_type = #{authType}中的“and ”将会被删除，并在前边添加“where ”，其结果是-->
<!--where auth_type = #{authType}-->
select * from app_test
<where>  
    <if test="null != authType and ''.toString() != authType">and auth_type = #{authType}</if>  
</where>

set 元素用于更新（update）语句中的set部分，和where一样，set的子元素返回的结果结尾如果有“, ”，set元素也会自动将其去除

<!--如果满足if条件，app_name = #{appName},中的“,”会被删除，其他的“,”不会删除，并在语句之前添加“set ”，其结果是-->
<!--update app_test set app_code = #{appCode},app_name = #{appName} where id=#{id}-->
update app_test  
<set>  
	<if test="appCode != null and '' != appCode">app_code = #{appCode},</if>
    <if test="appName != null and '' != appName">app_name = #{appName},</if>
</set>
where id=#{id}

trim

trim元素中可以自定义子句应该忽略的内容、和应该添加的内容，where和set标签的功能都能通过trim实现

trim实现where的功能

<!--prefix属性表示将要给子句添加的前缀，prefixOverrides属性表示子句如果出现这样的开头将其忽略-->
select * from app_test
<trim prefix="WHERE" prefixOverrides="AND |OR ">
	<if test="null != authType and ''.toString() != authType">AND auth_type = #{authType}</if>  
</trim>

trim实现set的功能

<!--suffixOverrides属性表示子句如果出现这样的结尾将其忽略-->
update app_test  
<trim prefix="SET" suffixOverrides=",">
	<if test="appCode != null and '' != appCode">app_code = #{appCode},</if>
    <if test="appName != null and '' != appName">app_name = #{appName},</if>
</trim>
where id=#{id}

trim标签属性详解

序号	属性名	效果说明
1	prefix	前缀
2	suffix	后缀
3	prefixOverrides	将被删除的前缀
4	suffixOverrides	将被删除的后缀

由此可见，以上的set内容可以写成如下形式

update upp_test set 
<trim suffix="where id=#{id}" suffixOverrides=",">
	<if test="appCode != null and '' != appCode">app_code = #{appCode},</if>
    <if test="appName != null and '' != appName">app_name = #{appName},</if>
</trim>

相应的where内容也可以写成如下形式

select * from app_test 
<trim prefix="where " prefixOverrides="AND |OR ">
	<if test="null != authType and ''.toString() != authType">AND auth_type = #{authType}</if>  
</trim>

foreach

foreach标签用来遍历集合数据

foreach标签的使用方式如下：

insert into app_test(app_name,app_code,auth_type,create_date,creator) values
<foreach collection="list" separator="," item="entity" index="index" open="" close="">  
    (#{entity.appName},#{entity.appCode},#{entity.authType},#{entity.createDate},#{entity.creator})  
</foreach>

foreach标签的属性说明

序号	属性名	属性说明
1	collection	集合数据的参数名称
2	index	集合数据的索引方式，一般默认为index
3	item	集合内部的元素命名，类似`for(T t,List<T>)`中的t
4	open	左侧需要添加的字符
5	close	右侧需要添加的字符

script

Mybatis支持通过注解的形式编写sql语句，主要通过@Select,@Insert,@Update,@Delete几个注解实现，示例如下

@Select("select * from app_test where auth_type=#{type}")  
List<AppTestEntity> queryList(@Param("type") String type);

这样就省略了创建xml映射文件的工作，但是这样有一个缺点，就是不方便编写动态sql，这时可以使用script标签

public interface ApplicationRepository {
	@Update({  
	    "<script>",  
	        "update app_test",  
			    "<set>",  
			        "<if test=\"appStatus != null and appStatus != '' \">app_status=#{appStatus},</if>",  
			    "</set>",  
	        "where id=#{id}",  
	    "</script>"  
	})  
	int updateByScript(AppTestEntity app);
}

这里需要注意，在字符串中使用 " 双引号字符，需要使用 \ 符号转义，如上例所示

动态sql API

除了通过映射文件使用动态sql的方式之外，Mybatis还提供了基于JavaAPI实现动态sql的方案。(这种方法可以弥补script标签的不足)

通过类名和方法名定位SQL

这里我们针对AppTest对象的条件查询进行改造

public interface ApplicationRepository {
	//此注解用于标注动态sql生成的类，方法名称
	@SelectProvider(type = ApplicationSqlProvider.class,method = "queryAppFunc")  
	List<AppTestEntity> queryAppProvider(AppSearchVo param);
}

//动态sql生成类
//这个类名称和方法名称需要和@SelectProvider注解标注的类型和方法名称相对应
package top.sunyog.mybatis.provider;  
  
import org.apache.ibatis.jdbc.SQL;  
import top.sunyog.common.entity.AppSearchVo;  
  
public class ApplicationSqlProvider {  
    public static String queryAppFunc(AppSearchVo param){  
        SQL sql = new SQL() {
	        //静态代码块
	        {  
	            SELECT("*");  
	            FROM("app_test");  
	            if (param.getAppName()!=null && !"".equals(param.getAppName())) {  
	                WHERE("app_name like concat('%',#{appName},'%')");  
	            }  
	            if (param.getAuthType()!=null && !"".equals(param.getAuthType())){  
	                WHERE("auth_type = #{authType}");  
	            }  
	            if (param.getStartDate() != null){  
	                WHERE("create_date >= #{startDate}");  
	            }  
	            if (param.getEndDate() != null){  
	                WHERE("create_date <= #{endDate}");  
	            }  
	  
	        }
        };  
        return sql.toString();  
    }  
}

功能测试类

public class ApplicationService extends MybatisService<ApplicationRepository>{
    @Override  
    public void doService() {  
        ApplicationRepository mapper = super.getMapper(ApplicationRepository.class);  
        this.testSelectProvider(mapper);  
    }
    
	private void testSelectProvider(ApplicationRepository mapper){  
	    AppSearchVo vo = new AppSearchVo();  
	    vo.setAppName("1");  
	    vo.setAuthType("2");  
	    vo.setStartDate(LocalDateTime.of(2023,11,1,12,10));  
	    vo.setEndDate(LocalDateTime.of(2023,11,3,12,10));  
	    List<AppTestEntity> list = mapper.queryAppProvider(vo);  
	    list.forEach(System.out::println);  
	}
}

通过方法名定位SQL

除了通过 @SelectProvider注解直接指定类和方法之外，还可以只指定类，但这种方式需要保证Mapper接口的方法名称和 Provider类的方法名称一一对应。

//mapper接口
@SelectProvider(type = ApplicationSqlProvider.class)  
List<AppTestEntity> queryAppProvider(AppSearchVo param);

//provider类
public class ApplicationSqlProvider implements ProviderMethodResolver {  
    public static String queryAppProvider(AppSearchVo param){
	    ...
    }
}

使用这种方式需要保证两点：

方法名称相同
provider类实现 ProviderMethodResolver 接口

自动定位SQL

通过配置默认的 SqlProvider类，可以将所有的 @*Provider 定位到同一个类中，只要保证 mapper接口的方法名称和 Provider类的方法名称相同即可

配置说明

<configuration>  
    <properties resource="..."/>
	<settings>  
        <!--设定默认的 sql provider-->
        <setting name="defaultSqlProviderType" value="top.sunyog.mybatis.provider.ApplicationSqlProvider"/>  
    </settings>
	...
</configuration>

mapper接口和provider类

//mapper接口
@SelectProvider  
List<AppTestEntity> queryAppProvider(AppSearchVo param);

//provider类
public class ApplicationSqlProvider implements ProviderMethodResolver {  
    public static String queryAppProvider(AppSearchVo param){
    ...
    }
}

总结

在MyBatis中，动态SQL是非常有用的特性，它们允许开发者根据不同的条件构建动态的SQL查询，以及更加灵活地生成SQL查询。动态SQL标签提供了灵活的逻辑控制，使我们可以根据不同的条件动态地添加或删除SQL片段。动态SQL API允许我们以编码的方式使用动态SQL，这就相当于在动态SQL之上引入了更加灵活的逻辑处理功能。

你是否曾经使用过MyBatis的动态SQL标签或相关API？如果有，请在评论区分享你的使用经验和心得。如果你还没有使用过这些特性，现在可以用起来了。