动态SQL,简单来说就是根据传过来的值有选择的添加条件
StudentDao.java
public interface StudentDao {
List<Student> selectStudentIf(Student student);
List<Student> selectStudentWhere(Student student);
List<Student> selectStudentForeachList(List<Integer> idlist);
List<Student> selectStudentForeachList2(List<Student> students);
List<Student> selectAllStudents();
}
1、if
在mapper文件中if可以作为select的字标签参与条件判断,test 属性是判断条件,传入的值会在属性test中进行判断,满足判断语句,则将条件加入加入到where 后面作为查询条件
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="接口全限定名">
<select id="selectStudentIf" resultType="返回值类型(引用对象全限定名)">
select id,name, email, age from student
where 1=1
<if test="name !=null and name !=''">
and name = #{name}
</if>
<if test="age != null ">
and age = #{age}
</if>
</select>
</mapper>
2、where
where标签,当存在查询条件时,where 会自动追加到查询语句后面。在上面介绍 if 时,查询语句出现使用where 1=1 的情况,这会造成全表查询,降低效率。使用where标签,则不需要使用1=1这个条件。
当出现第一个条件不满足,第二个条件满足时,添加where条件会自动省略and:select * from student where age < #{age}
注意:在xml文件中,小于号 “<” 会被认为是标签的左部分,在这里使用会报错,可以使用 “ < ” 代替
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="接口全限定名">
<select id="selectStudentWhere" resultType="返回值类型(引用对象全限定名)">
select id,name, email, age from student
<where>
<if test="name !=null and name !=''">
name = #{name}
</if>
<if test="age != null ">
and age < #{age}
</if>
</where>
</select>
</mapper>
3、forEach
collection="list" 传入的参数是list集合
open="(" 左括号开始
close=")" 右括号结束
item="stu" list集合中的值使用stu代替
separator="," 分隔符,使用“,”分隔开每个值
表现形式:where in (第一个list值,第二个list值,...)
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="接口全限定名">
<select id="selectStudentForeachList" resultType="返回值类型(引用对象全限定名)">
select id,name, email, age from student
<if test="list !=null and list.size > 0 ">
where id in
<foreach collection="list" open="(" close=")" item="stu" separator=",">
#{stu.id}
</foreach>
</if>
</select>
</mapper>
4、sql
可以将常用的查询语句写入到sql标签中,规定id值,使用时可以通过<include refid="sqlid" />
refid:可以理解为引用id,这里是引用sql的id
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="接口全限定名">
<!--定义一个sql语句的一部分, 片段,可以重复使用-->
<sql id="studentField">
select id,name, email, age from student
</sql>
<select id="selectStudentForeachList" resultType="返回值类型(引用对象全限定名)">
select
<include refid="studentField" />
from student
<if test="list != null and list.size>0">
where id in
<foreach collection="list" open="(" close=")" item="sid" separator=",">
#{sid}
</foreach>
</if>
</select>
<!-- <select id="selectStudentForeachList" resultType="返回值类型(引用对象全限定名)">
select
<include refid="studentField" />
from student
<if test="list !=null and list.size > 0 ">
where id in (
<foreach collection="list" open="" close="" item="stu" separator=",">
#{stu.id}
</foreach>
)
</if>
</select>
-->
</mapper>