MySQL模糊查询的三种方式
mysql模糊查询
·
一、Mybatis常用模糊查询方法
- 直接使用 % 字符串拼接,如 ‘%’#{name}‘%’ 或 “%”#{name}“%”,单引号或双引号都可以。
- 使用’%${name}%',会有SQL注入危险
- 使用函数concat拼接concat(“%”,#{name},“%”)
1.使用concat(“%”,#{name},“%”)
UserMapper.xml文件:
<select id="selectUsersByCondition" resultType="qin.com.entity.Users" parameterType="Users">
select * from users
<where>
<if test="name != null and name != ''">
<!--
and name like concat(concat("%",#{name},"%"))
==> Preparing: select * from users WHERE name like concat(concat("%",?,"%")) order by id desc
-->
and name like concat("%",#{name},"%")
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="birthday != null and birthday != ''">
and birthday = #{birthday}
</if>
<if test="createTime != null and createTime != ''">
and createTime>=#{createTime}
</if>
<if test="updateTime != null and updateTime != ''">
and updateTime <= #{updateTime}
</if>
</where>
order by id desc
</select>
Test测试代码
@Test
public void testSelectUsersByCondition() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
UsersService usersService =(UsersService) applicationContext.getBean("usersService");
Users users = new Users();
users.setName("小");
List<Users> list = usersService.selectUsersByCondition(users);
list.forEach(users1 -> System.out.println("根据条件查询用户信息:"+users1));
}
输出结果:
查询语句
==> Preparing: select * from users WHERE name like concat(“%”,?,“%”) order by id desc
==> Parameters: 小(String)
2.使用’%${name}%’
UserMapper.xml文件:
<if test="name != null and name != ''">
<!--and name like '%${name}%'-->
and name like '%${name}%'
</if>
输出结果:
==> Preparing: select * from users WHERE name like ‘%小%’ order by id desc
==> Parameters:
3.使用"%“#{name}”%"
<if test="name != null and name != ''">
and name like "%"#{name}"%"
</if>
输出语句:
==> Preparing: select * from users WHERE name like “%”?“%” order by id desc
==> Parameters: 小(String)
总结:
推荐使用第一和第三种方式
更多推荐
已为社区贡献1条内容
所有评论(0)