一、MyBatis简介

  1. MyBatis历史

    MyBatis最初是Apache的一个开源项目iBatis, 2010年6月这个项目由Apache Software Foundation迁 移到Google Code。随着开发团队转投Google Code旗下， iBatis3.x正式更名为MyBatis。
    代码于 2013年11月迁移到Github。 iBatis一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。 iBatis提供的持久层框架 包括SQL Maps和Data Access Objects（DAO）。

  2. MyBatis特性

    1） MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架
    2） MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集
    3） MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录
    4） MyBatis 是一个 半自动的ORM（Object Relation Mapping）框架

  3. MyBatis下载

https://github.com/mybatis/mybatis-3
image.png

二、搭建MyBatis

  1. 开发环境

IDE：IntelliJ IDEA 2020.2.4 (Ultimate Edition)
构建工具：apache-maven 3.6.3
MySQL版本：8.0.26(远程服务器)
MyBatis版本：3.5.7

  1. 创建maven工程

    1. 打包方式：jar
    2. 引入依赖 ```properties
    org.mybatis mybatis 3.5.7 junit junit 4.12 test mysql mysql-connector-java 8.0.26 

  1. <br />
  3. 3. **创建MyBatis核心配置文件**
  5. ![image.png](https://cdn.nlark.com/yuque/0/2022/png/28229185/1651660530377-0fa2090e-b397-46da-959c-b8300a008b2a.png#clientId=ua6177b53-cc8e-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=329&id=ude2fe65b&margin=%5Bobject%20Object%5D&name=image.png&originHeight=1030&originWidth=1920&originalType=binary&ratio=1&rotation=0&showTitle=false&size=210778&status=done&style=none&taskId=ub1626db1-6368-4b31-87ee-5ed2bac519e&title=&width=614)
  6. ```xml
  7. <?xml version="1.0" encoding="UTF-8" ?>
  8. <!DOCTYPE configuration
  9.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  10.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  11. <configuration>
  13.   <!-- 配置数据库连接环境-->
  14.   <environments default="development">
  15.     <environment id="development">
  16.       <transactionManager type="JDBC"/>
  17.       <dataSource type="POOLED">
  18.         <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
  19.         <property name="url" value="jdbc:mysql://zhouge.xyz:3306/db_mybatis"/>
  20.         <property name="username" value="admin"/>
  21.         <property name="password" value="991227zg@."/>
  22.       </dataSource>
  23.     </environment>
  24.   </environments>
  26.   <!--引入映射文件-->
  27.   <mappers>
  28.     <mapper resource="mappers/UserMapper.xml"/>
  29.   </mappers>
  31. </configuration>


  1. 创建mapper接口

image.png

  1. public interface UserMapper {
  3.     int insertUser();
  5. }


  1. 创建MyBatis映射文件

image.png

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <!--
  6.     两个一致：
  7.         1、namespace需要和映射文件的所在的全类名保持一致
  8.         2、id需要和执行的方法名保持一致
  9. -->
  10. <mapper namespace="xyz.zhouge.mybatis.mapper.UserMapper">
  12.     <insert id="insertUser">
  13.         insert into tb_user values(null,'喻雅婷1','123456789',20,'女','1974388746@qq.com')
  14.     </insert>
  16. </mapper>


  1. 加入junit测试功能

image.png

  1. @Test
  2. public void testMyBatis(){
  4.         SqlSession sqlSession = null ;
  6.         try {
  7.             //1、加载核心配置文件
  8.             InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
  10.             //2、获取 SqlSessionFactoryBuilder
  11.             SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
  13.             //3、获取 SqlSessionFactory
  14.             SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
  16.             //4、获取SqlSession
  17.             sqlSession = sqlSessionFactory.openSession(); //ctrl + p  openSession 默认不自动提交事务
  19.             //5、获取Mapper对象
  20.             UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  22.             //6、调用方法
  23.             int res = userMapper.insertUser();
  25.             if(res > 0){
  26.                 System.out.println("添加成功！");
  28.                 //7、 提交事务
  29.                 sqlSession.commit();
  30.             }else{
  31.                 System.out.println("添加失败！");
  33.                 //8、 失败则回滚事务
  34.                 sqlSession.rollback();
  35.             }
  37.         } catch (IOException e) {
  38.             e.printStackTrace();
  39.         }finally {
  41.             if(sqlSession != null){
  42.                 //9、 关闭连接会话
  43.                 sqlSession.close();
  44.             }
  46.         }
  48. }

  1. 加入log4j日志功能

    1. 引入依赖

      1. <!-- log4j日志 -->
      2.   <dependency>
      3.       <groupId>log4j</groupId>
      4.       <artifactId>log4j</artifactId>
      5.       <version>1.2.17</version>
      6.   </dependency>

    2. 加入log4j的配置文件

image.png

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
  3. <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  5.   <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
  6.     <param name="Encoding" value="UTF-8" />
  8.     <layout class="org.apache.log4j.PatternLayout">
  9.       <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
  10.     </layout>
  12.   </appender>
  14.   <logger name="java.sql">
  15.     <level value="debug" />
  16.   </logger>
  18.   <logger name="org.apache.ibatis">
  19.     <level value="info" />
  20.   </logger>
  22.   <root>
  23.     <level value="debug" />
  24.     <appender-ref ref="STDOUT" />
  25.   </root>
  27. </log4j:configuration>

三、核心配置文件详解

  1. environment

    1. transactionManager：事务管理器
      1. type 可取值 JDBC 或 MANAGED
        1. jdbc 意味着事物的提交与回滚需要手动配置
        2. manged 意味着事务的提交与回滚交给其他的管理器来管理
    2. 数据源
      1. type 可取值POOLED UNPOOLED JNDI
        1. POOLED 意味着 数据的连接使用数据连接池技术
        2. UNPOOLED 意味着 不适用数据连接池技术
        3. JNDI 意味着 使用上下文中的数据源
          1. <environment id="test">
          2. <transactionManager type="JDBC"/>
          3. <dataSource type="POOLED">
          4.     <property name="driver" value="${jdbc.Driver}"/>
          5.     <property name="url" value="${jdbc.url}"/>
          6.     <property name="username" value="${jdbc.username}"/>
          7.     <property name="password" value="${jdbc.password}"/>
          8. </dataSource>
          9. </environment>

  2. properties

注意：需要在配置文件中 使用properties 标签引入jdbc.properties配置文件
image.png

  1. jdbc.Driver=com.mysql.cj.jdbc.Driver
  2. jdbc.url=jdbc:mysql://zhouge.xyz:3306/db_mybatis
  3. jdbc.username=admin
  4. jdbc.password=991227zg@.
  1. typeAliases

image.png

  1. <!--设置类型别名 type必须写，alias可写可不写且不区分大小写-->
  2.     <typeAliases>
  3.         <typeAlias type="xyz.zhouge.mybatis.bean.User" alias="User"/>
  4.     </typeAliases>
  1. mappers

image.png

  1. <!--引入映射文件-->
  2.     <mappers>
  4.         <!--<mapper resource="mappers/UserMapper.xml"/>-->
  6.         <!--
  7.             以包的方式引入mapper映射文件，要求如下：
  8.                 1、包路径一致
  9.                 2、mapper接口要和映射文件的名字一致
  10.         -->
  11.         <package name="xyz.zhouge.mybatis.mapper"/>
  13.     </mappers>

