MyBatis高级 - 《MySQL》

多表关联：一对一关联(重点)
- association标签的作用：一对一关联映射
多表关联：一对多关联(重点)
多表关联: 多对多(两个一对多)
- collection的作用：一对多的关联映射
涉及数据表
MyBatis缓存
- 概述
- 一级缓存
二级缓存
- 概述
- 实现步骤

多表关联：一对一关联(重点)

association标签的作用：一对一关联映射
property 指定另一方对象的属性名字 resultMap 指定另一方的映射

// 这个一个MyBatis工具类,简化MyBatis开发
public class MyBatisUtils {
    // SqlSessionFactory: 理解为连接池
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            // 核心配置文件的名字
            String resource = "mybatis-config.xml";
            // 把核心配置文件变成流(相当于创建了一个输入流)
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // new SqlSessionFactoryBuilder().build(inputStream): 解析mybatis-config.xml文件和UserMapper.xml文件
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            System.out.println("MyBatis初始化失败: " + e.toString());
        }
    }
    // SqlSession: 相当于数据库连接池中的连接 (SqlSession有操作事务的功能每个功能都要拿到一个新的SqlSession,保证事务准确)
    public static SqlSession openSession() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
    // 可以自动提交事务
    public static SqlSession openSession(boolean autoCommit) {
        SqlSession sqlSession = sqlSessionFactory.openSession(autoCommit);
        return sqlSession;
    }
}

//user表实现类
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //一对一,一个用户号包含一个扩展信息
    private UserInfo userInfo;
    //构造方法setgettoString方法
}
//user_into表实现类
public class UserInfo {
    private int id;
    private double height;
    private double weight;
    private boolean married;
}

public class TestMybatis {
    public static SqlSession sqlSession;
    @Test
    public void test01() {
        SqlSession sqlSession = MyBatisUtils.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findUserAndInfo(1);
        System.out.println(user);
        sqlSession.close();
    }
}
public interface UserMapper {
    User findUserAndInfo(int uid);
}

<?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">
  <!--MyBatis默认多表查询,表中字段名和类中变量名相同的也要配置
      autoMapping="true": 字段名和变量名相同的就不用指定-->
<mapper namespace="com.gdkm.mapper.UserMapper">
    <resultMap id="InfoMap" type="UserInfo" autoMapping="true">
        <id column="id" property="id"/>
    </resultMap> 
    <resultMap id="UserMap" type="user" autoMapping="true">
        <id column="id" property="id"/>
        <!--配置一对一-->
        <association property="userInfo" resultMap="InfoMap"/>
    </resultMap>
    <!--根据用户id查询用户基本信息和扩展信息-->
    <select id="findUserAndInfo" resultMap="UserMap">
        SELECT
            * 
        FROM 
            USER INNER JOIN user_info ON user.id = user_info.id 
        WHERE 
            user.id = #{uid};
    </select>
</mapper>

多表关联：一对多关联(重点)

//user表实现类
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //一对多,一个用户包含多个订单
    private List<Order> order;
}
//order表实现类
public class Order {
    private int oid;
    private int userId;
    private String number;
    private Timestamp createTime;
    private String note;
}

public class TestMybatis {
    public static SqlSession sqlSession;
    @Test
    public void test02() {
        SqlSession sqlSession = MyBatisUtils.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findUserAndOrders(1);
        System.out.println(user);
        user.getOrder().forEach(System.out::println);
        sqlSession.close();
    }
}
public interface UserMapper {
    User findUserAndOrders(int uid);
}

<?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">
  <!--MyBatis默认多表查询,表中字段名和类中变量名相同的也要配置
      autoMapping="true": 字段名和变量名相同的就不用指定-->
<mapper namespace="com.gdkm.mapper.UserMapper">
    <resultMap id="OrderMap" type="Order" autoMapping="true">
        <id column="oid" property="oid"/>
    </resultMap>
    <resultMap id="UserMap" type="user" autoMapping="true">
        <id column="id" property="id"/>
        <!--配置一对多-->
        <collection property="order" resultMap="OrderMap"/>
    </resultMap>
    <!--根据用户id查询用户基本信息和所有订单-->
    <select id="findUserAndOrders" resultMap="UserMap">
        SELECT 
            *
        FROM
            USER INNER JOIN `order` ON user.id = `order`.user_id 
        WHERE
            user.id = #{uid};
    </select>
</mapper>

多表关联: 多对多(两个一对多)

collection的作用：一对多的关联映射
property 多方属性的名字 javaType 属性的类型 ofType 元素的类型

根据用户id查询用户基本信息和所有角色

//user表实现类
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    // 一对多(一个用户包含多个角色)
    private List<Role> roles;
}
//role表实现类
public class Role {
    private int roleId;
    private String roleName;
    private String roleDetail;
}

