spring个人心得888

package com.oppo.upgrade.publicSentiment.service.impl; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.fastjson.JSON; import com.oppo.basic.heracles.client.core.spring.annotation.HeraclesDynamicConfig; import com.oppo.upgrade.common.constant.ConstantConfig; import com.oppo.upgrade.common.constant.EnduranceBoardConfig; import com.oppo.upgrade.common.constant.MidasConstant; import com.oppo.upgrade.common.enums.UpgradeErrorEnum; import com.oppo.upgrade.common.exception.BusinessException; import com.oppo.upgrade.common.util.*; import com.oppo.upgrade.publicSentiment.entity.dto.EnduranceBoardDTO; import com.oppo.upgrade.publicSentiment.entity.dto.NotworkIndexDTO; import com.oppo.upgrade.publicSentiment.entity.vo.*; import com.oppo.upgrade.publicSentiment.enums.*; import com.oppo.upgrade.publicSentiment.service.NotworkIndexService; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Service; import org.springframework.util.CollectionUtils; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.sql.*; import java.util.*; import java.util.Date; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.stream.Collectors; import static com.oppo.upgrade.common.constant.MidasConstant.IMEI; /** * @author W9004879 */ @Slf4j @Service public class NotworkIndexServiceImpl implements NotworkIndexService { @HeraclesDynamicConfig(key = "notwork_index.class_forName", fileName = "application.yml") private String classForName; @HeraclesDynamicConfig(key = "notwork_index.url", fileName = "application.yml") private String notworkIndexUrl; @HeraclesDynamicConfig(key = "notwork_index.user", fileName = "application.yml") private String notworkIndexUser; @HeraclesDynamicConfig(key = "notwork_index.password", fileName = "application.yml") private String notworkIndexPassword; @HeraclesDynamicConfig(key = "notwork_index.class_forName_face", fileName = "application.yml") private String classForNameFace; @HeraclesDynamicConfig(key = "notwork_index.url_face", fileName = "application.yml") private String notworkIndexUrlFace; @HeraclesDynamicConfig(key = "notwork_index.user_face", fileName = "application.yml") private String notworkIndexUserFace; @HeraclesDynamicConfig(key = "notwork_index.password_face", fileName = "application.yml") private String notworkIndexPasswordFace; @HeraclesDynamicConfig(key = "notwork_index.class_forName_board", fileName = "application.yml") private String classForNameBoard; @HeraclesDynamicConfig(key = "notwork_index.url_board", fileName = "application.yml") private String notworkIndexUrlBoard; @HeraclesDynamicConfig(key = "notwork_index.user_board", fileName = "application.yml") private String notworkIndexUserBoard; @HeraclesDynamicConfig(key = "notwork_index.password_board", fileName = "application.yml") private String notworkIndexPasswordBoard; @HeraclesDynamicConfig(key = "notwork_index.imei_limit_size_board", fileName = "application.yml") private String imeiLimitSize; @HeraclesDynamicConfig(key = "notwork_index.initial_subsidence_imei_view_limit_size", fileName = "application.yml") private String initialSubsidenceImeiViewLimitSize; @HeraclesDynamicConfig(key = "notwork_index.board_view_top_num", fileName = "application.yml") private String topNum; private static final String A = "A"; private static final String B = "B"; private static final String C = "C"; private static final String D = "D"; private static final String DAY_NO = "dayNo"; private static final String MODEL = "model"; private static final String ANDROID_VERSION = "android_version"; private static final String ROM_VERSION = "rom_version"; private static final String NPS_SQL = "select imei from commercial_data.nps_question_new_label_test where 1=1"; private static final String SATISFY_SQL = "select imei from commercial_data.satisfy_all_label_huiduhou_test where 1=1"; /** * 查询游戏网络数据 * * @param notworkIndexDTO notworkIndexDTO * @return 游戏网络数据 * @throws SQLException SQLException */ @Override public List<GamingNetWorkVO> findGamingNetWorkIndex(NotworkIndexDTO notworkIndexDTO) throws SQLException { return getGamingNetWorkResult(notworkIndexDTO); } public List<GamingNetWorkVO> getGamingNetWorkResult(NotworkIndexDTO notworkIndexDTO) throws SQLException { log.info("开始连接clickHouse查询-->{}", classForName + notworkIndexUrl + notworkIndexUser + notworkIndexPassword); Connection connection = getConnection(); assert connection != null; Statement statement = connection.createStatement(); log.info("开始连接clickHouse查询成功"); // 处理游戏网络sql NotworkSqlVO notworkSqlVO = handleGamingNetWorkSql(notworkIndexDTO); log.info("查询CK库游戏网络明细数据查询sql打印-->" + JSON.toJSONString(notworkSqlVO.getGameNetworkDetailSql())); log.info("查询CK库异常场景明细查询sql打印-->" + JSON.toJSONString(notworkSqlVO.getExceptionScenarioDetailSql())); log.info("查询CK库游戏网络异常小区查询sql打印-->" + JSON.toJSONString(notworkSqlVO.getGameNetworkExceptionCellSql())); // 执行游戏网络等sql,获取结果返回集 ResultSet gameNetworkDetailSqlResult = statement.executeQuery(notworkSqlVO.getGameNetworkDetailSql()); ResultSet exceptionScenarioDetailSqlResult = statement.executeQuery(notworkSqlVO.getExceptionScenarioDetailSql()); ResultSet gameNetworkExceptionCellSqlResult = statement.executeQuery(notworkSqlVO.getGameNetworkExceptionCellSql()); // 获取游戏网络明细数据结果列表 List<GamingNetWorkVO> gameNetworkDetailList = handleGameNetworkDetailSqlResult(gameNetworkDetailSqlResult); log.info("查询CK库游戏网络明细数据查询结果打印-->" + JSON.toJSONString(gameNetworkDetailList)); // 获取异常场景明细结果列表 List<GamingNetWorkVO> exceptionScenarioDetailList = handleExceptionScenarioDetailSqlResult(exceptionScenarioDetailSqlResult); log.info("查询CK库异常场景明细查询结果打印-->" + JSON.toJSONString(exceptionScenarioDetailList)); // 获取游戏网络异常小区结果列表 List<GamingNetWorkVO> networkExceptionCellList = handleGameNetworkExceptionCellSqlResult(gameNetworkExceptionCellSqlResult); log.info("查询CK库游戏网络异常小区查询结果打印-->" + JSON.toJSONString(networkExceptionCellList)); // 获取游戏网络异常小区查询结果分组及组合拼接处理后的列表 List<GamingNetWorkVO> topNetworkExceptionCellList = handleNetworkExceptionCellList(networkExceptionCellList); for (GamingNetWorkVO exceptionCellVO : topNetworkExceptionCellList) { for (GamingNetWorkVO detailVO : gameNetworkDetailList) { if (exceptionCellVO.getImeiRomVersionEventId().equals(detailVO.getImeiRomVersionEventId())) { BeanUtil.copyPropertiesIgnoreNull(detailVO, exceptionCellVO); Double sumCell = exceptionCellVO.getSumCell().doubleValue(); Double signal = detailVO.getSignal().doubleValue(); Double totalSub = detailVO.getTotalSub().doubleValue(); String exceptionCellRatio = Double.parseDouble(UpgradeStringUtil.formatNumber2XDecimal(sumCell / totalSub * 100, 2)) + "%";