四、MyBatis的增删改查

  1. 添加数据

    1. @Test
    2. public void testCURD(){
    3.  SqlSession sqlSession = null;
    4.  try {
    5.      InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    6.      SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    7.      SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
    8.      sqlSession = sqlSessionFactory.openSession(true);
    9.      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    11.      int res = userMapper.insertUser();
    12.      //int res = userMapper.updateUser();
    13.      //int res = userMapper.deleteUserById();
    15.      if (res > 0){
    16.          System.out.println("执行成功");
    17.      }else{
    18.          System.out.println("执行失败");
    19.      }
    20.  } catch (IOException e) {
    22.      if(sqlSession!=null){
    23.          sqlSession.rollback();
    24.      }
    26.      e.printStackTrace();
    27.  }finally {
    28.      if(sqlSession!=null){
    29.          sqlSession.close();
    30.      }
    31.  }
    32. }

  2. 修改数据

    1. @Test
    2. public void testCURD(){
    3.  SqlSession sqlSession = null;
    4.  try {
    5.      InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    6.      SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    7.      SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
    8.      sqlSession = sqlSessionFactory.openSession(true);
    9.      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    11.      //int res = userMapper.insertUser();
    12.      int res = userMapper.updateUser();
    13.      //int res = userMapper.deleteUserById();
    15.      if (res > 0){
    16.          System.out.println("执行成功");
    17.      }else{
    18.          System.out.println("执行失败");
    19.      }
    20.  } catch (IOException e) {
    22.      if(sqlSession!=null){
    23.          sqlSession.rollback();
    24.      }
    26.      e.printStackTrace();
    27.  }finally {
    28.      if(sqlSession!=null){
    29.          sqlSession.close();
    30.      }
    31.  }
    32.  }

  3. 删除数据

    1. @Test
    2. public void testCURD(){
    3.  SqlSession sqlSession = null;
    4.  try {
    5.      InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    6.      SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    7.      SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
    8.      sqlSession = sqlSessionFactory.openSession(true);
    9.      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    11.      //int res = userMapper.insertUser();
    12.      //int res = userMapper.updateUser();
    13.      int res = userMapper.deleteUserById();
    15.      if (res > 0){
    16.          System.out.println("执行成功");
    17.      }else{
    18.          System.out.println("执行失败");
    19.      }
    20.  } catch (IOException e) {
    22.      if(sqlSession!=null){
    23.          sqlSession.rollback();
    24.      }
    26.      e.printStackTrace();
    27.  }finally {
    28.      if(sqlSession!=null){
    29.          sqlSession.close();
    30.      }
    31.  }
    32.  }

  4. 查询数据

    1. @Test
    2.  public void testSelect(){
    3.      SqlSession sqlSession = null ;
    4.      try {
    5.          InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    6.          SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    7.          SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
    8.          sqlSession = sqlSessionFactory.openSession(true);
    9.          UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    11.          //User user = userMapper.findUserById();
    12.          //System.out.println(user);
    13.          List<User> users = userMapper.findAllUser();
    14.          users.forEach(user -> System.out.println(user));
    16.      } catch (IOException e) {
    17.          e.printStackTrace();
    18.      }finally {
    19.          if(sqlSession!=null){
    20.              sqlSession.close();
    21.          }
    22.      }
    23.  }

  5. 封装SqlSession

    1. public static SqlSession getSqlSession(){
    3.      SqlSession sqlSession = null;
    4.      try {
    5.          InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    6.          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    7.          sqlSession = sqlSessionFactory.openSession(true);
    8.      } catch (IOException e) {
    9.          e.printStackTrace();
    10.      }
    12.      return sqlSession;
    13.  }

五、MyBatis获取参数值的两种方式（重点）

MyBatis获取参数值的两种方式：${ } 和 #{ } 。${ } 本质是字符串拼接 ； #{ } 本质是占位符赋值
${}使用字符串拼接的方式拼接sql，若为字符串类型或日期类型的字段进行赋值时，需要手动加单引 号；但是#{}使用占位符赋值的方式拼接sql，此时为字符串类型或日期类型的字段进行赋值时，可以自 动添加单引号

  1. 单个字面量类型的参数

    1. 添加mapper接口 ```java public interface ParameterMapper {

      List getAllUser();

      User getUserByUsername(String username);

}

  2.    2. 设置mapper映射
  3. ```java
  4.     <!--User getUserByUsername(String username);-->
  5.     <select id="getUserByUsername" resultType="user">
  6.         <!--select * from tb_user where username = #{username}-->
  7.         select * from tb_user where username = '${username}'
  8.     </select>
  1. 设置传值方式
  2. 测试
    1. @Test
    2. public void testGetUserByUsername(){
    3.   ParameterMapper mapper = SqlSessionUtil.getSqlSession().getMapper(ParameterMapper.class);
    4.   User user = mapper.getUserByUsername("喻雅婷");
    5.   System.out.println(user);
    6. }
  1. 多个字面量类型的参数

当mapper接口方法的参数为多个时，mybatis会将这些参数存放在一个map集合中，以两种方式进行存放：
以arg0，arg1，arg2…为键，以参数值为值进行存放
以param1，param2，param3…为键，以参数值为值进行存放

  1. <!--User checkLogin(String username , String password);-->
  2.     <select id="checkLogin" resultType="user">
  3.         <!--select * from tb_user where username = #{arg0} and password = #{arg1}-->
  4.         select * from tb_user where username = '${param1}' and password = '${param2}';
  5.     </select>
  1. @Test
  2.     public void testCheckLogin(){
  3.         ParameterMapper mapper = SqlSessionUtil.getSqlSession().getMapper(ParameterMapper.class);
  4.         User user = mapper.checkLogin("喻雅婷","123456789");
  5.         System.out.println(user);
  6.     }
  1. map集合类型的参数

思路：将参数封装在map集合中，在配置文件里通过访问map的键的方式来获取值

  1. User checkLoginByMap(Map<String,Object> map);
  3. <!--User checkLoginByMap(String username , String password);-->
  4. <select id="checkLoginByMap" resultType="user">
  5.     select * from tb_user where username = #{username} and password = #{password}
  6. </select>
  8. @Test
  9.     public void testCheckLoginByMap(){
  10.         ParameterMapper mapper = SqlSessionUtil.getSqlSession().getMapper(ParameterMapper.class);
  11.         Map<String,Object> map = new HashMap<>();
  12.         map.put("username","喻雅婷");
  13.         map.put("password","123456789");
  15.         //将参数封装在map集合中，在配置文件里通过访问map的键的方式来获取值
  16.         User user = mapper.checkLoginByMap(map);
  17.         System.out.println(user);
  18.     }
  1. 实体类类型的参数 ```java int insertUser(User user);

!—int insertUser(User user);—> insert into tb_user values(null,#{username},#{password},#{age},#{sex},#{email});

@Test public void testInsertUser(){ ParameterMapper mapper = SqlSessionUtil.getSqlSession().getMapper(ParameterMapper.class); int res = mapper.insertUser(new User(null, “张三”, “123456”, 18, “男”, “zhangsan@163.com”)); System.out.println(res); }

  2. 5. 使用@Param标识参数
  3. ```java
  4. User checkLoginByParam(@Param("username") String username , @Param("password") String password);
  6. <!--User checkLoginByParam(@Param("username") String username ,@Param("password") String password);-->
  7.     <select id="checkLoginByParam" resultType="user">
  8.         select * from tb_user where username = #{username} and password = #{password};
  9.     </select>
  11. @Test
  12.     public void testCheckLoginByParam(){
  13.         ParameterMapper mapper = SqlSessionUtil.getSqlSession().getMapper(ParameterMapper.class);
  14.         User user = mapper.checkLoginByParam("admin", "123456");
  15.         System.out.println(user);
  16.     }

