当前位置：首页 > article >正文

根据表名动态获取数据

article 2024/9/23 1:34:19

查询接口

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

        return dynamicDataRetrievalService.highSearch(highSearchVO);
    }

Service

    @Override
    @Transactional
    public 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 会导致各种问题，请直接用浏览器或者用postman
        response.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;
    }

完整代码

@Override
    public void download(HttpServletResponse response, HighSearchVO highSearchVO) throws UnsupportedEncodingException {
        String tableName = highSearchVO.getTableName();


        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.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();
        }
    }

查看全文

http://www.kler.cn/news/134947.html