MyBatis之多对多联合查询

摘要：
本文详细记录了SpringBoot整合MyBatis之多对多联合查询，从实体类、接口类、配置文件以及接口测试。

相关环境：
SpringBoot + MyBatis

---

1. 什么是多对多关系？

多对多关系：多对多关系是关系数据库中两个表之间的一种关系， 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。

举例：博客与博客标签的多对多关系

2. 具体配置

2.1 pom.xml

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.3.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

2.2 application.yml

mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath*:mapper/**/*.xml
  type-aliases-package: com.mzr.blog.dao

3. 表设计

blog表：

Tag表：

blog_tags表：

4. 实体类（pojo）：

4.1 博客类：

//Blog类

@Getter
@Setter
@ToString
public class Blog {
    private Long id;    //博客编号
    private Integer views;  //浏览次数
    private String description; //博客描述
    private String firstPicture;    //博客首图
    private String flag;    //博客标记
    private String title;   //博客标题
    private Boolean appreciation;   //博客赞赏是否开启
    private Boolean commendable;   //博客评论是否开启
    private Boolean published;  //是否发布
    private Boolean recommend;  //是否推荐
    private Boolean shareStatement; //转载声明是否开启
    private Date createTime;    //创建时间
    private Date updateTime;    //更新时间
    private Long typeId;    //博客类别编号
    private Long userId;    //用户编号
    private String content; //博客内容
    private Type type;  //博客类别
    private User user;  //用户
    private List<Comment> comments = new ArrayList<>();	//博客评论
    private List<Tag> tags = new ArrayList<>(); //博客标签，多对多关系
}

4.2 标签类：

//Tag类

@Getter
@Setter
@ToString
public class Tag {
    private Long id;    //标签编号
    private String name;    //标签名
}

dao层（mapper）：

@Mapper
@Component
public interface BlogMapper {
    //根据标签id查询博客
    List<Blog> listBlogByTagId(@Param("tagId") Long tagId);
}

Service层：

public interface BlogService {
	List<Blog> findBlogsByTagId(Long tagId);
}

接口实例（BlogServiceImpl ）：

@Service
public class BlogServiceImpl implements BlogService {

    @Autowired
    private BlogMapper blogMapper;
    
    @Override
    public List<Blog> findBlogsByTagId(Long tagId) {
        return blogMapper.listBlogByTagId(tagId);
    }
}

BlogMapper.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.mzr.blog.dao.BlogMapper">
  <!--自定义结果映射-->
  <resultMap id="TagWithBlogResult" type="com.mzr.blog.pojo.Blog">
    <id property="id" column="id" />
    <result column="appreciation" property="appreciation" />
    <result column="commendable" property="commendable" />
    <result column="create_time" property="createTime" />
    <result column="description" property="description" />
    <result column="first_picture" property="firstPicture" />
    <result column="content" property="content" />
    <result column="flag" property="flag" />
    <result column="published" property="published" />
    <result column="recommend" property="recommend" />
    <result column="share_statement" property="shareStatement" />
    <result column="title" property="title" />
    <result column="update_time" property="updateTime" />
    <result column="views" property="views" />
    <result column="type_id" property="typeId" />
    <result column="user_id" property="userId" />
    <!-- 指定联合查询出对博客信息-->
    <association property="type" javaType="com.mzr.blog.pojo.Type">
      <id column="id" property="id"></id>
      <result column="name" property="name"/>
    </association>
    <!-- 指定联合查询出对用户信息-->
    <association property="user" javaType="com.mzr.blog.pojo.User">
      <id column="id" property="id"></id>
      <result column="avatar" property="avatar"/>
      <result column="nickname" property="nickname"/>
      <result column="email" property="email"/>
    </association>
    <!--多表关联-->
    <collection property="tags" ofType="com.mzr.blog.pojo.Tag" column="id" select="com.mzr.blog.dao.TagMapper.findTagById">
    </collection>
  </resultMap>

  <!--根据标签查询博客以及博客类别和博主-->
  <select id="listBlogByTagId" parameterType="java.lang.Long" resultMap="TagWithBlogResult">
    select distinct tb.*,tu.*,tp.*,tt.* from t_blog tb
            left join t_blog_tags tbt on tb.id = tbt.blogs_id
            left join t_tag tt on tt.id = tbt.tags_id
            left join t_user tu on tu.id = tb.user_id
			left join t_type tp on tp.id = tb.type_id
            where tt.id = #{tagId}
  </select>
</mapper>

TagMapper.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.mzr.blog.dao.TagMapper">
  <resultMap id="BaseResultMap" type="com.mzr.blog.pojo.Tag">
    <result column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
  </resultMap>
  
  <select id="findTagById" resultMap="BaseResultMap" parameterType="java.lang.Long">
    select * from t_tag where id in(select tags_id from t_blog_tags WHERE blogs_id = #{id})
  </select>

</mapper>

5. 编写测试类

import com.mzr.blog.service.BlogService;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MapperTest {

   @Autowired
    private BlogService blogServiceImpl;

    /**
     * 测试BlogMapper
     */

    @Before
    public void init() {
        System.out.println("-----------------开始测试-----------------");
    }

    @After
    public void after() {
        System.out.println("-----------------测试结束-----------------");
    }

    @Test
    public void testListBlogByTagId(){
        System.out.println(blogServiceImpl.findBlogsByTagId(Long.parseLong("3")));
    }

}

结果如下 (去除部分字段)：

-----------------开始测试-----------------
[Blog(id=1,  title=学习日志, type=Type(id=1, name=后端), user=User(id=1, nickname=Ryan), tags=[Tag(id=7, name=Spring), Tag(id=3, name=Java)])]
-----------------测试结束-----------------