mybatis mysql 自定义sql，union两张表实现提取公共字段并分页、排序

文章目录

一.详解
- 1.业务需求：
- 2.解决思路：
二.实例
- controller:
- serviceImpl 类
- mapper:
- Mapper.xml:
- 以下是实体类:

一.详解 1.业务需求：

业务为个人重大事项报告和单位重大事项报告两种。业务类似于：发起办公流程-》部门领导审核-》报备给单位领导。流程发起时，两种流程时分开发起的，所以设计时设计了两张表来记录两种业务。流程发起、审核、报备，设计的是完全独立的。后来由于甲方的脑残需求，要求在单位领导查看报备报备信息时，两张业务合并在一个列表里，并且要按时间排序，要有分页。

2.解决思路：

自定义sql，利用union 将两张业务表的公共字段合并成一个虚拟表

		(
		SELECt
			gaform_lpt_30.file_id file_id,
			gaform_lpt_30.unit unit,
			gaform_lpt_30.`name` `name`,
			gaform_lpt_30.gettime time 
		FROM
			`gaform_lpt_30` 
		WHERe
			allagree = 1 
		) UNIOn ALL
		(
		SELECt
			gaform_lpt_29.file_id file_id,
			gaform_lpt_29.report_unit unit,
			gaform_lpt_29.`report_name` `name`,
			gaform_lpt_29.gettime time 
		FROM
			`gaform_lpt_29` 
		WHERe
			allagree = 1 
		)

然后在上面union结果的外层套上排序
这里需要注意一下: 合并 union两边的数据列数(as的字段列)必须一致,否则会合并失败

SELECt
	* 
FROM
	(
		(
		SELECt
			gaform_lpt_30.file_id file_id,
			gaform_lpt_30.unit unit,
			gaform_lpt_30.`name` `name`,
			gaform_lpt_30.gettime time 
		FROM
			`gaform_lpt_30` 
		WHERe
			allagree = 1 
		) UNIOn ALL
		(
		SELECt
			gaform_lpt_29.file_id file_id,
			gaform_lpt_29.report_unit unit,
			gaform_lpt_29.`report_name` `name`,
			gaform_lpt_29.gettime time 
		FROM
			`gaform_lpt_29` 
		WHERe
			allagree = 1 
		) 
	) AS t #此处一定注意，union后务必派生声明虚拟表，不然会出现报错
ORDER BY
	time DESC #time是派生的虚拟表的字段

这时排序和两表合并就完成了。分页利用mybatis框架，只要给dao层方法正常传IPage就可以了(本来怎么分页,这里就可以直接接分页对象了)

以下是java代码：
controller层

    @ApiOperation("报备列表-29+30共用")
    @PostMapping("selectBblist")
    public ServiceResult selectBblist(@RequestParam Integer page,@RequestParam Integer pageCount) {
        IPage page2=  gaformLpt29Service.selectBb(page,pageCount);
       return success(page2);
    }

service层

  @Override
    public IPage selectBb(Integer page, Integer pageCount) {

        return gaformLpt29Dao.selectBb( new Page<>(page, pageCount));
    }

dao层
(只是把上边的sql拿到了这里)

  @Select("SELECTn" +
            "t*n" +
            "FROMn" +
            "t(n" +
            "tt( SELECt  gaform_lpt_30.file_id file_id, gaform_lpt_30.unit unit, gaform_lpt_30.`name` `name`, gaform_lpt_30.gettime time ,gaform_lpt_30.event_id event_id FROM `gaform_lpt_30` WHERe allagree = 1 ) n" +
            "ttUNIOn ALLn" +
            "tt( SELECt gaform_lpt_29.file_id file_id, gaform_lpt_29.report_unit unit, gaform_lpt_29.`report_name` `name`, gaform_lpt_29.gettime time,gaform_lpt_29.event_id event_id FROM `gaform_lpt_29` WHERe allagree = 1 ) n" +
            "n" +
            ")  as t ORDER BY time DESC")
    IPage selectBb(IPage page);//此处传入page 插件会自动实现分页

接收数据的vo

@Data
public class Lpt2930BaobeiVO {
    public String fileId;
    public String unit;
    public String name;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date time;
    public String eventId;

}

