MyBatis实现模糊查询的几种方式
在学习MyBatis过程中想实现模糊查询,下面列举几种方式:
1.用${…}代替#{…}
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
parameterType="com.example.entity.StudentEntity">
SELECT * FROM test_student
<where>
<if test="age != null and age != '' and compare != null and compare != ''">
age
${compare}
#{age}
</if>
<if test="name != null and name != ''">
AND name LIKE '%${name}%'
</if>
<if test="address != null and address != ''">
AND address LIKE '%${address}%'
</if>
</where>
ORDER BY id
</select>
查询结果如下图:
注:使用${…}不能有效防止SQL注入,所以这种方式虽然简单但是不推荐使用!!!
2.把’%#{name}%’改为”%”#{name}”%”
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
parameterType="com.example.entity.StudentEntity">
SELECT * FROM test_student
<where>
<if test="age != null and age != '' and compare != null and compare != ''">
age
${compare}
#{age}
</if>
<if test="name != null and name != ''">
AND name LIKE "%"#{name}"%"
</if>
<if test="address != null and address != ''">
AND address LIKE "%"#{address}"%"
</if>
</where>
ORDER BY id
</select>
查询结果:
3.使用sql中的字符串拼接函数
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
parameterType="com.example.entity.StudentEntity">
SELECT * FROM test_student
<where>
<if test="age != null and age != '' and compare != null and compare != ''">
age
${compare}
#{age}
</if>
<if test="name != null and name != ''">
AND name LIKE CONCAT(CONCAT('%',#{name}),'%')
</if>
<if test="address != null and address != ''">
AND address LIKE CONCAT(CONCAT('%',#{address}),'%')
</if>
</where>
ORDER BY id
</select>
查询结果:
4.使用标签
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
parameterType="com.example.entity.StudentEntity">
<bind name="pattern1" value="'%' + _parameter.name + '%'" />
<bind name="pattern2" value="'%' + _parameter.address + '%'" />
SELECT * FROM test_student
<where>
<if test="age != null and age != '' and compare != null and compare != ''">
age
${compare}
#{age}
</if>
<if test="name != null and name != ''">
AND name LIKE #{pattern1}
</if>
<if test="address != null and address != ''">
AND address LIKE #{pattern2}
</if>
</where>
ORDER BY id
</select>
查询结果: