2021 June 12 Java后端开发

SSM-MyBatis(三)-多表查询、延迟加载

复杂多表查询

简介

  1. 多表关系
    1. 数据库的表之间可能存在一定的关系
      1. 一对多、多对一：人可以有多张银行卡，一张银行卡只能有一个人
      2. 一对一：人只有一张身份证
      3. 多对多：人与职业之间的关系，1个人可以同时干n个职位，1个职位可以有n个人
  2. 各种数据的bean设计如下图所示 图1
  3. 如何在MyBatis中表达这中多表关系呢？

代码举例

1.创建表结构

# drop
DROP TABLE IF EXISTS bank_card;
DROP TABLE IF EXISTS id_card;
DROP TABLE IF EXISTS person_job;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS job;
    
# person
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL
);
    
# bank_card
CREATE TABLE bank_card(
	id INT PRIMARY KEY AUTO_INCREMENT,
	no VARCHAR(30) NOT NULL UNIQUE,
	amout DECIMAL(18, 2) NOT NULL,
	person_id INT NOT NULL,
	FOREIGN KEY (person_id) REFERENCES person(id)
);
    
# id_card
CREATE TABLE id_card(
	id INT PRIMARY KEY AUTO_INCREMENT,
	no VARCHAR(30) NOT NULL UNIQUE,
	address VARCHAR(50) NOT NULL,
	person_id INT NOT NULL UNIQUE,
	FOREIGN KEY (person_id) REFERENCES person(id)
);
    
# job
CREATE TABLE job(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL UNIQUE,
	duty VARCHAR(50) NOT NULL
);
    
# person_job
CREATE TABLE person_job(
	person_id INT,
	job_id INT,
	PRIMARY KEY (person_id, job_id),
	FOREIGN KEY (person_id) REFERENCES person(id),
	FOREIGN KEY (job_id) REFERENCES job(id)
);
    
# data
INSERT INTO person(name) VALUES ('Jack'), ('Rose'), ('Larry'), ('Mike'), ('Tom'), ('James');
    
INSERT INTO id_card(no, address, person_id) VALUES 
('9527', '北京', 4),
('8866', '广州', 1),
('2495', '上海', 5),
('4378', '成都', 2),
('5454', '杭州', 6),
('9923', '深圳', 3);
    
INSERT INTO bank_card(no, amout, person_id) VALUES 
('6223', 0, 1),
('75556', 2098.56, 2),
('5345', 1010000.56, 1),
('87876', 534423.34, 3),
('654645', 432.45, 1),
('5434534', 234765.19, 4),
('76853', 98945.39, 4),
('6456867', 435534.78, 1),
('4324654', 874343.99, 4),
('53455', 5.20, 2);
    
INSERT INTO job(name, duty) VALUES 
('程序员', '每一天都在写新的bug和修改昨天的bug'),
('保安', '公司全系统物理安全保障专员'),
('网管', '世界互联网信息终端及人类信息科技部信息集成应用导师'),
('厨师', '类口腔神经末梢感应实验中心及绿色环保邮寄肥转换加工基地负责人'),
('贴膜', '智能高端移动设备表面高化合物平面处理'),
('搬砖', '长方体混泥土瞬间移动师'),
('算命', '主观性逻辑推论及心理引导'),
('理发师', '人体无用副组织切除手术主刀');
    
INSERT INTO person_job(person_id, job_id) VALUES 
(1, 1),
(1, 3),
(1, 5),
(1, 7),
(2, 5),
(3, 1),
(3, 2),
(5, 3),
(5, 5),
(5, 7);

2.创建bean对象（略）

3.mybatis-config.xml添加mapper

<!-- 映射文件 -->
<mappers>
    <mapper resource="mappers/person.xml"/>
    <mapper resource="mappers/idCard.xml"/>
    <mapper resource="mappers/bankCard.xml"/>
    <mapper resource="mappers/job.xml"/>
</mappers>

