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、准备材料
数据库脚本
/*
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;
SpringBoot项目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>
SpringBoot项目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、项目代码
目录结构
各类代码
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、一些缺点
虽然Mybatis可以帮我们实现多行合并的功能，但并不是没有问题的。
当使用角色当做查询条件时，由于角色已经指定了，那么roleList里面必定只有这一个角色，不再会有聚合效果，也就看不到这个用户所有的角色了。
我只能说具体看产品要求吧，大部分时候上面那种问题产品都是可以接受的。
本文来自博客园，作者：sum墨，转载请注明原文链接：https://www.cnblogs.com/wlovet/p/16406502.html
浙公网安备 33010602011771号