在学习mybatis过程中想实现 模糊查询 ，可惜失败了。后来上百度上查了一下，算是解决了。记录一下mybatis实现模糊查询的几种方式。
数据库表名为test_student,初始化了几条记录，如图：
起初我在mybatis的mapper文件中是这样写的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<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> |
写完后自我感觉良好，很开心的就去跑程序了，结果当然是报错了：
经百度得知，这么写经mybatis转换后(‘%#{name}%')会变为(‘%?%'),而(‘%?%')会被看作是一个字符串，所以java代码在执行找不到用于匹配参数的 ‘?' ,然后就报错了。
解决方法
1.用${…}代替#{…}
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<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}]%]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<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中的字符串拼接函数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<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.使用标签
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<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> |
查询结果：
5.在java代码中拼接字符串
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
public static void main(string[] args) { try { int count = 500 ;
long begin = system.currenttimemillis(); teststring(count); long end = system.currenttimemillis(); long time = end - begin; system.out.println( "string 方法拼接" +count+ "次消耗时间：" + time + "毫秒" );
begin = system.currenttimemillis(); teststringbuilder(count); end = system.currenttimemillis(); time = end - begin; system.out.println( "stringbuilder 方法拼接" +count+ "次消耗时间：" + time + "毫秒" );
} catch (exception e) { e.printstacktrace(); }
}
private static string teststring( int count) { string result = "" ;
for ( int i = 0 ; i < count; i++) { result += "hello " ; }
return result; }
private static string teststringbuilder( int count) { stringbuilder sb = new stringbuilder();
for ( int i = 0 ; i < count; i++) { sb.append( "hello" ); }
return sb.tostring(); } |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。
原文链接：https://blog.csdn.net/lonely_dog/article/details/74171314
查看更多关于MyBatis实现模糊查询的几种方式的详细内容...