4.mappers对应的bean.xml如下

  1. person.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="person">
     <!--查询人信息对应的：身份证信息、银行卡信息、工作信息-->
     <sql id="sqlListAll">
         SELECT
             p.*,
             ic.id id_card_id,
             ic.no id_card_no,
             ic.address id_card_address,
             bc.id bank_card_id,
             bc.no bank_card_no,
             bc.amout bank_card_amout,
             j.id job_id,
             j.name job_name,
             j.duty job_duty
         FROM
             person p
         JOIN id_card ic ON p.id = ic.person_id
         LEFT JOIN bank_card bc ON p.id = bc.person_id
         LEFT JOIN person_job pj ON p.id = pj.person_id
         LEFT JOIN job j ON j.id = pj.job_id
     </sql>
    
     <resultMap id="rmList" type="com.zh.bean.Person">
         <id property="id" column="id"/>
         <result property="name" column="name"/>
         <!--映射内容是一个对象，即bean有一个对象bean属性属性-->
         <association property="idCard"
                      javaType="com.zh.bean.IdCard">
             <id property="id" column="id_card_id"/>
             <result property="no" column="id_card_no"/>
             <result property="address" column="id_card_address"/>
         </association>
         <!--映射内容是一个集合，即bean有一个集合属性，集合中存放bean对象-->
         <collection property="bankCards" ofType="com.zh.bean.BankCard">
             <id property="id" column="bank_card_id"/>
             <result property="no" column="bank_card_no"/>
             <result property="amout" column="bank_card_amout"/>
         </collection>
         <collection property="jobs" ofType="com.zh.bean.Job">
             <id property="id" column="job_id"/>
             <result property="name" column="job_name"/>
             <result property="duty" column="job_duty"/>
         </collection>
     </resultMap>
    
     <select id="list" resultMap="rmList">
         <include refid="sqlListAll"/>
     </select>
    
     <select id="get" parameterType="int" resultMap="rmList">
         <include refid="sqlListAll"/> WHERE p.id = #{id}
     </select>
 </mapper>
    1. 注意：映射成一个对象用的是association，映射成一个集合用的是collection

  2. idCard.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="idCard">
     <!--查询所有的身份证信息对应的： 人的信息-->
     <sql id="sqlListAll">
         SELECT
             ic.*,
             p.name person_name
         FROM
             id_card ic
         JOIN person p ON p.id = ic.person_id
     </sql>
    
     <resultMap id="rmList" type="com.zh.bean.IdCard">
         <id property="id" column="id"/>
         <result property="no" column="no"/>
         <result property="address" column="address"/>
         <association property="person"
                      javaType="com.zh.bean.Person">
             <id property="id" column="person_id"/>
             <result property="name" column="person_name"/>
         </association>
     </resultMap>
    
     <select id="list" resultMap="rmList">
         <include refid="sqlListAll"/>
     </select>
    
     <select id="get" parameterType="int" resultMap="rmList">
         <include refid="sqlListAll"/> WHERE ic.id = #{id}
     </select>
 </mapper>

  3. bankCard.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="bankCard">
     <!--查询所有的银行卡信息对应的：人的信息-->
     <sql id="sqlListAll">
         SELECT
             bc.*,
             p.name person_name
         FROM
             bank_card bc
         JOIN person p ON p.id = bc.person_id
     </sql>
    
     <resultMap id="rmList" type="com.zh.bean.BankCard">
         <id property="id" column="id"/>
         <result property="no" column="no"/>
         <result property="amout" column="amout"/>
         <association property="person"
                      javaType="com.zh.bean.Person">
             <id property="id" column="person_id"/>
             <result property="name" column="person_name"/>
         </association>
     </resultMap>
    
     <select id="list" resultMap="rmList">
         <include refid="sqlListAll"/>
     </select>
    
     <select id="get" parameterType="int" resultMap="rmList">
         <include refid="sqlListAll"/> WHERE bc.id = #{id}
     </select>
 </mapper>

  4. job.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="job">
     <!-- 查询所有的职业信息，对应的人的信息 -->
     <sql id="sqlListAll">
         SELECT
             j.*,
             p.id person_id,
             p.name person_name
         FROM
             job j
         LEFT JOIN person_job pj ON j.id = pj.job_id
         LEFT JOIN person p ON p.id = pj.person_id
     </sql>
    
     <resultMap id="rmList" type="com.zh.bean.Job">
         <id property="id" column="id"/>
         <result property="name" column="name"/>
         <result property="duty" column="duty"/>
         <collection property="persons" ofType="com.zh.bean.Person">
             <id property="id" column="person_id"/>
             <result property="name" column="person_name"/>
         </collection>
     </resultMap>
    
     <select id="list" resultMap="rmList">
         <include refid="sqlListAll"/>
     </select>
    
     <select id="get" parameterType="int" resultMap="rmList">
         <include refid="sqlListAll"/> WHERE j.id = #{id}
     </select>
 </mapper>

