mybatis-plus 多表关联条件分页查询

此处以一对多，条件分页查询为例：

一.表结构：

主表

CREATETABLE`t_user`(`id`bigintNOTNULLAUTO_INCREMENT,`user_name`varchar(255)DEFAULTNULLCOMMENT'用户名',`sex`tinyintDEFAULTNULL,`email`varchar(255)DEFAULTNULLCOMMENT'邮箱',`phone`varchar(12)DEFAULTNULLCOMMENT'手机号',`password`varchar(255)DEFAULTNULLCOMMENT'密码',`is_delete`tinyint(2)unsigned zerofill DEFAULT'00',`create_time`datetimeDEFAULTNULL,`update_time`datetimeDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

明细表

CREATETABLE`t_user_detail`(`id`bigintNOTNULLAUTO_INCREMENTCOMMENT'主键id',`user_id`bigintNOTNULLCOMMENT't_user表主键id',`address`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci DEFAULTNULLCOMMENT'住址',`hobby`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NOTNULLCOMMENT'爱好',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户详情表';

二.代码实现：

0.请求dto

importio.swagger.annotations.ApiModelProperty;importlombok.Data;@DatapublicclassPageQuery{@ApiModelProperty("页数据条数")publicInteger pageSize =10;@ApiModelProperty("当前为第几页")publicInteger currentPage =1;}

importio.swagger.annotations.ApiModelProperty;importlombok.Data;importlombok.EqualsAndHashCode;@Data@EqualsAndHashCodepublicclassUserInfoPageDTOextendsPageQuery{@ApiModelProperty("用户名")privateString userName;privateInteger sex;@ApiModelProperty("邮箱")privateString email;@ApiModelProperty("手机号")privateString phone;@ApiModelProperty("爱好")privateString hobby;}

1.Controller 层：

@RestController@RequestMapping("/user")publicclassUserController{//用户表读的service@Resource@Qualifier("userServiceWriteImpl")privateIUserService userWService;//用户表写的service@Resource@Qualifier("userServiceReadImpl")privateIUserService userRService;/**
     * 多表关联分页 条件查询
     * @param dto
     * @return IPage<UserVO>
     */@PostMapping("/userInfoPage")publicIPage<UserVO>findByPage(@RequestBodyUserInfoPageDTO dto){return userRService.findByPage(dto);}}

注：我的项目中进行了service 读写分类配置，实际使用中，直接使用mybatis-plus中的 IUserService 对应的接口就行。
2.service 层

publicinterfaceIUserServiceextendsIService<User>{IPage<UserVO>findByPage(UserInfoPageDTO dto);}

service impl实现层：

importcom.baomidou.mybatisplus.core.metadata.IPage;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.baomidou.mybatisplus.extension.service.impl.ServiceImpl;importcom.up.openfeign.api.user.dto.UserInfoPageDTO;importcom.up.openfeign.api.user.vo.UserVO;importcom.up.user.entity.User;importcom.up.user.mapper.UserMapper;importcom.up.user.service.IUserService;importorg.springframework.stereotype.Service;importjavax.annotation.Resource;@Service@DS("slave")publicclassUserServiceReadImplextendsServiceImpl<UserMapper,User>implementsIUserService{@ResourceprivateUserMapper userMapper;@OverridepublicIPage<UserVO>findByPage(UserInfoPageDTO dto){Page<UserVO> page =newPage<>(dto.currentPage, dto.pageSize);IPage<UserVO> queryVoPage = userMapper.findByPage(page, dto);return queryVoPage;}}

3.mapper 层

importcom.baomidou.mybatisplus.core.metadata.IPage;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.up.openfeign.api.user.dto.UserInfoPageDTO;importcom.up.openfeign.api.user.vo.UserVO;importcom.up.user.entity.User;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importorg.apache.ibatis.annotations.Mapper;importorg.apache.ibatis.annotations.Param;/**
 * Mapper 接口
 */@MapperpublicinterfaceUserMapperextendsBaseMapper<User>{IPage<UserVO>findByPage(Page<UserVO> page,@Param("dto")UserInfoPageDTO dto);}

4.mapper.xml层

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.up.user.mapper.UserMapper"><resultMapid="page_user_vo"type="com.up.openfeign.api.user.vo.UserVO"><idcolumn="id"jdbcType="BIGINT"property="id"/><resultcolumn="user_name"jdbcType="VARCHAR"property="userName"/><resultcolumn="sex"jdbcType="TINYINT"property="sex"/><resultcolumn="email"jdbcType="VARCHAR"property="email"/><resultcolumn="phone"jdbcType="VARCHAR"property="phone"/><resultcolumn="password"jdbcType="VARCHAR"property="password"/><resultcolumn="is_delete"jdbcType="TINYINT"property="isDelete"/><resultcolumn="create_time"property="createTime"/><resultcolumn="update_time"property="updateTime"/><!--collection:一对多
            assocication:一对一
            --><collectionproperty="details"ofType="com.up.openfeign.api.user.vo.UserDetailVO"><!--  一对多，如果多个表字段名相同，要记住使用别名，否则多条数据只显示一条   --><idcolumn="udId"jdbcType="BIGINT"property="id"/><resultcolumn="user_id"jdbcType="BIGINT"property="userId"/><resultcolumn="address"jdbcType="VARCHAR"property="address"/><resultcolumn="hobby"jdbcType="VARCHAR"property="hobby"/></collection></resultMap><selectid="findByPage"resultMap="page_user_vo"parameterType="com.up.openfeign.api.user.dto.UserInfoPageDTO">
        select u.id,u.user_name,u.sex,u.email,u.phone,u.password,u.is_delete,u.create_time,u.update_time,
        ud.id as udId,ud.user_id,ud.address,ud.hobby from t_user u left join t_user_detail ud on u.id=ud.user_id
        <where><iftest="dto.userName !='' and dto.userName != null">
                and u.user_name = #{dto.userName,jdbcType=VARCHAR}
            </if><iftest="dto.sex != null">
                and u.sex = #{dto.sex,jdbcType=TINYINT}
            </if><iftest="dto.email !='' and dto.email != null">
                and u.email = #{dto.email,jdbcType=VARCHAR}
            </if><iftest="dto.phone != null and dto.phone!=''">
                and u.phone = #{dto.phone,jdbcType=VARCHAR}
            </if><iftest="dto.hobby != null and dto.hobby!=''">
                and ud.hobby = #{dto.hobby,jdbcType=VARCHAR}
            </if></where></select></mapper>

5.测试：

结果body:

{
    "records": [
        {
            "id": 2,
            "userName": "hc",
            "sex": 1,
            "email": "[email protected]",
            "phone": "18062731203",
            "password": "123456",
            "isDelete": 0,
            "createTime": "2022-08-04T13:59:38.000+0000",
            "updateTime": "2022-08-04T14:00:56.000+0000",
            "details": [
                {
                    "id": 3,
                    "userId": 2,
                    "address": "上海",
                    "hobby": "足球"
                }
            ]
        },
        {
            "id": 1,
            "userName": "hc1",
            "sex": 2,
            "email": "[email protected]",
            "phone": "18062731203",
            "password": "123456",
            "isDelete": 0,
            "createTime": "2022-10-20T06:35:12.000+0000",
            "updateTime": "2022-10-21T06:35:15.000+0000",
            "details": [
                {
                    "id": 4,
                    "userId": 1,
                    "address": "北京",
                    "hobby": "足球"
                }
            ]
        }
    ],
    "total": 2,
    "size": 10,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 1
}

Q:todo page 分页会把details个数也计入总数，后面修复，再补博客