Mybatis篇：Mybatis一对多、多个多对多、加分页、排序、where动态条件，以及分页后数据不匹配的解决方案

﹏ヽ暗。殇╰゛Y 2022-11-03 10:52 170阅读 0赞

**目录**

前言

资料准备

需求1：查询用户列表同时查询用户积分（简单左外连接）

需求2：查询用户列表同时查询用户积分、所属岗位（左外连接+1个多对多）

需求3：查询用户列表同时查询用户积分、所属岗位、所属角色（左外连接+2个多对多）

需求4：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页（左外连接+2个多对多+分页）

需求5：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页、加排序（按注册时间）（左外连接+2个多对多+分页+排序）

需求6：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页、加排序（按注册时间）、加where条件（动态SQL）（左外连接+2个多对多+分页+排序+where条件）

参考资料

--------------------

### 前言 ###

因为是在公司项目上写的一些测试代码，这里就不分享整个项目了，只贴上重要的代码，希望对您有帮助hahaha。

### 资料准备 ###

***测试数据库表（无需积分，点击即可下载）：[zyykin\_test\_db][zyykin_test_db]***

![2021030116535435.png][]

***（不想看贴代码的可以直接跳到后半段看SQL）***

***贴上代码：UserMapper.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.***.***.modules.system.mapper.UserMapper">
        
        <resultMap id="userResultMap" type="com.***.***.modules.system.service.dto.UserDto">
            <id column="user_id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="create_time" property="createTime"></result>
            <result column="points" property="points"></result>
            <collection property="roles" ofType="com.***.***.modules.system.service.dto.RoleDto">
                <id property="id" column="role_id"/>
                <result property="name" column="role_name"/>
                <result property="level" column="role_level"/>
            </collection>
            <collection property="jobs" ofType="com.***.***.modules.system.service.dto.JobDto">
                <id property="id" column="job_id"/>
                <result property="name" column="job_name"/>
            </collection>
        </resultMap>
        <select id="getUsers" resultMap="userResultMap">
            SELECT
                sys_user.user_id,
                sys_user.username,
                sys_user.create_time,
                points.points,
                users_jobs.job_id,
                sys_job.`name` job_name,
                users_roles.role_id,
                sys_role.`name` role_name,
                sys_role.LEVEL role_level
            FROM
                sys_user
                LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
                LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
                LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
                LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
                LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id
            WHERE
                sys_user.user_id IN (
                SELECT
                    table1.user_id
                FROM
                    (
                    SELECT DISTINCT
                        sys_user.user_id,
                        sys_user.create_time
                    FROM
                        sys_user
                        LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
                        LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
                        LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
                        LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
                        LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id
                    <where>
                        <if test="criteria.enabled != null">
                            AND sys_user.enabled = #{criteria.enabled}
                        </if>
                        <if test="criteria.referer != null">
                            AND sys_user.referer = #{criteria.referer}
                        </if>
                    </where>
                    ORDER BY
                        sys_user.create_time DESC
                    LIMIT #{begin}, #{size}
                    ) AS table1
                )
            ORDER BY
                sys_user.create_time DESC;
        </select>
    </mapper>

***贴上代码：DTO类***

***UserDto类***

/**
     * @author zyykin
     * @date 2021/2/24
     * 用户DTO
     */
    @Data
    public class UserDto implements Serializable {
    
        /**
         * 用户ID
         */
        private Long id;
    
        /**
         * 角色集合
         */
        private Set<RoleDto> roles;
    
        /**
         * 岗位集合
         */
        private Set<JobDto> jobs;
    
        /**
         * 用户名称
         */
        private String username;
    
        /**
         * 注册日期
         */
        private Timestamp createTime;
    }

***RoleDto类***

/**
     * @author zyykin
     * @date 2021/2/24
     * 角色DTO
     */
    @Data
    public class RoleDto implements Serializable {
    
        /**
         * 角色ID
         */
        private Long id;
    
        /**
         * 角色名称
         */
        private String name;
    
        /**
         * 角色等级
         */
        private Integer level;
    }

***JobDto类***

/**
     * @author zyykin
     * @date 2021/2/24
     * 岗位DTO
     */
    @Data
    public class JobDto implements Serializable {
    
        /**
         * 岗位ID
         */
        private Long id;
    
        /**
         * 岗位名称
         */
        private String name;
    }

***UserMapper类***

/**
     * @author zyykin
     * @date 2020/9/16
     */
    public interface UserMapper {
    
        /**
         * 获取用户
         *
         * @param begin    从第几条数据开始
         * @param size     每页显示记录数
         * @param criteria 封装查询条件的类
         * @return
         */
        List<UserDto> getUsers(@Param("begin") long begin, @Param("size") long size, @Param("criteria") UserQueryCriteria criteria);
    }

***UserQueryCriteria类***

/**
     * @author zyykin
     * @date 2021/2/23
     * UserQueryCriteria是封装查询条件的类，也就是拼接在where后面的条件
     */
    @Data
    public class UserQueryCriteria implements Serializable {
    
        /**
         * 是否开启
         */
        private Boolean enabled;
    
        /**
         * 注册来源
         */
        private String referer;
    }

--------------------

***---接下来是拆分需求***

***select字段注释：***

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70][]

