什么是动态SQL
动态SQL是MyBatis框架中强大特征之一。在一些组合查询页面,需要根据用户输入的查询条件生成不同的查询SQL,这在JDBC或其他相 似框架中需要在代码中拼写SQL,经常容易犯错,但是在MyBatis中 可以解决这种问题。
<select id="getStuCardByStuNo" parameterType="String" resultMap="stuCardMap">
select * from stu_info where stuNo = #{value}
</select>
这是非动态SQL语句,如果我们输入的stuNo是空的那么这个查询便无法正常执行,在有多个信息时更不能出现失误,但是动态SQL可以解决这样的问题
if+where语句
<!-- if条件查询 -->
<select id="getStuCardByAttrs" parameterType="com.zzh.mybatis.mappers.entity.StuCard" resultMap="stuCardMap">
select stuNo, stuName, money from stu_info
<where>
<if test="stuNo != null and stuNo != ''">
and stuNo = #{stuNo}
</if>
<if test="stuName != null and stuName != ''">
and stuName = #{stuName}
</if>
<if test="money != null and money != ''">
and money = #{money}
</if>
</where>
</select>
这是测试用的代码:
StuCard stuCard = new StuCard();
stuCard.setStuNo("2018160203");
stuCard.setStuName("老王");
System.out.println("=================>" + cardMapper.getStuCardByAttrs(stuCard));
将日志引入我们可以看到SQL语句会被自动拼接,如果没有返回值便不会写入SQL中去,且where标签会自动删除第一个and。
10:29:44.081 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByAttrs - ==> Preparing: select stuNo, stuName, money from stu_info WHERE stuNo = ? and stuName = ?
10:29:44.111 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByAttrs - ==> Parameters: 2018160203(String), 老王(String)
10:29:44.140 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByAttrs - <== Total: 1
=================>[StuCard [stuNo=2018160203, stuName=老王, money=19999]]
※这个很常用,需要熟练掌握
foeach语句
当我们需要查询数组或者List目标时,我们需要用“foeach”标签来完成查询
<select id="getStuCardByStuNos" parameterType="List" resultMap="stuCardMap">
select stuNo, stuName, money from stu_info
<where>
<if test="list != null">
<foreach collection="list" item="stuNo" open="and stuNo in(" separator=", " close=")">
#{stuNo}
</foreach>
</if>
</where>
</select>
这样写系统会自动把SQL语句拼接为 select stuNo, stuName, money from stu_info WHERE stuNo in( 1,2,3,···)的形式,我们可以创建一个数组完成查询
例如:
List<String> stuNos = new ArrayList<String>();
stuNos.add("2018160201");
stuNos.add("2018160202");
System.out.println("=================>" + cardMapper.getStuCardByStuNos(stuNos));
查询结果如下:
11:52:50.988 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByStuNos - ==> Preparing: select stuNo, stuName, money from stu_info WHERE stuNo in( ? , ? )
11:52:51.016 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByStuNos - ==> Parameters: 2018160201(String), 2018160202(String)
11:52:51.045 [main] DEBUG com.zzh.mybatis.mappers.dao.StuCardMapper.getStuCardByStuNos - <== Total: 2
=================>[StuCard [stuNo=2018160201, stuName=老李, money=10000], StuCard [stuNo=2018160202, stuName=老赵, money=9999]]
if+set语句
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
<update id="updateStuCard" parameterType="StuCard">
update stu_info
<set>
<if test="age!= null">
age= #{age},
</if>
<if test="stuName != null">
stuName = #{stuName},
</if>
<if test="money != null">
money = #{money},
</if>
</set> where stuNo=#{stuNo}
</update>
"set"语句可以省略最后一个逗号,将if中有返回值的语句拼接在一起
如果第一个条件 age为空,那么 sql 语句为:update stu_info set stuName=?, money=? where stuNo = ?
如果第一个条件不为空,那么 sql 语句为:update stu_info set age=?, stuName=?, money=? where stuNo = ?