MyBatis一对多嵌套查询结果映射
简单理解就是：此种方法指直接书写连表SQL，然后将结果一次性映射到几个实体上，而不是先查主表映射到实体后,再查从表再映射到实体
比较官方的说法叫做一对多collection集合嵌套结果映射
步骤
- 主表对应实体类添加从表实体类的集合
- 书写主表的xml文件
- 书写主表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 [org.apache.ibatis.session.defaults.DefaultSqlSession@6e489bb8] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@595755231 wrapping com.mysql.cj.jdbc.ConnectionImpl@182fd26b] 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, admin@admin.com, 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, admin@admin.com, 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 [org.apache.ibatis.session.defaults.DefaultSqlSession@6e489bb8]
[TkUser(id=1, oId=1, name=admin, password=admin, mobile=15166623676, email=admin@admin.com, 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查询结果一共是有三条结果，其中两条是重复的，这里框架自动合并了，其原理就是设置了