MyBatis学习总结（三）——多表关联查询与动态SQL

在上一章中我们学习了《MyBatis学习总结（二）——MyBatis核心配置文件与输入输出映射》，这一章主要是介绍一对一关联查询、一对多关联查询与动态SQL等内容。

一、多表关联查询

表与表之间有三种常见的关联关系，分别是一对一，一对多与多对多关系，MyBatis直接提供一对一与一对多的关联关系，可能通过间接的方式实现一对多关联。

1.1、一对一关系

1.1.1、执行环境

假定一个员工（emp）拥有一个登录用户（user），员工与用户表之间是一对一关系：

用户表：

员工表：

SQL：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
 `username` varchar(64) NOT NULL COMMENT '用户名',
 `password` varchar(64) NOT NULL COMMENT '密码',
 PRIMARY KEY (`id`),
 UNIQUE KEY `users_username_uindex` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'tom', '123456');
INSERT INTO `user` VALUES ('2', 'rose', '888888');
INSERT INTO `user` VALUES ('3', 'mark', 'qwerty');
INSERT INTO `user` VALUES ('4', 'jack', 'qaz123');
INSERT INTO `user` VALUES ('5', 'mali', 'uio890');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
 `user_id` int(11) DEFAULT NULL COMMENT '用户编号',
 `realname` varchar(32) NOT NULL COMMENT '姓名',
 `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`),
 KEY `emp_user_id` (`user_id`),
 CONSTRAINT `emp_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工表';

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '1', '汤姆', 'tom@gmail.com');
INSERT INTO `emp` VALUES ('2', '2', '梅贵', 'rose@163.com');
INSERT INTO `emp` VALUES ('3', '3', '马克', 'mark@sina.com');
INSERT INTO `emp` VALUES ('4', '4', '岳翰', 'jack@gmail.com');
INSERT INTO `emp` VALUES ('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 JOIN `user` ON 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">
 SELECT
 `user`.id,
 `user`.username,
 `user`.`password`
 FROM
 `user` where 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
        <!--如果test为真会输出中间的内容-->
        <if test="name!=null and name!=''">
            and name=#{name}
        </if>

        <if test="sex!=null and sex!=''">
            and sex=#{sex}
        </if>

    </select>

结果：

参考：

<!-- 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

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

测试：

    /**
     * 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  
        
        <if test="name!=null and name.lenght()>0">
            name=#{name} ,
        </if>

        <if test="sex!=null and sex.lenght()>0">
            sex=#{sex}
        </if>
        
        where id=#{id}
        
    </update>

这样做也会有问题，就是当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>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

注意：某些情况下逗号必须添加，如下所示：

    <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name} , 
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex} , 
            </if>
        </set>
        where id=#{id}
    </update>

View Code

结尾的逗号会被自动删除。

测试：

    /**
     * Method: updateStudent
     */
    @Test
    public void testUpdateStudent() throws Exception {
        //会将实体中的每一个字段都更新，不好
//        Student entity=dao.selectStudentById(11);
//        //entity.setName("张丽美");
//        entity.setSex("girl");
//
//        Assert.assertEquals(1,dao.updateStudent(entity));

        //不需要先执行查询
        Student student=new Student();
        student.setId(9);
        //只更新了name与sex没有关系
        student.setName("malili");
        Assert.assertEquals(1,dao.updateStudent(student));
    }

结果：

这样写，如果第一个条件 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
        <where>
        <choose>
            <when test="id>0">
                id=#{id}
            </when>
            <when test="name!=null and name!=''">
                name=#{name}
            </when>
            <otherwise>
                sex=#{sex}
            </otherwise>
        </choose>
        </where>
    </select>

测试：

    /**
     * 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

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

trim的办法：

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

测试结果：

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>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

trim的方法：

    <update id="updateStudent" parameterType="student">
        update student

           <trim prefix="set" suffixOverrides=",">
               <if test="name!=null and name.length()>0">
                   name=#{name},
               </if>

               <if test="sex!=null and sex.length()>0">
                   sex=#{sex},
               </if>
           </trim>

        where id=#{id}
    </update>

结果：

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>

    <select id="selectStudentsByIdOrSex" resultType="student">
        SELECT id,name,sex from student where id=#{no} or sex=#{sex};
    </select>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

加粗的内容是重复的，通过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>

    <select id="selectStudentsByIdOrSex" resultType="student">
        SELECT <include refid="col_student"></include> from student where id=#{no} or sex=#{sex};
    </select>

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT <include refid="col_student"></include> from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

结果：

注意：①、最好基于单表来定义 sql 片段，提高片段的可重用性

　　　②、在 sql 片段中不要包括 where

sql片段带参数：

定义时使用参数：

    <!--定义sql片段-->
    <sql id="col_student">
        ${alias}.id,${alias}.name,${alias}.sex
    </sql>

引用时指定参数：

    <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT

        <include refid="col_student">
            <property name="alias" value="s"></property>
        </include>

        from student s

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

结果：

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>

View Code

假定我们要多删除：

接口：

    /**
     * 删除多个学生通过编号
     */
    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、富文本编辑器

View Code

6、使用任意的原型开发工具设计出个人项目的1-2个界面，工具：Balsamiq Mockups，Axure RP Pro 7.0。

7、将动态sql中的所有知识点全部应用于作业4

posted @ 2018-09-30 11:21 张果阅读(28460) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

笔记

MyBatis学习总结（三）——多表关联查询与动态SQL

一、多表关联查询

1.1、一对一关系

1.1.1、执行环境

1.1.2、关联查询（1次查询）

1.1.3、嵌套查询（2次查询）

1.2、一对多关系

1.2.1、执行环境

1.2.2、关联查询（1次查询）

1.1.3、嵌套查询（多次查询）

二、动态SQL

2.0、MySQL环境与前置要求

2.1、什么是动态SQL

2.2、if条件判断

2.3、where条件

2.4、if+set设置值

2.5、choose(when,otherwise) 开关

2.6、trim裁剪

2.7、SQL 片段

2.8、foreach循环

2.9、bind 绑定变量

三、视频

四、示例

五、作业

公告