北纬36度
MyBatis一对多嵌套查询结果映射

简单理解就是:此种方法指直接书写连表SQL,然后将结果一次性映射到几个实体上,而不是先查主表映射到实体后,再查从表再映射到实体
比较官方的说法叫做一对多collection集合嵌套结果映射
步骤

  1. 主表对应实体类添加从表实体类的集合
  2. 书写主表的xml文件
  3. 书写主表Mapper方法

1.书写主表对应实体类添加从表实体类集合

package com.quinntian.admin.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.time.LocalDateTime;
import java.io.Serializable;
import java.util.List;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * <p>
 * 用户表
 * </p>
 *
 * @author QuinnTian
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName(excludeProperty = {"tkGroups","tkRoles","tkPermits","tkOrg"})
public class TkUser implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 部门外键
     */
    private Long oId;

    /**
     * 用户名
     */
    private String name;

    /**
     * 用户密码
     */
    private String password;

    /**
     * 用户手机号
     */
    private String mobile;

    /**
     * 用户邮箱
     */
    private String email;

    /**
     * 用户创建时间
     */
    private LocalDateTime creatime;

    /**
     * 用户登录时间
     */
    private LocalDateTime loginTime;

    /**
     * 用户上次登录时间
     */
    private LocalDateTime lastLoginTime;

    /**
     * 用户登录次数
     */
    private Long loginCount;
    //
    private List<TkGroup>  tkGroups;
    

}


2.书写主表的xml文件

其中xml中要新增一个resultMap,用于与从表实体绑定

<?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.quinntian.admin.mapper.TkUserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.quinntian.admin.model.TkUser">
        <id column="id" property="id" />
        <result column="o_id" property="oId" />
        <result column="name" property="name" />
        <result column="password" property="password" />
        <result column="mobile" property="mobile" />
        <result column="email" property="email" />
        <result column="creatime" property="creatime" />
        <result column="login_time" property="loginTime" />
        <result column="last_login_time" property="lastLoginTime" />
        <result column="login_count" property="loginCount" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, o_id, name, password, mobile, email, creatime, login_time, last_login_time, login_count
    </sql>
    <!--ID:ID唯一
        type:指明映射实体类
        extend:指明集成的resultMap,用于简化写法
        -->
    <resultMap id="UserAndGroup" type="com.quinntian.admin.model.TkUser" extends="BaseResultMap">
        <!--
        property:指明实体类中集合名
        columPrefix;指明别名
        resultMap:指明从表xml文件的resultMap,用于简化写法-->
        <collection property="tkGroups" columnPrefix="role_" resultMap="com.quinntian.admin.mapper.TkGroupMapper.BaseResultMap"></collection>
    </resultMap>
    <!-- 1对多查询:一次性映射到
            此处的SQL是三表连接-->
    <!---->
    <select id="selectUserAndGroup" resultMap="UserAndGroup">
        select
        u.id, u.o_id, u.name, u.password, u.mobile, u.email, u.creatime, u.login_time, u.last_login_time, u.login_count,
        g.id, g.group_name, g.f_id, g.creatime, g.description
        from tk_user u
        left join tk_user_group ug on ug.user_id = u.id
        left join tk_group g on g.id = ug.group_id
    </select>

</mapper>


3.编写mapper接口

package com.quinntian.admin.mapper;

import com.quinntian.admin.model.TkUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.List;

/**
 * <p>
 * 用户表 Mapper 接口
 * </p>
 *
 * @author QuinnTian
 */
public interface TkUserMapper extends BaseMapper<TkUser> {
    public List<TkUser> selectUserAndGroup();
}


4.编写测试类

package com.quinntian.admin.test;

import com.quinntian.admin.mapper.TkUserMapper;
import com.quinntian.admin.model.TkUser;
import com.quinntian.admin.service.ITkUserService;
import com.quinntian.admin.service.impl.TkUserServiceImpl;
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;

import java.util.List;

/**
 * <pre></pre>
 *
 * @author QuinnTian
 * @since
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class Test {
    
    @Autowired
    private TkUserMapper tkUserMapper;
    @org.junit.Test
    public void testSelect() {
        System.out.println(("----- selectAll method test ------"));
        System.out.println(tkUserMapper.selectUserAndGroup());

    }
  

}


运行结果

Creating a new SqlSession
SqlSession [[email protected]] was not registered for synchronization because synchronization is not active
JDBC Connection [[email protected] wrapping [email protected]] will not be managed by Spring
==>  Preparing: select u.id, u.o_id, u.name, u.password, u.mobile, u.email, u.creatime, u.login_time, u.last_login_time, u.login_count, g.id, g.group_name, g.f_id, g.creatime, g.description from tk_user u left join tk_user_group ug on ug.user_id = u.id left join tk_group g on g.id = ug.group_id
==> Parameters: 
<==    Columns: id, o_id, name, password, mobile, email, creatime, login_time, last_login_time, login_count, id, group_name, f_id, creatime, description
<==        Row: 1, 1, admin, admin, 15166623676, [email protected], 2020-12-08 16:18:59, 2020-12-08 16:19:03, 2020-12-07 16:19:07, 0, 1, 用户组1, 0, 2020-12-08 16:34:00, 用户组1描述
<==        Row: 1, 1, admin, admin, 15166623676, [email protected], 2020-12-08 16:18:59, 2020-12-08 16:19:03, 2020-12-07 16:19:07, 0, 3, 用户组3, 0, 2020-12-08 16:34:47, 用户组3描述
<==        Row: 2, null, test1, null, null, null, null, null, null, 0, null, null, null, null, null
<==      Total: 3
Closing non transactional SqlSession [[email protected]]
[TkUser(id=1, oId=1, name=admin, password=admin, mobile=15166623676, [email protected], creatime=2020-12-08T16:18:59, loginTime=2020-12-08T16:19:03, lastLoginTime=2020-12-07T16:19:07, loginCount=0, tkGroups=[TkGroup(id=1, groupName=用户组1, fId=0, creatime=2020-12-08T16:18:59, description=用户组1描述)], tkRoles=null, tkPermits=null, tkOrg=null), TkUser(id=2, oId=null, name=test1, password=null, mobile=null, email=null, creatime=null, loginTime=null, lastLoginTime=null, loginCount=0, tkGroups=[TkGroup(id=2, groupName=null, fId=null, creatime=null, description=null)], tkRoles=null, tkPermits=null, tkOrg=null)]



4.关于自动合并问题

重点说明:关于SQL查询结果一共是有三条结果,其中两条是重复的,这里框架自动合并了,其原理就是设置了标签,框架会自动判断查询结果的ID进行自动合并。另外如果没有ID标签框架会自动比对每条字段下的结果,相同合并