帮忙重构代码; package com.matech.audit.insight.service.statistics.impl;
import com.alibaba.excel.util.StringUtils;
import com.matech.audit.insight.service.statistics.DLQueryService;
import com.matech.audit.insight.service.statistics.StatisticsBoardQueryService;
import com.matech.audit.insight.statistics.DataLakeQueryParamDTO;
import com.matech.base.exception.MtBusinessException;
import com.matech.base.model.common.PageDataDTO;
import com.matech.base.util.ConfigurationUtil;
import com.matech.base.util.json.JsonHelper;
import com.matech.source.config.DynamicDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
@Service
@Slf4j
public class DLQueryServiceImpl implements DLQueryService {
private final NamedParameterJdbcTemplate financeJdbcTemplate;
private final StatisticsBoardQueryService statisticsBoardQueryService;
private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
//正则表达式：yyyy-MM-dd
private static final String YMD_REG = "^\\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$";
private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
public DLQueryServiceImpl(Optional<DynamicDataSource> dataSource,
@Value("${matech.finance.env.prefix}") String envPrefix, StatisticsBoardQueryService statisticsBoardQueryService) {
if (dataSource.isPresent()) {
javax.sql.DataSource financeDataSource = dataSource.get().getResolvedDataSources().get("finance");
if (Objects.nonNull(financeDataSource)) {
this.financeJdbcTemplate = new NamedParameterJdbcTemplate(financeDataSource);
} else {
this.financeJdbcTemplate = null;
}
} else {
this.financeJdbcTemplate = null;
}
this.statisticsBoardQueryService = statisticsBoardQueryService;
}
@Override
public List<Map<String, Object>> findByCondition(DataLakeQueryParamDTO queryParamDTO) {
log.info("findOnePage queryParamDTO:{}", JsonHelper.toJson(queryParamDTO));
if(StringUtils.isBlank(queryParamDTO.getModelName())){
return List.of();
}
String sqlName = "model-zc-" + queryParamDTO.getModelName();
//招采的六个查询sql
if(StringUtils.equals(queryParamDTO.getModelName(),"1") ){
return getZcData1(transitionQueryParam1(queryParamDTO.getQueryParam()));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"2") ){
return getZcData2(transitionQueryParam2(queryParamDTO.getQueryParam()));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"4") ){
return getZcData4(transitionQueryParam4(queryParamDTO.getQueryParam()));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"5") ){
return getZcData5(transitionQueryParam5(queryParamDTO.getQueryParam()));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"6") ){
return getZcData6(transitionQueryParam6(queryParamDTO.getQueryParam()));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"3") ){
sqlName = "model-zc-3";
//转换数据库查询参数
Map<String, Object> queryParam = transitionQueryParam3(queryParamDTO.getQueryParam());
queryParamDTO.setQueryParam(queryParam);
}
List<Map<String, Object>> list = statisticsBoardQueryService.findDataLakeByCondition(sqlName, queryParamDTO.getQueryParam());
return list;
}
private Map<String, Object> transitionQueryParam1(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
//用户编码
String userCode = MapUtils.getString(queryParam, "userCode");
if(StringUtils.isNotBlank(userCode)) {
result.put("userCode", userCode);
}else {
//throw new MtBusinessException("ERR4000", "用户编码不可为空");
}
//时间的格式yyyy-MM-dd
String endDateStr = MapUtils.getString(queryParam, "方案截止时间");
String spqj = MapUtils.getString(queryParam, "方案审批区间");
if(StringUtils.isBlank(spqj)) {
Integer qj = null;
try {
qj = Integer.parseInt(spqj);
} catch (NumberFormatException e) {
qj = 3;//默认三年
}
if(StringUtils.isNotBlank(endDateStr)) {
endDateStr = endDateStr.trim();
if(!checkDateStr(endDateStr)) {
Date now = new Date();
endDateStr = DATE_FORMAT.format(now);
}
String startDateStr = calculateStartDate(endDateStr, qj);
result.put("方案开始时间", startDateStr);
result.put("方案截止时间", endDateStr);
} else {
Date now = new Date();
endDateStr = DATE_FORMAT.format(now);
String startDateStr = calculateStartDate(endDateStr, qj);
result.put("方案开始时间", startDateStr);
result.put("方案截止时间", endDateStr);
}
}
//工程限额
String gcxe = MapUtils.getString(queryParam, "工程限额");
if(StringUtils.isNotBlank(gcxe)) {
result.put("工程限额", gcxe);
}
//货物限额
String hwxe = MapUtils.getString(queryParam, "货物限额");
if(StringUtils.isNotBlank(hwxe)) {
result.put("货物限额", hwxe);
}
//服务限额
String fwxe = MapUtils.getString(queryParam, "服务限额");
if(StringUtils.isNotBlank(fwxe)) {
result.put("服务限额", fwxe);
}
//是否紧急采购(是、否、全部)
String sfjj = MapUtils.getString(queryParam, "是否紧急采购");
if(StringUtils.isNotBlank(sfjj)) {
if(!StringUtils.equals("全部", sfjj)) {
result.put("是否紧急采购", sfjj);
}
}
//时间差
String sjc = MapUtils.getString(queryParam, "时间差");
if(StringUtils.isNotBlank(sjc)) {
Integer t = null;
try {
t = Integer.parseInt(sjc);
} catch (NumberFormatException e) {
t = 30;
}
result.put("时间差", t);
}
//采购单位名称列表
String unitName = MapUtils.getString(queryParam, "采购单位名称");
if(StringUtils.isNotBlank(unitName)) {
if(!StringUtils.equals("全集团", unitName)) {
List<String> unitNames = Arrays.asList(unitName);
result.put("采购单位名称列表", unitNames);
}
}
return result;
}
private Map<String, Object> transitionQueryParam2(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
return result;
}
/**
* "采购单位名称；
* 采购日期区间（日期，默认3年）；
* 问题类型，匹配方式（等于、不等于、模糊匹配、不匹配、开头为、开头不为、结尾为）"
*
* {
* "采购单位名称列表" : null,
* "方案开始时间" : null,
* "方案截止时间" : null,
* "userCode" : null
* }
* @param queryParam
* @return
*/
private Map<String, Object> transitionQueryParam3(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
return result;
}
private Map<String, Object> transitionQueryParam4(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
return result;
}
private Map<String, Object> transitionQueryParam5(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
return result;
}
private Map<String, Object> transitionQueryParam6(Map<String, Object> queryParam) {
Map<String, Object> result = new HashMap<>();
return result;
}
private boolean checkDateStr(String endDateStr) {
if(StringUtils.isBlank(endDateStr)) {
return false;
}
return endDateStr.matches(YMD_REG);
}
/**
* 根据结束日期和年限计算开始日期
* @param endDateStr 结束日期字符串（格式：yyyy-MM-dd）
* @param years 年限（整数）
* @return 开始日期字符串（格式：yyyy-MM-dd）
*/
public static String calculateStartDate(String endDateStr, int years) {
// 解析结束日期
LocalDate endDate = LocalDate.parse(endDateStr, FORMATTER);
// 处理闰年边界情况（2月29日）
if (endDate.getMonthValue() == 2 && endDate.getDayOfMonth() == 29) {
LocalDate targetDate = endDate.minusYears(years);
// 目标年不是闰年时调整为2月28日
return targetDate.isLeapYear() ?
targetDate.format(FORMATTER) :
targetDate.withDayOfMonth(28).format(FORMATTER);
}
// 普通日期直接减年
return endDate.minusYears(years).format(FORMATTER);
}
@Override
public PageDataDTO<Map<String, Object>> findOnePage(DataLakeQueryParamDTO queryParamDTO) {
log.info("findOnePage queryParamDTO:{}", JsonHelper.toJson(queryParamDTO));
if(StringUtils.isNotBlank(queryParamDTO.getModelName())){
return emptyPage(queryParamDTO.getPage(), queryParamDTO.getSize());
}
String sqlName = "model-zc-" + queryParamDTO.getModelName();
//招采的六个查询sql
if(StringUtils.equals(queryParamDTO.getModelName(),"1") ){
return findOnePage(getZCSql(1, queryParamDTO.getQueryParam()), queryParamDTO.getQueryParam(), queryParamDTO.getPage(), queryParamDTO.getSize(), "",getZCParamName(1));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"2") ){
return findOnePage(getZCSql(2, queryParamDTO.getQueryParam()), queryParamDTO.getQueryParam(), queryParamDTO.getPage(), queryParamDTO.getSize(), "",getZCParamName(2));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"4") ){
return findOnePage(getZCSql(4, queryParamDTO.getQueryParam()), queryParamDTO.getQueryParam(), queryParamDTO.getPage(), queryParamDTO.getSize(), "",getZCParamName(4));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"5") ){
return findOnePage(getZCSql(5, queryParamDTO.getQueryParam()), queryParamDTO.getQueryParam(), queryParamDTO.getPage(), queryParamDTO.getSize(), "",getZCParamName(5));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"6") ){
return findOnePage(getZCSql(6, queryParamDTO.getQueryParam()), queryParamDTO.getQueryParam(), queryParamDTO.getPage(), queryParamDTO.getSize(), "",getZCParamName(6));
}
if(StringUtils.equals(queryParamDTO.getModelName(),"3") ){
sqlName = "model-zc-3";
}
//补充参数到查询的sql中
if(queryParamDTO.getQueryParam() != null) {
queryParamDTO.getQueryParam().put("page", queryParamDTO.getPage());
queryParamDTO.getQueryParam().put("size", queryParamDTO.getSize());
}
return statisticsBoardQueryService.findDataLakeOnePage(sqlName, queryParamDTO.getQueryParam());
}
private String getZCParamName(int n) {
return String.format("model-zc-%d-param", n);
}
/**
* 获取招采大模型的SQL
*
* @param n
* @param queryParam
* @return
*/
private String getZCSql(int n, Map<String, Object> queryParam) {
switch (n) {
case 1:
return getZCSql1(queryParam);
case 2:
return getZCSql2(queryParam);
case 4:
return getZCSql4(queryParam);
case 5:
return getZCSql5(queryParam);
case 6:
return getZCSql6(queryParam);
default:
return "";
}
}
private PageDataDTO<Map<String, Object>> findOnePage(String baseSql, Map<String, Object> param, int page, int size, String sort, String paramName) {
if (param == null) {
param = new HashMap<>();
}
String configSql = baseSql;
String countSql = getCountSql(configSql);
String sql = generatePageQuerySql(configSql, page, size, sort);
//获取系统默认的参数
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( paramName);
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
Long totalRecords = financeJdbcTemplate.queryForObject(countSql, param, new SingleColumnRowMapper<>(Long.class));
List<Map<String, Object>> result = financeJdbcTemplate.queryForList(sql, param);
int totalPage = (int) (totalRecords / size);
if (totalPage == 0 || totalRecords % size != 0) {
totalPage++;
}
return new PageDataDTO<>(page, size, totalRecords.longValue(), totalPage, result);
}
/**
* 生成分页查询语句
*
* @param configSql 配置的SQL
* @param page 页码
* @param size 页数
* @param sort 排序
* @return
*/
private String generatePageQuerySql(String configSql, Integer page, Integer size, String sort) {
StringBuilder sb = new StringBuilder();
sb.append("select * from (");
sb.append(configSql);
sb.append(") as t_result where 1=1 ");
if (org.apache.commons.lang3.StringUtils.isNotBlank(sort)) {
sb.append(" order by ")
.append(sort);
}
if (page != null && size != null) {
sb.append(" limit ").append(page*size).append(",").append(size);
}
return sb.toString();
}
/**
* 生成count的SQL
*
* @param sql sql
* @return
*/
private String getCountSql(String sql) {
return " select count(1) from (" + sql + ") t_count where 1=1 ";
}
/**
* 空的分页
*
* @param page
* @param size
* @return
*/
public PageDataDTO<Map<String, Object>> emptyPage(int page, int size) {
PageDataDTO<Map<String, Object>> pageDataDTO = new PageDataDTO<Map<String, Object>>();
pageDataDTO.setData(List.of());
pageDataDTO.setPageSize(0);
return pageDataDTO;
}
/**
* 获取招采2的查询sql
*
* @param param
* @return
*/
private List<Map<String, Object>> getZcData2(Map<String, Object> param) {
String sql = getZCSql2(param);
//设置默认值
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( "model-zc-2-param");
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
log.info(generatePrintableSql(sql, param));
return financeJdbcTemplate.queryForList(sql, param);
}
/**
* 获取招采1的查询sql
*
* @param param
* @return
*/
private List<Map<String, Object>> getZcData1(Map<String, Object> param) {
String sql = getZCSql1(param);
//设置默认值
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( "model-zc-1-param");
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
log.info(generatePrintableSql(sql, param));
return financeJdbcTemplate.queryForList(sql, param);
}
/**
* 获取招采的查询sql
*
* @param param
* @return
*/
private List<Map<String, Object>> getZcData4(Map<String, Object> param) {
String sql = getZCSql4(param);
//设置默认值
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( "model-zc-4-param");
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
return financeJdbcTemplate.queryForList(sql, param);
}
/**
* 获取招采的查询sql
*
* @param param
* @return
*/
private List<Map<String, Object>> getZcData5(Map<String, Object> param) {
String sql = getZCSql5(param);
//设置默认值
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( "model-zc-5-param");
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
log.info(generatePrintableSql(sql, param));
return financeJdbcTemplate.queryForList(sql, param);
}
private List<Map<String, Object>> getZcData6(Map<String, Object> param) {
String sql = getZCSql6(param);
//设置默认值
Map<String, Object> defaultParam = new HashMap<>();
String defaultParamValue = ConfigurationUtil.getValue( "model-zc-6-param");
if (org.apache.commons.lang3.StringUtils.isNotBlank(defaultParamValue)) {
defaultParam = JsonHelper.string2Map(defaultParamValue);
}
for (Map.Entry<String, Object> entry : defaultParam.entrySet()) {
if (!param.containsKey(entry.getKey())) {
param.put(entry.getKey(), entry.getValue());
}
}
return financeJdbcTemplate.queryForList(sql, param);
}
/**
* 判断是否为空字数组活列表
*
* @param value
* @return
*/
private static boolean checkListValue(Object value) {
if(value == null) {
return false;
}
if(value instanceof Collection ) {
return CollectionUtils.isNotEmpty((Collection<?>) value);
}
if(value.getClass().isArray()){
int length = java.lang.reflect.Array.getLength(value);
//System.out.println(length);
return length !=0;
}
return true;
}
public static String generatePrintableSql(String sql, Map<String, Object> paramMap) {
StringBuilder printableSql = new StringBuilder(sql);
// 遍历参数映射
for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
// 替换命名参数占位符（格式为 :key）
String placeholder = ":" + key;
int index;
while ((index = printableSql.indexOf(placeholder)) != -1) {
// 根据类型处理值的格式
String replacement = formatSqlValue(value);
printableSql.replace(index, index + placeholder.length(), replacement);
}
}
return printableSql.toString();
}
private static String formatSqlValue(Object value) {
if (value == null) {
return "NULL";
}
// 字符串类型需添加单引号并转义内部单引号
if (value instanceof String || value instanceof Character) {
return "'" + value.toString().replace("'", "''") + "'";
}
// 数字类型直接返回
if (value instanceof Number || value instanceof Boolean) {
return value.toString();
}
// 其他复杂类型（如日期）转为JSON格式
return "'" + value.toString() + "'";
}
private String getZCSql1(Map<String, Object> param) {
StringBuffer sb = new StringBuffer();
return sb.toString();
}
private String getZCSql2(Map<String, Object> param) {
StringBuffer sb = new StringBuffer();
return sb.toString();
}
private String getZCSql4(Map<String, Object> param) {
StringBuffer sb = new StringBuffer();
return sb.toString();
}
private String getZCSql5(Map<String, Object> param) {
StringBuffer sb = new StringBuffer();
return sb.toString();
}
private String getZCSql6(Map<String, Object> param) {
StringBuffer sb = new StringBuffer();
return sb.toString();
}
}
}
最新发布
本文探讨了在使用JSON.fromObject方法进行对象转换时遇到的问题，特别是当字符串变量为null时如何正确转换为JSON对象。通过具体示例展示了如何避免转换过程中出现的错误，并确保能够成功解析。
扫一扫
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
