基于jeecgboot复杂sql查询的列表自定义列实现

       一般简单的sql列表实现，可以通过系统自带的在线开发里的online表单开发实现，但一些复杂的混合多表的实现，同时对需要查询出来的结果进行数据更新，这种复杂的数据列表只能通过我们自己手工实现了。

      下面就举一个出入库的商品选择的例子，这个商品选择的时候需要进行多表联合查询，同时还需要进行库存的更新。

      一、ErpGoodsDto数据结构类

package com.nbcio.modules.erp.goods.dto;

import java.io.Serializable;

import lombok.Data;

@Data
public class ErpGoodsDto implements Serializable {
    private static final long serialVersionUID = 1L;

    /**ID*/
    private java.lang.String id;
    
    /**编号*/
    private java.lang.String code;
    
    /**名称*/
    private java.lang.String name;
    
    /**SPU编号*/
    //private java.lang.String spuId;
    
    /**类别编号*/
    private java.lang.String categoryId;
    
    /**类别名称*/
    private java.lang.String categoryName;
    
    /**品牌编号*/
    private java.lang.String brandId;
    
    /**品牌名称*/
    private java.lang.String brandName;
    
    /**规格*/
    private java.lang.String spec;
    
    /**单位*/
    private java.lang.String unit;
    
    /**采购价格*/
    private java.math.BigDecimal purchasePrice;
    
    /**销售价格*/
    private java.math.BigDecimal salePrice;
    
    /**零售价格*/
    private java.math.BigDecimal retailPrice;
    
    /**税率（%）*/
    private java.math.BigDecimal taxRate;
    
    /**数量*/
    private java.lang.Integer num;
    
    /**总价格*/
    private java.math.BigDecimal totalPrice;
    
    /**库存数量*/
    private java.lang.Integer stockNum;

}

二、ErpGoodsDtoController类

package com.nbcio.modules.erp.goods.controller;

import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.jeecg.common.api.vo.Result;

import com.nbcio.modules.erp.goods.dto.ErpGoodsDto;
import com.nbcio.modules.erp.goods.service.IErpGoodsDtoService;
import com.nbcio.modules.erp.goods.vo.QueryGoodsVo;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;

import org.jeecg.common.system.base.controller.JeecgController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.jeecg.common.aspect.annotation.AutoLog;

 /**
 * @Description: erp_goods_dto
 * @Author: nbacheng
 * @Date:   2023-02-09
 * @Version: V1.0
 */
@Api(tags="erp_goods_dto")
@RestController
@RequestMapping("/goods/erpGoodsDto")
@Slf4j
public class ErpGoodsDtoController extends JeecgController<ErpGoodsDto, IErpGoodsDtoService> {
  @Autowired
  private IErpGoodsDtoService erpGoodsDtoService;
  
  
  /**
   * 分页列表查询
   *
   * @param erpGoods
   * @param pageNo
   * @param pageSize
   * @param req
   * @return
   */
  @AutoLog(value = "erp_goods-商品信息列表查询")
  @ApiOperation(value="erp_goods-商品信息列表查询", notes="erp_goods-商品信息列表查询")
  @GetMapping(value = "/list")
  public Result<?> queryGoodsList(QueryGoodsVo queryGoodsvo,
                   @RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
                   @RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
                   HttpServletRequest req) {
    Page<ErpGoodsDto> page = new Page<ErpGoodsDto>(pageNo, pageSize);
    IPage<ErpGoodsDto> pageList = erpGoodsDtoService.queryGoodsList(page, queryGoodsvo);
    return Result.OK(pageList);
  }
  
}

三、ErpGoodsDtoMapper 类

package com.nbcio.modules.erp.goods.mapper;

import com.nbcio.modules.erp.goods.dto.ErpGoodsDto;
import com.nbcio.modules.erp.goods.vo.QueryGoodsVo;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

/**
 * @Description: erp_goods_dto
 * @Author: nbacheng
 * @Date:   2023-02-09
 * @Version: V1.0
 */
public interface ErpGoodsDtoMapper extends BaseMapper<ErpGoodsDto> {
   List<ErpGoodsDto> queryGoodsList(Page<ErpGoodsDto> page, @Param("vo") QueryGoodsVo vo);
   public List<ErpGoodsDto> getByIds(@Param("idArray") String[] idArray);
}