### 需求1：查询用户列表同时查询用户积分（简单左外连接） ###

***tips：下面只贴SQL，只需要替换一下UserMapper.xml中的SQL即可测试***

SELECT
    	sys_user.user_id,
    	sys_user.username,
    	sys_user.create_time,
    	points.points 
    FROM
    	sys_user
    	LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id;

### 需求2：查询用户列表同时查询用户积分、所属岗位（左外连接+1个多对多） ###

SELECT
    	sys_user.user_id,
    	sys_user.username,
    	sys_user.create_time,
    	points.points,
    	users_jobs.job_id,
    	sys_job.`name` job_name 
    FROM
    	sys_user
    	LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    	LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    	LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id

### 需求3：查询用户列表同时查询用户积分、所属岗位、所属角色（左外连接+2个多对多） ###

### 需求4：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页（左外连接+2个多对多+分页） ###

***错误写法：******（导致数据不匹配）***

***原因：多对多情况下，直接使用LIMIT，会导致数据不匹配（数据变少）***

***正确写法：***

***解释：根据from后面表的关系，查出userId（去重），LIMIT，再做一次子查询就可以得到想要的结果。***

SELECT
    	sys_user.user_id,
    	sys_user.username,
    	sys_user.create_time,
    	points.points,
    	users_jobs.job_id,
    	sys_job.`name` job_name,
    	users_roles.role_id,
    	sys_role.`name` role_name,
    	sys_role.`level` role_level  
    FROM
    	sys_user
    	LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    	LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    	LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    	LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    	LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    WHERE
    	sys_user.user_id IN (
    	SELECT
    		table1.user_id 
    	FROM
    		(
    		SELECT DISTINCT
    			sys_user.user_id 
    		FROM
    			sys_user
    			LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    			LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    			LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    			LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    			LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    			LIMIT #{begin}, #{size}
    		) AS table1 
    	)

### 需求5：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页、加排序（按注册时间）（左外连接+2个多对多+分页+排序） ###

***tips：这里用了两次：ORDER BY sys\_user.create\_time DESC***

***原因：第1次order by是为了limit出最近的userId；第2次order by：在多对多的情况下，某些关联表的字段有默认排序，会导致最终的结果乱序，所以需求再order by一次（当然在前端处理也行）***

SELECT
    	sys_user.user_id,
    	sys_user.username,
    	sys_user.create_time,
    	points.points,
    	users_jobs.job_id,
    	sys_job.`name` job_name,
    	sys_job.job_sort,
    	users_roles.role_id,
    	sys_role.`name` role_name,
    	sys_role.`level` role_level 
    FROM
    	sys_user
    	LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    	LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    	LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    	LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    	LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    WHERE
    	sys_user.user_id IN (
    	SELECT
    		table1.user_id 
    	FROM
    		(
    		SELECT DISTINCT
    			sys_user.user_id,
    			sys_user.create_time 
    		FROM
    			sys_user
    			LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    			LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    			LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    			LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    			LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    		ORDER BY
    			sys_user.create_time DESC 
    			LIMIT #{begin}, #{size} 
    		) AS table1 
    	) 
    ORDER BY
    	sys_user.create_time DESC

### 需求6：查询用户列表同时查询用户积分、所属岗位、所属角色、加分页、加排序（按注册时间）、加where条件（动态SQL）（左外连接+2个多对多+分页+排序+where条件） ###

SELECT
    	sys_user.user_id,
    	sys_user.username,
    	sys_user.create_time,
    	points.points,
    	users_jobs.job_id,
    	sys_job.`name` job_name,
    	users_roles.role_id,
    	sys_role.`name` role_name,
    	sys_role.`level` role_level 
    FROM
    	sys_user
    	LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    	LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    	LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    	LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    	LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    WHERE
    	sys_user.user_id IN (
    	SELECT
    		table1.user_id 
    	FROM
    		(
    		SELECT DISTINCT
    			sys_user.user_id,
    			sys_user.create_time 
    		FROM
    			sys_user
    			LEFT JOIN fc_points_statistics points ON sys_user.user_id = points.user_id
    			LEFT JOIN sys_users_jobs users_jobs ON sys_user.user_id = users_jobs.user_id
    			LEFT JOIN sys_job ON users_jobs.job_id = sys_job.job_id
    			LEFT JOIN sys_users_roles users_roles ON users_roles.user_id = sys_user.user_id
    			LEFT JOIN sys_role ON sys_role.role_id = users_roles.role_id 
    			<where>
    					<if test="criteria.enabled != null">
    							AND sys_user.enabled = #{criteria.enabled}
    					</if>
    					<if test="criteria.referer != null">
    							AND sys_user.referer = #{criteria.referer}
    					</if>
    			</where>
    		ORDER BY
    			sys_user.create_time DESC 
    			LIMIT #{begin}, #{size}			
    		) AS table1 
    	) 
    ORDER BY
    	sys_user.create_time DESC;

### 参考资料 ###

[mysql官方手册][mysql]

[zyykin_test_db]: https://download.csdn.net/download/zyydfhlw/15502535
[2021030116535435.png]: /images/20221024/7277da6faacc47f9baa69aa797862548.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70]: /images/20221024/c86ae5b37f554834b57fc85cb2560e15.png
[mysql]: https://dev.mysql.com/doc/