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

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

一.表结构：

主表

CREATE TABLE `t_user` (`id` bigint NOT NULL AUTO_INCREMENT,`user_name` varchar(255) DEFAULT NULL COMMENT '用户名',`sex` tinyint DEFAULT NULL,`email` varchar(255) DEFAULT NULL COMMENT '邮箱',`phone` varchar(12) DEFAULT NULL COMMENT '手机号',`password` varchar(255) DEFAULT NULL COMMENT '密码',`is_delete` tinyint(2) unsigned zerofill DEFAULT '00',`create_time` datetime DEFAULT NULL,`update_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

明细表

CREATE TABLE `t_user_detail` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',`user_id` bigint NOT NULL COMMENT 't_user表主键id',`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '爱好',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户详情表';

二.代码实现：

0.请求dto

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;@Data
public class PageQuery {@ApiModelProperty("页数据条数")public Integer pageSize = 10;@ApiModelProperty("当前为第几页")public Integer currentPage = 1;
}

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;@Data
@EqualsAndHashCode
public class UserInfoPageDTO extends PageQuery {@ApiModelProperty("用户名")private String userName;private Integer sex;@ApiModelProperty("邮箱")private String email;@ApiModelProperty("手机号")private String phone;@ApiModelProperty("爱好")private String hobby;
}

1.Controller 层：

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

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

public interface IUserService extends IService<User> {IPage<UserVO> findByPage(UserInfoPageDTO dto);
}

service impl实现层：

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.up.openfeign.api.user.dto.UserInfoPageDTO;
import com.up.openfeign.api.user.vo.UserVO;
import com.up.user.entity.User;
import com.up.user.mapper.UserMapper;
import com.up.user.service.IUserService;
import org.springframework.stereotype.Service;import javax.annotation.Resource;@Service
@DS("slave")
public class UserServiceReadImpl extends ServiceImpl<UserMapper, User> implements IUserService {@Resourceprivate UserMapper userMapper;@Overridepublic IPage<UserVO> findByPage(UserInfoPageDTO dto) {Page<UserVO> page = new Page<>(dto.currentPage, dto.pageSize);IPage<UserVO> queryVoPage = userMapper.findByPage(page, dto);return queryVoPage;}
}

3.mapper 层

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

4.mapper.xml层

DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.up.user.mapper.UserMapper"><resultMap id="page_user_vo" type="com.up.openfeign.api.user.vo.UserVO"><id column="id" jdbcType="BIGINT" property="id"/><result column="user_name" jdbcType="VARCHAR" property="userName"/><result column="sex" jdbcType="TINYINT" property="sex"/><result column="email" jdbcType="VARCHAR" property="email"/><result column="phone" jdbcType="VARCHAR" property="phone"/><result column="password" jdbcType="VARCHAR" property="password"/><result column="is_delete" jdbcType="TINYINT" property="isDelete"/><result column="create_time"  property="createTime"/><result column="update_time"  property="updateTime"/><collection property="details" ofType="com.up.openfeign.api.user.vo.UserDetailVO"><id column="udId" jdbcType="BIGINT" property="id"/><result column="user_id" jdbcType="BIGINT" property="userId"/><result column="address" jdbcType="VARCHAR" property="address"/><result column="hobby" jdbcType="VARCHAR" property="hobby"/>collection>resultMap><select id="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><if test="dto.userName !='' and dto.userName != null">and u.user_name = #{dto.userName,jdbcType=VARCHAR}if><if test="dto.sex != null">and u.sex = #{dto.sex,jdbcType=TINYINT}if><if test="dto.email !='' and dto.email != null">and u.email = #{dto.email,jdbcType=VARCHAR}if><if test="dto.phone != null and dto.phone!='' ">and u.phone = #{dto.phone,jdbcType=VARCHAR}if><if test="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": "46494588@qq.com","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": "46494588@qq.com","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个数也计入总数，后面修复，再补博客

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！