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

最新推荐文章于 2024-05-04 13:51:18 发布

哈喽，树先生

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量1.5w

点赞数 17

分类专栏： mybatis 文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/huangchong0107/article/details/127427061

版权

mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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

一.表结构：

主表

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<UserVO>
     */
    @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 {
    @Resource
    private UserMapper userMapper;
    @Override
    public 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层

<?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.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:一对多
            assocication:一对一
            -->
        <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个数也计入总数，后面修复，再补博客