Mybatis Plus一对多联表查询及分页解决方案

这篇具有很好参考价值的文章主要介绍了Mybatis Plus一对多联表查询及分页解决方案。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

需求

查询用户信息列表,其中包含用户对应角色信息,页面检索条件有根据角色名称查询用户列表;

需求分析

一个用户对应多个角色,用户信息和角色信息分表根据用户id关联存储,用户和角色一对多进行表连接查询,

创建对应表:

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COMMENT='用户信息表';

CREATE TABLE `sys_role` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(30) NOT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COMMENT='角色信息表';


CREATE TABLE `sys_user_role` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `role_id` bigint NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='用户和角色关联表';


INSERT INTO tsq.sys_user (name,age) VALUES
	 ('张三',18),
	 ('王二',19);

INSERT INTO tsq.sys_role (role_name) VALUES
	 ('角色1'),
	 ('角色2'),
	 ('角色3'),
	 ('角色4');

INSERT INTO tsq.sys_user_role (user_id,role_id) VALUES
	 (1,1),
	 (1,2),
	 (1,3),
	 (2,4);


对应实体类:

@Data
@ApiModel("用户信息表")
@TableName("sys_user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;
    
    @ApiModelProperty("用户id")
    private Long id;
    @ApiModelProperty("姓名")
    private String name;
    @ApiModelProperty("年龄")
    private Integer age;
}


@Data
@ApiModel("角色信息表")
@TableName("sys_role")
public class Role implements Serializable {

    private static final long serialVersionUID = 1L;
    
    @ApiModelProperty("角色id")
    private Long id;
    @ApiModelProperty("角色名称")
    private String roleName;
}



@Data
@ApiModel("用户信息表")
public class UserVo implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("用户id")
    private Long id;
    @ApiModelProperty("姓名")
    private String name;
    @ApiModelProperty("年龄")
    private Integer age;
    
    private List<Role> roleList;

}


分页问题说明

在使用一对多连接查询并且分页时,发现返回的分页列表数据数量不对
比如这里查询用户对应角色列表,如果使用直接映射,那么 roleList 的每个 Role 对象都会算一条数据;比如查第一页,一个用户有三个角色每页三条数据,就会出现查出一个 User ,三个 Role 的这些情况,这它也算每页三条(其实就只查到一个用户)

分页问题原因

mybatis-plus一对多分页时,应该使用子查询的映射方式,使用直接映射就会出错
所以直接映射适用于一对一,子查询映射使用于一对多;

一对多场景一

查询用户表的内容,角色表不参与条件查询,用懒加载形式

// controller
  @GetMapping("/pageList")
	public Map<String, Object> pageList(@RequestParam(required = false, defaultValue = "0") int offset,
	                                    @RequestParam(required = false, defaultValue = "10") int pagesize) {
		return userService.pageList(offset, pagesize);
	}
  
  
// serviceimpl
  @Override
	public Map<String, Object> pageList(int offset, int pagesize) {
      List<UserVo> pageList = userMapper.pageList(offset, pagesize);
      int totalCount = userMapper.pageListCount();
      Map<String, Object> result = new HashMap<String, Object>();
      result.put("pageList", pageList);
      result.put("totalCount", totalCount);

      return result;
	}
  
// mapper.xml

   <resultMap id="getUserInfo" type="com.tsq.democase.onetomany.domain.vo.UserVo" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <collection property="roleList" javaType="ArrayList" ofType="com.tsq.democase.onetomany.domain.Role"
                    select="getRolesByUserId" column="{userId = id}"/>
    </resultMap>

    <select id="getRolesByUserId" resultType="com.tsq.democase.onetomany.domain.Role">
        SELECT *
        FROM sys_user_role ur
                 inner join sys_role r on ur.role_id = r.id
        where ur.user_id = #{userId}
    </select>
    
    <select id="pageList"  resultMap="getUserInfo">
        SELECT *
        FROM sys_user
        LIMIT #{offset}, #{pageSize}
    </select>
    
    <select id="pageListCount" resultType="java.lang.Integer">
        SELECT count(1)
        FROM sys_user
    </select>

查询结果
Mybatis Plus一对多联表查询及分页解决方案

一对多场景二

查询用户表的内容,角色表要作为查询条件参与查询,例如要根据角色名称查询出用户列表

// controller
	@GetMapping("/pageListByRoleName")
	public Map<String, Object> pageListByRoleName(@RequestParam(required = false, defaultValue = "0") int offset,
	                                    @RequestParam(required = false, defaultValue = "10") int pagesize,
	                                    @RequestParam String roleName) {
		return userService.pageListByRoleName(offset, pagesize, roleName);
	}
  
  
// serviceimpl
	@Override
	public Map<String, Object> pageListByRoleName(int offset, int pagesize,String roleName) {
      List<UserVo> pageList = userMapper.pageListByRoleName(offset, pagesize, roleName);
      int totalCount = userMapper.pageListCount();
      Map<String, Object> result = new HashMap<String, Object>();
      result.put("pageList", pageList);
      result.put("totalCount", totalCount);

      return result;
	}
  
// mapper.xml
   <resultMap id="getUserInfoByRoleName" type="com.tsq.democase.onetomany.domain.vo.UserVo" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <collection property="roleList" javaType="ArrayList" ofType="com.tsq.democase.onetomany.domain.Role"
                    select="getRolesByUserIdAndRoleName" column="{userId = id,roleName = roleName}"/>
    </resultMap>

    <select id="getRolesByUserIdAndRoleName" resultType="com.tsq.democase.onetomany.domain.Role">
        SELECT *
        FROM sys_user_role ur
        inner join sys_role r on ur.role_id = r.id
        where ur.user_id = #{userId}
        <if test="roleName != null and roleName != ''" >
            and r.role_name LIKE concat('%', #{roleName}, '%')
        </if>
    </select>

    <select id="pageListByRoleName"  resultMap="getUserInfoByRoleName">
        SELECT temp.* FROM (
        SELECT distinct u.*,#{roleName} as roleName
        FROM sys_user u
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on r.id = ur.role_id
        <where>
            <if test="roleName != null and roleName != ''" >
                r.role_name LIKE concat('%', #{roleName}, '%')
            </if>
        </where>
        ) temp
        LIMIT #{offset}, #{pageSize}
    </select>

查询结果
Mybatis Plus一对多联表查询及分页解决方案

性能优化

原因:

场景一二中使用 select方式会触发多次子查询(SELECT *FROM sys_user_role ur inner join sys_role …),当数据量大时会使查询速度很慢。

场景二中查询时产生的sql日志如下:

--  ==>  
SELECT
    temp.* 
FROM
    ( SELECT
        distinct u.*,
        '角色' as roleName 
    FROM
        sys_user u 
    left join
        sys_user_role ur 
            on u.id = ur.user_id 
    left join
        sys_role r 
            on r.id = ur.role_id 
    WHERE
        r.role_name LIKE concat('%', '角色', '%') ) temp LIMIT 0,
    10 
 --  ====>  
SELECT
    * 
FROM
    sys_user_role ur 
inner join
    sys_role r 
        on ur.role_id = r.id 
where
    ur.user_id = 1 
    and r.role_name LIKE concat('%', '角色', '%') 
 --  ====>  
SELECT
    * 
FROM
    sys_user_role ur 
inner join
    sys_role r 
        on ur.role_id = r.id 
where
    ur.user_id = 2 
    and r.role_name LIKE concat('%', '角色', '%') 
 --  ==>  
SELECT
    count(1) 
FROM
    sys_user 

sql可见如果有100各用户就要执行一百次子查询,效率极低。

优化解决方案

sql中只查询sys_user相关信息并且做roleName 过滤,roleList在java代码中用stream关联role并赋值roleList;

// serviceimpl
  @Override
  public Map<String, Object> pageListByRoleName(int offset, int pagesize,String roleName) {
     // List<UserVo> pageList = userMapper.pageListByRoleName(offset, pagesize, roleName);
     List<UserVo> pageList = userMapper.pageListByRoleName2(offset, pagesize, roleName);
     List<Long> userIds = pageList.stream().map(UserVo::getId).collect(Collectors.toList());
     List<UserRoleVo> userRoleVos =  userMapper.getUserRoleByUserIds(userIds);
     Map<Long, List<UserRoleVo>> userRoleMap = userRoleVos.stream().collect(Collectors.groupingBy(UserRoleVo::getUserId, Collectors.toList()));
     pageList.forEach(u -> {
        List<UserRoleVo> roleVos = userRoleMap.get(u.getId());
        List<RoleVo> roles = BeanUtils.listCopy(roleVos, CopyOptions.create(), RoleVo.class);
        u.setRoleList(roles);
     });
     int totalCount = userMapper.pageListCount();
     Map<String, Object> result = new HashMap<String, Object>();
     result.put("pageList", pageList);
     result.put("totalCount", totalCount);

     return result;
  }

// mapper.xml
<select id="pageListByRoleName2"  resultType="com.tsq.democase.onetomany.domain.vo.UserVo">
    SELECT distinct u.*
    FROM sys_user u
    left join sys_user_role ur on u.id = ur.user_id
    left join sys_role r on r.id = ur.role_id
    <where>
        <if test="roleName != null and roleName != ''" >
            r.role_name LIKE concat('%', #{roleName}, '%')
        </if>
    </where>
    LIMIT #{offset}, #{pageSize}
</select>  

查询结果

同场景二。

查询时产生的sql如下:

  --  ==>  
  SELECT
      distinct u.* 
  FROM
      sys_user u 
  left join
      sys_user_role ur 
          on u.id = ur.user_id 
  left join
      sys_role r 
          on r.id = ur.role_id 
  WHERE
      r.role_name LIKE concat('%', '角色', '%') LIMIT 0, 10 
   --  ==>  
  SELECT
      ur.user_id ,
      r.id roleId,
      r.role_name 
  FROM
      sys_user_role ur 
  inner join
      sys_role r 
          on ur.role_id = r.id 
   --  ==>  
  SELECT
      count(1) 
  FROM
      sys_user 
 

由sql日志可见这种方式比纯sql方式效率高一些文章来源地址https://www.toymoban.com/news/detail-410435.html

到了这里,关于Mybatis Plus一对多联表查询及分页解决方案的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mybatis plus 分页查询出现count()

    出现问题现象 使用mybatisplus分页时候count语句出错,控制台打印的sql为### The error may involve defaultParameterMap, The error occurred while setting parameters 查看打印的sql 不知道为啥不是count(*)而是count()导致分页报错 出现问题的原因和解决办法

    2024年02月15日
    浏览(32)
  • mybatis-plus实现分页查询

    分页查询使用的方法是: IPage:用来构建分页查询条件 Wrapper:用来构建条件查询的条件,目前我们没有可直接传为Null IPage:返回值,你会发现构建分页条件和方法的返回值都是IPage IPage是一个接口,我们需要找到它的实现类来构建它,具体的实现类,可以进入到IPage类中按ctrl+

    2023年04月08日
    浏览(52)
  • MyBatis-Plus分页查询(快速上手运用)

    Mybatis-Plus知识点[MyBatis+MyBatis-Plus的基础运用]_心态还需努力呀的博客-CSDN博客   Mybatis-Plus+SpringBoot结合运用_心态还需努力呀的博客-CSDN博客 MyBaits-Plus中@TableField和@TableId用法_心态还需努力呀的博客-CSDN博客 MyBatis-Plus中的更新操作(通过id更新和条件更新)_心态还需努力呀的博

    2024年02月16日
    浏览(53)
  • mybatis-plus分页查询三种方法

    说明: 1、mybatis-plus中分页接口需要包含一个IPage类型的参数。 2、多个实体参数,需要添加@Param参数注解,方便在xml中配置sql时获取参数值。 注意这里我虽然加了@Param但是我并没有使用 这是控制台打印的查询语句,大家发现最后的LIMIT 函数没,正常来说mybatis-plus里是没有写

    2024年01月25日
    浏览(69)
  • mybatis-plus 多表关联条件分页查询

    此处以一对多,条件分页查询为例: 主表 明细表 0.请求dto 1.Controller 层: 注:我的项目中进行了service 读写分类配置,实际使用中,直接使用mybatis-plus中的 IUserService 对应的接口就行。 2.service 层 service impl实现层: 3.mapper 层 4.mapper.xml层 5.测试: 结果body: Q:todo page 分页会把

    2024年02月12日
    浏览(48)
  • 窥探系列之Mybatis-plus XML分页查询

    Page类在mybatisPlus中用于分页查询,继承Pagination类,Pagination类的searchCount字段控制是否查询总记录数 顺着看哪里用到了searchCount: com.baomidou.mybatisplus.plugins.PaginationInterceptor 是mybatisPlus的一个插件,也就是说mybatis是通过插件的方式在分页的时候查询总数; 红圈中使用sql解析包

    2024年02月13日
    浏览(45)
  • 【SpringBoot】MyBatis与MyBatis-Plus分页查询 & github中的PageHelper

            笔者写这篇博客是因为近期遇到的关于两者之间的分页代码差距,其实之前也遇见过但是没有去整理这篇博客,但由于还是被困扰了小一会儿时间,所以还是需要 加深记忆 。其实会看前后端传参解决这个问题很快、不麻烦。关于这两个框架的分页代码问题主要就

    2024年02月03日
    浏览(58)
  • spring boot集成mybatis-plus——Mybatis Plus 多表联查(包含分页关联查询,图文讲解)...

     更新时间 2023-01-03 21:41:38 大家好,我是小哈。 本小节中,我们将学习如何通过 Mybatis Plus 实现 多表关联查询 ,以及 分页关联查询 。 本文以 查询用户所下订单 ,来演示 Mybatis Plus 的关联查询,数据库表除了前面小节中已经定义好的用户表外,再额外创建一张订单表,然后

    2024年02月01日
    浏览(92)
  • SpringBoot整合mybatis-plus实现分页查询(建议收藏)

    一、前言         最近学习了SpringBoot分页查询的两种写法,一种是手动实现,另一种是使用框架实现。现在我将具体的实现流程分享一下。 二、手动实现分页查询         先复习一下,SQL中的limit,下面一行sql语句的意思是从第二个数据开始查,查询出两条数据

    2024年01月16日
    浏览(70)
  • mybatis-plus分页查询(springboot中实现单表和多表查询)

    一、mybatis-plus单表查询 使用mybatis-plus实现单表分页查询 非常方便,主要操作步骤如下: 配置分页查询拦截器 进行分页查询 1.首先,打开mybatis-plus官网的插件(插件主体) 或者点击mybatis-plus插件 我是配置在springboot项目中,所以找到springboot的分页配置 2.配置分页查询拦截器

    2024年02月08日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包