四、ErpGoodsDtoMapper.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.nbcio.modules.erp.goods.mapper.ErpGoodsDtoMapper">
  <resultMap id="ErpGoodsDtoMap" type="com.nbcio.modules.erp.goods.dto.ErpGoodsDto">
        <id column="id" property="id"/>
        <result column="code" property="code"/>
        <result column="name" property="name"/>
        <result column="category_id" property="categoryId"/>
        <result column="category_name" property="categoryName"/>
        <result column="brand_id" property="brandId"/>
        <result column="brand_name" property="brandName"/>
        <result column="spec" property="spec"/>
        <result column="unit" property="unit"/>
        <result column="purchase_price" property="purchasePrice"/>
        <result column="sale_price" property="salePrice"/>
        <result column="retail_price" property="retailPrice"/>
        <result column="tax_rate" property="taxRate"/>
        <result column="num" property="num"/>
        <result column="total_price" property="totalPrice"/>
    </resultMap>
    <sql id="ErpGoodsDto_sql">
        SELECT
            a.id,
            a.code,
            a.name,
            c.id AS category_id,
            c.name AS category_name,
            b.id AS brand_id,
            b.name AS brand_name,
            a.spec,
            a.unit,
            price.purchase AS purchase_price,
            price.sale AS sale_price,
            price.retail AS retail_price,
      a.tax_rate,
      1 as num,
      price.sale*1 as total_price
        FROM erp_goods AS a
        LEFT JOIN erp_goods_price AS price ON price.id = a.id
        LEFT JOIN erp_goods_category AS c ON c.id = a.category_id
        LEFT JOIN erp_goods_brand AS b ON b.id = a.brand_id
    </sql>
    <select id="queryGoodsList" resultMap="ErpGoodsDtoMap">
        <include refid="ErpGoodsDto_sql"/>
        <where>
           <if test="vo != null">
              <if test="vo.condition != null and vo.condition != ''">
                  AND (
                  a.id LIKE CONCAT('%', #{vo.condition}, '%')
                  OR a.code LIKE CONCAT('%', #{vo.condition}, '%')
                  OR a.name LIKE CONCAT('%', #{vo.condition}, '%')
                  )
              </if>
              <if test="vo.brandId != null and vo.brandId != ''">
                  AND b.id = #{vo.brandId}
              </if>
              <if test="vo.categoryId != null and vo.categoryId != ''">
                  AND (c.id = #{vo.categoryId} 
              </if>
            </if>
            AND a.status = '1' 
        </where>
        ORDER BY a.code
    </select>
    
    
</mapper>

五、IErpGoodsDtoService类

package com.nbcio.modules.erp.goods.service;

import com.nbcio.modules.erp.goods.dto.ErpGoodsDto;
import com.nbcio.modules.erp.goods.vo.QueryGoodsVo;

import java.util.List;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * @Description: erp_goods_dto
 * @Author: nbacheng
 * @Date:   2023-02-09
 * @Version: V1.0
 */
public interface IErpGoodsDtoService extends IService<ErpGoodsDto> {
  IPage<ErpGoodsDto> queryGoodsList(Page<ErpGoodsDto> page, QueryGoodsVo queryGoodsVo);
}

六、ErpGoodsDtoServiceImpl类

package com.nbcio.modules.erp.goods.service.impl;

import com.nbcio.modules.erp.goods.dto.ErpGoodsDto;
import com.nbcio.modules.erp.goods.mapper.ErpGoodsDtoMapper;
import com.nbcio.modules.erp.goods.service.IErpGoodsDtoService;
import com.nbcio.modules.erp.goods.vo.QueryGoodsVo;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

/**
 * @Description: erp_goods
 * @Author: nbacheng
 * @Date:   2022-08-30
 * @Version: V1.0
 */
@Service
public class ErpGoodsDtoServiceImpl extends ServiceImpl<ErpGoodsDtoMapper, ErpGoodsDto> implements IErpGoodsDtoService {

  @Autowired
  private ErpGoodsDtoMapper erpGoodsDtoMapper;
  
  @Override
  public IPage<ErpGoodsDto> queryGoodsList(Page<ErpGoodsDto> page, QueryGoodsVo queryGoodsVo) {
    List<ErpGoodsDto> erpGoodsDtoLists = this.baseMapper.queryGoodsList(page, queryGoodsVo);
        //这里后面还要根据仓库id进行库存数据的更新
        return page.setRecords(erpGoodsDtoLists);
  }

}

七、前端的显示实现

<template>
  <a-modal
    :width="1200"
    :visible="visible"
    :title="title"
    @ok="handleSubmit"
    @cancel="close"
    cancelText="关闭"
    style="top:5%;height: 100%;overflow-y: hidden"
    wrapClassName="ant-modal-cust-warp"
  >
    <a-row :gutter="10" style="padding: 10px; margin: -10px">
      <a-col :md="24" :sm="24">
        <!-- 查询区域 -->
        <div class="table-page-search-wrapper">
          <!-- 搜索区域 -->
          <a-form layout="inline" @keyup.enter.native="onSearch">
            <a-row :gutter="24">
              <a-col :md="6" :sm="8">
                <a-form-item label="商品" :labelCol="{span: 5}" :wrapperCol="{span: 18, offset: 1}">
                  <a-input ref="goods" placeholder="编码或名称模糊查询" v-model="queryParam.condition"></a-input>
                </a-form-item>
              </a-col>
              <a-col :md="6" :sm="8">
                <a-form-item label="类别" :labelCol="{span: 5}" :wrapperCol="{span: 18, offset: 1}">
                  <a-input ref="goods" placeholder="类别" v-model="queryParam.categoryId"></a-input>
                </a-form-item>
              </a-col>
              <a-col :md="6" :sm="8">
                <a-form-item label="品牌" :labelCol="{span: 5}" :wrapperCol="{span: 18, offset: 1}">
                  <a-input ref="goods" placeholder="品牌" v-model="queryParam.brandId"></a-input>
                </a-form-item>
              </a-col>
              <span style="float: left;overflow: hidden;" class="table-page-search-submitButtons">
                <a-col :md="6" :sm="24">
                  <a-button type="primary" @click="loadGoodsData(1)">查询</a-button>
                  <a-button style="margin-left: 8px" @click="searchReset(1)">重置</a-button>
                </a-col>
              </span>
            </a-row>
          </a-form>
          <a-table
            ref="table"
            :scroll="scrollTrigger"
            size="middle"
            rowKey="id"
            :columns="columns"
            :dataSource="dataSource"
            :pagination="ipagination"
            :rowSelection="{selectedRowKeys: selectedRowKeys, onChange: onSelectChange,type: getType}"
            :loading="loading"
            :customRow="rowAction"
            @change="handleTableChange">
            <template slot="customRenderEnableSerialNumber" slot-scope="enableSerialNumber">
              <a-tag v-if="enableSerialNumber==1" color="green">有</a-tag>
              <a-tag v-if="enableSerialNumber==0" color="orange">无</a-tag>
            </template>
            <template slot="customRenderEnableBatchNumber" slot-scope="enableBatchNumber">
              <a-tag v-if="enableBatchNumber==1" color="green">有</a-tag>
              <a-tag v-if="enableBatchNumber==0" color="orange">无</a-tag>
            </template>
          </a-table>
        </div>
      </a-col>
    </a-row>
  </a-modal>
</template>

<script>
  import { httpAction, getAction } from '@/api/manage'
  import {filterObj} from '@/utils/util'
  import { NbcioListMixin } from '@/mixins/NbcioListMixin'
  import Vue from 'vue'

  export default {
    name: 'NbcioSelectGoodsModal',
    mixins:[NbcioListMixin],
    components: {},
    props: ['modalWidth', 'rows', 'multi', 'goodsIds'],
    data() {
      return {
        queryParam: {
          condition: '',
          category: {},
          brand: {}
        },
        labelCol: {
          xs: { span: 24 },
          sm: { span: 5 },
        },
        wrapperCol: {
          xs: { span: 24 },
          sm: { span: 16 },
        },
        categoryTree:[],
        columns: [
          {dataIndex: 'id', title: '商品ID'},
          {dataIndex: 'code', title: '编码'},
          {dataIndex: 'name', title: '名称'},
          {dataIndex: 'categoryName', title: '类别'},
          {dataIndex: 'brandName', title: '品牌'},
          {dataIndex: 'unit', title: '单位'},
          {dataIndex: 'spec', title: '规格'},
          {dataIndex: 'salePrice', title: '销售价格'},
          {dataIndex: 'taxRate', title: '税率(%)'},
          {dataIndex: 'stockNum', title: '库存数量'}, 
        ],
        scrollTrigger: {},
        dataSource: [],
        selectedRowKeys: [],
        selectGoodsRows: [],
        selectGoodsIds: [],
        title: '选择商品',
        ipagination: {
          current: 1,
          pageSize: 10,
          pageSizeOptions: ['10', '20', '30'],
          showTotal: (total, range) => {
            return range[0] + '-' + range[1] + ' 共' + total + '条'
          },
          showQuickJumper: true,
          showSizeChanger: true,
          total: 0
        },
        visible: false,
        form: this.$form.createForm(this),
        loading: false,
        expandedKeys: [],
        disableMixinCreated: true,
        goodsType: [],
        url: {
          goodslist: "/goods/erpGoodsDto/list", 
        },
      }
    },
    computed: {
      // 计算属性的 getter
      getType: function () {
        return this.multi == true ? 'checkbox' : 'radio';
      }
    },
    watch: {
      goodId: {
        immediate: true,
        handler() {
          this.initcode()
        }
      },
    },
    created() {
      // 该方法触发屏幕自适应
      this.resetScreenSize()
    },
    methods: {
      initcode() {
        if (this.goodId) {
          this.$emit('initComp', this.goodCode)
        } else {
          // JSelectUserByDep组件bug issues/I16634
          this.$emit('initComp', '')
        }
      },
      loadGoodsData(arg) {
        if (arg === 1) {
          this.ipagination.current = 1;
        }
        this.loading = true
        let params = this.getQueryParams()//查询条件
        console.log("loadGoodsData params",params)
        getAction(this.url.goodslist,params).then((res) => {
          if (res) {
            console.log("goodslist res.result = ",res.result);
            this.dataSource = res.result.records;
            this.ipagination.total = res.result.total;
            if(res.total ===1) {
              this.title = '选择商品'
              this.$nextTick(() => this.$refs.goods.focus());
            } else {
              this.title = '选择商品'
            }
          }
        }).finally(() => {
          this.loading = false
        })
      },
      // 触发屏幕自适应
      resetScreenSize() {
        let screenWidth = document.body.clientWidth;
        if (screenWidth < 500) {
          this.scrollTrigger = {x: 800};
        } else {
          this.scrollTrigger = {};
        }
      },
      showModal(goodId) {
        this.visible = true;
        this.title = '选择商品'
        this.queryParam.condition = goodId;
        this.$nextTick(() => this.$refs.goods.focus());
        this.loadGoodsData();
        this.form.resetFields();
      },
      getQueryParams() {
        let param = Object.assign({}, this.buildSearchFormData());
        param.page = this.ipagination.current;
        param.rows = this.ipagination.pageSize;
        return filterObj(param);
      },
      // 查询前构建具体的查询参数
      buildSearchFormData() {
        return {
          scId: this.scId,
          condition: this.queryParam.condition,
          categoryId: this.queryParam.category.id || '',
          brandId: this.queryParam.brand.id || ''
        }
      },
      getQueryField() {
        let str = 'id,';
        for (let a = 0; a < this.columns.length; a++) {
          str += ',' + this.columns[a].dataIndex;
        }
        return str;
      },
      searchReset(num) {
        let that = this;
        if (num !== 0) {
          that.queryParam = {};
          that.loadGoodsData(1);
        }
        that.selectedRowKeys = [];
        that.selectGoodsIds = [];
      },
      close() {
        this.searchReset(0);
        this.visible = false;
      },
      handleTableChange(pagination, filters, sorter) {
        this.ipagination = pagination;
        this.loadGoodsData();
      },
      handleSubmit() {
        let that = this;
        this.getSelectGoodsRows();
        that.$emit('ok', that.selectGoodsRows, that.selectGoodsIds);
        that.searchReset(0)
        that.close();
      },
      //获取选择信息
      getSelectGoodsRows(rowId) {
        let dataSource = this.dataSource;
        console.log("getSelectGoodsRows datasource=",dataSource);
        let goodsIds = "";
        this.selectGoodsRows = [];
        for (let i = 0, len = dataSource.length; i < len; i++) {
          if (this.selectedRowKeys.includes(dataSource[i].id)) {
            this.selectGoodsRows.push(dataSource[i]);
            goodsIds = goodsIds + "," + dataSource[i].id;
          }
        }
        this.selectGoodsIds = goodsIds.substring(1);
        console.log("getSelectGoodsRows selectGoodsRows=",this.selectGoodsRows);
        console.log("getSelectGoodsRows selectGoodsIds=",this.selectGoodsIds);
      },
      onSelectChange(selectedRowKeys, selectionRows) {
        this.selectedRowKeys = selectedRowKeys;
        this.selectionRows = selectionRows;
      },
      onSearch() {
        if(this.dataSource && this.dataSource.length===1) {
          if(this.queryParam.q === this.dataSource[0].code||
            this.queryParam.q === this.dataSource[0].name) {
            let arr = []
            arr.push(this.dataSource[0].id)
            this.selectedRowKeys = arr
            this.handleSubmit()
          } else {
            this.loadGoodsData(1)
          }
        } else {
          this.loadGoodsData(1)
        }
      },
      modalFormOk() {
        this.loadGoodsData()
      },
      rowAction(record, index) {
        return {
          on: {
            click: () => {
              let arr = []
              arr.push(record.id)
              this.selectedRowKeys = arr
            },
            dblclick: () => {
              let arr = []
              arr.push(record.id)
              this.selectedRowKeys = arr
              this.handleSubmit()
            }
          }
        }
      }
    }
  }
</script>

<style scoped>
  .ant-table-tbody .ant-table-row td {
    padding-top: 10px;
    padding-bottom: 10px;
  }

  #components-layout-demo-custom-trigger .trigger {
    font-size: 18px;
    line-height: 64px;
    padding: 0 24px;
    cursor: pointer;
    transition: color .3s;
  }
</style>

八、效果图：