MyBatis框架

第一章 数据延迟加载

1.1 数据准备

<!--
   主键查询用户信息
-->
<select id="queryUserById" resultType="user" parameterType="int">
    select * from user where id = #{id}
</select>

/**
 * 主键查询用户信息
 */
@Test
public void queryUserById(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.queryUserById(1);
    System.out.println(user);
    sqlSession.close();
}

用户订单一对一查询

<!--
  订单，查询订单对应的用户
-->
<select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address FROM orders o LEFT JOIN USER u
    ON o.user_id = u.id
</select>

<resultMap id="queryOrdersUserResultMap" type="orders">
    <id column="id" property="id"></id>
    <result column="user_id" property="userId"></result>
    <result column="number" property="number"></result>
    <result column="createtime" property="createtime"></result>
    <result column="note" property="note"></result>

    <association property="user" javaType="user">
        <id column="user_id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>
    </association>
</resultMap>

public void queryOrdersUser(){
    System.out.println();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> ordersList = ordersMapper.queryOrdersUser();
    if(ordersList != null && ordersList.size() > 0){
        for (Orders orders : ordersList){
            System.out.println(orders);
        }
    }
    sqlSession.close();
}

用户查询订单信息 一对多

<!--
   根据用户查询订单，一对多
-->
<select id="queryUserOrders" resultMap="queryUserOrdersResultMap">
    SELECT u.id,u.username,u.sex,u.birthday,u.address,o.id oid,o.number,o.createtime  FROM USER u LEFT JOIN orders o
    ON u.id = o.user_id
</select>

<resultMap id="queryUserOrdersResultMap" type="user">
    <id column="id" property="id"></id>
    <result column="username" property="username"></result>
    <result column="sex" property="sex"></result>
    <result column="birthday" property="birthday"></result>
    <result column="address" property="address"></result>

    <collection property="ordersList" ofType="orders">
        <id column="oid" property="id"></id>
        <result column="number" property="number"></result>
        <result column="createtime" property="createtime"></result>
    </collection>
</resultMap>

public void queryUserOrders(){
    System.out.println();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> list = mapper.queryUserOrders();
    if(list != null && list.size() > 0){
        for(User user : list){
            System.out.println(user);
        }
    }
    sqlSession.close();
}

1.2 一对一延迟加载

• 针对的是关联对象

  用户和订单从面相对象的角度来说就是关联对象，当只需要订单数据，尚不需要用户数据的时候，就不应该去查询用户表，啥时候用到用户数据，啥时候查询

• 一对一延迟加载

  关联的sql语句肯定要拆分了

• association标签

  • column属性：关联查询条件的属性
  • select属性：指定另一个查询语句

<!--
  订单，查询订单对应的用户
  拆分SQL语句，实现延迟加载
-->
<select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note FROM orders o
</select>

<resultMap id="queryOrdersUserResultMap" type="orders">
    <id column="id" property="id"></id>
    <result column="user_id" property="userId"></result>
    <result column="number" property="number"></result>
    <result column="createtime" property="createtime"></result>
    <result column="note" property="note"></result>
    <!--
       column指定查询的参数
       select关联其他查询语句 
    -->
    <association property="user" javaType="user" column="user_id" select="queryUserByUserId">
       <!-- <id column="user_id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>-->
    </association>
</resultMap>

<select id="queryUserByUserId" parameterType="int" resultType="user">
    select id,username,sex,birthday,address from user where id=#{user_id}
</select>

public void queryOrdersUser(){
    System.out.println();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> ordersList = ordersMapper.queryOrdersUser();
    if(ordersList != null && ordersList.size() > 0){
        for (Orders orders : ordersList){
            System.out.println(orders);
            System.out.println(orders.getUser());
        }
    }
    sqlSession.close();
}

   <settings>
        <!-- 开启延迟加载功能 -->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>

1.3 一对多延迟加载

<!--
   根据用户查询订单，一对多
   延迟加载，拆分SQL语句
-->
<select id="queryUserOrders" resultMap="queryUserOrdersResultMap">
    SELECT u.id,u.username,u.sex,u.birthday,u.address  FROM USER u
</select>

