mybatis的使用及源码分析(十) mybatis使用xml两种方式一对多关联查询

上一章讲到xml方式的关联查询，查询的省份表，是多对一的，这里实现一对多的方式

本项目搭建源码：
https://github.com/zhuquanwen/mybatis-learn/releases/tag/for-xml-association
搭建过程：
在https://blog.csdn.net/u011943534/article/details/108411836文章基础上搭建，有些过程不详细描述.

一、嵌套查询的方式(分步)
1 创建一个MyProvince，为id和name重新定义名字，因为和city的字段有重复

package com.learn.zqw.association.domain;

import lombok.Data;

import java.util.List;

/**
 * //TODO
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/9/6 20:59
 * @since jdk1.8
 */
@Data
public class MyProvince {
    private Integer provinceId;

    private String provinceName;

    private List<City> cityList;
}

2 定义结果集

 <resultMap id="withCitysResultMap0" type="com.learn.zqw.association.domain.MyProvince">
    <id column="province_id" jdbcType="INTEGER" property="provinceId" />
    <result column="province_name" jdbcType="VARCHAR" property="provinceName" />
    <collection property="cityList" column="province_id" javaType="ArrayList" ofType="com.learn.zqw.association.domain.City" select="com.learn.zqw.association.mapper.CityMapper.selectByPid">
    </collection>
  </resultMap>

上面定义的selectByPid函数，需要在cityMapper 和cityMapper.xml中定义

  <select id="selectByPid" parameterType="java.lang.Integer" resultType="com.learn.zqw.association.domain.City">
    select id,name,pid from city where pid = #{pid, jdbcType=INTEGER}
  </select>

City selectByPid(Integer pid);

3 定义select

  <select id="selectWithCitysById0" parameterType="java.lang.Integer" resultMap="withCitysResultMap0">
    select pid as province_id, p.name as province_name
    from province
    where id = #{id,jdbcType=INTEGER}
  </select>

4 定义接口

MyProvince selectWithCitysById0(Integer id);

5 测试

    /**
     * 一对多嵌套(多步)关联查询
     * */
    @Test
    public void test4() throws IOException {
        @Cleanup InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sessionFactory.openSession();
        ProvinceMapper mapper = session.getMapper(ProvinceMapper.class);
        MyProvince province = mapper.selectWithCitysById0(1);
        System.out.println(province);
    }

结果：

[2020/09/06 21:55:08,944] [DEBUG] [org.apache.ibatis.transaction.jdbc.JdbcTransaction:100] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@f2f2cc1]
[2020/09/06 21:55:08,949] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById0:143] - ==>  Preparing: select p.id as province_id, p.name as province_name from province p where id = ? 
[2020/09/06 21:55:08,977] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById0:143] - ==> Parameters: 1(Integer)
[2020/09/06 21:55:08,994] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectByPid:143] - ====>  Preparing: select id,name,pid from city where pid = ? 
[2020/09/06 21:55:08,994] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectByPid:143] - ====> Parameters: 1(Integer)
[2020/09/06 21:55:08,997] [DEBUG] [com.learn.zqw.association.mapper.CityMapper.selectByPid:143] - <====      Total: 6
[2020/09/06 21:55:08,997] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById0:143] - <==      Total: 1
[2020/09/06 21:55:08,998] [DEBUG] [com.learn.zqw.plugin.TestPlugin:38] - 耗时：381ms
MyProvince(provinceId=1, provinceName=黑龙江, cityList=[City(id=1, name=哈尔滨, pid=1, province=null), City(id=2, name=黑河, pid=1, province=null), City(id=3, name=牡丹江, pid=1, province=null), City(id=4, name=齐齐哈尔, pid=1, province=null), City(id=5, name=大庆, pid=1, province=null), City(id=6, name=鸡西, pid=1, province=null)])

Process finished with exit code 0

二、嵌套结果的方式(单步)

1 定义结果集

  <resultMap id="withCitysResultMap" type="com.learn.zqw.association.domain.MyProvince">
    <id column="province_id" jdbcType="INTEGER" property="provinceId" />
    <result column="province_name" jdbcType="VARCHAR" property="provinceName" />
    <collection property="cityList" ofType="com.learn.zqw.association.domain.City">
      <id property="id" jdbcType="INTEGER" column="id"/>
      <result property="name" jdbcType="VARCHAR" column="name"/>
      <result property="pid" jdbcType="INTEGER" column="pid"/>
    </collection>
  </resultMap>

2 定义select

  <select id="selectWithCitysById" parameterType="java.lang.Integer" resultMap="withCitysResultMap">
    select p.id as province_id, p.name as province_name, c.id, c.name, c.pid from province p left join city c on p.id = c.pid
    where p.id = #{id,jdbcType=INTEGER}
  </select>

3 定义接口

MyProvince selectWithCitysById(Integer id);

4 测试

    /**
     * 一对多嵌套结果(单步)关联查询
     * */
    @Test
    public void test3() throws IOException {
        @Cleanup InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sessionFactory.openSession();
        ProvinceMapper mapper = session.getMapper(ProvinceMapper.class);
        MyProvince province = mapper.selectWithCitysById(1);
        System.out.println(province);
    }

结果：

[2020/09/06 21:59:09,988] [DEBUG] [org.apache.ibatis.transaction.jdbc.JdbcTransaction:100] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@f2f2cc1]
[2020/09/06 21:59:09,994] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById:143] - ==>  Preparing: select p.id as province_id, p.name as province_name, c.id, c.name, c.pid from province p left join city c on p.id = c.pid where p.id = ? 
[2020/09/06 21:59:10,020] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById:143] - ==> Parameters: 1(Integer)
[2020/09/06 21:59:10,040] [DEBUG] [com.learn.zqw.association.mapper.ProvinceMapper.selectWithCitysById:143] - <==      Total: 6
[2020/09/06 21:59:10,040] [DEBUG] [com.learn.zqw.plugin.TestPlugin:38] - 耗时：379ms
MyProvince(provinceId=1, provinceName=黑龙江, cityList=[City(id=1, name=哈尔滨, pid=1, province=null), City(id=2, name=黑河, pid=1, province=null), City(id=3, name=牡丹江, pid=1, province=null), City(id=4, name=齐齐哈尔, pid=1, province=null), City(id=5, name=大庆, pid=1, province=null), City(id=6, name=鸡西, pid=1, province=null)])

Process finished with exit code 0