MyBatis——Sql 映射文件

最新推荐文章于 2024-10-17 14:24:21 发布

前丨尘忆·梦

最新推荐文章于 2024-10-17 14:24:21 发布

阅读量1.1k

点赞数 1

分类专栏：微服务核心：SSM+SpringBoot2+设计模式+MQ 文章标签： sql java 数据库

本文链接：https://blog.csdn.net/qq_36879493/article/details/122116777

版权

微服务核心：SSM+SpringBoot2+设计模式+MQ 专栏收录该内容

61 篇文章 3 订阅

订阅专栏

Sql 映射文件

MyBatis 的真正强大在于它的语句映射，这是它的魔力所在。由于它的异常强大，映射器的 XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。MyBatis 致力于减少使用成本，让用户能更专注于 SQL 代码。

1、增删改的实现

首先我们在接口中定义方法：

package mybatis.dao;

import mybatis.bean.Employee;

public interface EmployeeMapper {
    public Employee getEmpById(Integer id);

    public void addEmp(Employee employee);

    public void updateEmp(Employee employee);

    public void deleteEmpById(Integer id);
}

接着，在 Sql 映射文件中给出对应的 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">
<mapper namespace="mybatis.dao.EmployeeMapper">

    <!--namespace：命名空间；指定为接口的全类名
        id：唯一标识
        resultType：返回值类型
        #{id}：从传递过来的参数中取出id值-->

    <select id="selectEmp" resultType="mybatis.bean.Employee">
        <!--在这里我们使用了别名，使得字段名和类的属性名相同-->
        select id,last_name lastName,email,gender from tbl_employee where id = #{id}
    </select>

    <!--
        public Employee getEmpById(Integer id);
    -->
    <select id="getEmpById" resultType="mybatis.bean.Employee">
        <!--在这里我们使用了别名，使得字段名和类的属性名相同-->
        select id,last_name lastName,email,gender from tbl_employee where id = #{id}
    </select>


    <!--
        public void addEmp(Employee employee);
        parameterType可以省略
    -->
    <insert id="addEmp" parameterType="mybatis.bean.Employee">
        insert into tbl_employee(last_name, email, gender)
        values(#{lastName}, #{email}, #{gender})
    </insert>

    <!--
        public void updateEmp(Employee employee);
    -->
    <update id="updateEmp">
        update tbl_employee
        set last_name = #{lastName}, email = #{email}, gender = #{gender}
        where id = #{id}
    </update>

    <!--
        public void deleteEmpById(Integer id);
    -->
    <delete id="deleteEmpById">
        delete from tbl_employee where id = #{id}
    </delete>

</mapper>

测试代码：

/**
 * 测试增
 * @throws IOException
 */
@Test
public void test4() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象，不会自动提交数据
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        mapper.addEmp(new Employee(null, "jerry", "jerry@qq.com", "1"));

        //4、手动提交
        sqlSession.commit();
    }finally {
        sqlSession.close();
    }
}


/**
 * 测试修
 * @throws IOException
 */
@Test
public void test5() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象，不会自动提交数据
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        mapper.updateEmp(new Employee(1, "jerry01", "jerry01@qq.com", "0"));

        //4、手动提交
        sqlSession.commit();
    }finally {
        sqlSession.close();
    }
}


/**
 * 测试删除
 * 1、mybatis允许增删改直接定义以下类型返回值
 *      Integer、Long、Boolean、void
 * 2、手动提交数据
 * @throws IOException
 */
@Test
public void test6() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象，不会自动提交数据
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        mapper.deleteEmpById(1);

        //4、手动提交
        sqlSession.commit();
    }finally {
        sqlSession.close();
    }
}

注意：

mybatis 允许增删改直接定义以下类型返回值：

Integer、Long、Boolean、void

必须手动提交数据：

sqlSessionFactory.openSession() ==> 手动提交

sqlSessionFactory.openSession(true) ==> 自动提交

2、获取自增主键的值

insert 标签里边的 useGeneratedKeys 属性和 keyProperty 属性。

<!--
    public void addEmp(Employee employee);
    parameterType可以省略

    获取自增主键的值：
    mysql支持自增主键，自增主键值的获取，mybatis也是利用statment.getGeneratedKeys()
    useGeneratedKeys="true" 使用自增主键获取主键值策略
    keyProperty：指定对应的主键属性，也就是mybatis获取到主键值之后，将这个值封装给javaBean的哪个属性