<resultMap id="queryUserOrdersResultMap" type="user">
    <id column="id" property="id"></id>
    <result column="username" property="username"></result>
    <result column="sex" property="sex"></result>
    <result column="birthday" property="birthday"></result>
    <result column="address" property="address"></result>

    <collection property="ordersList" ofType="orders" column="id" select="queryOrdersByUserId">
      <!--  <id column="oid" property="id"></id>
        <result column="number" property="number"></result>
        <result column="createtime" property="createtime"></result>-->
    </collection>
</resultMap>

<select id="queryOrdersByUserId" parameterType="int" resultType="orders">
    select id,number,createtime from orders where user_id = #{user_id}
</select>

public void queryUserOrders(){
    System.out.println();
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> list = mapper.queryUserOrders();
    if(list != null && list.size() > 0){
        for(User user : list){
            System.out.println(user);
            List<Orders> ordersList = user.getOrdersList();
            System.out.println(ordersList);
        }
    }
    sqlSession.close();
}

第二章 MyBatis缓存机制

1.1 MyBatis一级缓存

一级缓存：是SqlSession级别的，也就是同一个SqlSession内执行相同select语句的时候，不再去查询数据库，而是从Mybatis内部的缓存内存结构去直接拿到数据。

• 缓存失效时机：
  • sqlSession关闭
  • sqlSession提交事务（意味着可能是一个增删改的动作，需要更新缓存，那么这个时候Mybatis就会把已有的一级缓存给清理掉）

1.2 MyBatis二级缓存

• 开启二级缓存

<setting name="cacheEnabled" value="true" />
<!-- 具体Mapper.xml配置-->
<cache></cache>

二级缓存是针对于mapper文件的缓冲，不是针对于SqlSession对象的缓存

public void queryUserById(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.queryUserById(1);
    System.out.println(user);
    sqlSession.close();
    SqlSession sqlSession1 = sqlSessionFactory.openSession();
    UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
    User user1 = userMapper1.queryUserById(1);
    System.out.println(user1);
    sqlSession1.close();
}

1.3 EHCache缓存

Ehcache是一种广泛使用的开源Java分布式缓存。主要面向通用缓存,Java EE和轻量级容器。

1.3.1 整合MyBatis框架

<!-- Mybatis EHCache整合包 -->
<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.2.1</version>
</dependency>
<!-- slf4j日志门面的一个具体实现 -->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
</dependency>

1.3.2 创建EHCache配置文件

• resource目录下创建文件 ehcache.xml

<?xml version="1.0" encoding="utf-8" ?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="ehcache.xsd" updateCheck="true"
         monitoring="autodetect" dynamicConfig="true">
    <!-- 磁盘保存路径 -->
    <diskStore path="D:\atguigu\ehcache"/>

    <defaultCache
            maxElementsInMemory="1000"
            maxElementsOnDisk="10000000"
            eternal="false"
            overflowToDisk="true"
            timeToIdleSeconds="120"
            timeToLiveSeconds="120"
            diskExpiryThreadIntervalSeconds="120"
            memoryStoreEvictionPolicy="LRU">
    </defaultCache>
</ehcache>

defaultCache标签各属性说明：

1.3.3 集成logback日志

• resource目录下创建文件 logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="true">
    <!-- 指定日志输出的位置 -->
    <appender name="STDOUT"
              class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <!-- 日志输出的格式 -->
            <!-- 按照顺序分别是：时间、日志级别、线程名称、打印日志的类、日志主体内容、换行 -->
            <pattern>[%d{HH:mm:ss.SSS}] [%-5level] [%thread] [%logger] [%msg]%n</pattern>
        </encoder>
    </appender>

    <!-- 设置全局日志级别。日志级别按顺序分别是：DEBUG、INFO、WARN、ERROR -->
    <!-- 指定任何一个日志级别都只打印当前级别和后面级别的日志。 -->
    <root level="DEBUG">
        <!-- 指定打印日志的appender，这里通过“STDOUT”引用了前面配置的appender -->
        <appender-ref ref="STDOUT" />
    </root>

    <!-- 根据特殊需求指定局部日志级别 -->
    <logger name="com.atguigu.crowd.mapper" level="DEBUG"/>

</configuration>

实现：

1.3.4 测试

<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println("user = " + user);
sqlSession.close();
SqlSession sqlSession1 = sqlSessionFactory.openSession();
UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
User user1 = mapper1.queryUserById(1);
System.out.println("user1 = " + user1);
sqlSession1.close();

