目录
本次练习继上一篇我的文章,不懂的可以先看这篇:
JavaWeb自我学习——利用MyBatis完成基础查询https://blog.csdn.net/weixin_58035422/article/details/123802866
动态SQL
SQL语句会随着用户的输入或外部条件的变化而变化。
if标签(多条件):
用于判断参数是否有值,使用test属性进行条件判断。
测试①
StudentMapper.xml
<select id="selectByCondition" resultType="Study.test1.Student">
select * from student where
<if test="age != null">
age = #{age}
</if>
<if test="major != null">
and major = #{major}
</if>
<if test="address != null">
and address = #{address}
</if>
</select>
MyBatisTest.java
String major2 = "JK";
int age2 = 20;
String address2 = "SiChuan";
Map map2 = new HashMap();
map2.put("age",age2);
map2.put("major",major2);
map2.put("address",address2);
List<Student> students5= studentMapper.selectByCondition(map2);
System.out.println("results6:");
System.out.println(students5);
运行结果:(重点看)
但是当注释掉age = #{age}对应的设置参数语句map2.put("age",age2);时就会报错,
原因是没有设置age这个参数
SQL语句变成了
select * from student where
and major = #{major}
and address = #{address}
where后面直接接了and而错误。问题就来了。
存在的问题:
第一个条件不需要逻辑运算符。
解决方案:(都是基于第一个参数age不设置测试)
1)使用恒等式让所有条件格式都一样。
StudentMapper.xml
<select id="selectByCondition" resultType="Study.test1.Student">
select * from student where 1 = 1
<if test="age != null">
and age = #{age}
</if>
<if test="major != null">
and major = #{major}
</if>
<if test="address != null">
and address = #{address}
</if>
</select>
运行结果:成功运行(因为此时第一个参数age为null,SQL语句也是正确的)
2)<where>标签替换where关键字。
StudentMapper.xml
<select id="selectByCondition" resultType="Study.test1.Student">
select * from student
<where>
<if test="age != null">
and age = #{age}
</if>
<if test="major != null and major != ''">
and major = #{major}
</if>
<if test="address != null and address != ''">
and address = #{address}
</if>
</where>
</select>
运行结果:
choose标签(单条件,多个参数多选一,只传一个参数):
选择,类似于Java 中的switch语句
when标签类似于case
otherwise类似于default
<select id="selectByCondition" resultType="Study.test1.Student">
select * from student where
<choose>
<when test="age != null">
age = #{age}
</when>
<when test="major != null and major != ''">
major = #{major}
</when>
<when test="address != null and address != ''">
address = #{address}
</when>
//为了解决一个参数都没有传的问题
<otherwise>
1 = 1
</otherwise>
</choose>
</select>
也可用where来替代
<otherwise>
1 = 1
</otherwise>
来解决问题:(MyBatis这时候会自动判断where之后时候有条件,没有则去掉where)
<select id="selectByCondition" resultType="Study.test1.Student">
select * from student
<where>
<choose>
<when test="age != null">
age = #{age}
</when>
<when test="major != null and major != ''">
major = #{major}
</when>
<when test="address != null and address != ''">
address = #{address}
</when>
</choose>
</where>
</select>
今天学习到此为止!每天进步一点点!!!