mybatisplus 多表關(guān)聯(lián)條件分頁查詢的實(shí)現(xiàn)

更新時間：2025年01月16日 09:16:21 作者：哈嘍，樹先生

本文主要介紹了mybatisplus 多表關(guān)聯(lián)條件分頁查詢的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一.表結(jié)構(gòu)：

主表

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 '手機(jī)號',
  `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;

在這里插入圖片描述

明細(xì)表

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='用戶詳情表';

在這里插入圖片描述

二.代碼實(shí)現(xiàn)：

0.請求dto

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class PageQuery {
    @ApiModelProperty("頁數(shù)據(jù)條數(shù)")
    public Integer pageSize = 10;

    @ApiModelProperty("當(dāng)前為第幾頁")
    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("手機(jī)號")
    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;

    /**
     * 多表關(guān)聯(lián)分頁 條件查詢
     * @param dto
     * @return IPage<UserVO>
     */
    @PostMapping("/userInfoPage")
    public IPage<UserVO> findByPage(@RequestBody UserInfoPageDTO dto) {
        return userRService.findByPage(dto);
    }
}

注：我的項(xiàng)目中進(jìn)行了service 讀寫分類配置，實(shí)際使用中，直接使用mybatis-plus中的 IUserService 對應(yīng)的接口就行。

2.service 層

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

service impl實(shí)現(xiàn)層：

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">
            <!--  一對多，如果多個表字段名相同，要記住使用別名，否則多條數(shù)據(jù)只顯示一條   -->
            <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.測試：

在這里插入圖片描述

結(jié)果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個數(shù)也計入總數(shù)，后面修復(fù)，再補(bǔ)博客

到此這篇關(guān)于mybatisplus 多表關(guān)聯(lián)條件分頁查詢的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)mybatisplus 多表分頁查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: