Mybatis - collection 一对多级联（单层级联、

上一节中说到了association 一对一级联，本节继续上一节的内容来实现 collection 一对多级联，本节继续使用上一节的demo

• 单层级联

1. 新建一个主题表specials，与上一节中讲到的authors表形成一个一对多的关系，一个作者可以创建多个主题

   
   image.png image.png
2. 将specials表通过mybatis-generator生成对应的信息到项目中
3. Special类内容如下

@Data
public class Special {
    private String id;

    private String name;

    private String authorId;

}

4. SpecialMapper 类内容如下

@Mapper
public interface SpecialMapper {

    List<Special> getSpecialsByAuthorId(String authorId);
    
}

5. SpecialMapper.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="com.sy.mybatis.mapper.SpecialMapper" >
  <resultMap id="BaseResultMap" type="com.sy.mybatis.pojo.Special" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="author_id" property="authorId" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, author_id
  </sql>

  <select id="getSpecialsByAuthorId" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 'false' as QUERYID,
    <include refid="Base_Column_List" />
    from specials
    where author_id = #{author_id,jdbcType=VARCHAR}
  </select>

</mapper>

• 上面的内容就是平时普通用的，下面开始加入一对多级联的逻辑

6. 编辑 Author类，添加specials属性，形成级联

@Data
public class Author {
    private String id;

    private String name;

    private String loginName;

    private String pwssword;

    private AuthorInformation authorInformation;

    private List<Special> specials;
}

7. 编辑AuthorMapper.xml，在 <resultMap> 中加入<collection>

<?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="com.sy.mybatis.mapper.AuthorMapper" >
  <resultMap id="BaseResultMap" type="com.sy.mybatis.pojo.Author" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="login_name" property="loginName" jdbcType="VARCHAR" />
    <result column="pwssword" property="pwssword" jdbcType="VARCHAR" />
    <association property="authorInformation" column="id" select="com.sy.mybatis.mapper.AuthorInformationMapper.getAuthorInformationbyAuthorId"></association>
    <collection property="specials" column="id" select="com.sy.mybatis.mapper.SpecialMapper.getSpecialsByAuthorId"></collection>
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, login_name, pwssword
  </sql>

  <select id="getAuthorById" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 'false' as QUERYID,
    <include refid="Base_Column_List" />
    from authors
    where id = #{id,jdbcType=VARCHAR}
  </select>

</mapper>

<collection property="specials" column="id" select="com.sy.mybatis.mapper.SpecialMapper.getSpecialsByAuthorId"></collection>
select 元素来指定指定的sql去查询
column 传递给 select 语句的参数，多个参数，使用逗号分隔
与上一节中讲到的 <association> 一样的用法

测试

调用AuthorService的getAuthorById方法获取作者信息

image.png

可以看到在获取authors表信息的时候把specials对应的信息也查询了出来。

查看控制台打印的日志也可以看到整个sql的执行过程。 image.png

• 多层级联

1. 新建一个文章表articles，与上面讲到的specials表形成一个多对多的关系，一个作者可以创建多个主题，一个主题可以有多篇文章

   
   image.png
   image.png

2. 将articles表通过mybatis-generator生成对应的信息到项目中

3. Article类内容如下

@Data
public class Article {
    private String id;

    private String title;

    private String specialId;

    private String content;

}

4. ArticleMapper 类内容如下

@Mapper
public interface ArticleMapper {

    List<Article> getArticlesBySpecialId(String authorId);

}

5. ArticleMapper.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="com.sy.mybatis.mapper.ArticleMapper" >
  <resultMap id="BaseResultMap" type="com.sy.mybatis.pojo.Article" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="title" property="title" jdbcType="VARCHAR" />
    <result column="special_id" property="specialId" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.sy.mybatis.pojo.Article" extends="BaseResultMap" >
    <result column="content" property="content" jdbcType="LONGVARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, title, special_id
  </sql>
  <sql id="Blob_Column_List" >
    content
  </sql>
  <select id="getArticlesBySpecialId" resultMap="ResultMapWithBLOBs" parameterType="java.lang.String" >
    select 'false' as QUERYID,
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from articles
    where special_id = #{special_id,jdbcType=VARCHAR}
  </select>

</mapper>

• 上面的内容就是平时普通用的，下面开始加入多对多级联的逻辑

6. 编辑 Special类，添加articles属性，形成级联

@Data
public class Special {
    private String id;

    private String name;

    private String authorId;

    private List<Article> articles;

}

7. 编辑SpecialMapper.xml，在 <resultMap> 中加入<collection>

<?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="com.sy.mybatis.mapper.SpecialMapper" >
  <resultMap id="BaseResultMap" type="com.sy.mybatis.pojo.Special" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="author_id" property="authorId" jdbcType="VARCHAR" />
    <collection property="articles"  column="id" select="com.sy.mybatis.mapper.ArticleMapper.getArticlesBySpecialId"></collection>
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, author_id
  </sql>

  <select id="getSpecialsByAuthorId" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 'false' as QUERYID,
    <include refid="Base_Column_List" />
    from specials
    where author_id = #{author_id,jdbcType=VARCHAR}
  </select>

</mapper>

<collection property="articles" column="id" select="com.sy.mybatis.mapper.ArticleMapper.getArticlesBySpecialId"></collection>
select 元素来指定指定的sql去查询
column 传递给 select 语句的参数，多个参数，使用逗号分隔

测试

调用AuthorService的getAuthorById方法获取作者信息

image.png

可以看到在获取authors表信息的时候把special对应的信息也查询了出来,同时special与article的级联信息也一并查询出来了。

查看控制台打印的日志也可以看到整个sql的执行过程。 image.png