注册

MyBatis实现多行合并(collection标签使用)

一、举个栗子


现有如下表结构，用户表、角色表、用户角色关联表。
一个用户有多个角色，一个角色有可以给多个用户，也即常见的多对多场景。
在这里插入图片描述


现有这样一个需求，分页查询用户数据，除了用户ID和用户名称字段，还要查出这个用户的所有角色。
在这里插入图片描述
从上面的表格我们可以看出，用户有三个，但每个人的角色不止一个，而且有重复的角色，这里角色的数据从多行合并到了1行。


二、难点分析


SQL存在的问题：



想使用SQL实现上面的效果不是不可以，但是很复杂且效率低下，尤其这个地方还需要分页，所以为了保证查询效率，我们需要把逻辑放到服务端来写；



服务端存在的问题：



服务端可以把需要的数据都查询出来，然后自己判断整合，首先十分复杂不说，而且这里有个问题：如果角色也是一个查询条件如何处理呢？



三、解决方案


核心方案就是使用Mybatis的collection标签自动实现多行合并。


下面是collection标签的一些介绍
在这里插入图片描述


常见写法


<resultMap id="ExtraBaseResultMap" type="com.example.mybatistest.entity.UserInfoDO">
<!--

          WARNING - @mbg.generated

        -->
<result column="user_id" jdbcType="INTEGER" property="userId"/>
<result column="user_name" jdbcType="INTEGER" property="userName"/>
<collection javaType="java.util.ArrayList" ofType="com.example.mybatistest.entity.MyRole"
property="roleList">
<result column="role_id" jdbcType="INTEGER" property="roleId"/>
<result column="role_name" jdbcType="VARCHAR" property="roleName"/>
</collection>
</resultMap>

四、尝试一下


1. 准备材料


（1）数据库脚本