-->
<insert id="addEmp" parameterType="mybatis.bean.Employee"
        useGeneratedKeys="true" keyProperty="id">
    insert into tbl_employee(last_name, email, gender)
    values(#{lastname}, #{email}, #{gender})
</insert>

测试代码：

/**
 * 测试增
 * @throws IOException
 */
@Test
public void test4() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象，不会自动提交数据
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        Employee employee = new Employee(null, "jerry", "jerry@qq.com", "1");
        //此时employee对象的id还为null
        
        mapper.addEmp(employee);

        System.out.println(employee.getId());//3，打印输出3

        //4、手动提交
        sqlSession.commit();
    }finally {
        sqlSession.close();
    }
}

3、参数处理

异常：

org.apache.ibatis.binding.BindingException:
Parameter 'id' not found.
Available parameters are [1, 0, param1, param2]
操作：

方法：public Employee getEmpByIdAndLastName(Integer id,String lastName);

取值：#{id},#{lastName}
单个参数：mybatis 不会做特殊处理

#{参数名/任意名}：取出参数值。
多个参数：mybatis 会做特殊处理

多个参数会被封装成一个 map：

key：param1…paramN，或者参数的索引也可以

value：传入的参数值

#{} 就是从 map 中获取指定的 key 的值；

定义一个多参数查询方法：

public interface EmployeeMapper {
    public Employee getEmpByIdAndLastName(Integer id, String lastname);

    public Employee getEmpById(Integer id);

    public void addEmp(Employee employee);

    public void updateEmp(Employee employee);

    public void deleteEmpById(Integer id);
}

在 Sql 映射文件中写出 sql 语句：

<!--
    public Employee getEmpByIdAndLastName(Integer id, String lastname);
-->
<select id="getEmpByIdAndLastName" resultType="mybatis.bean.Employee">
    select * from tbl_employee where id = #{param1} and last_name = #{param2}
</select>

测试代码：

@Test
public void test7() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        Employee jerry = mapper.getEmpByIdAndLastName(2, "jerry");
        System.out.println(jerry);

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-22 20:12:43,544 ==>  Preparing: select * from tbl_employee where id = ? and last_name = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-22 20:12:43,600 ==> Parameters: 2(Integer), jerry(String)  (BaseJdbcLogger.java:137) 
DEBUG 12-22 20:12:43,630 <==      Total: 1  (BaseJdbcLogger.java:137) 
Employee{id=2, lastname='jerry', email='jerry@qq.com', gender='1'}

【命名参数】：明确指定封装参数时 map 的 key；@Param("id")

多个参数会被封装成一个 map：

key：使用 @Param 注解指定的值

value：参数值

#{指定的key} 取出对应的参数值

修改接口：

public interface EmployeeMapper {
    public Employee getEmpByIdAndLastName(@Param("id")Integer id, @Param("lastname")String lastname);

修改 Sql 映射文件：

<!--
    public Employee getEmpByIdAndLastName(Integer id, String lastname);
-->
<select id="getEmpByIdAndLastName" resultType="mybatis.bean.Employee">
    <!-- select * from tbl_employee where id = #{param1} and last_name = #{param2} -->
    select * from tbl_employee where id = #{id} and last_name = #{lastname}
</select>

POJO

如果多个参数正好是我们业务逻辑的数据模型，我们就可以直接传入 pojo；

#{属性名}：取出传入的 pojo 的属性值
Map

如果多个参数不是业务模型中的数据，没有对应的 pojo，不经常使用，为了方便，我们也可以传入 map

#{key}：取出 map 中对应的值

添加一个查询：

public interface EmployeeMapper {
    public Employee getEmpByMap(Map<String, Object> map);

在 Sql 映射文件中也对应添加：

<!--
    public Employee getEmpByMap(Map<String, Object> map);
-->
<select id="getEmpByMap" resultType="mybatis.bean.Employee">
    select * from tbl_employee where id = #{id} and last_name = #{lastname}
</select>

测试代码：

@Test
public void test8() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        map.put("id", 2);
        map.put("lastname", "jerry1");
        Employee employee = mapper.getEmpByMap(map);
        System.out.println(employee);

    }finally {
        sqlSession.close();
    }
}

3.1、关于参数的扩展思考

public Employee getEmp(@Param("id")Integer id, String lastName);

取值：id ==> #{id/param1} lastName ==> #{param2}
public Employee getEmp(Integer id, @Param("e")Employee emp);

取值：id ==> #{param1} lastName ===> #{param2.lastName/e.lastName}

特别注意：如果是 Collection(List、Set) 类型或者是数组也会特殊处理。也是把传入的 list 或者数组封装在 map 中。

