springboot's mybatis 模糊查询

╰+哭是因爲堅強的太久メ 2023-10-09 12:01 7阅读 0赞

在学习MyBatis过程中想实现模糊查询，可惜失败了。后来上百度上查了一下，算是解决了。记录一下MyBatis实现模糊查询的几种方式。   
　　数据库表名为test\_student,初始化了几条记录，如图：   
　　![数据库表内容][SouthEast]  
　　   
　　起初我在MyBatis的mapper文件中是这样写的：

1.  `<select id="searchStudents" resultType="com.example.entity.StudentEntity"`
2.  `parameterType="com.example.entity.StudentEntity">`
3.  `SELECT * FROM test_student`
4.  `<where>`
5.  `<if test="age != null and age != '' and compare != null and compare != ''">`
6.  `age`
7.  `${compare}`
8.  `#{age}`
9.  `</if>`
10. `<if test="name != null and name != ''">`
11. `AND name LIKE '%#{name}%'`
12. `</if>`
13. `<if test="address != null and address != ''">`
14. `AND address LIKE '%#{address}%'`
15. `</if>`
16. `</where>`
17. `ORDER BY id`
18. `</select>`

写完后自我感觉良好，很开心的就去跑程序了，结果当然是报错了：   
![报错][SouthEast 1]

经百度得知，这么写经MyBatis转换后(‘%\#\{name\}%’)会变为(‘%?%’),而(‘%?%’)会被看作是一个字符串，所以Java代码在执行找不到用于匹配参数的 ‘?’ ,然后就报错了。

### **解决方法** ###

1.用$\{…\}代替\#\{…\}

1.  `<select id="searchStudents" resultType="com.example.entity.StudentEntity"`
2.  `parameterType="com.example.entity.StudentEntity">`
3.  `SELECT * FROM test_student`
4.  `<where>`
5.  `<if test="age != null and age != '' and compare != null and compare != ''">`
6.  `age`
7.  `${compare}`
8.  `#{age}`
9.  `</if>`
10. `<if test="name != null and name != ''">`
11. `AND name LIKE '%${name}%'`
12. `</if>`
13. `<if test="address != null and address != ''">`
14. `AND address LIKE '%${address}%'`
15. `</if>`
16. `</where>`
17. `ORDER BY id`
18. `</select>`

查询结果如下图：   
![查询结果][SouthEast 2]

*注：使用$\{…\}**不能有效防止SQL注入**，所以这种方式虽然简单但是不推荐使用！！！*

2.把’%\#\{name\}%’改为”%”\#\{name\}”%”

1.  `<select id="searchStudents" resultType="com.example.entity.StudentEntity"`
2.  `parameterType="com.example.entity.StudentEntity">`
3.  `SELECT * FROM test_student`
4.  `<where>`
5.  `<if test="age != null and age != '' and compare != null and compare != ''">`
6.  `age`
7.  `${compare}`
8.  `#{age}`
9.  `</if>`
10. `<if test="name != null and name != ''">`
11. `AND name LIKE "%"#{name}"%"`
12. `</if>`
13. `<if test="address != null and address != ''">`
14. `AND address LIKE "%"#{address}"%"`
15. `</if>`
16. `</where>`
17. `ORDER BY id`
18. `</select>`

查询结果：   
![查询结果][SouthEast 3]

3.使用sql中的字符串拼接函数

1.  `<select id="searchStudents" resultType="com.example.entity.StudentEntity"`
2.  `parameterType="com.example.entity.StudentEntity">`
3.  `SELECT * FROM test_student`
4.  `<where>`
5.  `<if test="age != null and age != '' and compare != null and compare != ''">`
6.  `age`
7.  `${compare}`
8.  `#{age}`
9.  `</if>`
10. `<if test="name != null and name != ''">`
11. `AND name LIKE CONCAT(CONCAT('%',#{name},'%'))`
12. `</if>`
13. `<if test="address != null and address != ''">`
14. `AND address LIKE CONCAT(CONCAT('%',#{address},'%'))`
15. `</if>`
16. `</where>`
17. `ORDER BY id`
18. `</select>`

查询结果：   
![查询结果][SouthEast 4]

4.使用标签

1.  `<select id="searchStudents" resultType="com.example.entity.StudentEntity"`
2.  `parameterType="com.example.entity.StudentEntity">`
3.  `<bind name="pattern1" value="'%' + _parameter.name + '%'" />`
4.  `<bind name="pattern2" value="'%' + _parameter.address + '%'" />`
5.  `SELECT * FROM test_student`
6.  `<where>`
7.  `<if test="age != null and age != '' and compare != null and compare != ''">`
8.  `age`
9.  `${compare}`
10. `#{age}`
11. `</if>`
12. `<if test="name != null and name != ''">`
13. `AND name LIKE #{pattern1}`
14. `</if>`
15. `<if test="address != null and address != ''">`
16. `AND address LIKE #{pattern2}`
17. `</if>`
18. `</where>`
19. `ORDER BY id`
20. `</select>`

查询结果：   
![查询结果][SouthEast 5]

5.在Java代码中拼接字符串   
　　这个方法没试过，就不贴代码和结果了。

转载于:https://www.cnblogs.com/MaxElephant/p/8418276.html

[SouthEast]: https://img-blog.csdn.net/20170703093720051?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 1]: https://img-blog.csdn.net/20170703095635604?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 2]: https://img-blog.csdn.net/20170703102206982?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 3]: https://img-blog.csdn.net/20170703103247970?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 4]: https://img-blog.csdn.net/20170703104342646?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 5]: https://img-blog.csdn.net/20170703112337185?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbG9uZWx5X2RvZw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast