mybatisPlus實(shí)現(xiàn)多表連接分頁(yè)動(dòng)態(tài)查詢方式

更新時(shí)間：2025年12月24日 16:34:42 作者：紅顏い笑傾城

文章主要講述了Java開(kāi)發(fā)中常用的一些設(shè)計(jì)模式和代碼結(jié)構(gòu),包括實(shí)體類(lèi)（VO）、Mapper接口、Service實(shí)現(xiàn)類(lèi)（ServiceImpl）等,通過(guò)這些組件的合理使用,可以提高代碼的可維護(hù)性和可擴(kuò)展性

mybatisPlus多表連接分頁(yè)動(dòng)態(tài)查詢

實(shí)體類(lèi)

package com.byx.communitycontrol.common.entity.baseinfo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;

/**
 * 點(diǎn)位信息
 **/
@Data
@TableName(value = "T_DWXX")
public class PointInfo implements Serializable {

    /**
     * 相機(jī)編碼
     */
    @TableField(value = "CAMERAINDEXCODE")
    private String cameraIndexCode;

    /**
     * 點(diǎn)位名稱
     */
    @TableField(value = "NAME")
    private String name;

    /**
     * 區(qū)域編碼
     */
    @TableField(value = "UNITINDEXCODE")
    private String unitIndexCode;

    /**
     * 國(guó)標(biāo)編碼
     */
    @TableField(value = "GBINDEXCODE")
    private String gbIndexCode;

    /**
     * 設(shè)備編碼
     */
    @TableField(value = "DEVICEINDEXCODE")
    private String deviceIndexCode;

    /**
     * 緯度
     */
    @TableField(value = "LATITUDE")
    private String latitude;

    /**
     * 經(jīng)度
     */
    @TableField(value = "LONGITUDE")
    private String longitude;

    /**
     * 高度
     */
    @TableField(value = "ALTITUDE")
    private String altitude;

    /**
     * 像素
     */
    @TableField(value = "PIXEL")
    private Integer pixel;

    /**
     * 相機(jī)類(lèi)別
     */
    @TableField(value = "CAMERATYPE")
    private Integer cameraType;

    /**
     * 安裝位置
     */
    @TableField(value = "INSTALLPLACE")
    private String installPlace;

    /**
     * 矩陣編號(hào)
     */
    @TableField(value = "MATRIXCODE")
    private String matrixCode;

    /**
     * 頻道號(hào)
     */
    @TableField(value = "CHANNUM")
    private Integer chanNum;

    /**
     * 查看棚
     */
    @TableField(value = "VIEWSHED")
    private String viewShed;

    /**
     * 能力集
     */
    @TableField(value = "CAPABILITYSET")
    private String capabilitySet;

    /**
     * 智能套裝
     */
    @TableField(value = "INTELLIGENTSET")
    private String intelligentSet;


    /**
     * 記錄位置
     */
    @TableField(value = "RECORDLOCATION")
    private String recordLocation;

    /**
     * 云臺(tái)控制
     */
    @TableField(value = "PTZCONTROLLER")
    private String ptzController;

    /**
     * 設(shè)備資源類(lèi)型
     */
    @TableField(value = "DEVICERESOURCETYPE")
    private String deviceResourceType;

    /**
     * 信道類(lèi)型
     */
    @TableField(value = "CHANNELTYPE")
    private String channelType;

    /**
     * 轉(zhuǎn)換類(lèi)型
     */
    @TableField(value = "TRANSTYPE")
    private Integer transType;


    /**
     * 更新時(shí)間
     */
    @TableField(value = "UPDATETIME")
    private String updateTime;

    /**
     * 創(chuàng)建時(shí)間
     */
    @TableField(value = "CREATETIME")
    private String createTime;

    /**
     * 狀態(tài)
     */
    @TableField(value = "STATUS")
    private Integer status;

    /**
     * 相機(jī)類(lèi)型名稱
     */
    @TableField(value = "CAMERATYPENAME")
    private String cameraTypeName;

    /**
     * 能力集名稱
     */
    @TableField(value = "CAPABILITYSETNAME")
    private String capabilitySetName;

    /**
     * 智能設(shè)置名稱
     */
    @TableField(value = "INTELLIGENTSETNAME")
    private String intelligentSetName;

    /**
     * 記錄位置名稱
     */
    @TableField(value = "RECORDLOCATIONNAME")
    private String recordLocationName;

    /**
     * 云臺(tái)控制名稱
     */
    @TableField(value = "PTZCONTROLLERNAME")
    private String ptzControllerName;

    /**
     * 設(shè)備資源類(lèi)型名稱
     */
    @TableField(value = "DEVICERESOURCETYPENAME")
    private String deviceResourceTypeName;

    /**
     *
     * 頻道類(lèi)型名稱
     */
    @TableField(value = "CHANNELTYPENAME")
    private String channelTypeName;
    /**
     * 轉(zhuǎn)換類(lèi)型名稱
     */
    @TableField(value = "TRANSTYPENAME")
    private String transTypeName;
    /**
     * 條約類(lèi)型
     */
    @TableField(value = "TREATYTYPE")
    private String treatyType;
    /**
     * 條約類(lèi)型名稱
     */
    @TableField(value = "TREATYTYPENAME")
    private String treatyTypeName;
    /**
     * 狀態(tài)名稱
     */
    @TableField(value = "STATUSNAME")
    private String statusName;
}

VO類(lèi)

package com.byx.communitycontrol.common.vo;

import com.byx.communitycontrol.common.entity.baseinfo.PointInfo;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;

@Data
@EqualsAndHashCode(callSuper = true)
public class PointInfoVo extends PointInfo implements Serializable {

    /**
     * 相機(jī)名稱
     */
    private String cameraName;

    /**
     * 區(qū)域名稱
     */
    private String qyName;

    /**
     * IP
     */
    private String complexIp;

    /**
     * 上級(jí)IP
     */
    private String parentIp;

}

mapper接口

package com.byx.communitycontrol.gm.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.byx.communitycontrol.common.entity.baseinfo.PointInfo;
import com.byx.communitycontrol.common.vo.PointInfoVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


@Mapper
public interface PointInfoMapper extends BaseMapper<PointInfo> {


    
    //常規(guī)寫(xiě)法
    @Select("SELECT\n" +
            "\td.CAMERAINDEXCODE,\n" +
            "\td.NAME,\n" +
            "\td.UNITINDEXCODE,\n" +
            "\td.GBINDEXCODE,\n" +
            "\td.DEVICEINDEXCODE,\n" +
            "\tqd.CAMERANAME,\n" +
            "\tqx.NAME AS qyname,\n" +
            "\td.STATUS,\n" +
            "\td.STATUSNAME,\n" +
            "\td.LATITUDE,\n" +
            "\td.LONGITUDE,\n" +
            "\td.ALTITUDE,\n" +
            "\ti.COMPLEXIP,\n" +
            "\ti.PARENTIP,\n" +
            "\td.CREATETIME,\n" +
            "\td.UPDATETIME \n" +
            "FROM\n" +
            "\tT_DWXX d\n" +
            "\tLEFT JOIN T_DWXXIP i ON d.CAMERAINDEXCODE = i.CAMERAINDEXCODE\n" +
            "\tLEFT JOIN T_QY_DW qd ON d.CAMERAINDEXCODE = qd.CAMERAINDEXCODE\n" +
            "\tLEFT JOIN T_QYXX qx ON d.UNITINDEXCODE = qx.INDEXCODE \n" +
            "${ew.customSqlSegment} " +
            "ORDER BY\n" +
            "\td.UPDATETIME DESC")
    IPage<PointInfoVo> getPointInfoPageList(Page<?> page, @Param("ew") Wrapper wrapper);