key：Collection(collection)，如果是 List 还可以使用这个 key(list)、数组(array)

public Employee getEmpById(List<Integer> ids);

取值：取出第一个 id 的值：#{list[0]}

3.2、结合源码，mybatis 怎么处理参数

总结：参数多时会封装 map，为了不混乱，我们可以使用 @Param 来指定封装时使用的 key；

#{key} 就可以取出 map 中的值；

(@Param("id")Integer id,@Param("lastName")String lastName);

ParamNameResolver 解析参数封装 map 的；

//1、names：{0=id, 1=lastName}；构造器的时候就确定好了

确定流程：
1. 获取每个标了 param 注解的参数的 @Param 的值：id，lastName；赋值给 name;
2. 每次解析一个参数给 map 中保存信息：（key：参数索引，value：name 的值）
  
  name 的值：
  
  标注了 param 注解：注解的值
  
  没有标注：
  1. 全局配置：useActualParamName（jdk1.8）：name=参数名
  2. name=map.size()；相当于当前元素的索引
{0=id, 1=lastName,2=2}

args【1，“Tom”, ‘hello’】:

public Object getNamedParams(Object[] args) {
    final int paramCount = names.size();
    //1、参数为null直接返回
    if (args == null || paramCount == 0) {
      return null;
     
    //2、如果只有一个元素，并且没有Param注解；args[0]：单个参数直接返回
    } else if (!hasParamAnnotation && paramCount == 1) {
      return args[names.firstKey()];
      
    //3、多个元素或者有Param标注
    } else {
      final Map<String, Object> param = new ParamMap<Object>();
      int i = 0;
      
      //4、遍历names集合；{0=id, 1=lastName,2=2}
      for (Map.Entry<Integer, String> entry : names.entrySet()) {
      
      	//names集合的value作为key;  names集合的key又作为取值的参考args[0]:args【1，"Tom"】:
      	//eg:{id=args[0]:1,lastName=args[1]:Tom,2=args[2]}
        param.put(entry.getValue(), args[entry.getKey()]);
        
        
        // add generic param names (param1, param2, ...)param
        //额外的将每一个参数也保存到map中，使用新的key：param1...paramN
        //效果：有Param注解可以#{指定的key}，或者#{param1}
        final String genericParamName = GENERIC_NAME_PREFIX + String.valueOf(i + 1);
        // ensure not to overwrite parameter named with @Param
        if (!names.containsValue(genericParamName)) {
          param.put(genericParamName, args[entry.getKey()]);
        }
        i++;
      }
      return param;
    }
  }
}

3.3、参数值的获取：# 与 $ 的区别

#{}：可以获取 map 中的值或者 pojo 对象属性的值；
${}：可以获取 map 中的值或者 pojo 对象属性的值；

select * from tbl_employee where id=${id} and last_name=#{lastName}

查询结果：

DEBUG 12-24 10:11:13,514 ==>  Preparing: select * from tbl_employee where id = 2 and last_name = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:11:13,558 ==> Parameters: null  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:11:13,584 <==      Total: 0  (BaseJdbcLogger.java:137) 
null

区别：

#{}：是以预编译的形式，将参数设置到 sql 语句中；PreparedStatement；防止 sql 注入
${}：取出的值直接拼装在 sql 语句中；会有安全问题；

大多情况下，我们去参数的值都应该去使用 #{}；

原生 jdbc 不支持占位符的地方我们就可以使用 ${} 进行取值：比如分表、排序…；按照年份分表拆分：

select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} ${order}

#{}：更丰富的用法，

规定参数的一些规则：

javaType、 jdbcType、 mode（存储过程）、 numericScale、
resultMap、 typeHandler、 jdbcTypeName、 expression（未来准备支持的功能）；

jdbcType 通常需要在某种特定的条件下被设置：在我们数据为 null 的时候，有些数据库可能不能识别 mybatis 对 null 的默认处理。比如 Oracle（报错）；

JdbcType OTHER：无效的类型；因为 mybatis 对所有的 null 都映射的是原生 Jdbc 的 OTHER 类型，oracle 不能正确处理;

由于全局配置中：jdbcTypeForNull=OTHER；oracle 不支持；两种办法：

#{email,jdbcType=OTHER};
jdbcTypeForNull=NULL

<setting name="jdbcTypeForNull" value="NULL"/>

4、Select 返回 List

数据库中的数据：

