目录
需求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;
参考资料