MyBatis进行模糊查询时SQL语句拼接引起的异常问题

项目场景：

CRM项目，本文遇到的问题是在实现根据页面表单中输入条件，在数据库中分页模糊查询数据，并在页面分页显示的功能时，出现的“诡异”bug。
开发环境如下：
操作系统：Windows11
Java：jdk-21.0.2
IDE：intelij IDEA-2024
Tomcat：apache-tomcat-10.1.11
Maven：apache-maven-3.9.6
数据库：MariaDB11.0
项目地址：https://gitcode.com/weixin_44803446/crm-project/overview

---

问题描述

在项目中，通过一下两个查询，分别查询对象列表跟总条数，通过日期查询及空条件查询结果均无异常，但是在通过名称及所有者名称进行模糊查询时，返回的查询结果为0，即使是全字段匹配也无法正常查询到想要的数据，Mapper文件片段如代码所示：

<!-- 通过条件查询市场活动表 -->
	<select id="selectActivityListByConditionForPage" parameterType="map" resultMap="BaseResultMap">
	   select a.id, a.name, u1.name as owner, a.start_date, a.end_date, a.cost, u2.name as create_by, a.create_time
	   from tbl_activity a
	   join tbl_user u1 on a.owner = u1.id
	   join tbl_user u2 on a.create_by = u2.id
	   <where>
	       <if test="name != null and name !=''">
	           and a.name like '%'#{name}'%'	       
	       </if>
	       <if test="owner != null and owner != ''"> 
	           and u1.name like '%'#{owner}'%' 
	       </if>
	       <if test="startDate != null and startDate != ''"> 
	           and a.start_date &gt;= #{startDate}
	       </if>
	       <if test="endDate != null and endDate != ''"> 
	           and a.end_date &lt;= #{endDate} 
	       </if>
	   </where>
	   order by a.create_time desc
	   limit #{beginNo},#{pageSize}
	</select>
	
	<!-- 查询对应条件下的市场活动总条数 -->
	<select id="selectActivityCounts" parameterType="map" resultType="int">
	    select count(*)
	    from tbl_activity a
	    join tbl_user u1 on a.owner = u1.id
        join tbl_user u2 on a.create_by = u2.id
       <where>
           <if test="name != null and name !=''">
               and a.name like '%'#{name}'%'           
           </if>
           <if test="owner != null and owner != ''"> 
               and u1.name like '%'#{owner}'%' 
           </if>
           <if test="startDate != null and startDate != ''"> 
               and a.start_date &gt;= #{startDate}
           </if>
           <if test="endDate != null and endDate != ''"> 
               and a.end_date &lt;= #{endDate} 
           </if>
       </where>
	</select>

---

原因分析：

1. 首先，确定前端的字段是否完整的传递到Controller，通过Console.log(参数)的方式将参数打印在浏览器控制台中，经过验证参数传递无异常；
2. 其次，在Controller及Service中获取参数并打印，确保参数传递过程没有缺失等；
3. 查看查询日志：

JDBC Connection [org.mariadb.jdbc.Connection@5516cc8d] will be managed by Spring
==>  Preparing: select a.id, a.name, u1.name as owner, a.start_date, a.end_date,
 a.cost, u2.name as create_by, a.create_time from tbl_activity a join tbl_user u
1 on a.owner = u1.id join tbl_user u2 on a.create_by = u2.id WHERE a.name like '
%'?'%' order by a.create_time desc limit ?,?
==> Parameters: n(String), 0(Integer), 10(Integer)
<==      Total: 0
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSq
lSession@2ef7efff]
Transaction synchronization committing SqlSession [org.apache.ibatis.session.def
aults.DefaultSqlSession@2ef7efff]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.
defaults.DefaultSqlSession@2ef7efff]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaul
ts.DefaultSqlSession@2ef7efff]
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.sessio
n.defaults.DefaultSqlSession@66e3b3d0]
JDBC Connection [org.mariadb.jdbc.Connection@b60a270] will be managed by Spring
==>  Preparing: select count(*) from tbl_activity a join tbl_user u1 on a.owner 
= u1.id join tbl_user u2 on a.create_by = u2.id WHERE a.name like '%'?'%'
==> Parameters: n(String)
<==    Columns: count(*)
<==        Row: 0
<==      Total: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSq
lSession@66e3b3d0]
Transaction synchronization committing SqlSession [org.apache.ibatis.session.def
aults.DefaultSqlSession@66e3b3d0]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.
defaults.DefaultSqlSession@66e3b3d0]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaul
ts.DefaultSqlSession@66e3b3d0]

由数据库查询日志可以看出，在执行模糊查询时，参数准确无误的传递到了Sql语句中，但是经过模糊查询后，查到的数量Total为0，但是实际上数据库中是有一条符合模糊条件的数据的。
猜想：

<if test="name != null and name !=''">
	   and a.name like '%'#{name}'%'	       
</if>

这一句模糊查询语句有问题，尝试在’%‘与#{name} 之间加上空格后重启服务器测试，发现模糊查询功能正常。问题就出在MyBatis在处理字符串拼接时，如果以’%‘#{name}’%’ 这样紧密的格式书写，则会导致其将整个字段识别为一个整体，最终的拼接体可能为%‘#{name}’%。

---

解决方案：

1. 通过在#{name}前后添加空格，让MyBatis正确的识别并拼接参数与字符串；
2. 更严谨的方式是使用CONCAT函数，通过CONCAT()函数将"%" 与#{name}拼接起来，这样避免了因为拼写错误等原因导致最终SQL与我们预想的不一致的情况。

<!-- 通过条件查询市场活动表 -->
	<select id="selectActivityListByConditionForPage" parameterType="map" resultMap="BaseResultMap">
	   select a.id, a.name, u1.name as owner, a.start_date, a.end_date, a.cost, u2.name as create_by, a.create_time
	   from tbl_activity a
	   join tbl_user u1 on a.owner = u1.id
	   join tbl_user u2 on a.create_by = u2.id
	   <where>
	       <if test="name != null and name !=''">
	           and a.name like concat('%',#{name},'%')
	       </if>
	       <if test="owner != null and owner != ''"> 
	           and u1.name like concat('%', #{owner},'%' )
	       </if>
	       <if test="startDate != null and startDate != ''"> 
	           and a.start_date &gt;= #{startDate}
	       </if>
	       <if test="endDate != null and endDate != ''"> 
	           and a.end_date &lt;= #{endDate} 
	       </if>
	   </where>
	   order by a.create_time desc
	   limit #{beginNo},#{pageSize}
	</select>
	
	<!-- 查询对应条件下的市场活动总条数 -->
	<select id="selectActivityCounts" parameterType="map" resultType="int">
	    select count(*)
	    from tbl_activity a
	    join tbl_user u1 on a.owner = u1.id
        join tbl_user u2 on a.create_by = u2.id
       <where>
           <if test="name != null and name !=''">
               and a.name like concat('%',#{name},'%')
           </if>
           <if test="owner != null and owner != ''"> 
               and u1.name like concat('%', #{owner},'%' )
           </if>
           <if test="startDate != null and startDate != ''"> 
               and a.start_date &gt;= #{startDate}
           </if>
           <if test="endDate != null and endDate != ''"> 
               and a.end_date &lt;= #{endDate} 
           </if>
       </where>
	</select>