mysql> select * from tbl_employee;
+----+-----------+--------+---------------+
| id | last_name | gender | email         |
+----+-----------+--------+---------------+
|  1 | jerry2    | 0      | jerry3@qq.com |
|  2 | jerry1    | 1      | jerry1@qq.com |
|  3 | jerry     | 1      | jerry@qq.com  |
+----+-----------+--------+---------------+
3 rows in set (0.00 sec)

定义接口中的查询方法：

public interface EmployeeMapper {
    public List<Employee> getEmpsByLastNameLike(String lastname);

定义 sql 映射文件中的查询语句：

<!--
    public List<Employee> getEmpsByLastNameLike(String lastname);
        resultType：如果返回的是一个集合，要写集合中元素的类型
-->
<select id="getEmpsByLastNameLike" resultType="mybatis.bean.Employee">
    select * from tbl_employee where last_name like #{lastName}
</select>

测试代码：

@Test
public void test9() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        List<Employee> empsByLastNameLike = mapper.getEmpsByLastNameLike("%e%");
        for (Employee e :
             empsByLastNameLike   ) {
            System.out.println(e);
        }

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-24 10:38:41,001 ==>  Preparing: select * from tbl_employee where last_name like ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:38:41,035 ==> Parameters: %e%(String)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:38:41,150 <==      Total: 3  (BaseJdbcLogger.java:137) 
Employee{id=1, lastname='jerry2', email='jerry3@qq.com', gender='0'}
Employee{id=2, lastname='jerry1', email='jerry1@qq.com', gender='1'}
Employee{id=3, lastname='jerry', email='jerry@qq.com', gender='1'}

如果返回的是一个集合，只需要写集合中元素的类型即可！

5、Select 记录封装 Map

定义接口中的查询方法：

public interface EmployeeMapper {
    //返回一条记录的map：key就是列名，值就是对应的值
    public Map<String, Object> getEmpByIdReturnMap(Integer id);

定义 sql 映射文件中的查询语句：

<!--
    public Map<String, Object> getEmpByIdReturnMap(Integer id);
-->
<select id="getEmpByIdReturnMap" resultType="map">
    select * from tbl_employee where id = #{id}
</select>

测试代码：

@Test
public void test10() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        Map<String, Object> empByIdReturnMap = mapper.getEmpByIdReturnMap(1);
        System.out.println(empByIdReturnMap);


    }finally {
        sqlSession.close();
    }
}

DEBUG 12-24 10:54:45,092 ==>  Preparing: select * from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:54:45,125 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 10:54:45,149 <==      Total: 1  (BaseJdbcLogger.java:137) 
{gender=0, last_name=jerry2, id=1, email=jerry3@qq.com}	//HashMap 是无序的

如果想封装多条记录：

public interface EmployeeMapper {
    //多条记录封装一个map：Map<Integer, Employee>，键是这条记录的主键，值是记录封装后的javaBean
    @MapKey("id")   //告诉mybatis，封装这个map的时候使用哪个属性作为主键
    public Map<Integer, Employee> getEmpByLastNameLikeRetrunMap(String lastName);

<!--
    public Map<Integer, Employee> getEmpByLastNameLikeRetrunMap(String lastName);
-->
<select id="getEmpByLastNameLikeRetrunMap" resultType="mybatis.bean.Employee">
    select * from tbl_employee where last_name like #{lastName}
</select>

@Test
public void test11() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        Map<Integer, Employee> empByLastNameLikeRetrunMap = mapper.getEmpByLastNameLikeRetrunMap("%r%");
        System.out.println(empByLastNameLikeRetrunMap);


    }finally {
        sqlSession.close();
    }
}

DEBUG 12-24 11:01:43,224 ==>  Preparing: select * from tbl_employee where last_name like ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:01:43,256 ==> Parameters: %r%(String)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:01:43,277 <==      Total: 3  (BaseJdbcLogger.java:137) 
{1=Employee{id=1, lastname='jerry2', email='jerry3@qq.com', gender='0'}, 2=Employee{id=2, lastname='jerry1', email='jerry1@qq.com', gender='1'}, 3=Employee{id=3, lastname='jerry', email='jerry@qq.com', gender='1'}}

如果想把 lastName 作为 key：

@MapKey("lastname")
public Map<String, Employee> getEmpByLastNameLikeRetrunMap(String lastName);