5.test测试

//OneToOne
public class OneToOne {
    @Test
    //查询所有的人信息：n个人对应n张身份证
    public void personList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<Person> persons = session.selectList("person.list");
            System.out.println(persons);
        }
    }

    @Test
    //查询某个人
    public void personGet() {
        try (SqlSession session = MyBatises.openSession()) {
            Person person = session.selectOne("person.get", 1);
            System.out.println(person);
        }
    }

    @Test
    //查询所有的身份证信息
    public void idCardList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<IdCard> idCards = session.selectList("idCard.list");
            System.out.println(idCards);
        }
    }

    @Test
    //查询某个身份证
    public void idCardGet() {
        try (SqlSession session = MyBatises.openSession()) {
            IdCard idCard = session.selectOne("idCard.get", 1);
            System.out.println(idCard);
        }
    }
}

//OneToMany
public class OneToMany {
    //n个人对应多张银行卡
    @Test
    public void personList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<Person> persons = session.selectList("person.list");
            System.out.println(persons);
        }
    }

    //一个人对应n张银行卡
    @Test
    public void personGet() {
        try (SqlSession session = MyBatises.openSession()) {
            Person person = session.selectOne("person.get", 1);
            System.out.println(person);
        }
    }

    //n张银行卡对应n个人
    @Test
    public void bankCardList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<BankCard> bankCards = session.selectList("bankCard.list");
            System.out.println(bankCards);
        }
    }

    //一张银行卡对应1个人
    @Test
    public void bankCardGet() {
        try (SqlSession session = MyBatises.openSession()) {
            BankCard bankCard = session.selectOne("bankCard.get", 1);
            System.out.println(bankCard);
        }
    }
}

//ManyToMany
public class ManyToMany {
    //多有的人
    @Test
    public void personList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<Person> persons = session.selectList("person.list");
            System.out.println(persons);
        }
    }

    //一个人对应n个工作
    @Test
    public void personGet() {
        try (SqlSession session = MyBatises.openSession()) {
            Person person = session.selectOne("person.get", 1);
            System.out.println(person);
        }
    }

    //n个工作，对应多个人
    @Test
    public void jobList() {
        try (SqlSession session = MyBatises.openSession()) {
            List<Job> jobs = session.selectList("job.list");
            System.out.println(jobs);
        }
    }

    //一份工作对应n个人
    @Test
    public void jobGet() {
        try (SqlSession session = MyBatises.openSession()) {
            Job job = session.selectOne("job.get", 1);
            System.out.println(job);
        }
    }
}

延迟加载

简介

  1. 何为延迟加载？
    1. 在一对多、多对多的表关系中，某些时候并不需要用到那张多表，可以等用到的时候再查询出来
    2. 比如：一个人对应n张银行卡，一开始根本用不到这n张卡的集合信息。等用到的时候再返回n张卡的信息
  2. 关联对象 (association、collection)可以实现延迟加载
    1. 上面讲到mapper映射文件，当bean的属性为对象时用association，当为集合时用collection
    2. 比如person对象的idCard属性与bankCards属性
    3. 因此如果要实现延迟加载操作也就是在mapper文件（person.xml）的association与collection标签中操作
  3. 实现延迟加载的步骤，还需要设置以下属性
    1. fetch Type：设置为lazy
    2. select：指定一个select标签的id， 用于查询需要延迟加载的记录
    3. column：指定一个列的值， 作为调用select查询时传入的参数值
  4. 在很多时候，都会给collection设置延迟加载

