动态表名-实现数据库表CRUD

谁践踏了优雅 2022-10-29 07:50 13阅读 0赞

### 1. 工具方法 ###

动态实现的关键在于拼接sql时去掉传入字符串两端的单引号

import org.apache.commons.lang.StringEscapeUtils;
    
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    public class PgNameUtil { 
    
        public static String handleFieldName(String name) { 
            System.out.println("****************"+name);
            if(judgeContainsStr(name)){ 
                return "\"" + StringEscapeUtils.escapeSql(name) + "\"";
            }else{ 
                return StringEscapeUtils.escapeSql(name);
            }
        }
    
        public static String handleName(String name) { 
            if(judgeContainsStr(name)){ 
                name = "\"" + StringEscapeUtils.escapeSql(name) + "\"";
                return name;
            }else{ 
                return StringEscapeUtils.escapeSql(name);
            }
        }
    
        public static boolean judgeContainsStr(String str) { 
            String regex=".*[A-Z]+.*";
            Matcher m= Pattern.compile(regex).matcher(str);
            return m.matches();
        }
    }

### 2 查询 ###

通过param 对象传递需要的表名称，和查询字段

#### 2.1 sql ####

param 为java 对象 TableSelectVo，见下2.2

select
            <foreach collection="param.columnList" item="item" index="index" separator=",">
                ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(item)}
            </foreach>
            from ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(param.tableName)}
            where 1 = 1
            <if test="param.keyword != '' and param.keyword != null">
                and (
                <foreach collection="param.columnList" item="item" index="index" separator="or">
                    ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(item)} like CONCAT('%',${@com.leinovo.npi.portal.util.PgNameUtil@handleName(param.keyword)},'%')
                </foreach>
                )
            </if>

#### 2.2 TableSelectVo 对象 ####

@Data
    @NoArgsConstructor
    public class TableSelectVo extends QueryParam { 
        /** * 动态表主键 */
        private String id;
        /** /** * 字段 集合 */
        private List<String> columnList;
        /** * 表名称 */
        private String tableName;
        /** * 查询关键字 */
        private String keyword;;
    
        /** * 行Id */
        private List<String> rowIds;
    
        public TableSelectVo(String tableName, List<String> rowIds) { 
            this.tableName = tableName;
            this.rowIds = rowIds;
        }
    }

#### 2.3 QueryParam 对象 ####

import com.leinovo.platform.constant.CommonConstant;
    import io.swagger.annotations.ApiModel;
    import io.swagger.annotations.ApiModelProperty;
    import lombok.Data;
    
    import java.io.Serializable;
    
    /** * 查询参数 * * @author geekidea * @since 2018-11-08 */
    @Data
    @ApiModel("查询参数对象")
    public abstract class QueryParam implements Serializable { 
        private static final long serialVersionUID = -3263921252635611410L;
    
        @ApiModelProperty(value = "页码,默认为1", example = "1")
        private Integer current = CommonConstant.DEFAULT_PAGE_INDEX;
        @ApiModelProperty(value = "页大小,默认为10", example = "10")
        private Integer size = CommonConstant.DEFAULT_PAGE_SIZE;
        @ApiModelProperty(value = "搜索字符串", example = "")
        private String keyword;
    
        public void setCurrent(Integer current) { 
            if (current == null || current <= 0) { 
                this.current = CommonConstant.DEFAULT_PAGE_INDEX;
            } else { 
                this.current = current;
            }
        }
    
        public void setSize(Integer size) { 
            if (size == null || size <= 0) { 
                this.size = CommonConstant.DEFAULT_PAGE_SIZE;
            } else { 
                this.size = size;
            }
        }
    
    }

### 3. 创建表 ###

#### 3.1 拼接建表sql ####

<update id="createTable" statementType="STATEMENT" parameterType="com.leinovo.npi.portal.masterdata.vo.TableColumnVo">
            CREATE TABLE ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(schemaName)}.${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)} (
            <foreach collection="columns" item="column" separator=",">
                <choose>
                    <when test="column != null and column !=''">${@com.leinovo.npi.portal.util.PgNameUtil@handleName(column)}</when>
                    <otherwise></otherwise>
                </choose>
                <![CDATA[ varchar (255) ]]>
            </foreach>
            <![CDATA[ , PRIMARY KEY ("id") ]]>
            )
        </update>