DEBUG 12-24 11:06:00,448 ==>  Preparing: select * from tbl_employee where last_name like ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:06:00,482 ==> Parameters: %r%(String)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:06:00,503 <==      Total: 3  (BaseJdbcLogger.java:137) 
{jerry2=Employee{id=1, lastname='jerry2', email='jerry3@qq.com', gender='0'}, jerry1=Employee{id=2, lastname='jerry1', email='jerry1@qq.com', gender='1'}, jerry=Employee{id=3, lastname='jerry', email='jerry@qq.com', gender='1'}}

6、Select 中 resultMap 属性：自定义结果映射查询

主要解决的问题是：数据库中的列名与类中属性名不一致，不使用别名，也无法通过驼峰命名解决。就可以自定义映射对应。

新建一个接口 EmployeePlus：

public interface EmployeePlus {
    public Employee getEmpById(Integer id);
}

新建一个 sql 映射文件 EmployeeMapperPlus.xml：

<?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="mybatis.dao.EmployeeMapperPlus">

    <!--
        自定义某个javabean的封装规则
            type：自定义规则的Java类型
            id：唯一标识，方便引用
    -->
    <resultMap type="mybatis.bean.Employee" id="MyEmp">
        <!--
            指定主键列的封装规则
            id标签：定义主键会有底层优化
                column：指定哪一列
                property：指定对应的javaBean的属性
        -->
        <id column="id" property="id"></id>
        <!--
            定义普通列封装规则
        -->
        <result column="last_name" property="lastName"></result>
        <!--
            其他不指定的列会自动封装：我们只要写resultMap就把全部的映射规则全写上
        -->
        <result column="email" property="email"></result>
        <result column="gender" property="gender"></result>
    </resultMap>

    <!--
        public Employee getEmpById(Integer id);
            emp:是别名
    -->
    <!--使用resultType-->
    <!--<select id="getEmpById" resultType="emp">
        select * from tbl_employee where id = #{id}
    </select>-->
    <!--resultMap：自定义结果集映射规则-->
    <select id="getEmpById" resultMap="MyEmp">
        select * from tbl_employee where id = #{id}
    </select>

</mapper>

测试代码：

public class MyBatisPlus {

    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);

    }

    @Test
    public void test1() throws IOException {
        //1、获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

        //2、获取sqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee empById = mapper.getEmpById(1);
            System.out.println(empById);

        }finally {
            sqlSession.close();
        }
    }
}

DEBUG 12-24 11:36:01,243 ==>  Preparing: select * from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:36:01,293 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 11:36:01,328 <==      Total: 1  (BaseJdbcLogger.java:137) 
Employee{id=1, lastname='jerry2', email='jerry3@qq.com', gender='0'}

7、Select 中 resultMap 标签的关联查询：级联查询

7.1、环境准备

在 Employee 类中添加 Department 属性：

public class Employee {
    private Integer id;
    private String lastName;//此处应注意，lastname和数据库中的字段last_name不一致，故意设置的错误
    private String email;
    private String gender;
    private Department dept;

    public Employee() {
    }

