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

最新推荐文章于 2023-11-16 11:56:30 发布

zyykin

最新推荐文章于 2023-11-16 11:56:30 发布

阅读量2.8w

点赞数

分类专栏：数据库 Mybatis 文章标签： mysql mybatis

本文链接：https://blog.youkuaiyun.com/zyydfhlw/article/details/114181140

版权

数据库同时被 2 个专栏收录

4 篇文章

订阅专栏

Mybatis

1 篇文章

订阅专栏

该博客围绕MySQL和MyBatis展开，介绍了在不同需求下查询用户列表的方法，包括简单左外连接、多对多关联、分页、排序及动态SQL等。详细说明了各需求的实现方式，还指出了分页时的错误写法及原因，并给出正确写法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

资料准备

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

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

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

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

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

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

参考资料

前言

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

资料准备

测试数据库表（无需积分，点击即可下载）：zyykin_test_db

（不想看贴代码的可以直接跳到后半段看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字段注释：

需求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个多对多）

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

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

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

原因：多对多情况下，直接使用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 
	LIMIT #{begin}, #{size}

正确写法：

解释：根据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官方手册