当一个实体类中包括另一个实体类的 list ,对这个实体类进行分页查询时会发生一些问题:相同的数据会合并成一条,导致前台显示时每页的数据量不一样,比如第一页8条,第二页7条,第三页10条的情况。
问题模拟:
假设一个员工有多个部门,因为部门下面可能也会有子部门,这时就会出现这种情况。
javaBean:
// 用户类,包括用户基本属性和部门相关信息
public class User {
// 用户id
private String userId;
// 用户姓名
private String userName;
// 用户部门关联表信息
private List<UserDept> userDeptList;
// 部门信息
private List<Dept> deptList;
}
// 用户部门关联表,存放了用户在部门中的相关信息
public class UserDept {
// 用户id
private String userId;
// 部门id
private String deptId;
// 是否是部门领导
private String isLeader;
// 在部门中的排序
private String userOrderInDept;
}
// 部门表
public class Dept {
// 部门id
private String deptId;
// 部门名称
private String deptName;
}
<!-- mybatis xml配置 -->
<resultMap id="UserResult" type="User">
<id property="userId" colunm="user_id" />
<result property="userName" colunm="user_name" />
<collection property="userDeptList" ofType="UserDept" resultMap="UserDeptResult">
<collection property="userDeptList" ofType="Dept" resultMap="DeptResult">
</resultMap>
<resultMap id="UserDeptResult" type="UserDept">
<result property="userId" colunm="user_id" />
<result property="deptId" colunm="dept_id" />
<result property="isLeader" colunm="is_leader" />
<result property="userOrderInDept" colunm="dept_order" />
</resultMap>
<resultMap id="DeptResult" type="Dept">
<result property="deptId" colunm="dept_id" />
<result property="deptName" colunm="dept_name" />
</resultMap>
此时如果要查询用户信息并对其进行分页,第一反应就是用join
来关联表查就行了;这种方法会出现这样的问题:对同一个userid,如果有多个部门就会出现多条,比如查了10条,然后经过mybatis的处理,将多个部门放到collection里面,处理完后一个userid就会对应一条User实体,看上去数量会变少几条。
参考了网上很多文章,大体就两种:
第一种是利用mybatis的嵌套查询,这种方式的原理就是先对执行的第一条sql分页,然后根据第一条sql的结果来查询其他相关的信息,可见这种方式需要执行多次sql,而且查询条件不好控制。
要使用第一种方式,需要改造实体类,这种方式一般一个实体类种包含一个list,如果包含多个list就很难用这种方式(除非这些 list 只需要主表的条件,而不需要其他从表的条件来查询;拿本例来说,查部门相关信息需要取用户部门关联表中的部门id才能获得部门信息,这种需要从表的条件来查询的无法使用该方式,因为嵌套查询的参数只能从主表的字段传下去)。
由于user_dept表和dept表是一一对应的,可以把这两个表的信息放在一个实体类。
// 用户类,包括用户基本属性和部门相关信息
public class User {
// 用户id
private String userId;
// 用户姓名
private String userName;
// 部门分页相关信息
private List<UserDeptPage> userDeptPageList;
}
// 部门分页相关信息实体类
public class UserDeptPage {
// 用户id
private String userId;
// 部门id
private String deptId;
// 是否是部门领导
private String isLeader;
// 在部门中的排序
private String userOrderInDept;
// 部门id
private String deptId;
// 部门名称
private String deptName;
}
<!-- mybatis xml配置 -->
<resultMap id="UserResult" type="User">
<id property="userId" colunm="user_id" />
<result property="userName" colunm="user_name" />
<!-- colunm表示selectUserDeptPage的参数,即根据userid查询部门信息 -->
<collection property="userDeptPageList" ofType="UserDeptPage " select="selectUserDeptPage" colunm="userId=user_id">
</resultMap>
<resultMap id="UserDeptPageResult" type="UserDeptPage ">
<result property="userId" colunm="user_id" />
<result property="deptId" colunm="dept_id" />
<result property="isLeader" colunm="is_leader" />
<result property="userOrderInDept" colunm="dept_order" />
<result property="deptId" colunm="dept_id" />
<result property="deptName" colunm="dept_name" />
</resultMap>
<!-- 先分页查询用户信息,之后的sql将不会使用pagehelper的分页参数 -->
<select id="selectUserList" resultMap="UserResult">
select * from user
</select>
<!-- 查询部门信息 -->
<select id="selectUserDeptPage" resultMap="UserDeptPageResult">
select ud.*,d.* from user_dept ud left join dept d on ud.dept_id = d.dept_id
where ud.user_id = #{userId}
</select>
结果:
{
userid: 1,
...,
userDeptPageList: [
{
userId: 1,
deptId: 1,
deptName: "部门1",
...
},
{
userId: 2,
deptId: 2,
deptName: "部门2",
...
},...
]
}
分页查出来的正好是10个人(分页大小),这样的结果看起来确实是想要的效果;但是这时候如果要根据部门名称来查询部门用户,这种写法就不行了,因为它其实是先分页查询用户信息,这时候是无法添加部门名称的限制的;就算加在selectUserDeptPage
中,此时也只是对分页查询出来的用户添加部门名称限制,并不符合我们的要求。
如果要加查询条件,那么必须要在查用户表时就加上;
因此在小小的思考过后,我们可以这样改一下sql:
<!-- 如果没有部门名称条件,那么就只从user表中查;否则连接部门表查询指定部门的用户 -->
<!-- 此时的select后面一定只能有user表中的字段 -->
<select id="selectUserList" resultMap="UserResult">
select u.* from user u
<if test="deptName != null and deptName != ''">
left join user_dept ud on u.user_id = ud.user_id
left join dept d on ud.dept_id = u.dept_id
where d.dept_name = #{deptName}
</if>
</select>
这样最后的效果就是我们想要的效果了。
第二种是先分好页再查,原理与嵌套查询的原理差不多,具体的sql如下:
select *
from (select * from user limit #{offset}, #{pagesize}) u
left join user_dept ud on u.user_id = ud.user_id
left join dept d on ud.dept_id = d.dept_id
where d.dept_name = #{deptName}
可以看到原理和嵌套查询是一样的,而且只会执行一条sql;但问题与嵌套查询的相同,限制条件不好加,而且不能像嵌套查询那样加限制条件,否则最后的结果并不是我们想要的结果;另外这种方式不能使用pageHelper,需要手动写分页,很麻烦。
最后,还想到了另一种方法:利用group by来达到效果:
把同一个用户的部门拼接起来,达成一个用户返回一条数据的效果。
修改实体类:
public class User {
// 用户id
private String userId;
// 用户姓名
private String userName;
// 部门名称
private String deptNameStr
}
select u.user_id, u.user_name,GROUP_CONCAT(d.dept_name) deptNameStr
from user u
left join user_dept ud on u.user_id = ud.user_id
left join dept d on ud.dept_id = d.dept_id
group by u.user_id HAVING deptNameStr like concat('%', #{deptName}, '%')
order by convert(u.user_name using gbk);
最后返回的效果:
{
userid: 1,
userName: '用户1',
deptNameStr: '部门1,部门2'
}
这种方式可以将需要的字段值拼接起来,好处是前台可以直接显示,缺点是如果需要获取比如部门id等其他属性时需要另作处理。