/*

 Navicat Premium Data Transfer


 Source Server         : 本机

 Source Server Type    : MySQL

 Source Server Version : 80021

 Source Host           : localhost:3306

 Source Schema         : mybatis-test


 Target Server Type    : MySQL

 Target Server Version : 80021

 File Encoding         : 65001


 Date: 23/06/2022 19:16:34

*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for my_role
-- ----------------------------
DROP TABLE IF EXISTS `my_role`;
CREATE TABLE `my_role` (

  `role_id` int NOT NULL COMMENT '角色主键',

  `role_code` varchar(32) DEFAULT NULL COMMENT '角色code',

  `role_name` varchar(32) DEFAULT NULL COMMENT '角色名称',
PRIMARY KEY (`role_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of my_role
-- ----------------------------
BEGIN;
INSERT INTO `my_role` VALUES (1, 'admin', '超级管理员');
INSERT INTO `my_role` VALUES (2, 'visitor', '游客');
COMMIT;

-- ----------------------------
-- Table structure for my_user
-- ----------------------------
DROP TABLE IF EXISTS `my_user`;
CREATE TABLE `my_user` (

  `user_id` int NOT NULL COMMENT '用户主键',

  `user_name` varchar(32) DEFAULT NULL COMMENT '用户名称',

  `user_gender` tinyint DEFAULT NULL COMMENT '用户性别，1:男/2:女/3:未知',
PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of my_user
-- ----------------------------
BEGIN;
INSERT INTO `my_user` VALUES (1, '用户1', 1);
COMMIT;

-- ----------------------------
-- Table structure for my_user_role_rel
-- ----------------------------
DROP TABLE IF EXISTS `my_user_role_rel`;
CREATE TABLE `my_user_role_rel` (

  `rel_id` int NOT NULL COMMENT '角色主键',

  `role_id` int DEFAULT NULL COMMENT '角色ID',

  `user_id` int DEFAULT NULL COMMENT '用户ID',
PRIMARY KEY (`rel_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of my_user_role_rel
-- ----------------------------
BEGIN;
INSERT INTO `my_user_role_rel` VALUES (1, 1, 1);
INSERT INTO `my_user_role_rel` VALUES (2, 2, 1);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;


（2）pom.xml


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>mybatis-test</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mybatis-test</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.22</version>
</dependency>
<!-- 我这里使用的是mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>javax.persistence-api</artifactId>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>


（3）application.properties


# 数据库配置

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

spring.datasource.url=jdbc:mysql://localhost:3306/mybatis-test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

spring.datasource.username=root

spring.datasource.password=xxx

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver


# mybatis

mybatis.configuration.auto-mapping-behavior=full

mybatis.configuration.map-underscore-to-camel-case=true

mybatis-plus.mapper-locations=classpath*:/mybatis/mapper/*.xml

2. 项目代码


（1）目录结构


在这里插入图片描述


（2）各类代码


MybatisTestApplication.java


import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan({"com.example.mybatistest.mapper"})
public class MybatisTestApplication {

public static void main(String[] args) {

        SpringApplication.run(MybatisTestApplication.class, args);

    }


}


QueryController.java


import com.example.mybatistest.entity.UserInfoDO;
import com.example.mybatistest.service.UserRoleRelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/mybatis")
public class QueryController {

@Autowired
private UserRoleRelService userRoleRelService;

@GetMapping("/queryList")
public List<UserInfoDO> queryList() {
return userRoleRelService.queryList();

    }

}

UserRoleRelService.java


import com.example.mybatistest.entity.UserInfoDO;

import java.util.List;

public interface UserRoleRelService {

    List<UserInfoDO> queryList();

}

UserRoleRelServiceImpl.java


import com.example.mybatistest.entity.UserInfoDO;
import com.example.mybatistest.repository.MyUserRoleRelRepository;
import com.example.mybatistest.service.UserRoleRelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserRoleRelServiceImpl implements UserRoleRelService {

@Autowired
private MyUserRoleRelRepository myUserRoleRelRepository;

@Override
public List<UserInfoDO> queryList() {
return myUserRoleRelRepository.queryList();

    }

}

MyUserRoleRelRepository.java


import com.example.mybatistest.entity.UserInfoDO;

import java.util.List;

public interface MyUserRoleRelRepository {

    List<UserInfoDO> queryList();

}

MyUserRoleRelRepositoryImpl.java


import com.example.mybatistest.entity.UserInfoDO;
import com.example.mybatistest.mapper.MyUserRoleRelMapper;
import com.example.mybatistest.repository.MyUserRoleRelRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class MyUserRoleRelRepositoryImpl implements MyUserRoleRelRepository {
@Autowired
public MyUserRoleRelMapper myUserRoleRelMapper;

@Override
public List<UserInfoDO> queryList() {
return myUserRoleRelMapper.queryList();

    }

}

MyUserRoleRelMapper.java


import com.example.mybatistest.entity.UserInfoDO;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface MyUserRoleRelMapper {

    List<UserInfoDO> queryList();

}

MyRole.java


import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Column;


@Builder
@Data
@TableName("my_role")
@NoArgsConstructor
@AllArgsConstructor
public class MyRole {

@Column(name = "role_id")
private Integer roleId;

@Column(name = "role_name")
private String roleName;

}

MyUserRoleRel.java


import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Column;

@Builder
@Data
@TableName("my_user_role_rel")
@NoArgsConstructor
@AllArgsConstructor
public class MyUserRoleRel {

@Column(name = "rel_id")
private Integer relId;

@Column(name = "user_id")
private Integer userId;

@Column(name = "role_id")
private Integer roleId;

}

UserInfoDO.java


import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserInfoDO {
private Integer userId;

private String userName;

private List<MyRole> roleList;

}

MyUserRoleRelMapper.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.example.mybatistest.mapper.MyUserRoleRelMapper">
<resultMap id="BaseResultMap" type="com.example.mybatistest.entity.MyUserRoleRel">
<!--

          WARNING - @mbg.generated

        -->
<result column="rel_id" jdbcType="INTEGER" property="relId"/>
<result column="role_id" jdbcType="INTEGER" property="roleId"/>
<result column="user_id" jdbcType="INTEGER" property="userId"/>
</resultMap>
<resultMap id="ExtraBaseResultMap" type="com.example.mybatistest.entity.UserInfoDO">
<!--

          WARNING - @mbg.generated

        -->
<result column="user_id" jdbcType="INTEGER" property="userId"/>
<result column="user_name" jdbcType="INTEGER" property="userName"/>
<collection javaType="java.util.ArrayList" ofType="com.example.mybatistest.entity.MyRole"
property="roleList">
<result column="role_id" jdbcType="INTEGER" property="roleId"/>
<result column="role_name" jdbcType="VARCHAR" property="roleName"/>
</collection>
</resultMap>
<select id="queryList" resultMap="ExtraBaseResultMap">

        SELECT

            t3.user_id,

            t3.user_name,

            t2.role_id,

            t2.role_name

        FROM

            my_user_role_rel t1

                LEFT JOIN my_role t2 ON t1.role_id = t2.role_id

                LEFT JOIN my_user t3 ON t1.user_id = t3.user_id
</select>
</mapper>

3. 实现效果


在这里插入图片描述


这里可以看到roleList里面有两条数据，说明mybatis已经自动聚合完成了。


4. 一些缺点


缺点1、查询条件不能支持很多


虽然Mybatis可以帮我们实现多行合并的功能，但并不是没有问题的。
当使用角色当做查询条件时，由于角色已经指定了，那么roleList里面必定只有这一个角色，不再会有聚合效果，也就看不到这个用户所有的角色了。
我只能说具体看产品要求吧，大部分时候上面那种问题产品都是可以接受的。


缺点2、不支持分页


这个缺点也看业务场景吧，产品可以接受就用，不能接受就别用，我这里只是介绍有这么一个办法。


作者：summo
来源：juejin.cn/post/7337849561479708735
0
分享
2024-02-28

0 个评论

要回复文章请先登录或注册

一个开放、互助、协作、创意的社区

关于imGeek
关于专职工程师值守
社区财富榜
赞助商
友情链接
热门标签
京ICP备2023000793号