    public Employee(Integer id, String lastName, String email, String gender, Department dept) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
        this.dept = dept;
    }

    public Employee(Integer id, String lastName, String email, String gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Department getDept() {
        return dept;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                ", dept=" + dept +
                '}';
    }
    ...

同时创建 Department 类：

public class Department {
    private Integer id;
    private String departmentName;

    public Integer getId() {
        return id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }
}

创建 tbl_department 表，并与 employee 关联：

CREATE TABLE tbl_dept(
	id INT(11) PRIMARY KEY auto_increment,
	dept_name VARCHAR(255)
);

alter TABLE tbl_employee add COLUMN d_id INT(11);

alter TABLE tbl_employee add CONSISTENT fk_emp_dept
FOREIGN KEY (d_id) REFERENCES tbl_dept(id);

mysql> select * from tbl_employee;
+----+-----------+--------+---------------+------+
| id | last_name | gender | email         | d_id |
+----+-----------+--------+---------------+------+
|  1 | jerry2    | 0      | jerry3@qq.com |    1 |
|  2 | jerry1    | 1      | jerry1@qq.com |    2 |
|  3 | jerry     | 1      | jerry@qq.com  |    1 |
+----+-----------+--------+---------------+------+
3 rows in set (0.00 sec)

mysql> select * from tbl_dept;
+----+-----------+
| id | dept_name |
+----+-----------+
|  1 | 开发部    |
|  2 | 测试部    |
+----+-----------+
2 rows in set (0.00 sec)

7.2、级联属性封装结果

在接口中定义查询方法：

public interface EmployeeMapperPlus {
    public Employee getEmpAndDept(Integer id);

在 sql 映射文件中定义语句：

<!--***********************resultMap级联操作***********************-->
    <!--**************级联属性**************-->
    <!--
    场景一：
        将查询Employee的同时查询员工对应的部门
        Employee===Department
        一个员工有与之对应的部门信息：
        id、last_name、gender、d_id、did(private Department department;)、dept_name(private Department department;)
    -->

    <!--级联查询：级联属性封装结果集-->
    <resultMap type="mybatis.bean.Employee" id="MyDifEmp">
        <id column="id" property="id"></id>
        <result column="last_name" property="lastName"></result>
        <result column="gender" property="gender"></result>
        <result column="did" property="dept.id"></result>
        <result column="dept_name" property="dept.departmentName"></result>
    </resultMap>

    <!--
        public Employee getEmpAndDept(Integer id);
    -->
    <select id="getEmpAndDept" resultMap="MyDifEmp">
        SELECT e.id id, e.last_name last_name, e.gender gender, e.d_id d_id, d.id did, d.dept_name dept_name
        FROM tbl_employee e, tbl_dept d
        WHERE e.d_id = d.id AND e.id = #{id};
    </select>
    <!--**************级联属性**************-->
<!--***********************resultMap级联操作***********************-->

测试代码：

@Test
public void test2() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);

        Employee empAndDept = mapper.getEmpAndDept(1);
        System.out.println(empAndDept);
        System.out.println(empAndDept.getDept());

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-24 17:10:39,622 ==>  Preparing: SELECT e.id id, e.last_name last_name, e.gender gender, e.d_id d_id, d.id did, d.dept_name dept_name FROM tbl_employee e, tbl_dept d WHERE e.d_id = d.id AND e.id = ?;  (BaseJdbcLogger.java:137) 
DEBUG 12-24 17:10:39,679 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-24 17:10:39,707 <==      Total: 1  (BaseJdbcLogger.java:137) 
Employee{id=1, lastname='jerry2', email='null', gender='0'}
Department{id=1, departmentName='开发部'}

7.3、association 定义关联对象封装规则

<!--association 定义关联对象封装规则-->
<resultMap type="mybatis.bean.Employee" id="MyDifEmp2">
    <id column="id" property="id"></id>
    <result column="last_name" property="lastName"></result>
    <result column="gender" property="gender"></result>

    <!--
          association标签可以指定联合的javaBean对象
              property属性：指定哪个属性是联合对象
              javaType属性：指定这个属性对象的类型
    -->
    <association property="dept" javaType="mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
    </association>

</resultMap>


<!--
      public Employee getEmpAndDept(Integer id);
-->
<select id="getEmpAndDept" resultMap="MyDifEmp2">
    SELECT e.id id, e.last_name last_name, e.gender gender, e.d_id d_id, d.id did, d.dept_name dept_name
    FROM tbl_employee e, tbl_dept d
    WHERE e.d_id = d.id AND e.id = #{id};
</select>

7.4、association 分步查询

实现的就是 sql 里边的分步查询。

首先给 Deptment 类创建 DepartmentMapper 接口和 DepartmentMapper.xml 配置文件：

package mybatis.dao;

import mybatis.bean.Department;

public interface DepartmentMapper {
    public Department getDeptById(Integer 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="mybatis.dao.DepartmentMapper">
    <!--
        public Department getDeptById(Integer id);
    -->
    <select id="getDeptById" resultType="mybatis.bean.Department">
        select id, dept_name departmentName from tbl_dept where id = #{id}
    </select>
</mapper>

在 EmployeeMapperPlus 接口中定义分步查询的方法：

public interface EmployeeMapperPlus {
    public Employee getEmpByIdStep(Integer id);

在 EmployeeMapperPlus.xml 中定义 sql 查询：

<!--
    使用association进行分步查询：
        1、先按照员工id查询员工信息；
        2、根据查询员工信息中的d_id值去部门表查出部门信息；
        3、部门设置到员工中；
-->
<resultMap id="MyEmpByStep" type="mybatis.bean.Employee">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"></result>
    <result column="gender" property="gender"></result>
    <result column="email" property="email"/>

    <!--
        association定义关联对象的封装规则
            select：表明当前属性是调用select指定的方法查出的结果
            column：指定将哪一列的值传给这个方法

            流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性
    -->
    <association property="dept" select="mybatis.dao.DepartmentMapper.getDeptById"
    column="d_id">

    </association>
</resultMap>
<!--public Employee getEmpByIdStep(Integer id);-->
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
    select * from tbl_employee where id = #{id}
</select>

测试方法：

@Test
public void test3() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);

        Employee empByIdStep = mapper.getEmpByIdStep(1);
        System.out.println(empByIdStep);
        System.out.println(empByIdStep.getLastName());
        System.out.println(empByIdStep.getDept());

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-26 16:11:08,757 ==>  Preparing: select * from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:11:08,803 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:11:08,828 ====>  Preparing: select id, dept_name departmentName from tbl_dept where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:11:08,829 ====> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:11:08,832 <====      Total: 1  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:11:08,833 <==      Total: 1  (BaseJdbcLogger.java:137) 
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=Department{id=1, departmentName='开发部'}}
jerry2
Department{id=1, departmentName='开发部'}

在结果中可以看到进行了两个查询。

7.5、association 分步查询——延迟查询

<!--
    可以使用延迟加载：
        Employee ==> Dept：
            我们每次查询Employee对象的时候，都将一起查询出来。
            部门信息在我们使用的时候再去查询。
            分段查询的基础之上加上两个配置：
                lazyLoadingEnabled和aggressiveLazyLoading
-->

在 mybatis-config.xml 中配置：

<!--显式地指定我们需要更改的配置的值，即使是默认的。防止更新带来的问题-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

测试代码：

@Test
public void test3() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);

        Employee empByIdStep = mapper.getEmpByIdStep(1);
        //System.out.println(empByIdStep);
        System.out.println(empByIdStep.getLastName());
        //System.out.println(empByIdStep.getDept());

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-26 16:18:29,370 ==>  Preparing: select * from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:18:29,425 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:18:29,525 <==      Total: 1  (BaseJdbcLogger.java:137) 
jerry2

我们只打印有关 employee 的信息。可以看到只进行了一次查询。

@Test
public void test3() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        EmployeeMapperPlus mapper = sqlSession.getMapper(EmployeeMapperPlus.class);

        Employee empByIdStep = mapper.getEmpByIdStep(1);
        //System.out.println(empByIdStep);
        System.out.println(empByIdStep.getLastName());
        //System.out.println(empByIdStep.getDept());

    }finally {
        sqlSession.close();
    }
}