代码举例

  1. 在bankCard.xml、idCard.xml、job.xml分别添加通过personid属性获取对应bean信息的select标签

     //bankCard.xml
    
 <!-- 通过一个personid查出对应的银行卡-->
 <select id="listByPerson" parameterType="int" resultType="com.zh.bean.BankCard">
     SELECT * FROM bank_card WHERE person_id = #{personId}
 </select>
    
 //idCard.xml
 <!-- 通过Person的id查询对应的IdCard -->
 <select id="getByPerson" parameterType="int" resultType="com.zh.bean.IdCard">
     SELECT * FROM id_card WHERE person_id = #{personId}
 </select>
    
 //job.xml
 <!-- 根据personid查询成对应的职业信息-->
 <select id="listByPerson" parameterType="int" resultType="com.zh.bean.Job">
     SELECT
         j.*
     FROM
         job j
     JOIN
         person_job pj
     ON
         j.id = pj.job_id AND pj.person_id = #{personId}
 </select>

  2. 改造person.xml代码

    1. 修改查询语句：只需要简单查询所有person信息

       <sql id="sqlListAll">
     SELECT * FROM person
 </sql>

    2. 设置person对象属性、集合属性对应的懒加载

       <resultMap id="rmList" type="com.zh.bean.Person">
     <id property="id" column="id"/>
     <result property="name" column="name"/>
     <!--映射内容是一个对象，即bean有一个对象bean属性属性-->
     <!--
     fetchType：指定抓取的方式是懒加载lazy
     select:指明如果要延迟加载这个javaType类型数据，通过哪个sql语句
     select="idCard.getByPerson":就相当于拿到了idCard.xml文件中命名空间为idCard下的id为getByPerson的标签对应的sql语句
     column：指定查询出来的数据的哪一列的字段作为参数传递给select指定的sql语句中去
     column="id"：将查询出来的person对象的id属性作为参数传递给select指定的sql语句
      -->
     <association property="idCard"
                  fetchType="lazy"
                  column="id"
                  select="idCard.getByPerson"
                  javaType="com.zh.bean.IdCard"/>
     <!--映射内容是一个集合，即bean有一个集合属性，集合中存放bean对象-->
     <collection property="bankCards"
                 fetchType="lazy"
                 column="id"
                 select="bankCard.listByPerson"
                 ofType="com.zh.bean.BankCard"/>
    
     <collection property="jobs"
                 fetchType="lazy"
                 column="id"
                 select="job.listByPerson"
                 ofType="com.zh.bean.Job"/>
 </resultMap>

    3. 查询语句不变

       <select id="list" resultMap="rmList">
     <include refid="sqlListAll"/>
 </select>
    
 <select id="get" parameterType="int" resultMap="rmList">
     <include refid="sqlListAll"/> WHERE id = #{id}
 </select>

    4. 代码测试

       public void personGet() {
     try (SqlSession session = MyBatises.openSession()) {
         //不会懒加载person对象对应的对象属性、集合属性
         Person person = session.selectOne("person.get", 1);
         //会自动懒加载获取idCard信息
         person.getIdCard();
         //会自动懒加载获取bankCards信息
         person.getBankCards();
         //会自动懒加载获取所有集合、对象信息
         //System.out.println(person);
     }
 }

3.如何设置有些延迟加载有些不延迟加载，person.xml代码如下

