mybatis + PageHelper 一对多分页查询及问题解决

mybatis + PageHelper 一对多分页查询及问题解决

0. demo 背景

SQL 如下：

# 家庭（family）和人（person）属于一对多关系
create table family(
	id int(11) primary key auto_increment,
	`count` int(2) comment '家庭成员数',
	address varchar(255) comment '家庭住址'
);
create table person(
	id int(11) primary key auto_increment,
	`name` varchar(20) comment '名字',
	sex varchar(20) comment 'male:男，female：女',
	age int(3) comment '年龄',
	family_id int(11) comment '家庭表主键'
);
insert into family(`count`,address) values(3,'湖北'),(2,'北京'),(4,'上海');
insert into person(`name`,sex,age,family_id) values
('张一',"male",21,1),('张二',"female",22,1),('张三',"male",23,1),
('李一',"male",21,2),('李二',"female",22,2),
('王一',"male",21,3),('王二',"female",22,3),('王三',"male",23,3),('王四',"female",24,3);

entity 对应的 dto 如下：

@Data
public class FamilyDto {
    private Integer id;
    private Integer count;
    private String address;
    private List<Person> personList;
}

@Data
public class PersonDto {
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    private Integer familyId;
}

1. mybatis 一对多查询使用 <collection/> 映射（不分页）

此方式不适合使用 PageHelper 插件分页。

FamilyMapper.xml 文件写法如下

<resultMap id="FamilyDtoMap2" type="com.caihao.mybatisdemo.dto.FamilyDto">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="count" jdbcType="INTEGER" property="count"/>
    <result column="address" jdbcType="VARCHAR" property="address"/>
    <collection property="personList" ofType="com.caihao.mybatisdemo.dto.PersonDto">
        <id column="p_id" jdbcType="INTEGER" property="id"/>
        <result column="p_name" jdbcType="VARCHAR" property="name"/>
        <result column="p_sex" jdbcType="VARCHAR" property="sex"/>
        <result column="p_age" jdbcType="INTEGER" property="age"/>
        <result column="p_family_id" jdbcType="INTEGER" property="familyId"/>
    </collection>
</resultMap>
<select id="selectFamilyDtoAllByCondition" parameterType="com.caihao.mybatisdemo.dto.FamilyQueryDto"
        resultMap="FamilyDtoMap2">
    SELECT f.id, f.`count`, f.address, p.id p_id, p.`name` p_name, p.sex p_sex, p.age p_age, p.family_id p_family_id
    FROM family f
    LEFT JOIN person p ON f.id = p.family_id
</select>

通过使用 <collection/> 标签将子表数据映射到对应的 personList 中。

但是，如果想通过此方式使用 PageHelper 分页的话，就会出现问题。例如，在 Service 中如下使用：

PageHelper.startPage(1,2);
// 此方法不适合通过PageHelper分页，因为PageHelper是按照子表数据进行分页的。只适用于查询所有数据
List<FamilyDto> familyDtoList = familyMapper.selectFamilyDtoAllByCondition(new FamilyQueryDto());
PageInfo<FamilyDto> pageInfo = new PageInfo<>(familyDtoList);

对应上面的 SQL 数据，会发现 pageInfo 的 list 中只有一条数据，且其中的 personList 只有两条数据。

因此，需要重写一个可以用来分页查询的方法。

2. mybatis + PageHelper 实现一对多分页查询

FamilyMapper.xml 文件写法如下

<resultMap id="FamilyDtoMap" type="com.caihao.mybatisdemo.dto.FamilyDto">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="count" jdbcType="INTEGER" property="count"/>
    <result column="address" jdbcType="VARCHAR" property="address"/>
    <collection property="personList" ofType="com.caihao.mybatisdemo.dto.PersonDto"
                select="selectPerson" column="id">
    </collection>
</resultMap>
<resultMap id="PersonDtoMap" type="com.caihao.mybatisdemo.dto.PersonDto">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="sex" jdbcType="VARCHAR" property="sex"/>
    <result column="age" jdbcType="INTEGER" property="age"/>
    <result column="family_id" jdbcType="INTEGER" property="familyId"/>
</resultMap>

<select id="selectFamilyDtoListByCondition" parameterType="com.caihao.mybatisdemo.dto.FamilyQueryDto"
        resultMap="FamilyDtoMap">
    SELECT id, `count`, address FROM family
</select>
<!-- 此方法可以写到PersonMapper.xml中去，那么修改collection标签中的select属性，指定该方法的位置-->
<select id="selectPerson" resultMap="PersonDtoMap">
    SELECT id, `name`, sex, age, family_id FROM  person WHERE family_id = #{id,jdbcType=INTEGER}
</select>

service 中调用 selectFamilyDtoListByCondition()

PageHelper.startPage(1, 2);
List<FamilyDto> familyDtoList = familyMapper.selectFamilyDtoListByCondition(new FamilyQueryDto());
PageInfo<FamilyDto> pageInfo = new PageInfo<>(familyDtoList);

解释：<resultMap id="FamilyDtoMap"/> 中的 <collection/> 标签中相比之前加了两个属性，select 和 column 。select 属性的值（selectPerson）对应着子查询的 <select/> 标签中的 id 属性的值（selectPerson 可自定义）。而 column 属性的值则表示主查询的什么字段用于子查询中，例如这里 column 属性的值是 id ，也就是主查询中的查出来的 id 字段，然后在子查询中便可以使用 column 属性的值 id 在 <resultMap id="FamilyDtoMap"/> 标签中对应的 property 属性的值（这里也是 id ）了，也就是说子查询中所使用的变量是是 java 类型的属性，只不过我这里列名和属性名重复了，都是 id ，如果在 <resultMap id="FamilyDtoMap"/> 中的内容是 <id column="id" jdbcType="INTEGER" property="fId"/> ，那么在子查询中使用变量就是 #{fId, jdbcType=INTEGER} 了。

扩展：

1.如果有子查询需要多个条件的话，那么可以写成 <collection column="{propName1=column1,propName2=column2}"/> ，然后在子查询中就可以使用 propName1 和 propName2 了。

2.由于主查询和子查询之间使用的是左连接，有时候我们希望是内连接。我的想法是在主查询中进行内连接查询，确保主查询中查询出来的数据都是符合条件的。

参考：https://my.oschina.net/softwarechina/blog/375762

源码放在 gitee ：https://gitee.com/caiworld/note-demo/tree/master/mybatis-demo