根据表名动态获取数据

查询接口

    @ApiOperation("通用高级搜索")@PostMapping("/highSearch")public ResponseResult highSearch(@RequestBody HighSearchVO highSearchVO) {return dynamicDataRetrievalService.highSearch(highSearchVO);}

Service

    @Override@Transactionalpublic ResponseResult highSearch(HighSearchVO highSearchVO) {// 检索highSearchVO.setPageNo((highSearchVO.getPageNo() - 1) * highSearchVO.getPageSize());for (HighSearch highSearch : highSearchVO.getHighSearches()) {if ("datetime".equals(highSearch.getColumnType())) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String[] times = highSearch.getSearchValue().split("\\|");try {highSearch.setStartDate(sdf.parse(times[0]));highSearch.setEndDate(sdf.parse(times[1]));} catch (ParseException e) {e.printStackTrace();return ResponseResult.fail("时间格式不正确");}}}List<Map<String, Object>> maps = dynamicDataRetrievalMapper.highSearch(highSearchVO);

由于是动态的，不能确定返回的List<>中填写哪个实体类型，所以可以用List<Map<String, Object>>数据结构来接，key是数据库字段名，value是对应的值：

list: [{"task_name": "落盘任务test1","center_freq": "9600MHz","file_location": "/files/2022_05_22_10_38_29_281_I_9600MHz_43803","pdw_format_id": 1,"file_name": "2022_05_22_10_38_29_281_I_9600MHz_43803","fileSuffix": [".dat",".pls"],"target_id": 3,"file_size": 9.2980568E7,"gather_time": "2022-05-22T10:38:29.281","in_time": "2023-11-15T16:04:37","pulse_num": 43803,"gather_id": 2,"id": 46,"system_model_id": 1},{"task_name": "落盘任务test1","center_freq": "9600MHz","file_location": "/files/2022_05_22_10_38_29_281_I_9600MHz_43803","pdw_format_id": 1,"file_name": "2022_05_22_10_38_29_281_I_9600MHz_43803","fileSuffix": [".dat",".pls"],"target_id": 3,"file_size": 9.2980568E7,"gather_time": "2022-05-22T10:38:29.281","in_time": "2023-11-15T16:04:37","pulse_num": 43803,"gather_id": 2,"id": 46,"system_model_id": 1}]

HighSearchVO

@ApiModel(description = "高级搜索前端传参")
@Data
public class HighSearchVO {@ApiModelProperty("表名")private String tableName;@ApiModelProperty("搜索字段集合")private List<HighSearch> highSearches;@ApiModelProperty("排序字段")private String orderColumn;@ApiModelProperty("排序控制(asc,desc)")private String orderControl;@ApiModelProperty("页数")private Integer pageSize;@ApiModelProperty("页码")private Integer pageNo;}

HighSearch

@ApiModel(description = "HIGH_SEARCH实体对象")
@Data
public class HighSearch {@ApiModelProperty(value = "字段名")private String columnName;@ApiModelProperty(value = "字段搜索值")private String searchValue;@ApiModelProperty(value = "字段类型")private String columnType;@ApiModelProperty(value = "开始时间（如果根据时间查询）")private Date startDate;@ApiModelProperty(value = "结束时间（如果根据时间查询）")private Date endDate;
}

动态sql：

<select id="highSearch" resultType="java.util.Map" parameterType="com.lin.entity.vo.HighSearchVO">select * from ${tableName}where 1 = 1<if test="highSearches != null"><foreach collection="highSearches" item="item" separator="AND" open="AND"><choose><when test="item.columnType == 'bigint'||item.columnType == 'int'||item.columnType == 'double'">${item.columnName} = ${item.searchValue}</when><when test="item.columnType == 'varchar'||item.columnType == 'VARCHAR'">${item.columnName} like concat(concat('%', #{item.searchValue}), '%')</when><when test="item.columnType == 'datetime'">${item.columnName} between #{item.startDate} and #{item.endDate}</when></choose></foreach></if><if test="orderColumn != null and orderColumn != ''">order by ${orderColumn} ${orderControl}</if>limit #{pageNo},#{pageSize}</select>

请求参数：

{"tableName": "file_meta_data","orderColumn": "id","orderControl": "desc","pageNo": 1,"pageSize": 10,"highSearches": [{"columnName": "id","searchValue": 21,"columnType": "bigint"},{"columnName": "name","searchValue": "小明","columnType": "varchar"}]
}

动态下载导出数据excel

使用到阿里的easy-excel

    @GetMapping("download")public void download(HttpServletResponse response) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());}private List<DownloadData> data() {List<DownloadData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DownloadData data = new DownloadData();data.setString("字符串" + 0);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}

上面那个是定死的下载哪个实体对应的数据，而我们现在要求动态，动态获取数据list已经搞定了，但是数据List<Map<String, Object>>要转为阿里api可以识别的（问题一），以及根据表名获取.class运行时类（问题二）。

问题二解决：

/*** 表对象枚举*/
public enum TableObjectEnum {file_meta_data(FileMetaData.class),radar_sort_pwd(RadarSortPwd.class),;private Class<?> aClass;TableObjectEnum(Class<?> aClass) {this.aClass = aClass;}public Class<?> getaClass() {return aClass;}public void setaClass(Class<?> aClass) {this.aClass = aClass;}// 根据表名获取对应的 Class（.class运行时类）public static Class getClassForTableName(String tableName) throws ClassNotFoundException {for (TableObjectEnum tableObjectEnum : TableObjectEnum.values()) {if (tableObjectEnum.name().equalsIgnoreCase(tableName)) {return tableObjectEnum.getaClass();}}throw new ClassNotFoundException("表名没找到对应的类" + tableName);}
}

问题一解决：

/*** 将数据转化为导出可以支持的数据** @param dataList*/private <T> List<T> convertData(List<Map<String, Object>> dataList, Class<T> objectType) {List<T> objectList = new ArrayList<>();ObjectMapper objectMapper = new ObjectMapper();objectMapper.registerModule(new JavaTimeModule()); // 注册 Java 8 日期/时间模块try {// 遍历 dataList 并将每个 Map 转换为对象，添加到列表中for (Map<String, Object> data : dataList) {T object = objectMapper.convertValue(data, objectType);objectList.add(object);}} catch (Exception e) {e.printStackTrace();}return objectList;}

完整代码

@Overridepublic void download(HttpServletResponse response, HighSearchVO highSearchVO) throws UnsupportedEncodingException {String tableName = highSearchVO.getTableName();// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");try {Class classForTableName = TableObjectEnum.getClassForTableName(tableName);// 查询数据List<Map<String, Object>> dataList = getData(highSearchVO);List list = convertData(dataList, classForTableName);// 忽略字段Set<String> excludeColumnFiledNames = new HashSet<String>();
//            excludeColumnFiledNames.add("pdwId");EasyExcel.write(response.getOutputStream(), classForTableName).excludeColumnFieldNames(excludeColumnFiledNames)
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽.sheet("模板").doWrite(list);} catch (ClassNotFoundException | IOException e) {e.printStackTrace();}}