本文主要是介绍MyBatis进行模糊查询时SQL语句拼接引起的异常问题,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
项目场景:
CRM项目,本文遇到的问题是在实现根据页面表单中输入条件,在数据库中分页模糊查询数据,并在页面分页显示的功能时,出现的“诡异”bug。
开发环境如下:
操作系统:Windows11
Java:jdk-21.0.2
IDE:eclipse 2024-3R
Tomcat:apache-tomcat-10.1.11
Maven:apache-maven-3.9.6
数据库:MariaDB11.0
项目地址:https://gitcode.com/weixin_44803446/crm-project.git
问题描述
在项目中,通过一下两个查询,分别查询对象列表跟总条数,通过日期查询及空条件查询结果均无异常,但是在通过名称及所有者名称进行模糊查询时,返回的查询结果为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_timefrom tbl_activity ajoin tbl_user u1 on a.owner = u1.idjoin 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 >= #{startDate}</if><if test="endDate != null and endDate != ''"> and a.end_date <= #{endDate} </if></where>order by a.create_time desclimit #{beginNo},#{pageSize}</select><!-- 查询对应条件下的市场活动总条数 --><select id="selectActivityCounts" parameterType="map" resultType="int">select count(*)from tbl_activity ajoin tbl_user u1 on a.owner = u1.idjoin 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 >= #{startDate}</if><if test="endDate != null and endDate != ''"> and a.end_date <= #{endDate} </if></where></select>
原因分析:
- 首先,确定前端的字段是否完整的传递到Controller,通过Console.log(参数)的方式将参数打印在浏览器控制台中,经过验证参数传递无异常;
- 其次,在Controller及Service中获取参数并打印,确保参数传递过程没有缺失等;
- 查看查询日志:
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}’%。
解决方案:
- 通过在#{name}前后添加空格,让MyBatis正确的识别并拼接参数与字符串;
- 更严谨的方式是使用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_timefrom tbl_activity ajoin tbl_user u1 on a.owner = u1.idjoin 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 >= #{startDate}</if><if test="endDate != null and endDate != ''"> and a.end_date <= #{endDate} </if></where>order by a.create_time desclimit #{beginNo},#{pageSize}</select><!-- 查询对应条件下的市场活动总条数 --><select id="selectActivityCounts" parameterType="map" resultType="int">select count(*)from tbl_activity ajoin tbl_user u1 on a.owner = u1.idjoin 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 >= #{startDate}</if><if test="endDate != null and endDate != ''"> and a.end_date <= #{endDate} </if></where></select>
这篇关于MyBatis进行模糊查询时SQL语句拼接引起的异常问题的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!