业务场景

主数据系统使用的是mysql数据库，需要访问其他系统（使用的是oracle数据库）数据库，以oracle数据库数据为基准进行对数据分组统计比对。

技术分析

由于连接的数据源并不多，因此没有把dynamic-datasource动态多数据源技术方案引入进来，这是使用的是纯原生手写oracle数据库连接并手写分页效果实现

功能实现

1、引入maven

<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc6</artifactId><version>11.2.0.4</version><scope>runtime</scope>
</dependency>

2、controller

	/*** 主数据与其他数据源比对* @param diffQueryDTO* @return*/@PostMapping("/compareDataDiffListPage")@ApiOperation(value = "主数据与杜巴特数据比对--组织/用户-分页集合", response = DiffResultVO.class)public R<DiffResultVO> compareDataDiffListPage(@RequestBody DiffQueryDTO diffQueryDTO) {DiffResultVO result = iDataProcessService.compareDataDiffListPage(diffQueryDTO);return R.data(result,"主数据与杜巴特数据比对--组织/用户查询成功！");}

模拟分页数据返回封装

@Data
public class DiffResultVO {/*** 总数*/private Integer total;/*** 分页数据*/List<DiffVO> resultPage;}

分页差异数据

@Data
public class DiffVO {/*** 分包商编码*/private String manufacturerCode;/*** 分包商名称*/private String manufacturerName;/*** 数据类型 0：组织 1：用户*/private Integer dataType;/*** 杜巴特（数量）*/private Integer dubateCount;/*** 主数据（数量）*/private Integer maindataCount;/*** 差异数*/private Integer diffCount;//>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>差异明细>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>/*** 组织差异infos*/private List<InstitutionVO> institutionDiffInfos;/*** 用户差异infos*/private List<StaffVO> staffDiffInfos;
}

分页查询入参封装

@Data
public class DiffQueryDTO {/*** 分页查询的参数，当前页数*/private Integer pageNum = 1;/*** 分页查询的参数，当前页面每页显示的数量*/private Integer pageSize = 10;/*** 分包商编码*/private String manufacturerCode;/*** 分包商名称*/private String manufacturerName;/*** 数据类型 0：组织 1：用户*/private Integer dataType;
}

3、serviceImpl

private static String USERNAMR = "XXX";
private static String PASSWORD = "XXXX";
private static String DRVIER = "oracle.jdbc.OracleDriver";
private static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XXX";

	@Overridepublic DiffResultVO compareDataDiffListPage(DiffQueryDTO diffQueryDTO) {DiffResultVO result = new DiffResultVO();List<DiffVO> resultPage = new ArrayList<>();Integer totalNum = 0;Integer pageNum = diffQueryDTO.getPageNum();Integer pageSize = diffQueryDTO.getPageSize();//计算分页Integer startNum = (pageNum*pageSize);Integer endNum = (pageNum - 1)*pageSize;String manufacturerName = diffQueryDTO.getManufacturerName();String manufacturerCode = diffQueryDTO.getManufacturerCode();Integer dataType = diffQueryDTO.getDataType();if(dataType == null){throw new SinomaException("入参数据类型不能为空，请核实！");}Connection connection = null;PreparedStatement pstm = null;ResultSet rs = null;if(dataType == 0){//组织//查询其他数据源数据>>>以其他数据源库为分页标准try {//DBUtils工具类在下方connection = DBUtils.getConnection(DRVIER, URL, USERNAMR, PASSWORD); if(connection == null){throw new SinomaException("获取数据库连接失败，请核实！");}//总数sqlString countSql = "SELECT COUNT(1) AS total FROM (SELECT DISTINCT ORG_ID, CODE as manufacturer_code,NAME as manufacturer_name FROM BUCP.FBS_V_ORG \n" +"WHERE unity  = '公司'  \n";StringBuilder countSb = new StringBuilder();countSb.append(countSql);if(StringUtils.isNotBlank(manufacturerName)){countSb.append("AND name like '%"+manufacturerName+"%' \n");}if(StringUtils.isNotBlank(manufacturerCode)){countSb.append("AND code like '%"+manufacturerCode+"%' \n");}countSb.append("AND  CODE IS NOT NULL \n");countSb.append("AND NAME IS NOT NULL) z");//分页sqlString pageSql = "SELECT * FROM \n" +"(SELECT z.*,ROWNUM ruw_num FROM (SELECT DISTINCT ORG_ID, CODE AS manufacturer_code,NAME as manufacturer_name FROM BUCP.FBS_V_ORG \n" +"WHERE unity  = '公司' \n";StringBuilder pageSb = new StringBuilder();pageSb.append(pageSql);if(StringUtils.isNotBlank(manufacturerName)){pageSb.append("AND name like '%"+manufacturerName+"%' \n");}if(StringUtils.isNotBlank(manufacturerCode)){pageSb.append("AND code like '%"+manufacturerCode+"%' \n");}pageSb.append("and  CODE IS NOT NULL \n");pageSb.append("AND NAME IS NOT NULL) z \n");pageSb.append("WHERE ROWNUM<= "+startNum+") \n");pageSb.append("WHERE ruw_num> "+endNum);try {//获取总数pstm = connection.prepareStatement(countSb.toString());rs = pstm.executeQuery();while (rs.next()) {totalNum = rs.getInt("total");}//oracle分页查询pstm = connection.prepareStatement(pageSb.toString());rs = pstm.executeQuery();while (rs.next()) {DiffVO diffVO = new DiffVO();diffVO.setManufacturerCode(rs.getString("manufacturer_code"));diffVO.setManufacturerName(rs.getString("manufacturer_name"));diffVO.setDataType(0);resultPage.add(diffVO);}} catch (SQLException e) {throw new SinomaException("sql执行异常，请核实！"+e.getMessage());}if(!CollectionUtils.isEmpty(resultPage)){//涉及到树结构数据递归下钻，初始全量treeCode来实现//oracle数据库查询的数据封装List<DubateOrgDTO> allDubateDto = new ArrayList<>();String allSql = "SELECT * FROM BUCP.FBS_V_ORG \n" +"WHERE unity  = '部门' AND  CODE IS NOT NULL AND NAME IS NOT NULL";try {pstm = connection.prepareStatement(allSql);rs = pstm.executeQuery();while (rs.next()) {DubateOrgDTO dto = new DubateOrgDTO();dto.setOrgId(rs.getInt("org_id"));dto.setCode(rs.getString("code"));dto.setName(rs.getString("name"));dto.setPname(rs.getString("pname"));dto.setPcode(rs.getString("pcode"));//初始全量treeCodeString code = dto.getCode();if(StringUtils.isNotBlank(code)){List<String> projectInstitutionCodes = new ArrayList<>();projectInstitutionCodes.add(code);//根据当前code递归获取全量父级code逗号分隔，递归方法在下方projectInstitutionCodes = getAllCode(projectInstitutionCodes,dto.getPcode(),connection,pstm,rs);if(!CollectionUtils.isEmpty(projectInstitutionCodes)){dto.setAllCode(String.join(",", projectInstitutionCodes));}}allDubateDto.add(dto);}} catch (SQLException e) {throw new SinomaException("sql执行异常，请核实！"+e.getMessage());}//主数据mysql数据库查询的数据封装（已包含全量treeCode）List<InstitutionVO> allMaindataDto = institutionMapper.getInstitutionPartList();//遍历resultPage初始化差异数量for (DiffVO diffVO : resultPage) {String dubateCode = diffVO.getManufacturerCode();if(StringUtils.isNotBlank(dubateCode)){//oracle分组统计数量Integer dubateCount = 0;for (DubateOrgDTO dubateOrgDTO : allDubateDto) {String allCode = dubateOrgDTO.getAllCode();if(StringUtils.isNotBlank(allCode)){List<String> codeList = Arrays.asList(allCode.split(","));if(codeList.contains(dubateCode)){dubateCount = dubateCount + 1;}}}diffVO.setDubateCount(dubateCount);//主数据mysql分组统计数量Integer maindataCount = 0;for (InstitutionVO institutionVO : allMaindataDto) {String institutionCode = institutionVO.getInstitutionCode();String allCode = institutionVO.getMainInstitutionAll();if(StringUtils.isNotBlank(allCode)){List<String> codeList = Arrays.asList(allCode.split(","));if(codeList.contains(dubateCode)&&!institutionCode.equals(dubateCode)){maindataCount = maindataCount + 1;}}}diffVO.setMaindataCount(maindataCount);}else{diffVO.setDubateCount(0);diffVO.setMaindataCount(0);}diffVO.setDiffCount(Math.abs(diffVO.getDubateCount()-diffVO.getMaindataCount()));}}} catch (SinomaException e) {throw new SinomaException("主数据与其他数据源的组织数据比对异常，请核实！"+e.getMessage());}finally {//释放数据库连接DBUtils.releaseResource(rs,pstm,connection);}}result.setTotal(totalNum);result.setResultPage(resultPage);return result;}

DBUtils 工具类

public class DBUtils {/*** 获取Connection对象** @return*/public static Connection getConnection(String drvier,String url,String usernamr,String password) {// 创建一个数据库连接Connection connection = null;try {Class.forName(drvier);connection = DriverManager.getConnection(url, usernamr, password);} catch (ClassNotFoundException e) {throw new RuntimeException("class not find !", e);} catch (SQLException e) {throw new RuntimeException("get connection error!", e);}return connection;}/**** @param rs 结果集对象* @param pstm 预编译语句对象* @param connection 数据库连接*/public static void releaseResource(ResultSet rs,PreparedStatement pstm,Connection connection) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (pstm != null) {try {pstm.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}
}

getAllCode向上递归所有的treeCode（当前code的全量code）

private List<String> getAllCode(List<String> mainInstitutionCodes, String pcode,Connection connection,PreparedStatement pstm,ResultSet rs) {try {//根据pcode获取组织信息String allSql = "SELECT * FROM BUCP.FBS_V_ORG  \n" +"WHERE  CODE IS NOT NULL AND NAME IS NOT NULL\n" +"AND CODE = ?";pstm = connection.prepareStatement(allSql);pstm.setString(1,pcode);rs = pstm.executeQuery();while (rs.next()) {DubateOrgDTO dto = new DubateOrgDTO();dto.setOrgId(rs.getInt("org_id"));dto.setCode(rs.getString("code"));dto.setName(rs.getString("name"));dto.setShortname(rs.getString("shortname"));dto.setRank(rs.getString("rank"));dto.setStatus(rs.getString("status"));dto.setUnity(rs.getString("unity"));dto.setPname(rs.getString("pname"));dto.setPcode(rs.getString("pcode"));dto.setProjectcode(rs.getString("projectcode"));dto.setArea(rs.getString("area"));dto.setCountry(rs.getString("country"));dto.setAddress(rs.getString("address"));dto.setCity(rs.getString("city"));dto.setSocialcreditcode(rs.getString("socialcreditcode"));dto.setPostcode(rs.getString("postcode"));dto.setEnname(rs.getString("enname"));dto.setTel(rs.getString("tel"));if (StringUtils.isNotBlank(dto.getCode())&&StringUtils.isNotBlank(dto.getPcode())) {mainInstitutionCodes.add(dto.getCode());getAllCode(mainInstitutionCodes, dto.getPcode(),connection,pstm,rs);}}} catch (SQLException e) {throw new SinomaException("sql执行异常，请核实！"+e.getMessage());}return mainInstitutionCodes;}