#### 3.2 创建、使用序列 ####

<insert id="createSequences" statementType="STATEMENT" parameterType="com.leinovo.npi.portal.masterdata.vo.TableColumnVo">
          CREATE SEQUENCE ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(schemaName)}.${@com.leinovo.npi.portal.util.PgNameUtil@handleName(sequencesName)}
                INCREMENT 1
                START 1
                MINVALUE 1
                MAXVALUE 99999999
                CACHE 1
        </insert>
        <update id="alterSequences" statementType="STATEMENT" parameterType="com.leinovo.npi.portal.masterdata.vo.TableColumnVo">
                alter table ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)} alter column id set default
                <![CDATA[ nextval('${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)}_id_seq') ]]>
        </update>

#### 3.3 查询表字段 ####

<select id="getColumnByTableName" resultType="map">
            select column_name as name from information_schema.columns
            where table_schema='public' and table_name= '${tableName}'
        </select>

#### 3.4 TableColumnVo ####

@Data
    public class TableColumnVo { 
    
    
        /** * 模式 public */
        private static String schemaName = "public";
        /** * 表名称 */
        private String tableName;
        /** * */
        private String sequencesName;
    
        /** * 表主键 */
        private Long tableId;
        /** * 动态字段 */
        private List<String> columns;
        /** * 动态数据 */
        private List<Map<String,Object>> dataList;
    
    }

### 4 新增 ###

<insert id="insertBatch" parameterType="com.leinovo.npi.portal.masterdata.vo.TableColumnVo">
            INSERT INTO ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)} (
            <foreach collection="dataList[0]" index="key" item="ent" separator=",">
                ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(key)}
            </foreach>
            ) VALUES
            <foreach collection="dataList" item="item" index="index" separator=",">
                ( <foreach collection="item" index="key" item="ent" separator=","> #{ent}</foreach>)
            </foreach>
        </insert>

### 5 修改 ###

<update id="updateGridData" parameterType="com.leinovo.npi.portal.masterdata.vo.TableUpdateVo">
            update ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)} set
            <foreach collection="dataMap" index="key" item="ent" separator=",">
                <if test="ent != null and ent != ''">
                    ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(key)} = #{ent}
                </if>
            </foreach>
            where id = #{id}
        </update>

### 6 删除 ###

#### 6.1 删除指定数据 ####

<delete id="deleteBatchGridDataByIds" parameterType="com.leinovo.npi.portal.masterdata.vo.TableDeleteVo">
            delete from ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)} where id in(
            <foreach collection="ids" item="item" index="index" separator=",">
                #{item}
            </foreach>
            )
        </delete>

#### 6.1 删除所有数据 ####

<delete id="truncateTable">
            truncate table ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)}
        </delete>

#### 6.1 删除表 ####

<delete id="dropTable" parameterType="com.leinovo.npi.portal.masterdata.vo.TableDeleteVo">
            drop table if exists ${@com.leinovo.npi.portal.util.PgNameUtil@handleName(tableName)}
        </delete>

### 7 其他 ###

#### 7.1 model ####

@Data
    public class PgTables { 
    
        private String schemaname;
        private String tablename;
    }

#### 7.2 controller ####

增、删、查、改，外加数据的导入和导出；