    //可返回一個(gè)List集合，之后再用page.setRecords()方法將集合set進(jìn)去
    @Select("SELECT\n" +
            "\td.CAMERAINDEXCODE,\n" +
            "\td.NAME,\n" +
            "\td.UNITINDEXCODE,\n" +
            "\td.GBINDEXCODE,\n" +
            "\td.DEVICEINDEXCODE,\n" +
            "\tqd.CAMERANAME,\n" +
            "\tqx.NAME AS qyname,\n" +
            "\td.STATUS,\n" +
            "\td.STATUSNAME,\n" +
            "\td.LATITUDE,\n" +
            "\td.LONGITUDE,\n" +
            "\td.ALTITUDE,\n" +
            "\ti.COMPLEXIP,\n" +
            "\ti.PARENTIP,\n" +
            "\td.CREATETIME,\n" +
            "\td.UPDATETIME \n" +
            "FROM\n" +
            "\tT_DWXX d\n" +
            "\tLEFT JOIN T_DWXXIP i ON d.CAMERAINDEXCODE = i.CAMERAINDEXCODE\n" +
            "\tLEFT JOIN T_QY_DW qd ON d.CAMERAINDEXCODE = qd.CAMERAINDEXCODE\n" +
            "\tLEFT JOIN T_QYXX qx ON d.UNITINDEXCODE = qx.INDEXCODE \n" +
            "${ew.customSqlSegment} " +
            "ORDER BY\n" +
            "\td.UPDATETIME DESC")
    List<PointInfoVo> getPointInfoPageList(Page<?> page, @Param("ew") Wrapper wrapper);


    //可用實(shí)體屬性代替查詢?cè)?
    @Select("SELECT\n" +
            "\td.*,\n" +
            "\tqd.cameraName,\n" +
            "\tqx.name AS qyName,\n" +
            "\ti.complexIp,\n" +
            "\ti.parentIp \n" +
            "FROM\n" +
            "\tT_DWXX d\n" +
            "\tLEFT JOIN T_DWXXIP i ON d.cameraIndexCode = i.cameraIndexCode\n" +
            "\tLEFT JOIN T_QY_DW qd ON d.cameraIndexCode = qd.cameraIndexCode\n" +
            "\tLEFT JOIN T_QYXX qx ON d.unitIndexCode = qx.indexCode \n" +
            "${ew.customSqlSegment} " +
            "ORDER BY\n" +
            "\td.updateTime DESC")
    IPage<PointInfoVo> getPointInfoPageList(Page<?> page, @Param("ew") Wrapper wrapper);
}

serviceImpl類(lèi)

package com.byx.communitycontrol.gm.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.byx.communitycontrol.common.entity.AppPage;
import com.byx.communitycontrol.common.entity.baseinfo.PointInfo;
import com.byx.communitycontrol.common.request.PointInfoPageReq;
import com.byx.communitycontrol.common.vo.PointInfoVo;
import com.byx.communitycontrol.context.helper.MyBatisPlusPageImpl;
import com.byx.communitycontrol.gm.mapper.PointInfoMapper;
import com.byx.communitycontrol.gm.service.IPointInfoService;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;

@Service
public class PointInfoServiceImpl extends ServiceImpl<PointInfoMapper, PointInfo> implements IPointInfoService {

    @Resource
    private PointInfoMapper pointInfoMapper;

    @Override
    public AppPage<PointInfoVo> findListByPageCondition(PointInfoPageReq request) {
        Page<PointInfoVo> page = new Page(request.getCurrentPage(), request.getPageSize());

        //方法一：
        QueryWrapper<PointInfoVo> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.hasText(request.getDwName()), "name", request.getDwName());
        queryWrapper.like(StringUtils.hasText(request.getQyName()), "qyName", request.getQyName());
        queryWrapper.eq(request.getStatus() != null, "status", request.getStatus());
        List<PointInfoVo> pointInfoPageList = pointInfoMapper.getPointInfoPageList(page, queryWrapper);
        Page<PointInfoVo> pointInfoVoPage = page.setRecords(pointInfoPageList);

        //方法二：
        QueryWrapper<PointInfoVo> eq = Wrappers.query(new PointInfoVo()).like(StringUtils.hasText(request.getDwName()),
                "d.name",
                request.getDwName()).like(StringUtils.hasText(request.getQyName()), "qx.name",
                request.getQyName()).eq(request.getStatus() != null, "d.status", request.getStatus());
        IPage<PointInfoVo> pointInfoPageList = pointInfoMapper.getPointInfoPageList(page, eq);

        方法三：可將 Wrappers.query(new PointInfoVo()).like(...)... 換成 Wrappers.lambdaQuery(PointInfoVo.class).like(StringUtils.hasText(request.getDwName()),PointInfoVo::getName,
                request.getDwName())...

        return new MyBatisPlusPageImpl<>(pointInfoPageList);
    }
}