总结：获取参数两大类情况
1、@Param 注解标识
2、实体类获取

六、MyBatis的各种查询功能

MyBatis的各种查询功能：
1、若查询出的数据只有一条，
a>可以通过实体类对象接收
b>可以通过list集合接收
c>可以通过map集合接收
2、若查询出的数据有多条
a>可以通过list集合接收
b>可以通过map集合接收

  1. 查询一个实体类对象 ```java User getUserById(@Param(“id”) int id);
  1. <select id="getUserById" resultType="user">
  2.     select * from tb_user where id = #{id};
  3. </select>

@Test public void testGetUserById(){ SelectMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SelectMapper.class); User user = mapper.getUserById(12); System.out.println(user); }

  2. 2. 查询一个list集合
  3. ```java
  4. List<User> getAllUser();
  6. <!--List<User> getAllUser();-->
  7.     <select id="getAllUser" resultType="user">
  8.         select * from tb_user;
  9.     </select>
  11. @Test
  12.     public void testGetAllUser(){
  13.         SelectMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SelectMapper.class);
  14.         List<User> users = mapper.getAllUser();
  15.         users.forEach(user -> System.out.println(user));
  16.     }
  1. 查询单个数据 ```java Integer getCount();
  1. <select id="getCount" resultType="java.lang.Integer">
  2.     select count(*) from tb_user;
  3. </select>

@Test public void testGetCount(){ SelectMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SelectMapper.class); Integer count = mapper.getCount(); System.out.println(count); }

  2. 4. 查询一条数据为map集合
  3. ```java
  4. Map<String,Object> getUserByIdToMap(@Param("id") Integer id);
  6. <!--Map<String,Object> getUserByIdToMap(@Param("id") Integer id);-->
  7.     <select id="getUserByIdToMap" resultType="map">
  8.         select * from tb_user where id = #{id};
  9.     </select>
  11. @Test
  12.     public void testGetUserByIdToMap(){
  13.         SelectMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SelectMapper.class);
  14.         Map<String, Object> user = mapper.getUserByIdToMap(12);
  15.         System.out.println(user);
  16.         /*{password=123456, sex=男, id=12, age=22, email=1974388746@qq.com, username=admin}*/
  17.     }
  1. 查询多条数据为map集合 ```java /List> getAllUserToMap();/

@MapKey(“id”) Map getAllUserToMap();

@Test public void testGetAllUserToMap(){ SelectMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SelectMapper.class); //List> users = mapper.getAllUserToMap(); //users.forEach(user-> System.out.println(user));

  1.   Map<String, Object> users = mapper.getAllUserToMap();
  2.   System.out.println(users);

}

  3. <a name="oGcG7"></a>
  4. #### 七、特殊SQL的执行
  6. 1. 模糊查询
  7. ```java
  8. User getUserByUsername(@Param("username") String username);
  10. <!--User getUserByUsername(@Param("username") String username);-->
  11.     <select id="getUserByUsername" resultType="user">
  12.         <!--select * from tb_user where username like '%#username}%';-->
  13.         <!--select * from tb_user where username like '%${username}%';-->
  14.         select * from tb_user where username like "%"#{username}"%"
  15.     </select>
  17. @Testc
  18.     public void testGetUserByUsername(){
  19.         SQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SQLMapper.class);
  20.         User user = mapper.getUserByUsername("admin");
  21.         System.out.println(user);
  22.     }
  1. 批量删除 ```java int deleteMore(@Param(“ids”) String ids);
  1. <delete id="deleteMore">
  2.     delete from tb_user where id in (${ids})
  3. </delete>

@Test public void testDeleteMore(){ SQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SQLMapper.class); int res = mapper.deleteMore(“11,23,50”); System.out.println(res); }

  2. 3. 动态设置表名
  3. ```java
  4. List<User> getAllUser(@Param("tableName") String tableName);
  6. <!--List<User> getAllUser(@Param("tableName") String tableName);-->
  7.     <select id="getAllUser" resultType="user">
  8.         select  * from ${tableName};
  9.     </select>
  11. @Test
  12.     public void testGetAllUser(){
  13.         SQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SQLMapper.class);
  14.         List<User> users = mapper.getAllUser("tb_user");
  15.         users.forEach(user -> System.out.println(user));
  16.     }
  1. 添加功能获取自增的主键 ```java int insertUser(User user);
  1. <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
  2.     insert into tb_user values(null,#{username},#{password},#{age},#{sex},#{email});
  3. </insert>

@Test public void testInsertUser(){ SQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(SQLMapper.class); User user = new User(null,”诸葛”,”123456”,23,”男”,”12456@163.com”); mapper.insertUser(user); System.out.println(user); }t

  2. <a name="onrsz"></a>
  3. #### 八、自定义映射resultMap
  4. 场景：当数据库的字段名与javabean中的属性名不一致时，使用resultMap
  7. 1. resultMap处理字段和属性的映射关系
  9. 解决方案一：别名
  10. ```java
  11. /**
  12. * 获取所有员工-通过别名的方式解决问题
  13. * @return
  14. */
  15. List<Employee> getAllEmployee();
  17. <!--List<Employee> getAllEmployee();-->
  18. <select id="getAllEmployee" resultType="Employee">
  19.         select emp_id as empId,emp_name as empName,emp_age as empAge,emp_sex as empSex,
  20.                emp_email as empEmail,emp_depId as empDeptId from tb_emp;
  21. </select>
  23. @Test
  24.     public void testGetAllEmployee(){
  25.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  26.         List<Employee> employees = mapper.getAllEmployee();
  28.         for (Employee employee : employees) {
  29.             System.out.println(employee);
  30.         }
  32.     }

解决方案二：设置全局配置的mapUnderscoreToCamelCase

  1. /**
  2. * 获取所有员工-通过全局配置mapUnderscoreToCamelCase解决问题
  3. * @return
  4. */
  5. List<Employee> getAllEmployee2();
  7. <!--List<Employee> getAllEmployee2();-->
  8. <select id="getAllEmployee2" resultType="Employee">
  9.     select * from tb_emp;
  10. </select>
  12. @Test
  13.     public void testGetAllEmployee2(){
  14.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  15.         List<Employee> employees = mapper.getAllEmployee2();
  17.         for (Employee employee : employees) {
  18.             System.out.println(employee);
  19.         }
  21.     }mapUnderscoreToCamelCase