/** * 数据新增 todo 根据表名称，在该表中新增一条数据 * * @param addVo * @return */
        @PostMapping("/addGrid")
        @ApiOperation(value = "新增grid节点", notes = "新增节点", response = ApiResult.class)
        @LogApi("addGrid")
        public ApiResult addGrid(@RequestBody TableAddVo addVo) { 
            String dictName = addVo.getTableName();
            boolean flag = pgTableService.tableExist(dictName);
            if (!flag) { 
                return ApiResult.fail("There is no table,please use Import function to create table and import data.");
            }
    
            String id = String.valueOf(addVo.getData().get("id"));
            TableSelectVo selectVo = new TableSelectVo(dictName, Arrays.asList(id));
            List<Map<String, Object>> data = pgTableService.selectGridSomeData(selectVo);
            if (CollectionUtils.isNotEmpty(data)) { 
                return ApiResult.fail("The id has exists.");
            }
    
            try { 
                pgTableService.insertGirdData(addVo);
            } catch (Exception e) { 
                return ApiResult.fail(e.getMessage());
            }
            return ApiResult.ok();
        }
    
        /** * Gird动态表数据分页查询 // todo 根据表名称，分页查询表中的数据 * * @param queryParam * @return */
        @PostMapping("/selectGirdListByPage")
        @ApiOperation(value = "分页查询grid数据字典", notes = "根据关键字分页查询数据字典", response = ApiResult.class)
        @LogApi("selectGridListByPage")
        public ApiResult selectGirdListByPage(@RequestBody TableSelectVo queryParam) { 
    
            String dictName = queryParam.getTableName();
            boolean flag = pgTableService.tableExist(dictName);
            if (!flag) { 
                return ApiResult.fail("There is no table,please use Import function to create table and import data.");
            }
            List<String> columnList = pgTableService.getColumnByTableName(dictName);
            queryParam.setColumnList(columnList);
    
            Paging<Map<String, Object>> paging = pgTableService.selectGirdListByPage(queryParam);
    
    
            return ApiResult.ok(paging);
        }
    
        /** * 动态修改数据表内容 * * @param tableUpdateVo * @return */
        @PostMapping("/updateGridData")
        @ApiOperation(value = "更新grid数据", notes = "更新grid数据", response = ApiResult.class)
        @LogApi("updateGridData")
        public ApiResult updateGridData(@RequestBody TableUpdateVo tableUpdateVo) { 
            String id = tableUpdateVo.getId();
            String tableName = tableUpdateVo.getTableName();
            Map<String, Object> map = tableUpdateVo.getDataMap();
            if (StringUtils.isAnyEmpty(id, tableName)) { 
                return ApiResult.fail("There is a non-empty field");
            }
            if (map.isEmpty()) { 
                return ApiResult.fail("The content of the data to be updated cannot be empty");
            }
            if (!qesSysDictService.checkExistTableFromSysDict(tableName)) { 
                return ApiResult.fail("There is no table,please use Import function to create table and import data.");
            }
            try { 
                pgTableService.updateGridData(tableUpdateVo);
                return ApiResult.ok();
            } catch (Exception e) { 
                return ApiResult.fail(e.getMessage());
            }
        }
    
        /** * 根据ID集合和数据表名称批量删除动态数据 * * @param tableDeleteVo * @return */
        @RequestMapping(value = "/deleteBatchGridData", method = RequestMethod.POST)
        @ApiOperation(value = "批量删除grid数据", notes = "更新grid数据", response = ApiResult.class)
        @LogApi("deleteBatchGridData")
        public ApiResult deleteBatchGridData(@RequestBody TableDeleteVo tableDeleteVo) { 
            List<String> ids = tableDeleteVo.getIds();
            String tableName = tableDeleteVo.getTableName();
    
            if (StringUtils.isAnyEmpty(tableName)) { 
                return ApiResult.fail("There is a non-empty field");
            }
            if (ids.isEmpty()) { 
                return ApiResult.fail("ID cannot be empty");
            }
    
            if (!qesSysDictService.checkExistTableFromSysDict(tableName)) { 
                return ApiResult.fail("There is no table,please use Import function to create table and import data.");
            }
            try { 
                pgTableService.deleteBatchGridDataByIds(tableDeleteVo);
                return ApiResult.ok();
            } catch (Exception e) { 
                return ApiResult.fail(e.getMessage());
            }
        }
    
    
        @PostMapping("/gridDataExport")
        @ApiOperation(value = "导出", notes = "导出表中数据", response = ApiResult.class)
        @LogApi("gridDataExport")
        public void gridDataExport(@RequestBody TableSelectVo queryParam, HttpServletResponse response) { 
            String dictName = queryParam.getTableName();
            boolean flag = pgTableService.tableExist(dictName);
            if (flag) { 
                List<String> columnList = pgTableService.getColumnByTableName(dictName);
                queryParam.setColumnList(columnList);
                List<List<String>> data = pgTableService.getExportData(queryParam);
    
                try { 
                    ExcelUtil.exportExcel(response, data, "sheet1", dictName + ".xlsx", 15);
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
    
        }
    
        @PostMapping("/dataImport")
        @ApiOperation(value = "数据导入", notes = "表格数据导入", response = ApiResult.class)
        @CrossOrigin
        @LogApi("dataImport")
        public ApiResult dataImport(@RequestParam String dictName, @RequestParam("file") MultipartFile file) { 
    
            Map<String, List<String[]>> excelMap = EasyPoiUtils.readExcel(file);
            Iterator entries = excelMap.entrySet().iterator();
            try { 
                //表字段集合
                List<String> columnList = new LinkedList<>();
                List<String[]> dataValueList = new LinkedList<>();
                while (entries.hasNext()) { 
                    Map.Entry entry = (Map.Entry) entries.next();
                    List<String[]> value = (List<String[]>) entry.getValue();
    
                    if (value != null) { 
                        String[] columnsTemp = value.get(0);
                        for (String str : columnsTemp) { 
                            columnList.add(str);
                        }
                        value.remove(0);
                        dataValueList = value;
                    }
                }
                if (!columnList.contains(CommonConstant.MASTER_DATA_GRID_ID)) { 
                    return ApiResult.fail("The imported Excel file must contain the id column!");
                }
                TableColumnVo tableColumnVo = new TableColumnVo();
                tableColumnVo.setTableName(dictName);
                tableColumnVo.setColumns(columnList);
                pgTableService.dropTable(dictName);
                //创建表,自动将id作为主键，后续id的唯一性，由客户保证
                pgTableService.createTable(tableColumnVo);
    
                List<Map<String, Object>> resultList = new LinkedList<>();
                if (dataValueList != null) { 
                    for (String[] fileDatum : dataValueList) { 
                        Map<String, Object> map = new HashMap<>(32);
                        for (int i = 0; i < columnList.size(); i++) { 
                            String colName = columnList.get(i);
                            map.put(colName, fileDatum[i]);
                        }
                        resultList.add(map);
                    }
                }
                tableColumnVo.setDataList(resultList);
                boolean flag = pgTableService.insertBatch(tableColumnVo);
                return ApiResult.ok(flag);
            } catch (Exception e) { 
                return ApiResult.fail(e.getMessage());
            }
    // return null;
        }

#### 7.3 service ####

/** * 系统表接口 */
    public interface PgTableService extends BaseService<PgTables> { 
        /** * 获取动态表的所有字段 * @param tableName * @return */
        List<String> getColumnByTableName(String tableName);
    
        /** * 查询导出数据项 * @param tableSelectVo * @return */
        List<List<String>> getExportData(TableSelectVo tableSelectVo);
    
        /** * 动态数据分页查询 * @param tableSelectVo * @return */
        Paging<Map<String,Object>> selectGirdListByPage(TableSelectVo tableSelectVo);
    
        List<Map<String, Object>> selectGridSomeData(TableSelectVo tableSelectVo);
    
        /** * 判断表是否存在 * @param dictName * @return */
        boolean tableExist(String dictName);
    
        boolean dropTable(String dictName);
        /** * 动态创建数据库表 * @param tableColumnVo * @return */
        boolean createTable(TableColumnVo tableColumnVo) throws Exception;
    
        /** * 批量插入 * @param tableColumnVo * @return */
        boolean insertBatch(TableColumnVo tableColumnVo) throws Exception;
        /** * 单条插入Grid动态数据 * @param tableAddVo * @return */
        int insertGirdData(TableAddVo tableAddVo) throws Exception;
        /** * 清空表 * @param tableColumnVo * @return */
        void truncateTable(TableColumnVo tableColumnVo) throws Exception;
    
        /** * 更新动态数据表数据内容 * @param tableUpdateVo * @return */
        int updateGridData(TableUpdateVo tableUpdateVo) throws Exception;
        /** * 根据ID和数据表名称删除动态数据 * @param tableDeleteVo * @return */
        int deleteGridDataById(TableDeleteVo tableDeleteVo) throws Exception;
    
        /** * 根据ID和数据表名称批量删除动态数据 * @param tableDeleteVo * @return */
        int deleteBatchGridDataByIds(TableDeleteVo tableDeleteVo) throws Exception;
    
        /** * 根据用户类型查询对应的下拉数据 * 1：odm；2：ems；3：part supplier * @param userType * @return */
        List<String> getDictGridByUserType(int userType) throws Exception;
    
        /** * 从DimProduct表获取designType列表 * @return */
        List<String> getDesignTypeFromDimProduct();
    
        /** * 动态分组查询GridData数据。 * @return */
        List<Map<String, String>> queryDimWithGroupByForGridData(String tableName, String columns);
    
    }

#### 7.4 serviceImpl ####

@Service
    public class PgTablesServiceImpl extends BaseServiceImpl<PgTablesMapper, PgTables> implements PgTableService { 
    
        @Resource
        private PgTablesMapper pgTablesMapper;
    
        /** * 获取动态表的所有字段 * * @param tableName * @return */
        @Override
        public List<String> getColumnByTableName(String tableName) { 
            List<Map<String, String>> columnListMap = pgTablesMapper.getColumnByTableName(tableName);
    
            //动态表的数据字段
            List<String> columnList = new LinkedList<>();
    
            for (Map<String, String> stringMap : columnListMap) { 
                String name = stringMap.get("name");
                columnList.add(name);
            }
            return columnList;
        }
    
        @Override
        public List<List<String>> getExportData(TableSelectVo tableSelectVo) { 
    
            List<Map<String, Object>> dataList = pgTablesMapper.selectGridExportData(tableSelectVo);
            List<String> columnList = tableSelectVo.getColumnList();
    
            List<List<String>> result = new LinkedList<>();
            result.add(0, columnList);
    
            for (Map<String, Object> stringObjectMap : dataList) { 
                List<String> excelData = new LinkedList<>();
                for (String column : columnList) { 
                    String data = String.valueOf(stringObjectMap.get(column));
                    excelData.add(data);
                }
                result.add(excelData);
            }
    
            return result;
        }
    
        @Override
        public List<Map<String, Object>> selectGridSomeData(TableSelectVo tableSelectVo) { 
            return pgTablesMapper.selectGridSomeData(tableSelectVo);
        }
    
        @Override
        public Paging<Map<String, Object>> selectGirdListByPage(TableSelectVo tableSelectVo) { 
            Page page = setPageParam(tableSelectVo);
            IPage<Map<String, Object>> iPage = pgTablesMapper.selectGirdListByPage(page, tableSelectVo);
            List<String> columnList = tableSelectVo.getColumnList();
    
            List<Map<String, Object>> mapList = iPage.getRecords();
            List<Map<String, Object>> resultList = new LinkedList<>();
    
            for (Map<String, Object> objectMap : mapList) { 
                Map<String, Object> map = new LinkedHashMap<>(128);
                for (String column : columnList) { 
                    map.put(column, objectMap.get(column));
                }
                resultList.add(map);
            }
            iPage.setRecords(resultList);
    
            return new Paging(iPage);
        }
    
        /** * 根据表名称判断是否存在该表 * * @param dictName * @return */
        @Override
        public boolean tableExist(String dictName) { 
            QueryWrapper<PgTables> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("schemaname", "public");
            queryWrapper.eq("tablename", dictName);
    
            PgTables pgTables = pgTablesMapper.selectOne(queryWrapper);
            boolean flag = false;
            if (pgTables != null) { 
                //代表存在
                flag = true;
            }
            return flag;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public boolean dropTable(String dictName) { 
            boolean flag = false;
            if (this.tableExist(dictName)) { 
                //查询所有数据，用于下发删除主数据
                List<Map<String, Object>> data = pgTablesMapper.selectAllByTableName(dictName);
    
                TableDeleteVo tableDeleteVo = new TableDeleteVo();
                tableDeleteVo.setTableName(dictName);
                pgTablesMapper.dropTable(tableDeleteVo);
    
                flag = true;
            }
    
            return flag;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public boolean createTable(TableColumnVo tableColumnVo) throws Exception { 
            boolean flag = false;
            if (!this.tableExist(tableColumnVo.getTableName())) { 
                pgTablesMapper.createTable(tableColumnVo);
                flag = true;
            }
            return flag;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public boolean insertBatch(TableColumnVo tableColumnVo) throws Exception { 
            boolean flag = false;
            if (this.tableExist(tableColumnVo.getTableName())) { 
                pgTablesMapper.truncateTable(tableColumnVo);
                int columns = tableColumnVo.getColumns().size();
                List<Map<String, Object>> dataList = tableColumnVo.getDataList();
                int rows = dataList.size();
                int cells = columns * rows;
                int maxLimit = 32767;
                // 针对Postgre 32767个参数限制问题进行分批提交操作
                if (cells > maxLimit) { 
                    int rowsPerTimes = maxLimit / columns;
                    int times = rows / rowsPerTimes + 1;
                    for (int i = 0; i < times; i++) { 
                        int startIndex = rowsPerTimes * i;
                        int lastIndex = rowsPerTimes * (i + 1) > rows ? rows - 1 : rowsPerTimes * (i + 1) - 1;
                        List<Map<String, Object>> list = dataList.subList(startIndex, lastIndex);
                        tableColumnVo.setDataList(list);
                        pgTablesMapper.insertBatch(tableColumnVo);
                    }
                } else { 
                    pgTablesMapper.insertBatch(tableColumnVo);
                }
                flag = true;
            }
            return flag;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public int insertGirdData(TableAddVo tableAddVo) throws Exception { 
    
            // 获取指定表的属性，表名称，拥有的字段；
            String dictName = tableAddVo.getTableName();
            List<String> columnList = this.getColumnByTableName(dictName);
            LinkedHashMap<String, Object> data = tableAddVo.getData();
    
            LinkedHashMap<String, Object> dataTemp = new LinkedHashMap<>();
            List<String> columnListTemp = new LinkedList<>();
    
            //产品和客户确认：Grid类型的表，id为主键，且由客户来确保唯一性
            for (String column : columnList) { 
                Object columnData = data.get(column);
                if (columnData != null) { 
                    columnListTemp.add(column);
                    dataTemp.put(column, columnData);
                }
            }
            tableAddVo.setColumnList(columnListTemp);
            tableAddVo.setData(dataTemp);
    
            int num =  pgTablesMapper.insertGirdData(tableAddVo);
    
            return num;
        }
    
        @Override
        public void truncateTable(TableColumnVo tableColumnVo) throws Exception { 
            if (this.tableExist(tableColumnVo.getTableName())) { 
                pgTablesMapper.truncateTable(tableColumnVo);
            }
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public int updateGridData(TableUpdateVo tableUpdateVo) throws Exception { 
            int num  = pgTablesMapper.updateGridData(tableUpdateVo);
            return num;
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public int deleteGridDataById(TableDeleteVo tableDeleteVo) throws Exception { 
            return pgTablesMapper.deleteGridDataById(tableDeleteVo);
        }
    
        @Override
        @Transactional(rollbackFor = Exception.class)
        public int deleteBatchGridDataByIds(TableDeleteVo tableDeleteVo) throws Exception { 
            String tableName = tableDeleteVo.getTableName();
            List<String> ids = tableDeleteVo.getIds();
    
            TableSelectVo selectVo = new TableSelectVo(tableName,ids);
            List<Map<String, Object>> data = pgTablesMapper.selectGridSomeData(selectVo);
    
            int num = pgTablesMapper.deleteBatchGridDataByIds(tableDeleteVo);
            return num;
        }
    
        @Override
        public List<String> getDictGridByUserType(int userType) throws Exception { 
            List<String> list = new ArrayList<>();
            switch (userType) { 
                //ODM
                case 5:
                    list = pgTablesMapper.getOdmFromDimProduct();
                    break;
                //EMS
                case 6:
                    list = pgTablesMapper.getEmsFromEms();
                    break;
                //Part Supplier
                case 4:
                    list = pgTablesMapper.getPartSupplierFromDimMaterialSupplier();
                    break;
            }
            return list;
        }
    
        @Override
        public List<String> getDesignTypeFromDimProduct() { 
            return pgTablesMapper.getDesignTypeFromDimProduct();
        }
    
        @Override
        public List<Map<String, String>> queryDimWithGroupByForGridData(String tableName, String columns) { 
            Map<String, Object> queryParams = new HashMap<String, Object>(){ 
                { 
                    this.put("tableName", tableName);
                    this.put("groupColumns", columns.split("\\,"));
                }
            };
    
            return pgTablesMapper.queryDimWithGroupByForGridData(queryParams);
        }
    }

#### 7.5 mapper ####

@Repository
    public interface PgTablesMapper extends BaseMapper<PgTables> { 
    
        /** * 获取动态表的所有字段 * * @param tableName * @return */
        List<Map<String, String>> getColumnByTableName(@Param("tableName") String tableName);
    
        /** * 根据条件查询出动态到处数据 * * @param param * @return */
        List<Map<String, Object>> selectGridExportData(@Param("param") TableSelectVo param);
    
    
        /** * 动态数据表数据分页查询 * * @param page * @param param * @return */
        IPage<Map<String, Object>> selectGirdListByPage(@Param("page") Page page, @Param("param") TableSelectVo param);
    
        /** * 动态创建数据库表 * * @param tableColumnVo * @return */
        int createTable(TableColumnVo tableColumnVo);
    
        /** * 根据表名删除字典表对应数据表 * @param tableDeleteVo * @return */
        int dropTable(TableDeleteVo tableDeleteVo);
    
        /** * @param tableColumnVo * @return */
        int createSequences(TableColumnVo tableColumnVo);
    
        /** * @param tableColumnVo * @return */
        int alterSequences(TableColumnVo tableColumnVo);
    
        /** * 批量插入 * * @param tableColumnVo * @return */
        int insertBatch(TableColumnVo tableColumnVo);
    
        /** * 单条插入Grid动态数据 * * @param tableAddVo * @return */
        int insertGirdData(TableAddVo tableAddVo);
    
        /** * 清空表数据 * * @param tableColumnVo * @return */
        int truncateTable(TableColumnVo tableColumnVo);
    
        /** * 更新动态数据表数据内容 * * @param tableUpdateVo * @return */
        int updateGridData(TableUpdateVo tableUpdateVo);
    
        /** * 根据ID和数据表名称删除动态数据 * * @param tableUpdateVo * @return */
        int deleteGridDataById(TableDeleteVo tableUpdateVo);
    
        /** * 根据ID和数据表名称批量删除动态数据 * * @param tableDeleteVo * @return */
        int deleteBatchGridDataByIds(TableDeleteVo tableDeleteVo);
    
        /** * 获取dimProduct表的odm字段列表 * @return */
        List<String> getOdmFromDimProduct();
    
        /** * 获取ems表的ems字段列表 * @return */
        List<String> getEmsFromEms();
    
        /** * 获取高保密项目 * @return */
        List<String> getProjectWithConfidential();
    
        /** * 获取dimMaterialSupplier表的material_supplier_standard字段列表 * @return */
        List<String> getPartSupplierFromDimMaterialSupplier();
    
        /** * 获取dimProduct表的designType字段列表 * @return */
        List<String> getDesignTypeFromDimProduct();
    
        /** * 动态分组查询GridData数据。 * @return */
        List<Map<String, String>> queryDimWithGroupByForGridData(Map<String, Object> queryParams);
    
        /** * 查询Grid类型表的数据 * @param vo * @return */
        List<Map<String, Object>> selectGridSomeData(@Param("param") TableSelectVo vo);
    
        List<Map<String, Object>> selectAllByTableName(@Param("tableName") String tableName);
    
    }