我们现在要实现这么一个功能,查询所有地址信息包含江西的用户列表
传统SQL语句
select id,userName, userPwd, age, sex, hobby, address from user where address like '%江西%';
为了程序可扩展性,我们这里采用Mybatis支持的动态SQL,并且对爱好字段也进行模糊查询,如爱好包含footabll或watch TV……那么在Mybatis中应该如何实现呢,我们来进行测试
测试:动态SQL语句:
<select id="queryAllUser" resultMap="BaseResultMap">
select <include refid="BaseSQL"/>
from t_user where state = 1
<if test="userName != '' and userName != null">
and userName = #{userName}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
<if test="hobby != '' and hobby != null">
and hobby like "%"{hobby}"%"
</if>
<if test="address != '' and address != null">
and address like '%'+#{address}+'%'
</if>
</select>
开始进行测试,我们在输入框输入“江西”,然后点击搜索框
结果如下:报错了
我们可以发现变量名自动加上了单引号,这是因为**#{…}解析成sql语句时候,会在变量外侧自动加单引号’** ',所以这里 % 需要使用双引号" ",不能使用单引号 ’ ',不然会查不到任何结果。
到这里可能有小伙伴会说,如何把两个“%”和变量拼接起来不就能实现了?那我们把代码改一下再试试看
<select id="queryAllUser" resultMap="BaseResultMap">
select <include refid="BaseSQL"/>
from t_user where state = 1
<if test="userName != '' and userName != null">
and userName = #{userName}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
<if test="hobby != '' and hobby != null">
and hobby like "%"+{hobby}+"%"
</if>
<if test="address != '' and address != null">
and address like "%"+#{address}+"%"
</if>
</select>
结果:很可惜,依旧没能成功
因为**#{}解析sql的时候不需要我们动态拼接参数**,所以我们直接将百分符号加上“”放在#{}外面就行了
代码修改如下:
<select id="queryAllUser" resultMap="BaseResultMap">
select <include refid="BaseSQL"/>
from t_user where state = 1
<if test="userName != '' and userName != null">
and userName = #{userName}
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
/*占位符实现模糊查询方法一*/
<if test="hobby != '' and hobby != null">
and hobby like "%"#{hobby}"%"
</if>
/*占位符实现模糊查询方法二*/
<if test="address != '' and address != null">
and address like concat('%',#{address},'%')
</if>
</select>
最终结果如下(终于成功了):
我们来看一下输出日志:
补充:可以使用contact函数将参数连接作为SQL执行需要的参数同样也可以实现
like concat('%',#{address},'%')