DEBUG 12-26 16:21:03,211 ==>  Preparing: select * from tbl_employee where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:21:03,272 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:21:03,362 <==      Total: 1  (BaseJdbcLogger.java:137) 
jerry2
DEBUG 12-26 16:21:03,364 ==>  Preparing: select id, dept_name departmentName from tbl_dept where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:21:03,366 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-26 16:21:03,369 <==      Total: 1  (BaseJdbcLogger.java:137) 
Department{id=1, departmentName='开发部'}

开启有关 Department 的查询，结果就看到有两次查询。

7.6、collection 定义关联集合封装规则

主要实现一对多的模型关系查询。

<!--
    场景二：
        查询部门的时候将部门对应的所有员工信息也查询出来
-->

定义 Department 里边包含一个 emps 的 List 集合，即：一个部门包含多个员工

public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps;//定义Department里边包含一个emps的list集合，即：一个部门包含多个员工
    ...

在 DepartmentMapper 接口中定义查询方法：

public interface DepartmentMapper {
    public Department getDeptByIdPlus(Integer id);

在 DepartmentMapper.xml 中定义 sql 语句实现：

<!--
    collection嵌套结果集的方式，定义关联的集合类型元素的封装规则
-->
<!--
    public class Department {
        private Integer id;
        private String departmentName;
        private List<Employee> emps;
    did dept_name || eid last_name email gender（员工的信息）
-->
<resultMap id="MyDept" type="mybatis.bean.Department">
    <id column="did" property="id"></id>
    <result column="dept_name" property="departmentName"></result>

    <!--
        collection 定义关联集合属性的封装规则：
            ofType属性：指定集合里面元素的类型
    -->
    <collection property="emps" ofType="mybatis.bean.Employee">
        <!-- 定义集合中元素的封装规则 -->
        <id column="eid" property="id"></id>
        <result column="last_name" property="lastName"></result>
        <result column="gender" property="gender"></result>
        <result column="email" property="email"/>
    </collection>
</resultMap>
<!--
    public Department getDeptByIdPlus(Integer id);
-->
<select id="getDeptByIdPlus" resultMap="MyDept">
    select d.id did, d.dept_name dept_name,
           e.id eid, e.last_name last_name, e.email email, e.gender gender
    from tbl_dept d
    left join tbl_employee e
    on d.id = e.d_id
    where d.id = 1
</select>

测试代码：

@Test
public void test4() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);