原文链接:https://blog.csdn.net/qq_27037397/article/details/121329562

二.实例

这里我把整个类(三个接口)贴过来了,很长,不过很贴近实际

controller:

package com.boot.reservation.controller.statistics;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import com.boot.reservation.service.statistics.StatisticsWebsiteService;
import com.boot.reservation.util.PageResponse;
import com.boot.reservation.util.PageResponseUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiResponse;
import io.swagger.annotations.ApiResponses;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Api(tags = "网页统计功能接口")
@RestController
@RequestMapping("/statistics/website")
public class StatisticsWebsiteController {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private StatisticsWebsiteService statisticsWebsiteService;

    
    @ApiOperation("出入库统计-库")
    @ApiResponses({
            @ApiResponse(code = 200,message = "请求成功",response = StatisticsGranaryWebsiteVO.class)
    })
    @PostMapping(value = "/granary/list")
    public PageResponse findGranaryByPage(@RequestBody StatisticsGranaryWebsiteVO query){
        if (query == null) {
            query = new StatisticsGranaryWebsiteVO();
            logger.error("出入库统计-库列表查询：query: {}", query);
        }
        Page byPage = statisticsWebsiteService.findGranaryByPage(query);
        logger.info("[{},{}], {}", byPage.getCurrent(), byPage.getSize(), byPage.getRecords());
        return PageResponseUtil.toPageResponce(byPage);
    }

    
    @ApiOperation("出入库统计 - 仓房列表")
    @ApiResponses({
            @ApiResponse(code = 200,message = "请求成功",response = StatisticsHouseWebsiteVO.class)
    })
    @PostMapping(value = "/house/list")
    public PageResponse findHouseByPage(@RequestBody StatisticsHouseWebsiteVO query){
        if (query == null) {
            query = new StatisticsHouseWebsiteVO();
            logger.error("出入库统计-库列表查询：query: {}", query);
        }
        if (StringUtils.isBlank(query.getGranaryAddress())) {
            Page voPage = new Page<>();
            return PageResponseUtil.toPageResponce(voPage);
        }
        Page byPage = statisticsWebsiteService.findHouseByPage(query);
        logger.info("[{},{}], {}", byPage.getCurrent(), byPage.getSize(), byPage.getRecords());
        return PageResponseUtil.toPageResponce(byPage);
    }

    
    @ApiOperation("出入库统计 - 仓房查看列表")
    @ApiResponses({
            @ApiResponse(code = 200,message = "请求成功",response = StatisticsHouseDetailsWebsiteVO.class)
    })
    @PostMapping(value = "/houseDetails/list")
    public PageResponse findHouseDetailsByPage(@RequestBody StatisticsHouseDetailsWebsiteVO query){
        if (query == null) {
            query = new StatisticsHouseDetailsWebsiteVO();
            logger.error("出入库统计-库列表查询：query: {}", query);
        }
        if (StringUtils.isBlank(query.getHouse())) {
            Page voPage = new Page<>();
            return PageResponseUtil.toPageResponce(voPage);
        }
        Page byPage = statisticsWebsiteService.findHouseDetailsByPage(query);
        logger.info("[{},{}], {}", byPage.getCurrent(), byPage.getSize(), byPage.getRecords());
        return PageResponseUtil.toPageResponce(byPage);
    }

}

serviceImpl 类