解决方案三：在映射文件中设置resultMap

  1. /**
  2. * 获取所有员工-通过ResultMap解决问题
  3. * @return
  4. */
  5. List<Employee> getAllEmployee3();
  8. <!--
  9.         resultMap :当数据库的字段名与javabean中的属性名不一致时，使用resultMap
  10.             id：识别id curd中的唯一标识
  11.             type：映射的类型
  13.             id：数据库表中的主键
  14.                 column 数据库中的字段名
  15.                 property javabean中的属性名
  16.             result：普通字段
  17.                 column 数据库中的字段名
  18.                 property javabean中的属性名
  19.     -->
  20.     <resultMap id="employeeResultMap" type="xyz.zhouge.mybatis.bean.Employee">
  21.         <id column="emp_id" property="empId"/>
  22.         <result column="emp_name" property="empName"/>
  23.         <result column="emp_age" property="empAge"/>
  24.         <result column="emp_sex" property="empSex"/>
  25.         <result column="emp_email" property="empEmail"/>
  26.         <result column="emp_depId" property="empDeptId"/>
  27.     </resultMap>
  30. <!--List<Employee> getAllEmployee3();-->
  31. <select id="getAllEmployee3" resultMap="employeeResultMap">
  32.      select * from tb_emp;
  33. </select>
  35.  @Test
  36.     public void testGetAllEmployee3(){
  37.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  38.         List<Employee> employees = mapper.getAllEmployee3();
  40.         for (Employee employee : employees) {
  41.             System.out.println(employee);
  42.         }
  44.     }

  1. 多对一映射解决方案

    1. 级联方式处理映射关系 ```java public class Employee {

      private Integer empId; private String empName; private Integer empAge; private String empSex; private String empEmail; private Integer empDeptId; private Department department;

      @Override public String toString() { return “Employee{“ +

      1.       "empId=" + empId +
      2.       ", empName='" + empName + '\'' +
      3.       ", empAge=" + empAge +
      4.       ", empSex='" + empSex + '\'' +
      5.       ", empEmail='" + empEmail + '\'' +
      6.       ", empDeptId=" + empDeptId +
      7.       ", department=" + department +
      8.       '}';

      }

      public Employee() { }

      public Employee(Integer empId, String empName, Integer empAge, String empSex, String empEmail, Integer empDeptId) { this.empId = empId; this.empName = empName; this.empAge = empAge; this.empSex = empSex; this.empEmail = empEmail; this.empDeptId = empDeptId; }

      public Employee(Integer empId, String empName, Integer empAge, String empSex, String empEmail, Integer empDeptId, Department department) { this.empId = empId; this.empName = empName; this.empAge = empAge; this.empSex = empSex; this.empEmail = empEmail; this.empDeptId = empDeptId; this.department = department; }

      public Integer getEmpId() { return empId; }

      public void setEmpId(Integer empId) { this.empId = empId; }

      public String getEmpName() { return empName; }

      public void setEmpName(String empName) { this.empName = empName; }

      public Integer getEmpAge() { return empAge; }

      public void setEmpAge(Integer empAge) { this.empAge = empAge; }

      public String getEmpSex() { return empSex; }

      public void setEmpSex(String empSex) { this.empSex = empSex; }

      public String getEmpEmail() { return empEmail; }

      public void setEmpEmail(String empEmail) { this.empEmail = empEmail; }

      public Integer getEmpDeptId() { return empDeptId; }

      public void setEmpDeptId(Integer empDeptId) { this.empDeptId = empDeptId; }

      public Department getDepartment() { return department; }

      public void setDepartment(Department department) { this.department = department; }

}

Employee getEmployeeAndDepartment(@Param(“empId”) Integer empId);

@Test public void testGetEmployeeAndDepartment(){ EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class); Employee employee = mapper.getEmployeeAndDepartment(2); System.out.println(employee); }

  2.    2. 使用association处理映射关系
  3. ```java
  4. Employee getEmployeeAndDepartment2(@Param("empId") Integer empId);
  6. <resultMap id="getEmployeeAndDepartment2" type="xyz.zhouge.mybatis.bean.Employee">
  7.         <id column="emp_id" property="empId"/>
  8.         <result column="emp_name" property="empName"/>
  9.         <result column="emp_age" property="empAge"/>
  10.         <result column="emp_sex" property="empSex"/>
  11.         <result column="emp_email" property="empEmail"/>
  12.         <result column="emp_depId" property="empDeptId"/>
  14.         <!-- 通过设置association属性解决多对一问题 -->
  15.         <association property="department" javaType="xyz.zhouge.mybatis.bean.Department">
  16.             <id column="dep_id" property="deptId"/>
  17.             <result column="dep_name" property="deptName"/>
  18.         </association>
  20. </resultMap>
  23. <select id="getEmployeeAndDepartment2" resultMap="getEmployeeAndDepartment2">
  24.         select  * from tb_emp left join tb_dept on tb_emp.emp_depId = tb_dept.dep_id where tb_emp.emp_id = #{empId};
  25. </select>
  27. @Test
  28.     public void testGetEmployeeAndDepartment2(){
  29.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  30.         Employee employee = mapper.getEmployeeAndDepartment2(3);
  31.         System.out.println(employee);
  32.     }
  1. 分步查询（重难点） ```java /**
    • 通过分步查询获取员工信息
    • 第一步：查询员工表信息
    • @param empId
    • @return */ Employee getEmployeeAndDepartmentByStep1(@Param(“empId”) Integer empId);

public interface DepartmentMapper {

  1. /**
  2.  * 通过分布查询 获取员工信息
  3.  *  第二步：查询部门表
  4.  *
  5.  *  将第一步查询的部门id 放入到column中 当作第二步查询的条件
  6.      此处的 @Param("emp_depId") 是第一步查询出来的结果
  8.  * @param deptId
  9.  * @return
  10.  */
  11. Department getEmployeeAndDepartmentByStep2(@Param("emp_depId") Integer deptId);

}

@Test public void testGetEmployeeAndDepartmentByStep1(){ EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class); Employee employee = mapper.getEmployeeAndDepartmentByStep1(2); System.out.println(employee); }

  2.    4. 延迟加载
  3. ```java
  4. <?xml version="1.0" encoding="UTF-8" ?>
  5. <!DOCTYPE configuration
  6.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  7.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  8. <configuration>
  10.     <settings>
  12.         <!--通过配置全局的设置，将sql的下划线字段名 转换为 javabean中符合驼峰规则的属性名-->
  13.         <setting name="mapUnderscoreToCamelCase" value="false"/>
  15.         <!-- 开启延迟加载 -->
  16.         <setting name="lazyLoadingEnabled" value="true"/>
  18.         <!-- 是否按需加载（搭配延迟加载一起使用） -->
  19.         <!--<setting name="aggressiveLazyLoading" value="true"/>-->
  20.     </settings>
  23. </configuration>
  25. /**
  26. * 通过分步查询获取员工信息
  27. *  第一步：查询员工表信息
  28. * @param empId
  29. * @return
  30. */
  31. Employee getEmployeeAndDepartmentByStep1(@Param("empId") Integer empId);
  33. <resultMap id="getEmployeeAndDepartmentByStep1ResultMap" type="xyz.zhouge.mybatis.bean.Employee">
  34.         <id column="emp_id" property="empId"/>
  35.         <result column="emp_name" property="empName"/>
  36.         <result column="emp_age" property="empAge"/>
  37.         <result column="emp_sex" property="empSex"/>
  38.         <result column="emp_email" property="empEmail"/>
  39.         <result column="emp_depId" property="empDeptId"/>
  41.         <!--
  42.             分布查询：
  43.                 property：需要查询的javaBean的属性值
  44.                 select：第二部查询的步骤
  45.                 column：注意，这里并非是前面数据库中查询的字段名，而是第二步查询的条件。取值是当前步骤查询的字段名
  46.                         即，把当前步骤中查询出来的某一字段名，当作后续步骤查询的条件。
  47.                 fetchType:搭配延迟加载一起使用，当延迟加载未启用时，默认eager,即实时加载
  48.                     lazy:延迟加载
  49.                     eager：实时加载
  50.         -->
  51.         <association property="department"
  52.                      select="xyz.zhouge.mybatis.mapper.DepartmentMapper.getEmployeeAndDepartmentByStep2"
  53.                      column="emp_depId"/>
  55. </resultMap>
  57. <select id="getEmployeeAndDepartmentByStep1" resultMap="getEmployeeAndDepartmentByStep1ResultMap">
  58.         select * from tb_emp where emp_id = #{empId};
  59. </select>
  62. public interface DepartmentMapper {
  64.     /**
  65.      * 通过分布查询 获取员工信息
  66.      *  第二步：查询部门表
  67.      *
  68.      *  将第一步查询的部门id 放入到column中 当作第二步查询的条件
  69.          此处的 @Param("emp_depId") 是第一步查询出来的结果
  71.      * @param deptId
  72.      * @return
  73.      */
  74.     Department getEmployeeAndDepartmentByStep2(@Param("emp_depId") Integer deptId);
  76. }
  78. <resultMap id="getEmployeeAndDepartmentByStep2ResultMap" type="xyz.zhouge.mybatis.bean.Department">
  79.         <id column="dep_id" property="deptId"/>
  80.         <result column="dep_name" property="deptName"/>
  81. </resultMap>
  83. <select id="getEmployeeAndDepartmentByStep2" resultMap="getEmployeeAndDepartmentByStep2ResultMap">
  84.         select * from tb_dept where  dep_id = #{emp_depId};
  85. </select>
  87. @Test
  88.     public void testGetEmployeeAndDepartmentByStep1(){
  89.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  90.         Employee employee = mapper.getEmployeeAndDepartmentByStep1(2);
  91.         System.out.println(employee.getEmpEmail());
  92.         System.out.println(employee.getDepartment());
  93.     }
  1. 一对多映射处理
    1. collection ```java /**
  • 处理一对多关系-collection 查询部门信息
  • @param deptId
  • @return */ Department getDepartmentAndEmployee(@Param(“deptId”) Integer deptId);
@Test public void testGetDepartmentAndEmployee(){ DepartmentMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DepartmentMapper.class); Department department = mapper.getDepartmentAndEmployee(2); System.out.println(department); } 2. 分步查询java /** 处理一对多关系 -分步查询 @param deptId @return / Department getDepartmentByStep1(@Param(“deptId”) Integer deptId); / 分步查询，将第一步查询出来的dep_id的值作为当前步骤的查询条件进行查询 @param depId @return / List getDepartmentAndEmployeeByStep2(@Param(“dep_id”) Integer depId); @Test public void testGetDepartmentByStep1(){ DepartmentMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DepartmentMapper.class); Department department = mapper.getDepartmentByStep1(3); System.out.println(department.getDeptName()); } <a name="uvPj2"></a> #### 九、动态SQL 1. ifjava public interface DynamicSQLMapper { / 通过动态sql if 根据条件查询员工信息 @param employee @return / Employee getEmployeeByCondition(Employee employee); }

/**

  • @BelongsProject: MyBatis
  • @BelongsPackage: xyz.zhouge.mybatis.test
  • @CreateTime: 2022-05-07 14:42
  • @Description: TODO
  • @Version: 1.0

  • @Author：zhouge */ public class TestDynamicSQLMapper {

    @Test public void testGetEmployeeByCondition(){

    1.  DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class);
    2.  Employee employee = mapper.getEmployeeByCondition(new Employee(null, "张三", 23, "男", "zhangsan@163.com", 2));
    3.  System.out.println(employee);

    } } ```

  1. where ```java public interface DynamicSQLMapper {

    /**

    • 通过动态sql where 根据条件查询员工信息
    • @param employee
    • @return */ Employee getEmployeeByCondition2(Employee employee); }
  1. <select id="getEmployeeByCondition2" resultMap="Employee">
  2.     select * from tb_emp
  3.     <where>
  4.         <if test="empName != null and empName != ''">
  5.             and emp_name = #{empName}
  6.         </if>
  8.         <if test="empAge != null and empAge != ''">
  9.             and emp_age = #{empAge}
  10.         </if>
  12.         <if test="empName != null and empName != ''">
  13.             and emp_name = #{empName}
  14.         </if>
  16.         <if test="empSex != null and empSex != ''">
  17.             and emp_sex = #{empSex}
  18.         </if>
  19.     </where>

/**

  • @BelongsProject: MyBatis
  • @BelongsPackage: xyz.zhouge.mybatis.test
  • @CreateTime: 2022-05-07 14:42
  • @Description: TODO
  • @Version: 1.0

  • @Author：zhouge */ public class TestDynamicSQLMapper {

    @Test public void testGetEmployeeByCondition2(){

    1.  DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class);
    2.  Employee employee = mapper.getEmployeeByCondition(new Employee(null, "张三", 23, "男", "zhangsan@163.com", 2));
    3.  System.out.println(employee);

    } } ```

  1. trim ```java public interface DynamicSQLMapper {

    /**

    • 通过动态sql trim 根据条件查询员工信息 prefix | suffix : 在trim标签的前面或后面添加指定内容 prefixOverrides | suffixOverrides ： 在trim标签的前面或后面去掉指定的内容
    • @param employee
    • @return */ Employee getEmployeeByCondition3(Employee employee); }
  1. <select id="getEmployeeByCondition3" resultMap="Employee">
  2.     select * from tb_emp
  3.     <trim prefix="where" suffixOverrides="and|or" prefixOverrides="and">
  4.         <if test="empName != null and empName != ''">
  5.             emp_name = #{empName} and
  6.         </if>
  8.         <if test="empAge != null and empAge != ''">
  9.             emp_age = #{empAge} and
  10.         </if>
  12.         <if test="empName != null and empName != ''">
  13.             emp_name = #{empName}
  14.         </if>
  16.         <if test="empSex != null and empSex != ''">
  17.             and emp_sex = #{empSex}
  18.         </if>
  19.     </trim>

/**

  • @BelongsProject: MyBatis
  • @BelongsPackage: xyz.zhouge.mybatis.test
  • @CreateTime: 2022-05-07 14:42
  • @Description: TODO
  • @Version: 1.0

  • @Author：zhouge */ public class TestDynamicSQLMapper {

    @Test public void testGetEmployeeByCondition2(){

    1.  DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class);
    2.  Employee employee = mapper.getEmployeeByCondition(new Employee(null, "张三", 23, "男", "zhangsan@163.com", 2));
    3.  System.out.println(employee);

    } } ```

  1. choose、when、otherwise ```java /**
    • 通过动态sql choose-when-otherwise 查询员工信息
    • @param employee
    • @return */ List getEmployeeByChoose(Employee employee);
  1. <select id="getEmployeeByChoose" resultMap="Employee">
  2.     select * from tb_emp
  3.     <where>
  4.         <choose>
  5.             <when test="empName != null and empName != ''">
  6.                 emp_name = #{empName}
  7.             </when>
  9.             <when test="empAge != null and empAge != ''">
  10.                 emp_age = #{empAge}
  11.             </when>
  13.             <when test="empSex != null and empSex != ''">
  14.                 emp_sex= #{empSex}
  15.             </when>
  17.             <when test="empEmail != null and empEmail != ''">
  18.                 emp_email = #{empEmail}
  19.             </when>
  21.             <otherwise>
  22.                 emp_id = 1
  23.             </otherwise>
  24.         </choose>
  25.     </where>

@Test public void testGetEmployeeByChoose(){ DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class); List employees = mapper.getEmployeeByChoose(new Employee(null, “”, 23, “男”, “zhangsan@163.com”, 2)); employees.forEach(employee -> System.out.println(employee)); }

  2. 5. foreach
  3. ```java
  4. /**
  5.      * 通过动态sql foreach 删除或添加员工
  6.      * @param eids
  7.      * @return
  8.      */
  9. int deleteMoreByArray(@Param("eids") Integer[] eids);
  11. <!--int deleteMoreByArray(@Param("eids") Integer[] eids);-->
  12.     <delete id="deleteMoreByArray">
  13.         <!--
  14.             delete from tb_emp where emp_id in
  15.         <foreach collection="eids" item="eid" separator="," open="(" close=")">
  16.             #{eid}
  17.         </foreach>
  18.         -->
  19.         delete from tb_emp where
  20.         <foreach collection="eids" item="eid" separator="or">
  21.             emp_id = #{eid}
  22.         </foreach>
  23. </delete>
  25. @Test
  26.     public  void testDeleteMoreByArray(){
  27.         DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class);
  28.         int res = mapper.deleteMoreByArray(new Integer[]{14,15,16});
  29.         System.out.println(res);
  30.     }
  1. /**
  2.      * 通过动态sql foreach 添加员工
  3.      * @param employeeList
  4.      * @return
  5.      */
  6. int insertMoreByArray(@Param("employees") List<Employee> employeeList);
  8. <!--int insertMoreByArray(@Param("employees") List<Employee> employeeList);-->
  9.     <insert id="insertMoreByArray">
  10.         insert into tb_emp values
  11.         <foreach collection="employees" item="employee" separator=",">
  12.             (null,#{employee.empName},#{employee.empAge},#{employee.empSex},#{employee.empEmail},#{employee.empDeptId})
  13.         </foreach>
  14. </insert>
  16. @Test
  17.     public void testInsertMoreByArray(){
  18.         DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class);
  19.         Employee emp1 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  20.         Employee emp2 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  21.         Employee emp3 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  22.         Employee emp4 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  23.         Employee emp5 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  24.         Employee emp6 = new Employee(null, "zg", 23, "男", "zg@163.com", 1);
  25.         List<Employee> list = Arrays.asList(emp1, emp2, emp3, emp4, emp5, emp6);
  26.         System.out.println(mapper.insertMoreByArray(list));
  27.     }
  1. sql片段 ```java Employee getEmployeeByCondition4(@Param(“id”) Integer id);
emp_id,emp_name,emp_age,emp_sex,emp_email,emp_depId @Test public void testGetEmployeeByCondition4(){ DynamicSQLMapper mapper = SqlSessionUtil.getSqlSession().getMapper(DynamicSQLMapper.class); Employee employee = mapper.getEmployeeByCondition4(1); System.out.println(employee); } <a name="h4jlp"></a> #### 十、MyBatis缓存 1. **MyBatis的一级缓存 : 基于SQLSession的**java public interface CacheMapper { /* 根据员工id查询员工信息 @param employeeId @return */ Employee getEmployeeById(@Param(“id”) Integer employeeId); } emp_id,emp_name,emp_age,emp_sex,emp_email,emp_depId

public class TestCacheMapper {

  1. /**
  2.  * MyBatis 一级缓存
  3.  *  基于SQLSession的
  4.  */
  5. @Test
  6. public void testGetEmployeeById(){
  8.     //同一个sqlsession创建
  9.     CacheMapper mapper1 = SqlSessionUtil.getSqlSession().getMapper(CacheMapper.class);
  10.     Employee employee1 = mapper1.getEmployeeById(1);
  11.     System.out.println(employee1);
  12.     Employee employee2 = mapper1.getEmployeeById(1);
  13.     System.out.println(employee2);
  14. }
  16. @Test
  17. public void testGetEmployeeById2(){
  19.     //通过同一个sqlsession创建 不同的mapper
  20.     SqlSession sqlSession = SqlSessionUtil.getSqlSession();
  21.     CacheMapper mapper1 = sqlSession.getMapper(CacheMapper.class);
  22.     CacheMapper mapper2 = sqlSession.getMapper(CacheMapper.class);
  24.     Employee employee1 = mapper1.getEmployeeById(1);
  25.     System.out.println(employee1);
  26.     Employee employee2 = mapper2.getEmployeeById(1);
  27.     System.out.println(employee2);
  28. }
  30. @Test
  31. public void testGetEmployeeById3(){
  32.     //不同的sqlsession创建不同的mapper
  33.     CacheMapper mapper1 = SqlSessionUtil.getSqlSession().getMapper(CacheMapper.class);
  34.     CacheMapper mapper2 = SqlSessionUtil.getSqlSession().getMapper(CacheMapper.class);
  35.     Employee employee1 = mapper1.getEmployeeById(1);
  36.     System.out.println(employee1);
  37.     Employee employee2 = mapper2.getEmployeeById(1);
  38.     System.out.println(employee2);
  39. }

}

  1. **一级缓存失效的四种情况：**
  3.    1. 不同的SQL session对应不同的一级缓存
  4.    1. 同一个SQL session，但是查询条件不同
  5.    1. 同一个SQL session，两次查询期间执行了任何一次增删改操作
  6.    1. 同一个SQL session，两次查询期间手动清空了缓存
  9. 2. **MyBatis的二级缓存 ： 基于SqlSessionFactor**y
  11. 开启二级缓存：
  13.    1.  在核心配置文件中，设置全局配置属性cacheEnabled="true"，默认为true，不需要设置
  14. ```java
  15. <settings>
  17.         <!--通过配置全局的设置，将sql的下划线字段名 转换为 javabean中符合驼峰规则的属性名-->
  18.         <setting name="mapUnderscoreToCamelCase" value="false"/>
  20.         <!-- 开启延迟加载 -->
  21.         <setting name="lazyLoadingEnabled" value="true"/>
  23.         <!-- 是否按需加载（搭配延迟加载一起使用） -->
  24.         <!--<setting name="aggressiveLazyLoading" value="true"/>-->
  26.         <!-- 开启二级缓存 -->
  27.         <setting name="cacheEnabled" value="true"/>
  28.     </settings>

  1. 在映射文件中设置标签

    1. <!-- 开启二级缓存 -->
    2. <cache />

  2. 二级缓存必须在SqlSession关闭或提交之后有效

  3. 查询的数据所转换的实体类类型必须实现序列化的接口 

    1. /**
    2. * 测试二级缓存 ： 基于SQLSessionFactory
    3. */
    4. @Test
    5. public void testSecondCache(){
    6. try {
    7.    //获取mybatis配置文件资源
    8.    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    10.    //创建SqlSessionFactory
    11.    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    13.    //创建SqlSession 并设置自动提交
    14.    SqlSession sqlSession1 = sqlSessionFactory.openSession(true);
    16.    //创建mapper
    17.    CacheMapper mapper1 = sqlSession1.getMapper(CacheMapper.class);
    19.    //sqlSession1 执行操作并关闭
    20.    Employee employee1 = mapper1.getEmployeeById(1);
    21.    sqlSession1.close();
    23.    //sqlSession2
    24.    SqlSession sqlSession2 = sqlSessionFactory.openSession(true);
    25.    CacheMapper mapper2 = sqlSession2.getMapper(CacheMapper.class);
    26.    //sqlSession2 执行操作并关闭
    27.    Employee employee2 = mapper2.getEmployeeById(1);
    28.    sqlSession2.close();
    30. } catch (IOException e) {
    31.    e.printStackTrace();
    32. }
    33. }

    注意： 使二级缓存失效的情况： 两次查询之间执行了任意的增删改，会使一级和二级缓存同时失效

  1. 二级缓存的相关配置

    在mapper配置文件中添加的cache标签可以设置一些属性：
    eviction属性：缓存回收策略
    LRU（Least Recently Used） – 最近最少使用的：移除最长时间不被使用的对象。
    FIFO（First in First out） – 先进先出：按对象进入缓存的顺序来移除它们。
    SOFT – 软引用：移除基于垃圾回收器状态和软引用规则的对象。
    WEAK – 弱引用：更积极地移除基于垃圾收集器状态和弱引用规则的对象。
    默认的是 LRU。
    flushInterval属性：刷新间隔，单位毫秒 默认情况是不设置，也就是没有刷新间隔，缓存仅仅调用语句时刷新
    size属性：引用数目，正整数 代表缓存最多可以存储多少个对象，太大容易导致内存溢出