        Department deptByIdPlus = mapper.getDeptByIdPlus(1);
        System.out.println(deptByIdPlus);
        System.out.println(deptByIdPlus.getEmps());


    }finally {
        sqlSession.close();
    }
}

DEBUG 12-27 16:03:35,791 ==>  Preparing: select d.id did, d.dept_name dept_name, e.id eid, e.last_name last_name, e.email email, e.gender gender from tbl_dept d left join tbl_employee e on d.id = e.d_id where d.id = 1  (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:03:35,839 ==> Parameters:   (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:03:35,866 <==      Total: 2  (BaseJdbcLogger.java:137) 
Department{id=1, departmentName='开发部'}
[Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}, Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}]

7.7、collection 分步查询 & 延迟查询

在接口 DepartmentMapper 中定义方法：

public interface DepartmentMapper {
    public Department getDeptByIdStep(Integer id);

在接口 EmployeeMapperPlus 中定义方法：

public interface EmployeeMapperPlus {
    public List<Employee> getEmpByDeptId(Integer deptId);

在 EmployeeMapperPlus.xml 中实现方法与 sql 的绑定：

<!-- public List<Employee> getEmpByDeptId(Integer deptId);   -->
<select id="getEmpByDeptId" resultType="mybatis.bean.Employee">
    select * from tbl_employee where d_id=#{deptId}
</select>

在 DepartmentMapper.xml 中实现方法与 sql 的绑定：

<resultMap id="MyDeptStep" type="mybatis.bean.Department">
    <id column="id" property="id"/>
    <id column="dept_name" property="departmentName" ></id>

    <collection property="emps" select="mybatis.dao.EmployeeMapperPlus.getEmpByDeptId"
                column="id">
    </collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
    select id,dept_name from tbl_dept where id=#{id}
</select>

测试代码：

@Test
public void test5() throws IOException {
    //1、获取sqlSessionFactory对象
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    //2、获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    try {
        //3、获取接口的实现类对象
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);

        Department deptByIdStep = mapper.getDeptByIdStep(1);
        System.out.println(deptByIdStep.getDepartmentName());
        System.out.println(deptByIdStep.getEmps());


    }finally {
        sqlSession.close();
    }
}

DEBUG 12-27 16:26:35,867 ==>  Preparing: select id,dept_name from tbl_dept where id=?  (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:26:35,907 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:26:35,994 <==      Total: 1  (BaseJdbcLogger.java:137) 
开发部
DEBUG 12-27 16:26:35,996 ==>  Preparing: select * from tbl_employee where d_id=?  (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:26:35,997 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 12-27 16:26:36,000 <==      Total: 2  (BaseJdbcLogger.java:137) 
[Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}, Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}]

7.8、select 中 resultMap 标签分步查询传递多列值

<!--  扩展：多列的值传递过去：
        将多列的值封装map传递：
        column="{key1=column1,key2=column2}"}

        fetchType="lazy"：表示使用延迟加载；
            - lazy：延迟
            - eager：立即
-->

<resultMap id="MyDeptStep" type="mybatis.bean.Department">
    <id column="id" property="id"/>
    <id column="dept_name" property="departmentName" ></id>

    <collection property="emps" select="mybatis.dao.EmployeeMapperPlus.getEmpByDeptId"
                column="{deptId=id}" fetchType="lazy">
    </collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
    select id,dept_name from tbl_dept where id=#{id}
</select>

7.9、discriminator 鉴别器

<!-- =======================鉴别器============================ -->
<!-- 鉴别器：mybatis可以使用discriminator判断某列的值，然后根据某列的值改变封装行为
     封装Employee：
        如果查出的是女生，就把部门信息查询出来，否则不要；
        如果是男生，把last_name这一列的值赋值给email
-->
<resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmpDis">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <!--
        column：指定判定的列名
        javaType：列值对应的java类型  -->
    <discriminator javaType="string" column="gender">
        <!--女生  resultType:指定封装的结果类型；不能缺少。/resultMap-->
        <case value="0" resultType="mybatis.bean.Employee">
            <association property="dept"
                         select="mybatis.dao.DepartmentMapper.getDeptById"
                         column="d_id">
            </association>
        </case>
        <!--男生 ;如果是男生，把last_name这一列的值赋值给email; -->
        <case value="1" resultType="mybatis.bean.Employee">
            <id column="id" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="last_name" property="email"/>
            <result column="gender" property="gender"/>
        </case>
    </discriminator>
</resultMap>