第三章 MyBatis注解开发

1.1 @Insert注解

• 注解属性value:写入SQL语句

1.2 @Options注解

• 实现添加新数据的主键封装
• 注解属性
  • useGeneratedKeys：使用生成的主键，配置为true
  • keyProperty：主键封装的pojo对象属性

1.3 @SelectKey注解

• 实现添加新数据的主键封装
• 注解属性
  • statement：要执行的SQL语句
  • before：在添加SQL语句之前还是之后进行，配置为false
  • keyProperty：主键封装的pojo对象属性

1.4 注解实现添加数据

• UserMapper接口

  //新增用户数据
  @Insert("insert into user(username,sex,birthday,address)values(#{username},#{sex},#{birthday},#{address})")
  void saveUser(User user);
  

• UserMapper测试

  /**
  * 注解开发
  * 添加用户数据
  */
  @Test
  public void testSaveUser(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   User user = new User();
   user.setUsername("孙权");
   user.setSex("1");
   user.setBirthday(new Date());
   user.setAddress("东吴");
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   userMapper.saveUser(user);
   sqlSession.commit();
   System.out.println(user);
   sqlSession.close();
  }
  

2.1 @Update注解

• 注解属性value:写入SQL语句

2.2 注解实现更新数据

• UserMapper接口

  //跟新用户
  @Update("update user set username=#{username},sex=#{sex},birthday=#{birthday},address=#{address} where id=#{id}")
  void updateUser(User user);
  

• UserMapper测试

  /**
  * 注解开发
  * 更新用户数据
  */
  @Test
  public void testUpdateUser(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   User user = new User();
   user.setId(21);
   user.setUsername("孙策");
   user.setSex("2");
   user.setBirthday(new Date());
   user.setAddress("江东");
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   userMapper.updateUser(user);
   sqlSession.commit();
   sqlSession.close();
  }
  

3.1 @Delete注解

• 注解属性value:写入SQL语句

3.2 注解实现删除数据

• UserMapper接口

  //删除用户
  @Delete("delete from user where id = #{id}")
  void deleteUser(int id);
  

• UserMapper测试

  /**
  * 注解开发
  * 更新用户数据
  */
  @Test
  public void testDeleteUser(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   userMapper.deleteUser(21);
   sqlSession.commit();
   sqlSession.close();
  }
  

3.1 @Select注解

• 注解属性value:写入SQL语句

3.2 注解实现主键查询用户数据

• UserMapper接口

  //主键查询用户
  @Select("select id,username,sex,birthday,address from user where id = #{id}")
  User queryUserById(int id);
  

• UserMapper测试

  /**
  * 注解开发
  * 主键查询用户
  */
  @Test
  public void testQueryUserById(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   User user = userMapper.queryUserById(1);
   System.out.println(user);
   sqlSession.close();
  }
  

3.3 注解实现查询全部用户数据

• UserMapper接口

  //查询全部用户
  @Select("select id,username,sex,birthday,address from user")
  List<User> queryUserByList();
  

• UserMapper测试

  /**
  * 注解开发
  * 查询全部用户
  */
  @Test
  public void testQueryUserByList(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   List<User> userList = userMapper.queryUserByList();
   if (userList != null && userList.size() > 0){
       for (User user : userList){
           System.out.println(user);
       }
   }
   sqlSession.close();
  }
  

3.4 动态SQL语句之where和if

• UserMapper接口

  //多条件查询用户
  @Select("<script>select id,username,sex,birthday,address from user" +
       "<where>" +
       "<if test = \"sex != null and sex != ''\">" +
       " and sex = #{sex}" +
       "</if>" +
       "<if test = \"username != null and username != ''\">" +
       " and username like #{username}" +
       "</if>" +
       "</where></script>")
  List<User> queryUserByWhere(User user);
  

• UserMapper测试

  /**
  * 注解开发
  * 多条件查询用户信息
  */
  @Test
  public void testQueryUserByWhere(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   User user = new User();
   user.setSex("2");
   user.setUsername("%王%");
   List<User> userList = userMapper.queryUserByWhere(user);
   if(userList != null && userList.size() > 0){
       for(User user1 : userList){
           System.out.println(user1);
       }
   }
   sqlSession.close();
  }
  