readOnly属性：只读，true/false
true：只读缓存；会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了 很重要的性能优势。
false：读写缓存；会返回缓存对象的拷贝（通过序列化）。这会慢一些，但是安全，因此默认是 false。

  1. MyBatis缓存查询的顺序

先查询二级缓存，因为二级缓存中可能会有其他程序已经查出来的数据，可以拿来直接使用。
如果二级缓存没有命中，再查询一级缓存
如果一级缓存也没有命中，则查询数据库
SqlSession关闭之后，一级缓存中的数据会写入二级缓存

  1. 整合第三方缓存EHCache（代替二级缓存，一级缓存没法代替）
    1. 添加依赖 ```xml org.mybatis.caches mybatis-ehcache 1.2.1
ch.qos.logback logback-classic 1.2.3 2. **各种jar包功能** | jar包名称 | 作用 | | --- | --- | | mybatis-ehcache | Mybatis和EHCache的整合包 | | ehcache | EHCache核心 | | slf4j-api | SLF4J日志门面包 | | logback-classic | 支持SLF4J门面接口的一个具体实现 | 3. **创建EHCache配置文件ehcache.xml**xml <?xml version=”1.0” encoding=”utf-8” ?> 4. **设置二级缓存的类型**xml 5. **加入logback日志(文件名为log-back.xml)**xml <?xml version=”1.0” encoding=”UTF-8”?> [%d{HH:mm:ss.SSS}] [%-5level] [%thread] [%logger] [%msg]%n utf8 6. **EHCache配置文件说明** ![image.png](https://cdn.nlark.com/yuque/0/2022/png/28229185/1652113494555-fd93a811-0f11-4b34-accc-c00e0038bddc.png#clientId=uce39accc-2997-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=538&id=uf35289ee&margin=%5Bobject%20Object%5D&name=image.png&originHeight=673&originWidth=553&originalType=binary&ratio=1&rotation=0&showTitle=false&size=159477&status=done&style=none&taskId=u4f223507-c376-425c-84ab-c5b31a4e2a4&title=&width=442.4) <a name="o30ot"></a> #### 十一、MyBatis的逆向工程 1. 创建逆向工厂的步骤 1. 添加依赖和插件xml org.mybatis mybatis 3.5.7 
  1.     <!-- junit测试 -->
  2.     <dependency>
  3.         <groupId>junit</groupId>
  4.         <artifactId>junit</artifactId>
  5.         <version>4.12</version>
  6.         <scope>test</scope>
  7.     </dependency>
  9.     <!-- MySQL驱动 -->
  10.     <dependency>
  11.         <groupId>mysql</groupId>
  12.         <artifactId>mysql-connector-java</artifactId>
  13.         <version>8.0.26</version>
  14.     </dependency>
  17.     <!-- log4j日志 -->
  18.     <dependency>
  19.         <groupId>log4j</groupId>
  20.         <artifactId>log4j</artifactId>
  21.         <version>1.2.17</version>
  22.     </dependency>
  24. </dependencies>
  26. <!-- 控制Maven在构建过程中相关配置 -->
  27. <build>
  29.     <!-- 构建过程中用到的插件 -->
  30.     <plugins>
  32.         <!-- 具体插件，逆向工程的操作是以构建过程中插件形式出现的 -->
  33.         <plugin>
  34.             <groupId>org.mybatis.generator</groupId>
  35.             <artifactId>mybatis-generator-maven-plugin</artifactId>
  36.             <version>1.3.0</version>
  37.             <!-- 插件的依赖 -->
  38.             <dependencies>
  39.                 <!-- 逆向工程的核心依赖 -->
  40.                 <dependency>
  41.                     <groupId>org.mybatis.generator</groupId>
  42.                     <artifactId>mybatis-generator-core</artifactId>
  43.                     <version>1.3.2</version>
  44.                 </dependency>
  46.                 <!-- 数据库连接池 -->
  47.                 <dependency>
  48.                     <groupId>com.mchange</groupId>
  49.                     <artifactId>c3p0</artifactId>
  50.                     <version>0.9.5.2</version>
  51.                 </dependency>
  53.                 <dependency>
  54.                     <groupId>com.alibaba</groupId>
  55.                     <artifactId>druid</artifactId>
  56.                     <version>1.2.8</version>
  57.                 </dependency>
  59.                 <!-- MySQL驱动 -->
  60.                 <dependency>
  61.                     <groupId>mysql</groupId>
  62.                     <artifactId>mysql-connector-java</artifactId>
  63.                     <version>8.0.26</version>
  64.                 </dependency>
  65.             </dependencies>
  67.         </plugin>
  68.     </plugins>
  69. </build>
  2.    2. 创建MyBatis的核心配置文件
  3. ```xml
  4. <?xml version="1.0" encoding="UTF-8" ?>
  5. <!DOCTYPE configuration
  6.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  7.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  8. <configuration>
  10.     <properties resource="jdbc.properties"/>
  12.     <settings>
  14.         <!--通过配置全局的设置，将sql的下划线字段名 转换为 javabean中符合驼峰规则的属性名-->
  15.         <setting name="mapUnderscoreToCamelCase" value="false"/>
  17.         <!-- 开启延迟加载 -->
  18.         <setting name="lazyLoadingEnabled" value="true"/>
  20.         <!-- 是否按需加载（搭配延迟加载一起使用） -->
  21.         <!--<setting name="aggressiveLazyLoading" value="true"/>-->
  23.         <!-- 开启二级缓存 -->
  24.         <setting name="cacheEnabled" value="true"/>
  25.     </settings>
  27.     <typeAliases>
  28.         <!-- 等逆向工程完成后再添加 -->
  29.         <typeAlias type="xyz.zhouge.mybatis.bean.Department"/>
  30.         <typeAlias type="xyz.zhouge.mybatis.bean.Employee"/>
  31.     </typeAliases>
  33.     <environments default="test">
  34.         <environment id="development">
  35.             <transactionManager type="JDBC"/>
  36.             <dataSource type="POOLED">
  37.                 <property name="driver" value=""/>
  38.                 <property name="url" value=""/>
  39.                 <property name="username" value=""/>
  40.                 <property name="password" value=""/>
  41.             </dataSource>
  42.         </environment>
  44.         <environment id="test">
  45.             <transactionManager type="JDBC"/>
  46.             <dataSource type="POOLED">
  47.                 <property name="driver" value="${jdbc.Driver}"/>
  48.                 <property name="url" value="${jdbc.url}"/>
  49.                 <property name="username" value="${jdbc.username}"/>
  50.                 <property name="password" value="${jdbc.password}"/>
  51.             </dataSource>
  52.         </environment>
  53.     </environments>
  56.     <mappers>
  57.         <package name="xyz.zhouge.mybatis.mapper"/>
  58.     </mappers>
  60. </configuration>

  1. 创建逆向工程的配置文件 ```xml <?xml version=”1.0” encoding=”UTF-8”?> <!DOCTYPE generatorConfiguration PUBLIC “-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN” “http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

     
  2.    4. 执行MBG插件的generate目标
  3. 2. QBC查询
  4. ```java
  5. package xyz.zhouge.mybatis.test;
  7. import org.junit.Test;
  8. import xyz.zhouge.mybatis.bean.Employee;
  9. import xyz.zhouge.mybatis.bean.EmployeeExample;
  10. import xyz.zhouge.mybatis.mapper.EmployeeMapper;
  11. import xyz.zhouge.mybatis.utils.SqlSessionUtil;
  12. import java.util.List;
  14. /**
  15. * @BelongsProject: MyBatis
  16. * @BelongsPackage: xyz.zhouge.mybatis.test
  17. * @CreateTime: 2022-05-10  15:44
  18. * @Description: TODO
  19. * @Version: 1.0
  20. * @Author：zhouge
  21. */
  22. public class TestEmployeeMapper {
  24.     @Test
  25.     public void testSelectAll(){
  26.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  27.         List<Employee> employees = mapper.selectByExample(null);
  28.         employees.forEach(employee -> System.out.println(employee));
  29.     }
  31.     @Test
  32.     public void testSelectByExample(){
  33.         EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
  35.         //创建查询条件 年龄大于20 且 小于25
  36.         EmployeeExample employeeExample = new EmployeeExample();
  37.         employeeExample.createCriteria().andEmpAgeGreaterThan(20).andEmpAgeLessThan(25);
  39.         //输出
  40.         List<Employee> employees = mapper.selectByExample(employeeExample);
  41.         employees.forEach(employee -> System.out.println(employee));
  42.     }
  44. }