public class TestMybatis {
    public static SqlSession sqlSession;
    @Test
    public void test03() {
        SqlSession sqlSession = MyBatisUtils.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findUserAndRoles(1);
        System.out.println(user);
        user.getRoles().forEach(System.out::println);
        sqlSession.close();
    }
}
public interface UserMapper {
    User findUserAndRoles(int uid);
}

<?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">
  <!--MyBatis默认多表查询,表中字段名和类中变量名相同的也要配置
      autoMapping="true": 字段名和变量名相同的就不用指定-->
<mapper namespace="com.gdkm.mapper.UserMapper">
    <resultMap id="UserMap" type="user" autoMapping="true">
        <id column="id" property="id"/>
        <!--配置一对多 简化写法-->
        <collection property="roles" javaType="List" ofType="Role" autoMapping="true">
            <id column="role_id" property="roleId"/>
        </collection>
    </resultMap>
    <!--根据用户id查询用户基本信息和所有角色-->
    <select id="findUserAndRoles" resultMap="UserMap">
        SELECT
            *
        FROM
            USER INNER JOIN user_role ON user.`id`= user_role.`user_id`
            INNER JOIN role ON user_role.`role_id`=role.`role_id`
        WHERE
            user.`id` = #{uid};
    </select>
</mapper>

根据角色id查询角色所有信息和所有用户

//user表实现类
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}
//role表实现类
public class Role {
    private int roleId;
    private String roleName;
    private String roleDetail;
    // 一对多(一个角色包含多个用户)
    private List<User> users;
}

public class TestMybatis {
    public static SqlSession sqlSession;
    @Test
    public void test04() {
        SqlSession sqlSession = MyBatisUtils.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Role role = mapper.findRoleAndUsers(1);
        System.out.println(role);
        role.getUsers().forEach(System.out::println);
        sqlSession.close();
    }
}
public interface UserMapper {
    Role findRoleAndUsers(int uid);
}

<?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">
  <!--MyBatis默认多表查询,表中字段名和类中变量名相同的也要配置
      autoMapping="true": 字段名和变量名相同的就不用指定-->
<mapper namespace="com.gdkm.mapper.UserMapper">
    <resultMap id="RoleMap" type="Role" autoMapping="true">
        <id column="role_id" property="roleId"/>
        <!--配置一对多-->
        <collection property="users" javaType="List" ofType="User" autoMapping="true">
            <id column="id" property="id"/>
        </collection>
    </resultMap>
    <!--根据角色id查询角色和所有用户-->
    <select id="findRoleAndUsers" resultMap="RoleMap">
        SELECT
        *
        FROM
        USER INNER JOIN user_role ON user.`id`=user_role.`user_id`
        INNER JOIN role ON user_role.`role_id`=role.`role_id`
        WHERE
        role.`role_id`=#{rid};
    </select>
</mapper>

涉及数据表

user

user_info

order

user_role

role

MyBatis缓存

概述

MyBatis框架提供了缓存策略，通过缓存策略可以减少查询数据库的次数，提升系统性能
MyBatis框架中缓存分为一级缓存和二级缓存

一级缓存

一级缓存是sqlSession范围的缓存，只能在同一个sqlSession内部有效。它本身已经存在，一级缓存不需要手动处理，可以直接使用
第一次查询数据时，会将查询的数据放入一级缓存中。后面的相同查询直接从缓存中获取
一级缓存是 SqlSession 范围缓存。当调用 SqlSession 的修改、添加、删除、提交、关闭等方法时，一级缓存会被清空

// 测试一级缓存
public void test03() {
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    // 第一次查询
    User user = mapper.findUserById(1);
    System.out.println(user.getUsername());
    // 清除缓存
    // sqlSession.clearCache();
    // 第二次查询
    User user2 = mapper.findUserById(1);
    System.out.println(user2.getUsername());
    sqlSession.close();
}

二级缓存

概述

二级缓存是mapper映射级别缓存，作用范围跨越SqlSession，即可以在多个SqlSession之间共享二级缓存数据
实现步骤
修改实体类实现Serializable接口

public class 实现类名 implements Serializable{ }

在 mybatis-config.xml 配置开启二级缓存

在 UserMapper.xml 开启二级缓存

使用准备2个SqlSession进行测试

public void test04() {
    // 第一个sqlSession
    SqlSession sqlSession1 = MyBatisUtils.getSqlSession();
    UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
    User user1 = mapper1.findUserById(1);
    System.out.println("第一次" + user1.getUsername());
    sqlSession1.close(); // 要关闭数据才会保存二级缓存中
    // 第二个sqlSession
    SqlSession sqlSession2 = MyBatisUtils.getSqlSession();
    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
    User user2 = mapper2.findUserById(1);
    System.out.println("第二次" + user2.getUsername());
    sqlSession2.close();
}