MyBatis学习总结（三）——多表关联查询与动态SQL
目录
在上一章中我们学习了《MyBatis 学习总结（二）——MyBatis 核心配置文件与输入输出映射》，这一章主要是介绍一对一关联查询、一对多关联查询与动态 SQL 等内容。
一、多表关联查询
表与表之间有三种常见的关联关系，分别是一对一，一对多与多对多关系，MyBatis 直接提供一对一与一对多的关联关系，可能通过间接的方式实现一对多关联。
1.1、一对一关系
1.1.1、执行环境
假定一个员工（emp）拥有一个登录用户（user），员工与用户表之间是一对一关系：
用户表：
员工表：
SQL：
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure foruser
-- ----------------------------
DROP TABLE IF EXISTS<span style="color: rgba(255, 0, 255, 1)">user</span><span style="color: rgba(0, 0, 0, 1)">;
CREATE TABLE<span style="color: rgba(255, 0, 255, 1)">user</span><span style="color: rgba(0, 0, 0, 1)">(idint(11) NOT NULL AUTO_INCREMENT COMMENT '编号',usernamevarchar(64) NOT NULL COMMENT '用户名',passwordvarchar(64) NOT NULL COMMENT '密码',
PRIMARY KEY (id),
UNIQUE KEYusers_username_uindex(username)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO<span style="color: rgba(255, 0, 255, 1)">user</span>VALUES ('1', 'tom', '123456');
INSERT INTO<span style="color: rgba(255, 0, 255, 1)">user</span>VALUES ('2', 'rose', '888888');
INSERT INTO<span style="color: rgba(255, 0, 255, 1)">user</span>VALUES ('3', 'mark', 'qwerty');
INSERT INTO<span style="color: rgba(255, 0, 255, 1)">user</span>VALUES ('4', 'jack', 'qaz123');
INSERT INTO<span style="color: rgba(255, 0, 255, 1)">user</span>VALUES ('5', 'mali', 'uio890');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure foremp
-- ----------------------------
DROP TABLE IF EXISTSemp;
CREATE TABLEemp(idint(11) NOT NULL AUTO_INCREMENT COMMENT '编号',</span><span style="color: rgba(255, 0, 255, 1)">user_id</span>int(11) DEFAULT NULL COMMENT '用户编号',realnamevarchar(32) NOT NULL COMMENT '姓名',
PRIMARY KEY (id),
KEYemp_user_id(<span style="color: rgba(255, 0, 255, 1)">user_id</span><span style="color: rgba(0, 0, 0, 1)">),
CONSTRAINTemp_user_idFOREIGN KEY (<span style="color: rgba(255, 0, 255, 1)">user_id</span>) REFERENCES<span style="color: rgba(255, 0, 255, 1)">user</span><span style="color: rgba(0, 0, 0, 1)">(id)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工表';-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTOempVALUES ('1', '1', '汤姆', 'tom@gmail.com');
INSERT INTOempVALUES ('2', '2', '梅贵', 'rose@163.com');
INSERT INTOempVALUES ('3', '3', '马克', 'mark@sina.com');
INSERT INTOempVALUES ('4', '4', '岳翰', 'jack@gmail.com');
INSERT INTOempVALUES ('5', '5', '马丽', 'mali@sina.com');
关系：
1.1.2、关联查询（1 次查询）
实体：
用户：
package com.zhangguo.mybatis03.entities;/**用户 POJO*/
public class User {
private int id;
private String username;
private String password;public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getUsername() {
return username;
}public void setUsername(String username) {
this.username = username;
}public String getPassword() {
return password;
}public void setPassword(String password) {
this.password = password;
}
}
员工：
package com.zhangguo.mybatis03.entities;/员工 POJO*/
public class Emp {
private int id;
/用户编号*/
private int user_id;
private String realname;
private String email;/*用户对象/
private User user;public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public int getUser_id() {
return user_id;
}public void setUser_id(int user_id) {
this.user_id = user_id;
}public String getRealname() {
return realname;
}public void setRealname(String realname) {
this.realname = realname;
}public String getEmail() {
return email;
}public void setEmail(String email) {
this.email = email;
}public User getUser() {
return user;
}public Emp setUser(User user) {
this.user = user;
return this;
}
}
接口：
package com.zhangguo.mybatis03.dao;import com.zhangguo.mybatis03.entities.Emp;
/**员工数据访口*/
public interface EmpMapper {/**获得员工通过员工编号*/
Emp getEmpById_1(int id);}
映射：
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhangguo.mybatis03.dao.EmpMapper"><!--一对一查询，方法 1，通过内联接-->
<select id="getEmpById_1" resultMap="empMap_1" parameterType="int">
SELECT
emp.id,
emp.user_id,
emp.realname,
emp.email,user.username,user.password
FROM
emp
INNER JOINuserON emp.user_id =user.id where emp.id=#{id}
</select><!--员工关联查询结果映射-->
<resultMap id="empMap_1" type="Emp">
<id property="id" column="id"></id>
<result property="user_id" column="user_id"></result>
<result property="realname" column="realname"></result>
<result property="email" column="email"></result>
<!--映射关系，指定属性与属性的类型-->
<association property="user" javaType="User">
<id property="id" column="user_id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
</association>
</resultMap></mapper>
测试：
package com.zhangguo.mybatis03.dao;import com.zhangguo.mybatis03.entities.Emp;
import org.junit.Assert;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;/**
- EmpDao Tester.
- @author <Authors name>
- @version 1.0
- @since <pre>09/30/2018</pre>
*/
public class EmpDaoTest {
EmpMapper empDao;
@Before
public void before() throws Exception {
empDao=new EmpDao();
}@After
public void after() throws Exception {
}/**
- Method: getEmpById_1(int id)
- 获得员工通过员工编号
*/
@Test
public void testGetEmpById_1() throws Exception {
Emp entity=empDao.getEmpById_1(1);
System.out.println(entity);
Assert.assertNotNull(entity);
}}
结果：
1.1.3、嵌套查询（2 次查询）
实体：同上
接口：
/**获得员工通过员工编号，多次查询*/ Emp getEmpById_2(int id);
映射：
<!--一对一查询，方法 2，通过多次查询（嵌套查询）--> <select id="getEmpById_2" resultMap="empMap_2"> SELECT emp.id, emp.user_id, emp.realname, emp.email FROM emp where id=#{id} </select><!--员工多次查询结果映射-->
<resultMap id="empMap_2" type="Emp">
<id property="id" column="id"></id>
<result property="user_id" column="user_id"></result>
<result property="realname" column="realname"></result>
<result property="email" column="email"></result>
<!--通过外键 user_id 再次发起查询，调用 selectUserById 获得 User 对象-->
<association property="user" column="user_id" select="selectUserById"></association>
</resultMap><!--根据用户编号获得用户对象-->
<select id="selectUserById" resultType="User">
SELECTuser.id,user.username,user.password
FROMuserwhere id=#{id}
</select>
测试：
/** * Method: getEmpById_2(int id) * 获得员工通过员工编号，一对一方法二 */ @Test public void testGetEmpById_2() throws Exception { Emp entity=empDao.getEmpById_2(2); System.out.println(entity); Assert.assertNotNull(entity); }
结果：
MyBatis 中使用 association 标签来解决一对一的关联查询，association 标签可用的属性如下：
- property: 对象属性的名称
- javaType: 对象属性的类型
- column: 所对应的外键字段名称
- select: 使用另一个查询封装的结果
1.2、一对多关系
1.2.1、执行环境
一个用户帐号可以被多个员工使用，形成一个一对多的关系，表中的数据如下：
员工表 emp：
用户表 user：
1.2.2、关联查询（1 次查询）
实体：
员工：
package com.zhangguo.mybatis03.entities;/员工 POJO*/
public class Emp {
private int id;
/用户编号*/
private int user_id;
private String realname;
private String email;/**用户对象*/
private User user;public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public int getUser_id() {
return user_id;
}public void setUser_id(int user_id) {
this.user_id = user_id;
}public String getRealname() {
return realname;
}public void setRealname(String realname) {
this.realname = realname;
}public String getEmail() {
return email;
}public void setEmail(String email) {
this.email = email;
}public User getUser() {
return user;
}public Emp setUser(User user) {
this.user = user;
return this;
}@Override
public String toString() {
return "Emp{" +
"id=" + id +
", user_id=" + user_id +
", realname='"+ realname +''' +
", email='"+ email +''' +
", user=" + user +
'}';
}
}
用户：
package com.zhangguo.mybatis03.entities;import java.util.List;
/**用户 POJO*/
public class User {
private int id;
private String username;
private String password;/*员工集合，一个用户对象对应多个员工对象/
private List<Emp> emps;public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getUsername() {
return username;
}public void setUsername(String username) {
this.username = username;
}public String getPassword() {
return password;
}public void setPassword(String password) {
this.password = password;
}public List<Emp> getEmps() {
return emps;
}public User setEmps(List<Emp> emps) {
this.emps = emps;
return this;
}@Override
public String toString() {
return "User{" +
"id=" + id +
", username='"+ username +''' +
", password='"+ password +''' +
", emps=" + emps +
'}';
}
}
接口：
/**获得用户通过用户编号，1 对多级联查询*/ User getUserById_1(int id);
映射：
<!--一对多查询，方法 1，通过内联接--> <select id="getUserById_1" resultMap="userMap_1" parameterType="int"> SELECT emp.id, emp.user_id, emp.realname, emp.email, `user`.username, `user`.`password` FROM emp INNER JOIN `user` ON emp.user_id = `user`.id where `user`.id=#{id} </select><resultMap id="userMap_1" type="User">
<id property="id" column="user_id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<!--将 emps 对象映射成一个集合，emps 是 user 类型中的属性，ofType 用于指定集合中存放的对象类型-->
<collection property="emps" ofType="Emp">
<id property="id" column="id"></id>
<result property="user_id" column="user_id"></result>
<result property="realname" column="realname"></result>
<result property="email" column="email"></result>
</collection>
</resultMap>
测试：
/** * Method: getUserById_1(int id) * 获得用户过用户编号，级联查询 */ @Test public void testGetUserById_1() throws Exception { User entity=empDao.getUserById_1(2); System.out.println(entity); Assert.assertNotNull(entity); }
结果：
上面的示例中会发现 User 对象中包含多个 Emp 对象，此时的 Emp 对象中又引用了 User 对象，但值是空的，如果想设置值可以继续用 1 对 1 的办法赋值：
映射：
<resultMap id="userMap_1" type="User"> <id property="id" column="user_id"></id> <result property="username" column="username"></result> <result property="password" column="password"></result> <!--将 emps 对象映射成一个集合，emps 是 user 类型中的属性，ofType 用于指定集合中存放的对象类型--> <collection property="emps" ofType="Emp"> <id property="id" column="id"></id> <result property="user_id" column="user_id"></result> <result property="realname" column="realname"></result> <result property="email" column="email"></result> <!--映射关系，指定属性与属性的类型--> <association property="user" javaType="User"> <id property="id" column="user_id"></id> <result property="username" column="username"></result> <result property="password" column="password"></result> </association> </collection> </resultMap>
结果：
1.1.3、嵌套查询（多次查询）
实体：同上
接口：
/**获得用户通过用户编号，1 对多嵌套查询*/ User getUserById_2(int id);
映射：
<!--一对多查询，方法 2，通过嵌套查询多次--> <select id="getUserById_2" resultMap="userMap_2" parameterType="int"> SELECT `user`.id, `user`.username, `user`.`password` FROM `user` where id=#{id} </select><resultMap id="userMap_2" type="User">
<id property="id" column="user_id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<!--将 emps 对象映射成一个集合，emps 是 user 类型中的属性，ofType 用于指定集合中存放的对象类型-->
<!--select 用于指定再次查询的 SQL 编号，column 用于指定参数列-->
<collection property="emps" ofType="Emp" column="id" select="selectEmpById"></collection>
</resultMap><!--根据员工编号获得员工对象-->
<select id="selectEmpById" resultType="Emp">
SELECT
emp.id,
emp.user_id,
emp.realname,
emp.email
FROM
emp where user_id=#{id}
</select>
测试：
/** * Method: getUserById_2(int id) * 获得用户过用户编号，嵌套查询 */ @Test public void testGetUserById_2() throws Exception { User entity=empDao.getUserById_2(5); System.out.println(entity); Assert.assertNotNull(entity); }
结果：
MyBatis 中使用 collection 标签来解决一对多的关联查询，ofType 属性指定集合中元素的对象类型。
二、动态 SQL
2.0、MySQL 环境与前置要求
数据与 SQL 环境如下：
前置要求：
2.1、什么是动态 SQL
MyBatis 的动态 SQL 是基于 OGNL 的表达式的。它对 SQL 语句进行灵活的操作，通过表达式判断来实现对 SQL 的灵活拼接、组装。
mybatis 核心对 sql 语句进行灵活操作，通过表达式进行判断，对 sql 进行灵活拼接、组装。
主要通过以下标签：if，where，choose（when，otherwise），trim，set，foreach。
2.2、if 条件判断
根据 name 和 sex 来查询数据。如果 name 为空，那么将只根据 sex 来查询；反之只根据 name 来查询
首先不使用 动态 SQL 来书写
接口：
/** * 根据学生姓名和性别获得学生集合 */ List<Student> selectStudentsByNameAndSex(@Param("name") String name,@Param("sex") String sex);
映射：
<select id="selectStudentsByNameAndSex" resultType="student"> SELECT id,name,sex from student where name=#{name} and sex=#{sex}; </select>
测试：
/** * Method: selectStudentsByNameAndSex */ @Test public void testSelectStudentsByNameAndSex() throws Exception { List<Student> students=dao.selectStudentsByNameAndSex("rose",null); System.out.println(students); Assert.assertNotNull(students); }
结果：
上面的查询语句，我们发现如果 #{sex} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断
<select id="selectStudentsByNameAndSex" resultType="student">SELECT id,name,sex from student where 1=1 </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">如果test为真会输出中间的内容</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and name=#{name} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
结果：
参考：
<!-- 2 if(判断参数) - 将实体类不为空的属性作为 where 条件 --> <select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ST.STUDENT_SEX, ST.STUDENT_BIRTHDAY, ST.STUDENT_PHOTO, ST.CLASS_ID, ST.PLACE_ID FROM STUDENT_TBL ST WHERE <if test="studentName !=null"> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') </if> <if test="studentSex != null and studentSex !='' "> AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} </if> <if test="studentBirthday != null"> AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} </if> <if test="classId != null and classId!='' "> AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} </if> <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' "> AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} </if> <if test="placeId != null and placeId !='' "> AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} </if> <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId !='' "> AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} </if> <if test="studentId != null and studentId !='' "> AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} </if> </select>
虽然 1=1 这种方法结合 if 可以解决我们的需求，但是 1=1 明显是冗余的，通过 where 可以解决。
2.3、where 条件
where 元素知道只有在一个以上的 if 条件有值的情况下才去插入“WHERE”子句，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。
修改后的映射：
<select id="selectStudentsByNameAndSex" resultType="student">SELECT id,name,sex from student </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、如果两个if只要有一个有输出就会在sql中添加 where</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">2、如果where后以and或or开始则会删除and或or</span><span style="color: rgba(0, 128, 0, 1)">--></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%'); </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
测试：
/** * Method: selectStudentsByNameAndSex */ @Test public void testSelectStudentsByNameAndSex() throws Exception { List<Student> students=dao.selectStudentsByNameAndSex("a",null); System.out.println(students); Assert.assertNotNull(students); }
结果：
这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。
where 标记的作用类似于动态 sql 中的 set 标记，他的作用主要是用来简化 sql 语句中 where 条件判断的书写的，如下所示：
<select id="selectByParams" parameterType="map" resultType="user"> select * from user <where> <if test="id != null">id=#{id}</if> <if test="name != null and name.length()>0" >and name=#{name}</if> <if test="gender != null and gender.length()>0">and gender = #{gender}</if> </where> </select>
在上述 SQL 中加入 ID 的值为 null 的话，那么打印出来的 SQL 为：select * from user where name="xx" and gender="xx"
where 标记会自动将其后第一个条件的 and 或者是 or 给忽略掉
2.4、if+set 设置值
当 update 语句中没有使用 if 标签时，如果有一个参数为 null，都会导致错误。
当在 update 语句中使用 if 标签时，如果前面的 if 没有执行，则或导致逗号多余错误。使用 set 标签可以将动态的配置 SET 关键字，和剔除追加到条件末尾的任何不相关的逗号。如果 set 包含的内容为空的话则会出错。
使用 if+set 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。
如果通过 if 判断表面可以解决问题，如下所示：
<update id="updateStudent" parameterType="student">update student set </span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name.lenght()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> name=#{name} , </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex.lenght()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> where id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">update</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
这样做也会有问题，就是当 sex 为空时的 sql 就变成了 update student set name=#{name} , where id=#{id}，这明显是错误的。
同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，我们怎么处理呢？
接口：
/** * 更新学生 */ int updateStudent(Student entity);
映射：
<update id="updateStudent" parameterType="student"> update student <!--自动添加 set--> <set> <!--智能处理逗号问题--> <if test="name!=null and name.length()>0"> name=#{name} </if><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex.length()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">set</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> where id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">update</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
注意：某些情况下逗号必须添加，如下所示：
<update id="updateStudent" parameterType="student"> update student <!--自动添加 set--> <set> <!--智能处理逗号问题--> <if test="name!=null and name.length()>0"> name=#{name} , </if><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex.length()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex} , </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">set</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> where id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">update</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
结尾的逗号会被自动删除。
测试：
/** * Method: updateStudent */ @Test public void testUpdateStudent() throws Exception { //会将实体中的每一个字段都更新，不好 // Student entity=dao.selectStudentById(11); // //entity.setName("张丽美"); // entity.setSex("girl"); // // Assert.assertEquals(1,dao.updateStudent(entity));</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">不需要先执行查询</span> Student student=<span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> Student(); student.setId(</span>9<span style="color: rgba(0, 0, 0, 1)">); </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">只更新了name与sex没有关系</span> student.setName("malili"<span style="color: rgba(0, 0, 0, 1)">); Assert.assertEquals(</span>1<span style="color: rgba(0, 0, 0, 1)">,dao.updateStudent(student)); }</span></pre>
结果：
这样写，如果第一个条件 name 为空，那么 sql 语句为：update student set sex=? where id=?
如果第一个条件不为空，那么 sql 语句为：update student u set name= ? , sex = ? where id=?
set 主要解决了自动添加标签与处理逗号的问题，另外这种更新方法比较以前的全部更新方式在开发中性能更高。
2.5、choose(when,otherwise) 开关
如果不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。
假定这里需要优先根据编号搜索，没有时选择 name，最后考虑 sex：
接口：
/** * 根据学生编号、姓名和性别获得学生集合 */ List<Student> selectStudentsByNameAndSex(@Param("id") int id, @Param("name") String name,@Param("sex") String sex);
映射：
<select id="selectStudentsByNameAndSex" resultType="student">SELECT id,name,sex from student </span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">choose</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">when </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="id>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">when</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">when </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> name=#{name} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">when</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">otherwise</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">otherwise</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">choose</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
测试：
/** * Method: selectStudentsByNameAndSex */ @Test public void testSelectStudentsByNameAndSex() throws Exception { List<Student> students=dao.selectStudentsByNameAndSex(1,"rose","girl"); System.out.println(students); Assert.assertNotNull(students); }
结果：
也就是说，这里我们有三个条件，id,name,sex，只能选择一个作为查询条件
如果 id 不为空，那么查询语句为：select * from student where id=?
如果 id 为空，那么看 name 是否为空，如果不为空，那么语句为 select * from student where name=?;
如果 name 为空，那么查询语句为 select * from student where sex=?
2.6、trim 裁剪
trim 标记是一个格式化的标记，可以完成 set 或者是 where 标记的功能
①、用 trim 改写上面第二点的 if+where 语句
if+where 的办法：
<select id="selectStudentsByNameAndSex" resultType="student">SELECT id,name,sex from student </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、如果两个if只要有一个有输出就会在sql中添加 where</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">2、如果where后以and或or开始则会删除and或or</span><span style="color: rgba(0, 128, 0, 1)">--></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%'); </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">where</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
trim 的办法：
<select id="selectStudentsByNameAndSex" resultType="student">SELECT id,name,sex from student </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、prefix表示将前置where，prefixOverrides将删除打头内容</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">trim </span><span style="color: rgba(255, 0, 0, 1)">prefix</span><span style="color: rgba(0, 0, 255, 1)">="where"</span><span style="color: rgba(255, 0, 0, 1)"> prefixOverrides</span><span style="color: rgba(0, 0, 255, 1)">="and | or"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%') </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">trim</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
测试结果：
prefix：将加上前缀
prefixoverride：去掉第一个 and 或者是 or
②、用 trim 改写上面第三点的 if+set 语句
if+set 的方法：
<update id="updateStudent" parameterType="student"> update student <!--自动添加 set--> <set> <!--智能处理逗号问题--> <if test="name!=null and name.length()>0"> name=#{name} </if><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex.length()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">set</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> where id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">update</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
trim 的方法：
<update id="updateStudent" parameterType="student"> update student</span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">trim </span><span style="color: rgba(255, 0, 0, 1)">prefix</span><span style="color: rgba(0, 0, 255, 1)">="set"</span><span style="color: rgba(255, 0, 0, 1)"> suffixOverrides</span><span style="color: rgba(0, 0, 255, 1)">=","</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name.length()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> name=#{name}, </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex.length()>0"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> sex=#{sex}, </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">trim</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> where id=#{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">update</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
结果：
suffix：后缀
suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的 and 一样）
可以自定义添加前后缀，与之对应的属性是 prefix 和 suffix。同时通过 prefixOverrides 和 suffixOverrides 分别来覆盖首尾部的内容，即忽略不必要的前后缀。就是说它可以充当 where 标签，也可以充当 set 标签啦 ~
充当 where 标签：
<trim prefix = "where" prefixOverrides="and|or" > ... </trim>
充当 set 标签：
<trim prefix="set" suffixOverrides=","> ... </trim>
例子：动态添加用户属性
<insert id="find" resultType="Admin"> insert into admin <trim prefix="(" suffix=")" suffixOverrides=","> <if test = "aname != null and aname !='' "> aname, </if> <if test = "city != null and city !='' "> city, </if> <if test = "age != null and age !='' "> age, </if> </trim> <trim prefix="values(" suffix=")" suffixOverrides=","> <if test = "aname != null and aname !='' "> #{aname}, </if> <if test = "city != null and city !='' "> #{city}, </if> <if test = "age != null and age !='' "> #{age}, </if> </trim> </insert>
上面相应的语句为：insert into admin (…) values(…);。通过 trim 标签用 () 包裹，以及自动忽略尾部的逗号。
2.7、SQL 片段
有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
比如：下面的映射文件中对于 id,name,sex 出现多次：
<select id="selectStudentsByNameOrSex" resultType="student"> SELECT id,name,sex from student where name like '%${realname}%' or sex=#{sex}; </select><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="selectStudentsByIdOrSex"</span><span style="color: rgba(255, 0, 0, 1)"> resultType</span><span style="color: rgba(0, 0, 255, 1)">="student"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> SELECT <strong>id,name,sex</strong> from student where id=#{no} or sex=#{sex}; </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="selectStudentsByNameAndSex"</span><span style="color: rgba(255, 0, 0, 1)"> resultType</span><span style="color: rgba(0, 0, 255, 1)">="student"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> SELECT <strong>id,name,sex</strong> from student </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、prefix表示将前置where，prefixOverrides将删除打头内容</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">trim </span><span style="color: rgba(255, 0, 0, 1)">prefix</span><span style="color: rgba(0, 0, 255, 1)">="where"</span><span style="color: rgba(255, 0, 0, 1)"> prefixOverrides</span><span style="color: rgba(0, 0, 255, 1)">="and | or"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%') </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">trim</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
加粗的内容是重复的，通过 sql 片段复用。
定义 sql 片段：
<!--定义 sql 片段--> <sql id="col_student"> id,name,sex </sql>
引用 sql 片段
<select id="selectStudentsByNameOrSex" resultType="student"> <!--引用 sql 片段--> SELECT <include refid="col_student"></include> from student where name like '%${realname}%' or sex=#{sex}; </select><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="selectStudentsByIdOrSex"</span><span style="color: rgba(255, 0, 0, 1)"> resultType</span><span style="color: rgba(0, 0, 255, 1)">="student"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> SELECT </span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">include </span><span style="color: rgba(255, 0, 0, 1)">refid</span><span style="color: rgba(0, 0, 255, 1)">="col_student"</span><span style="color: rgba(0, 0, 255, 1)">></</span><span style="color: rgba(128, 0, 0, 1)">include</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> from student where id=#{no} or sex=#{sex}; </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="selectStudentsByNameAndSex"</span><span style="color: rgba(255, 0, 0, 1)"> resultType</span><span style="color: rgba(0, 0, 255, 1)">="student"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> SELECT </span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">include </span><span style="color: rgba(255, 0, 0, 1)">refid</span><span style="color: rgba(0, 0, 255, 1)">="col_student"</span><span style="color: rgba(0, 0, 255, 1)">></</span><span style="color: rgba(128, 0, 0, 1)">include</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> from student </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、prefix表示将前置where，prefixOverrides将删除打头内容</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">trim </span><span style="color: rgba(255, 0, 0, 1)">prefix</span><span style="color: rgba(0, 0, 255, 1)">="where"</span><span style="color: rgba(255, 0, 0, 1)"> prefixOverrides</span><span style="color: rgba(0, 0, 255, 1)">="and | or"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%') </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">trim</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span> </pre>
结果：
注意：①、最好基于 单表来定义 sql 片段，提高片段的可重用性
②、在 sql 片段中不要包括 where
sql 片段带参数：
定义时使用参数：
<!--定义 sql 片段--> <sql id="col_student"> ${alias}.id,${alias}.name,${alias}.sex </sql>
引用时指定参数：
<select id="selectStudentsByNameAndSex" resultType="student">SELECT </span><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">include </span><span style="color: rgba(255, 0, 0, 1)">refid</span><span style="color: rgba(0, 0, 255, 1)">="col_student"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="alias"</span><span style="color: rgba(255, 0, 0, 1)"> value</span><span style="color: rgba(0, 0, 255, 1)">="s"</span><span style="color: rgba(0, 0, 255, 1)">></</span><span style="color: rgba(128, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">include</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> from student s </span><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)">1、prefix表示将前置where，prefixOverrides将删除打头内容</span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">trim </span><span style="color: rgba(255, 0, 0, 1)">prefix</span><span style="color: rgba(0, 0, 255, 1)">="where"</span><span style="color: rgba(255, 0, 0, 1)"> prefixOverrides</span><span style="color: rgba(0, 0, 255, 1)">="and | or"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="name!=null and name!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and name like concat(concat('%',#{name}),'%') </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">if </span><span style="color: rgba(255, 0, 0, 1)">test</span><span style="color: rgba(0, 0, 255, 1)">="sex!=null and sex!=''"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> and sex=#{sex} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">if</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">trim</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></pre>
结果：
2.8、foreach 循环
foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。
注意 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。
到此我们已经完成了涉及 XML 配置文件和 XML 映射文件的讨论。下一章将详细探讨 Java API，这样就能提高已创建的映射文件的利用效率。
foreach 的主要用在构建 in 条件中，他可以迭代一个集合。foreach 元素的属性主要有：item,index,collection,open,separator,close。
下面对属性进行简单的介绍：
item: 表示集合中每一个元素进行迭代时的别名。
index: 指定一个名字，用于表示在迭代过程中每次迭代的位置。
open: 表示以什么开始。
separator: 每次迭代以什么分割。
close: 以什么关闭。
collection: 最重要且必须指定的有三种情况：
1. 如果传入的是单独参数的 List 类型时，collection 的属性值为 list。
2. 如果传入的是单独参数的数组时，collection 的属性值为 array。
3. 如果传入多个参数时，我们把多个参数放入 map 中，单参数也可以放入 map 中。map 中的 key 就是参数名，所以 collection 属性值就是传入的 List 或者 array 对象在 Map 里的 key。
一、用 foreach 来改写 select * from user where id=1 or id=2 or id=3
<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.zhangguo.User"> select * from user <where> <!-- collection: 指定输入对象中的集合属性 item: 每次遍历生成的对象 open: 开始遍历时的拼接字符串 close: 结束时拼接的字符串 separator: 遍历对象之间需要拼接的字符串 select * from user where 1=1 and (id=1 or id=2 or id=3) --> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id=#{id} </foreach> </where> </select>
二、我们用 foreach 来改写 select * from user where id in (1,2,3)
<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.zhangguo.User"> select * from user <where> <!-- collection: 指定输入对象中的集合属性 item: 每次遍历生成的对象 open: 开始遍历时的拼接字符串 close: 结束时拼接的字符串 separator: 遍历对象之间需要拼接的字符串 select * from user where 1=1 and id in (1,2,3) --> <foreach collection="ids" item="id" open="and id in (" close=")" separator=","> #{id} </foreach> </where> </select>
其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态 sql 对照着改。
参考：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!-- namespace 的名字需要跟接口的类名一致 -->
<mapper namespace="cn.bdqn.dao.UserMapper">
<!--
1、resultMap 属性：type 为 java 实体类；id 为此 resultMap 的标识
2、resultMap 的子元素：
id – 一般对应到数据库中该行的 ID, 设置此项可以提高 Mybatis 性能.
result – 映射到 JavaBean 的某个“简单类型”属性,String,int 等.
association – 映射到 JavaBean 的某个“复杂类型”属性, 其他 JavaBean 类.
collection –复杂类型集合
--><!--根据 roleId 获取用户列表: 当数据库中的字段信息与对象的属性不一致时需要通过 resultMap 来映射 -->
<!-- <resultMap type="User" id="seachUserResult">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="roleId" column="roleId"/>
<result property="roleName" column="roleName"/>
</resultMap><select id="getUserListByRoleId" parameterType="Role" resultMap="seachUserResult">
select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id and u.roleId = #{id}
</select> --><!-- 根据 roleId 获取用户列表 association start-->
<resultMap type="User" id="seachUserResult">
<result property="id" column="id"/>
<result property="userCode" column="userCode" />
<result property="userName" column="userName" />
<result property="roleId" column="roleId" />
<!-- <association property="role" javaType="Role" >
<result property="id" column="id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</association> -->
<association property="role" javaType="Role" resultMap="roleMap"/>
</resultMap><resultMap type="Role" id="roleMap">
<result property="id" column="id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</resultMap><select id="getUserListByRoleId" parameterType="Role" resultMap="seachUserResult">
select u.*,r.roleCode as roleCode,r.roleName as roleName from user u,role r where u.roleId = r.id and u.roleId = #{id}
</select><!-- association end-->
<!-- 获取指定用户的地址列表 (user 表 -address 表：1 对多关系) collection start-->
<resultMap type="User" id="userMap">
<id property="id" column="userId"/>
<collection property="addressList" ofType="Address">
<id property="id" column="a_id"/>
<result property="postCode" column="postCode"/>
<result property="addressContent" column="addressContent"/>
</collection>
</resultMap><select id="getAddressListByUserId" parameterType="User" resultMap="userMap">
select *,a.id as a_id from user u,address a where u.id=a.userId and u.id=#{id}
</select>
<!-- collection end --><resultMap type="User" id="seachUser">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="roleId" column="roleId"/>
<result property="roleName" column="roleName"/>
</resultMap><!-- <select id="searchUserList" parameterType="User" resultMap="seachUser">
select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id
and u.roleId = #{roleId}
and u.userCode like CONCAT ('%',#{userCode},'%') // 防止 sql 注入
and u.userName like CONCAT ('%',#{userName},'%')
</select> --><!--
1、有些时候，sql 语句 where 条件中，需要一些安全判断，例如按性别检索，如果传入的参数是空的，此时查询出的结果很可能是空的，也许我们需要参数为空时，是查出全部的信息。这是我们可以使用动态 sql，增加一个判断，当参数不符合要求的时候，我们可以不去判断此查询条件。
2、mybatis 的动态 sql 语句是基于 OGNL 表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来 mybatis 动态 SQL 语句主要有以下几类:
if 语句 (简单的条件判断)
choose (when,otherwize) , 相当于 java 语言中的 switch , 与 jstl 中的 choose 很类似.
trim (对包含的内容加上 prefix, 或者 suffix 等，前缀，后缀)
where (主要是用来简化 sql 语句中 where 条件判断的，能智能的处理 and or , 不必担心多余导致语法错误)
set (主要用于更新时)
foreach (在实现 mybatis in 语句查询时特别有用)
--><!-- if(判断参数) - 将实体类不为空的属性作为 where 条件 -->
<select id="searchUserList" parameterType="User" resultMap="seachUser">
select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id
<if test="roleId!=null">
and u.roleId = #{roleId}
</if>
<if test="userCode != null">
and u.userCode like CONCAT ('%',#{userCode},'%')
</if>
<if test="userName != null">
and u.userName like CONCAT ('%',#{userName},'%')
</if>
</select><select id="count" resultType="int">
select count(1) from user
</select><insert id="add" parameterType="User">
insert into user (userCode,userName,userPassword)
values (#{userCode},#{userName},#{userPassword})
</insert><!-- if/set(判断参数) - 将实体类不为空的属性更新 -->
<!-- <update id="update" parameterType="User">
update user
<set>
<if test="userCode != null and userCode !=''">userCode=#{userCode},</if>
<if test="userName != null">userName=#{userName},</if>
<if test="userPassword != null">userPassword=#{userPassword},</if>
<if test="roleId != null">roleId=#{roleId}</if>
</set>
where id=#{id}
</update> --><!-- if/trim 代替 set(判断参数) - 将实体类不为空的属性更新 -->
<update id="update" parameterType="User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="userCode != null and userCode !=''">userCode=#{userCode},</if>
<if test="userName != null">userName=#{userName},</if>
<if test="userPassword != null">userPassword=#{userPassword},</if>
<if test="roleId != null">roleId=#{roleId}</if>
</trim>
where id=#{id}
</update><!--注意： 你可以传递一个 List 实例或者数组作为参数对象传给 MyBatis。
当你这么做的时候，MyBatis 会自动将它包装在一个 Map 中，用名称在作为键。
List 实例将会以“list”作为键，而数组实例将会以“array”作为键。
配置文件中的 parameterType 是可以不配置的-->
<resultMap type="User" id="userMapByDep">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
</resultMap>
<!-- foreach(循环 array 参数) - 作为 where 中 in 的条件 -->
<select id="getUserByDepId_foreach_array" resultMap="userMapByDep">
select * from user where depId in
<foreach collection="array" item="depIds" open="(" separator="," close=")">
#{depIds}
</foreach>
</select><!-- foreach(循环 List<String> 参数) - 作为 where 中 in 的条件 -->
<select id="getUserByDepId_foreach_list" resultMap="userMapByDep">
select * from user where depId in
<foreach collection="list" item="depIdList" open="(" separator="," close=")">
#{depIdList}
</foreach>
</select><delete id="delete" parameterType="User">
delete from user where id=#{id}
</delete><select id="getUserList" resultType="User">
select * from user
</select>
</mapper>
假定我们要多删除：
接口：
/** * 删除多个学生通过编号 */ int deleteStudents(List<Integer> ids);
映射：
<delete id="deleteStudents"> delete from student where id in <foreach collection="list" item="id" open="(" separator="," close=")"> #{id} </foreach> </delete>
注意 collection 这里只能是 list，不能是 ids，因为反射时获取不到参数名称。
测试：
/** * Method: deleteStudents */ @Test public void testDeleteStudents() throws Exception { List<Integer> ids=new ArrayList<Integer>(); ids.add(8); ids.add(9); Assert.assertEquals(2,dao.deleteStudents(ids)); }
结果：
2.9、bind 绑定变量
bind 标签可以使用 OGNL 表达式创建一个变量并将其绑定到上下文中。
bind 标签的两个属性都是不选项，name 为绑定到上下文的变量名，value 为 OGNL 表达式，创建一个 bind 标签后，就可以在下面直接使用了。 使用 bind 拼接字符串不仅可以避免因更换数据库而修改 SQL，也能预防 SQL 注入。
<!-- List<Employee> getEmpsTestInnerParameter(Employee employee); --> <select id="getEmpsTestInnerParameter" resultType="com.hand.mybatis.bean.Employee"> <!-- bind: 可以将 OGNL 表达式的值绑定到一个变量中，方便后来引用这个变量的值 --> <bind name="bindeName" value="'%'+eName+'%'"/> eName 是 employee 中一个属性值 SELECT * FROM emp <if test="_parameter!=null"> where ename like #{bindeName} </if> </select>
三、视频
https://www.bilibili.com/video/av32447485/
四、示例
https://git.coding.net/zhangguo5/MyBatis03.git
五、作业
1、个人项目的数据库设计，个人项目的静态页面设计（2 个，一个必须是首页，PC 端）
2、重现本章示例
3、任务指导手册所有 mybatis 理论题
4、根据如下 ER 图创建 4 个表，完成 1-1，1-N，M-N 关系的查询，无需界面，测试通过即可
5、完成图书管理系统中二表关联，显示图书类型
请实现一个简易图书管理系统（LibSystem），实现图书管理功能，要求如下： 1、管理数据库中所有图书（Books），包含图书编号（isbn）、书名（title）、作者（author）、价格（price）、出版日期（publishDate） 2、Maven 多模块 +MySQL+Git+MyBatis+JUnit 单元测试 3、表示层可以是 AJAX 或 JSTL请实现一个迷你图书管理系统（LibSystem），实现图书管理功能，要求如下：
1、管理数据库中所有图书分类（Categories），包含图书编号（id）, 名称（name）
2、管理数据库中所有图书（Books），包含图书编号（isbn）、类别（categoryId，外键）书名（title）、作者（author）、价格（price）、出版日期（publishDate）、封面（cover）、详细介绍（details）
3、分页
4、多条件组件查询（3 个以上的条件）
5、上传封面
6、富文本编辑器
6、使用任意的原型开发工具设计出个人项目的 1-2 个界面，工具：Balsamiq Mockups，Axure RP Pro 7.0。
7、将动态 sql 中的所有知识点全部应用于作业 4