package com.boot.reservation.impl.statistics;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.InStock;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import com.boot.reservation.entity.domain.vo.StorageInfoVO;
import com.boot.reservation.mapper.StatisticsAppletMapper;
import com.boot.reservation.mapper.StatisticsWebsiteMapper;
import com.boot.reservation.mapper.StorageInStockMapper;
import com.boot.reservation.service.project.ProjectManagerService;
import com.boot.reservation.service.statistics.StatisticsWebsiteService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class StatisticsWebsiteServiceImpl implements StatisticsWebsiteService {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private StatisticsWebsiteMapper statisticsWebsiteMapper;

    @Autowired
    private UserSessionRedisUtil userSessionRedisUtil;

    @Value("${authentication.switch}")
    private Boolean isSwitch;

    @Autowired
    private ProjectManagerService projectManagerService;

    @Autowired
    private StatisticsAppletMapper statisticsAppletMapper;

    @Autowired
    private StorageInStockMapper storageInStockMapper;

    @Override
    public Page findGranaryByPage(StatisticsGranaryWebsiteVO query) {
        List storageCodeList = toDataPowerList();
        Page granaryByPage = statisticsWebsiteMapper.findGranaryByPage(query, toDataPowerList());

        List records = granaryByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsGranaryWebsiteVO record : records) {
                Double alreadyOutStock = statisticsAppletMapper.findAlreadyOutStock(record.getGranaryAddress(), storageCodeList);
                Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), "", storageCodeList);
                record.setResidueOutStockNum(BigDecimal.valueOf(alreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
                record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());
            }
        }

        return granaryByPage;
    }

    @Override
    public Page findHouseByPage(StatisticsHouseWebsiteVO query) {
        Page houseByPage = statisticsWebsiteMapper.findHouseByPage(query);
        List records = houseByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsHouseWebsiteVO record : records) {
                Double houseAlreadyOutStock = statisticsAppletMapper.findHouseAlreadyOutStock(record.getGranaryAddress(), record.getHouse(),null);
                Double practicalOutStock = statisticsAppletMapper.findPracticalOutStock(record.getGranaryAddress(), record.getHouse(),null);
                record.setResidueOutStockNum(BigDecimal.valueOf(houseAlreadyOutStock).subtract(BigDecimal.valueOf(practicalOutStock)).doubleValue());
                record.setGrandTotalNum(BigDecimal.valueOf(record.getGrandTotalInStockNum()).subtract(BigDecimal.valueOf(record.getGrandTotalOutStockNum())).doubleValue());

                QueryWrapper inStockQueryWrapper = new QueryWrapper<>();
                inStockQueryWrapper.select("kind_name")
                                    .eq("granary_address",record.getGranaryAddress())
                                    .eq("house",record.getHouse())
                                    .eq("status", 02);
                List inStocks = storageInStockMapper.selectList(inStockQueryWrapper);
                if (inStocks != null && inStocks.size() > 0) {
                    record.setKindName(inStocks.get(0).getKindName());
                }
            }
        }
        return houseByPage;
    }

    @Override
    public Page findHouseDetailsByPage(StatisticsHouseDetailsWebsiteVO query) {
        Page houseDetailsByPage = statisticsWebsiteMapper.findHouseDetailsByPage(query);
        List records = houseDetailsByPage.getRecords();
        if (records != null && records.size() > 0) {
            for (StatisticsHouseDetailsWebsiteVO record : records) {
                if ("入库".equalsIgnoreCase(record.getBusinessType())) {
                    record.setWeight(
                            BigDecimal.valueOf(record.getGrossWeight())
                                    .subtract(BigDecimal.valueOf(record.getBareWeight()))
                                    .subtract(BigDecimal.valueOf(record.getRealityReduceWeight()))
                    );
                } else {
                    record.setWeight(
                            BigDecimal.valueOf(record.getGrossWeight())
                                    .subtract(BigDecimal.valueOf(record.getBareWeight()))
                    );
                }
            }
        }
        return houseDetailsByPage;
    }

// 获取数据权限
    private List toDataPowerList(){
        PerFrontUserEntity pu = userSessionRedisUtil.getSessionUserObjInfo();
        List storages = new LinkedList<>();//库点列表
        if (!isSwitch || pu == null) {
            storages = projectManagerService.queryStoragesNoUser();
        } else {
            storages = projectManagerService.queryStoragesHasUser(String.valueOf(pu.getId()));
        }
        List storageCodeList = storages.stream().map(StorageInfoVO::getStorageCode).collect(Collectors.toList());
        return storageCodeList;
    }
}

mapper:

package com.boot.reservation.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.vo.StatisticsGranaryWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseDetailsWebsiteVO;
import com.boot.reservation.entity.domain.vo.StatisticsHouseWebsiteVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface StatisticsWebsiteMapper {

    Page findGranaryByPage(@Param("query") StatisticsGranaryWebsiteVO query, List storageCodeList);

    Page findHouseByPage(@Param("query") StatisticsHouseWebsiteVO query);

    Page findHouseDetailsByPage(@Param("query") StatisticsHouseDetailsWebsiteVO query);
}