十二、分页插件

  1. 分页插件使用步骤

    1. 添加依赖

      1. <dependency>
      2. <groupId>com.github.pagehelper</groupId>
      3. <artifactId>pagehelper</artifactId>
      4. <version>5.2.0</version>
      5. </dependency>

    2. 配置分页插件

      1. <!--在mybatis核心配置文件中进行设置-->
      2. <plugins>
      3.   <!--设置分页插件-->
      4.   <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
      5. </plugins>

  2. 分页插件的使用 ```java package test;

import com.github.pagehelper.Page; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import org.junit.Test; import xyz.zhouge.mybatis.bean.Employee; import xyz.zhouge.mybatis.bean.EmployeeExample; import xyz.zhouge.mybatis.mapper.EmployeeMapper; import xyz.zhouge.mybatis.utils.SqlSessionUtil;

import java.util.List;

/**

  • @BelongsProject: MyBatis
  • @BelongsPackage: xyz.zhouge.mybatis.test
  • @CreateTime: 2022-05-10 15:44
  • @Description: TODO
  • @Version: 1.0

  • @Author：zhouge */ public class TestEmployeeMapper {

    @Test public void testSelectAll(){

    1.  EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
    3.  /**
    4.   * 在查询之前开启分页 PageHelper.startPage(int pageNum, int pageSize)
    5.   *
    6.   * pageNum：当前页的页码
    7.   * pageSize：每页显示的条数
    8.   */
    9.  Page<Object> page = PageHelper.startPage(1, 3);
    11.  List<Employee> employees = mapper.selectByExample(null);
    13.  /**
    14.   * PageInfo<T> pageInfo = new PageInfo<>(List<T> list, int navigatePages)获取分页相关数据
    15.   */
    16.  PageInfo<Employee> info = new PageInfo<>(employees, 2);
    18.  employees.forEach(employee -> System.out.println(employee));
    19.  System.out.println(info);
    21.  /**
    22.   * PageInfo
    23.   * {
    24.   *
    25.   *  pageNum=1, pageSize=3, size=3, startRow=1, endRow=3, total=13, pages=5, list=Page{count=true, pageNum=1, pageSize=3, startRow=0, endRow=3, total=13, pages=5, reasonable=false, pageSizeZero=false
    26.   *
    27.   *  }
    28.   *
    29.   *  [
    30.   *      Employee{empId=1, empName='张三', empAge=23, empSex='男', empEmail='zhangsan@163.com', empDepid=1},
    31.   *      Employee{empId=2, empName='李四', empAge=18, empSex='女', empEmail='lisi@qq.com', empDepid=2},
    32.   *      Employee{empId=3, empName='王五', empAge=23, empSex='男', empEmail='wangwu@163.com', empDepid=1}
    33.   *  ],
    34.   *
    35.   *  prePage=0, nextPage=2, isFirstPage=true, isLastPage=false, hasPreviousPage=false, hasNextPage=true, navigatePages=2, navigateFirstPage=1, navigateLastPage=2, navigatepageNums=[1, 2]}
    36.   */

    }

    @Test public void testSelectByExample(){

    1.  EmployeeMapper mapper = SqlSessionUtil.getSqlSession().getMapper(EmployeeMapper.class);
    3.  //创建查询条件 年龄大于20 且 小于25
    4.  EmployeeExample employeeExample = new EmployeeExample();
    5.  employeeExample.createCriteria().andEmpAgeGreaterThan(20).andEmpAgeLessThan(25);
    7.  //输出
    8.  List<Employee> employees = mapper.selectByExample(employeeExample);
    9.  employees.forEach(employee -> System.out.println(employee));

    }

}

```