动态SQL是MyBatis的强大特性之一,采用了功能强大的基于OGNL的表达式来完成动态SQL。
元素 | 说明 |
<if> | 判断语句,用于单条件分支判断 |
<choose>(<when>、<otherwise>) | 相当于Java中的switch..case..default语句,用于多条件分支判断 |
<where>、<set> | 辅助元素,用于处理一些SQL拼装、特殊字符问题 |
<foreach> | 循环语句,常用于in语句等列举条件中 |
<if>元素:
部分代码如下:
实体类:
public class Student implements Serializable {
private Integer id;
private String name;
private Integer age;
private String address;
public Student() {
}
public Student(Integer id, String name, Integer age, String address) {
this.id = id;
this.name = name;
this.age = age;
this.address = address;
}
//省略getter 和 setter 方法
持久层接口:
List<Student> findStudentByCondition(Student student);
映射文件:
<select id="findStudentByCondition" parameterType="com.df.domain.Student" resultType="com.df.domain.Student">
select * from student where 1 = 1
<if test="name != null and name != '' ">
and name = #{name}
</if>
<if test="age != null and age != '' ">
and age = #{age}
</if>
<if test="address != null and address != '' ">
and address = #{address}
</if>
</select>
测试类:
private InputStream in;
private SqlSession session;
private StudentDao studentDao;
@Before
public void before() throws IOException {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
session = factory.openSession();
studentDao = session.getMapper(StudentDao.class);
}
@After
public void after() throws IOException {
session.commit();
session.close();
in.close();
}
@Test
public void Test4() {
Student student = new Student();
student.setAge(20);
//student.setName("张三");
List<Student> student1 = studentDao.findStudentByCondition(student);
for(Student student2 : student1){
System.out.println(student2);
}
}
<choose>元素:
<select id="findStudentByAgeOrAddress" parameterType="com.df.domain.Student" resultType="com.df.domain.Student">
select * from student where 1 = 1
<choose>
<when test="address != null and address != '' ">
and address = #{address}
</when>
<when test="age != null and age != '' ">
and age = #{age}
</when>
<otherwise>
and name like '%${name}%'
</otherwise>
</choose>
</select>
测试类:
@Test
public void Test5(){
Student student = new Student();
student.setAddress(null);
student.setAddress("天津");
student.setAge(20);
student.setAge(null);
student.setName("王");
List<Student> studentByAgeOrAddress =
studentDao.findStudentByAgeOrAddress(student);
for(Student student1:studentByAgeOrAddress){
System.out.println(student1);
}
}
大白话:
当地址不为空的时候,根据地址进行查询;当地址为空,而年龄不为空的时候,则只根据年龄进行查询;当地址和年龄都为空的时候,则最后根据名称为【王】的进行模糊查询。
<where>元素:
映射文件中编写的SQL语句后面都加入了 【where 1 = 1】的条件。
用<where>元素可以替换这个条件。
<where>元素会自动判断组合条件下拼装的SQL语句,只有<where>元素内的条件成立时,才会在拼接SQL中加入where关键字,否则不会添加。
<select id="findStudentByCondition1" resultType="com.df.domain.Student" parameterType="com.df.domain.Student">
select * from student
<where>
<if test="name != null and name != '' ">
and name = #{name}
</if>
<if test="age != null and age != '' ">
and age = #{age}
</if>
<if test="address != null and address != '' ">
and address = #{address}
</if>
</where>
</select>
与之前SQL语句后面拼写 【where 1 = 1 】作用相同。
<set>元素:
传统写法:
<update id="updateStudent" parameterType="com.df.domain.Student">
update student set name = #{name},age = #{age},address = #{address} where id = #{id};
</update>
这样就需要发送所有字段给持久层对象,执行效率不是太高,然而在实际工作中,只需要修改其一个或几个字段,这里就需要用到 <set>元素。
<update id="updateStudent1" parameterType="com.df.domain.Student">
update student
<set>
<if test="name != null and name != '' ">
name = #{name}
</if>
<if test="age != null and age != '' ">
age = #{age}
</if>
<if test="address != null and address != '' ">
address = #{address}
</if>
</set>
where id = #{id}
</update>
这样就不需要传统所有的字段给持久层,只需要传递需要修改的字段即可。
注意:
在使用 <set>和<if>元素组合进行update语句动态SQL组装时,如果<set>元素内包含的内容都为空,则会出现SQL语法错误。所以在使用 <set>元素进行字段更新时,要确认传入的更新字段不能为空。
<foreach>元素:
示例:查询表中id值为 1,2,3的全部数据
传统写法:
<select id="findStudentNum" resultType="com.df.domain.Student" parameterType="list">
select * from student where 1 = 1 and (id=1 or id=2 or id=3)
</select>
<foreach>写法:
<select id="findStudentNum" parameterType="list" resultType="com.df.domain.Student">
select * from student where id in
<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
- item:循环中当前使用的元素
- index:当前元素在集合的位置下标
- collection:参数类型
- open和close:以什么符号将这些集合元素包装起来
- separator:元素之间的分隔符
@Test
public void Test7() {
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
List<Student> studentNum = studentDao.findStudentNum1(ids);
for (Student student : studentNum) {
System.out.println(student);
}
}