<mapper namespace="person">
    <!--查询人信息对应的：身份证信息、银行卡信息、工作信息-->
    <sql id="sqlListAll">
        SELECT
            p.*,
            ic.id id_card_id,
            ic.no id_card_no,
            ic.address id_card_address
        FROM
            person p
        JOIN id_card ic ON p.id = ic.person_id
    </sql>

    <resultMap id="rmList" type="com.zh.bean.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="idCard" javaType="com.zh.bean.IdCard">
            <id property="id" column="id_card_id"/>
            <result property="no" column="id_card_no"/>
            <result property="address" column="id_card_address"/>
        </association>
        <!--映射内容是一个集合，即bean有一个集合属性，集合中存放bean对象-->
        <collection property="bankCards"
                    fetchType="lazy"
                    column="id"
                    select="bankCard.listByPerson"
                    ofType="com.zh.bean.BankCard"/>

        <collection property="jobs"
                    fetchType="lazy"
                    column="id"
                    select="job.listByPerson"
                    ofType="com.zh.bean.Job"/>
    </resultMap>

    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>

    <select id="get" parameterType="int" resultMap="rmList">
        <include refid="sqlListAll"/> WHERE id = #{id}
    </select>
</mapper>
  1. 不需要延迟加载的idCard属性要在sql语句中写出来，而且要设置具体的association映射

全局延迟加载开关

  1. 全局延迟加载开关， 在mybatis-config.xml的settings中配置
  2. <setting name="lazyLoadingEnabled" value="true"/>
    1. true：所有设置了select属性的关联对象默认懒加载，可以通过fetchType覆盖这个全局设置
    2. false：所有设置了select属性的关联对象默认立即加载，可以通过fetchType覆盖这个全局设置
    3. fetchType有2个属性值：
      1. eager:立即加载
      2. lazy:延迟加载
    4. 说明：
      1. 这个是配置在核心文件mybatis-config.xml中，是针对所有的mapper.xml文件，相当于全局配置
      2. fetchType属性是针对某个xml（比如peson.xml）的，局部大于全局
      3. 比如：在mybatis-config.xml中设置<setting name="lazyLoadingEnabled" value="false"/>，代表项目的所有设置了select属性的关联对象都要立即加载
      4. 在peson.xml的collection的bankCards标签中设置fetchType=”lazy”，那么person的bankCards属性仍然会懒加载
  3. <setting name="aggressiveLazyLoading" value="false"/>
    1. true：当调用主对象的任意方法，都会触发立即加载关联对象
    2. false：只有当真正用到关联对象时，才会去加载关联对象

Mybatis总结

  1. 本质就是将dao层进行封装的一个框架，简化了dao层访问数据库的代码
  2. 就是将sql语句标签（xml）化
  3. 将数据库初始化放在全局文件mybatis-config.xml中
  4. 将针对每个表的数据库访问语句放在每个bean对应的xml中，相当于dao
  5. 将sql语句查询出来的数据直接转化为bean对象
  6. 如果是复杂数据类型，分别通过关联对象标签(association、collection)来具体设置
  7. java语法如何调用标签？

    1. 创建一个继承自UnpooledDataSourceFactory的类，然后实现无参构造函数，并设置数据源

       public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
     public DruidDataSourceFactory() {
         this.dataSource = new DruidDataSource();
     }
 }

    2. 封装一个session类

       public class MyBatises {
     private static SqlSessionFactory factory;
     static {
         try (Reader reader  = Resources.getResourceAsReader("mybatis-config.xml")) {
             // 创建一个工厂
             factory = new SqlSessionFactoryBuilder().build(reader);
         } catch (Exception e) {
             e.printStackTrace();
         }
     }
        
     public static SqlSession openSession(boolean autoCommit) {
         return factory.openSession(autoCommit);
     }
        
     public static SqlSession openSession() {
         return factory.openSession();
     }
 }

    3. 调用标签

       public void personGet() {
     try (SqlSession session = MyBatises.openSession()) {
         //调用person.xml下的get标签对应的sql语句
         Person person = session.selectOne("person.get", 1);
     }
 }

All content is licensed under CC BY-NC-SA

Buit with Jekyll and 3-Jekyll theme • Hosted on Github

Table of Contents