3.5 动态SQL语句之foreach

• UserMapper接口

  @Select("<script>select id,username,sex,birthday,address from user" +
       "<foreach collection = \"list\" open = \"where id in(\" close = \")\" separator = \",\" item = \"item\">" +
       "#{item}" +
       "</foreach>" +
       "</script>")
  List<User> queryUserByListIds(List<Integer> ids);
  

• UserMapper测试

  /**
  * 注解开发
  * 传入List集合，查询用户数据
  */
  @Test
  public void testQueryUserByListIds(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   List<Integer> ids = new ArrayList<Integer>();
   ids.add(1);
   ids.add(2);
   ids.add(3);
   List<User> userList = userMapper.queryUserByListIds(ids);
   if(userList != null && userList.size() > 0){
       for(User user : userList){
           System.out.println(user);
       }
   }
   sqlSession.close();
  }
  

4.1 一对一的注解开发

4.1.1 @Results注解

配置手动映射，取代resultMap标签

4.2.2 @Result注解

配置手动映射，取代result标签

• OrdersMapper接口

  //根据订单查询用户，一对一查询
  @Select(" SELECT o.id,o.user_id,o.number,o.createtime,o.note FROM orders o")
  @Results({
       //配置主键映射，id默认false,不是主键
       @Result(id = true,column = "id",property = "id"),
       //配置其他映射关系
       @Result(column = "user_id",property = "userId"),
       @Result(column = "number",property = "number"),
       @Result(column = "createtime",property = "createtime"),
       @Result(column = "note",property = "note"),
       /*
            配置关联查询用户表
            property查询的pojo对象哪个属性做为条件查询
              这个属性还是个pojo对象
            column查询条件的pojo对象的属性
            @One注解配置一对一的另一个查询语句
              此语句需要对应的接口方法出现
        */
       @Result(column = "user_id",property = "user",javaType = User.class,
               one = @One(select = "com.atguigu.mapper.UserMapper.queryUserByUserId",fetchType = FetchType.LAZY))
  })
  List<Orders> queryOrdersUser();
  

• UserMapper接口

  @Select("select id,username,sex,birthday,address from user where id=#{user_Id}")
  User queryUserByUserId(Integer id);
  

• OrdersMapp接口测试

  /**
  *  注解开发
  *  一对一延迟加载测试
  */
  @Test
  public void testQueryOrdersUser(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
   List<Orders> ordersList = ordersMapper.queryOrdersUser();
   if(ordersList != null && ordersList.size() > 0){
       for(Orders orders : ordersList){
           System.out.println(orders);
           System.out.println(orders.getUser());
       }
   }
   sqlSession.close();
  }
  

4.2 一对多注解开发

• UserMapper接口

  //用户查询订单，一对多
  @Select("select id,username,sex,birthday,address from user")
  @Results({
       @Result(id = true,column = "id",property = "id"),
       @Result(column = "username",property ="username" ),
       @Result(column = "sex",property ="sex" ),
       @Result(column = "birthday",property ="birthday" ),
       @Result(column = "address",property ="address" ),
       @Result(column = "id",property = "ordersList",javaType = List.class,
       many = @Many(select = "com.atguigu.mapper.OrdersMapper.queryOrdersByUserId",fetchType = FetchType.LAZY))
  })
  List<User> queryUserOrders();
  

• OrdersMapper接口

  //用户查询订单，一对多
  @Select("select number,createtime from orders where user_id = #{user_id}" )
  List<Orders> queryOrdersByUserId(Integer user_id);
  

• UserMapper接口测试

  /**
  * 注解开发
  * 查询用户下的订单，一对多
  */
  @Test
  public void testQueryUserOrders(){
   SqlSession sqlSession = sqlSessionFactory.openSession();
   UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
   List<User> userList = userMapper.queryUserOrders();
   if(userList != null && userList.size() > 0){
       for(User user : userList){
           System.out.println(user);
           System.out.println(user.getOrdersList());
       }
   }
   sqlSession.close();
  }
  

第五章 逆向工程

我们以前开发的所有项目都属于是正向工程，pojo -> service -> dao -> 数据表 顺序开发。

逆向工程，就是通过数据表，反向创建pojo，Mapper接口