Mapper.xml:





    
        select sins.granary_address as granaryAddress,
        sins.house as house,
        (select lps.storehouse_name from lp_project_storagehouse lps where lps.storehouse_code = sins.house) as houseName,
        ifnull(sum(sins.settlement_num),0) as grandTotalInStockNum,
        ifnull(sum(souts.gross_weight),0) as grandTotalOutStockNum,
        ifnull(sum(lcl.lading_Quality),0) as grandTotalRelieveNum
        from storage_in_stock sins
        left join storage_out_stock souts on (sins.house = souts.house and souts.status = 02)
        left join storage_receipt sr on sins.house = sr.house
        left join storage_pledge sp on (sr.receipt_serial_num = sp.receipt_serial_num and sp.status = 02)
        left join lp_collateral_list lcl on sp.pledge_serial_num = lcl.pledge_number
        where sins.status = 02
        
            and sins.granary_address = #{query.granaryAddress}
        
        group by sins.house

以下是实体类:

package com.boot.reservation.entity.domain.vo;

import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;



@Data
@ApiModel("仓房明细查看统计VO")
public class StatisticsHouseDetailsWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;

    
    @ApiModelProperty("仓房编码")
    private String house;
    
    @ApiModelProperty("流水号")
    private String serialNumber;
    
    @ApiModelProperty("业务类型")
    private String businessType;
    
    @ApiModelProperty("时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date time;
    
    @ApiModelProperty("商品名称")
    @DictValue(dictCode = "cargoType")
    private String kindName;
    
    @ApiModelProperty("商品净重")
    private BigDecimal weight;
    
    @ApiModelProperty("毛重 - 与前端无关")
    private Double grossWeight;
    
    @ApiModelProperty("皮重 - 与前端无关")
    private Double bareWeight;
    
    @ApiModelProperty("实际扣量 - 与前端无关")
    private Double realityReduceWeight;

}

package com.boot.reservation.entity.domain.vo;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;



@Data
@ApiModel("库点统计VO")
public class StatisticsGranaryWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;

    
    @ApiModelProperty("库点编号")
    private String granaryAddress;
    
    @ApiModelProperty("库点名称")
    private String granaryAddressName;
    
    @ApiModelProperty("累计入库数量（kg）")
    private Double grandTotalInStockNum;
    
    @ApiModelProperty("累计出库数量（kg）")
    private Double grandTotalOutStockNum;
    
    @ApiModelProperty("累计解压数量（kg）")
    private Double grandTotalRelieveNum;
    
    @ApiModelProperty("剩余可出库数量（kg）")
    private Double residueOutStockNum;
    
    @ApiModelProperty("库存数量（kg）")
    private Double grandTotalNum;
}

package com.boot.reservation.entity.domain.vo;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.boot.reservation.entity.domain.DictValue;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;



@Data
@ApiModel("仓房统计VO")
public class StatisticsHouseWebsiteVO extends Page implements Serializable {

    private static final long serialVersionUID = 1L;

    
    @ApiModelProperty("库点编号")
    private String granaryAddress;
    
    @ApiModelProperty("库点编号")
    private String house;
    
    @ApiModelProperty("仓房名称")
    private String houseName;
    
    @ApiModelProperty("商品名称")
    @DictValue(dictCode = "cargoType")
    private String kindName;
    
    @ApiModelProperty("累计入库数量（kg）")
    private Double grandTotalInStockNum;
    
    @ApiModelProperty("累计出库数量（kg）")
    private Double grandTotalOutStockNum;
    
    @ApiModelProperty("累计解压数量（kg）")
    private Double grandTotalRelieveNum;
    
    @ApiModelProperty("剩余可出库数量（kg）")
    private Double residueOutStockNum;
    
    @ApiModelProperty("库存数量（kg）")
    private Double grandTotalNum;
}

mybatis mysql 自定义sql，union两张表实现提取公共字段并分页、排序

Java相关栏目本月热门文章