江苏龙网

  1. 主页 > 生活百科 > >

JDBC连接池封装MaxCompute/Hive/Oracle/Mysql( 二 )

JDBC

> 通过LinkedHashMap接受，序列化时可保证顺序一致*/public List> queryData(String querySql, boolean... fullScan){Statement statement = null;ResultSet resultSet = null;try {// 创建statementstatement = this.connection.createStatement();// 执行全表扫描sqlfor (boolean b : fullScan) {if (b){statement.execute(FULL_SCAN_MAX_COMPUTE);break;}}// 执行查询语句resultSet = statement.executeQuery(querySql);// 构建结果返回return buildListMap(resultSet);} catch (SQLException e) {e.printStackTrace();throw new BizException(ResultCode.SQL_EXEC_ERR);} finally {// 关闭resultSet, statementclose(resultSet, statement);}}/*** 执行sql查询* @param querySql 查询sql* @return List>*/public List> queryData(String querySql, Integer page, Integer size){Statement statement = null;ResultSet resultSet = null;try {// 1、替换分号querySql = querySql.replaceAll(";", "");// 创建statementstatement = this.connection.createStatement();// 2、格式化SQLint offset = (page - 1 ) * size;String execSql = "";switch (this.dbDialect){case 1:// oracleexecSql = String.format(SELECT_PAGE_ORACLE, querySql, offset, size);break;case 2:// hiveexecSql = String.format(SELECT_PAGE_HIVE, querySql, offset, size);break;case 3:// mysqlexecSql = String.format(SELECT_PAGE_MYSQL, querySql, offset, size);break;case 4:// maxComputeexecSql = String.format(SELECT_PAGE_MAX_COMPUTE, querySql, offset, size);break;default:break;}// maxCompute开启全表扫描if (DbDialectEnum.MAX_COMPUTE.getCode().equals(this.dbDialect)){statement.execute(FULL_SCAN_MAX_COMPUTE);}log.info("=======>>>执行分页sql为：{}", execSql);// 执行查询语句resultSet = statement.executeQuery(execSql);// 构建结果返回return buildListMap(resultSet);} catch (SQLException e) {e.printStackTrace();throw new BizException(ResultCode.SQL_EXEC_ERR);} finally {// 关闭resultSet, statementclose(resultSet, statement);}}/*** 执行分页查询* @param querySql 分页查询sql* @param page 页码 从1开始 第n页传n* @param size 每页记录数* @return 分页查询结果*/public PageResult> pageQueryMap(String querySql, Integer page, Integer size){// 1、替换分号querySql = querySql.replaceAll(";", "");String countSql = "";switch (this.dbDialect){case 1:// oraclecountSql = String.format(SELECT_COUNT_ORACLE, querySql);break;case 2:// hivecountSql = String.format(SELECT_COUNT_HIVE, querySql);break;case 3:// mysqlcountSql = String.format(SELECT_COUNT_MYSQL, querySql);break;case 4:// maxComputecountSql = String.format(SELECT_COUNT_MAX_COMPUTE, querySql);break;default:break;}log.info("=======>>>执行分页统计总数sql为：{}", countSql);// 查询总数final List> countMap = queryData(countSql, DbDialectEnum.MAX_COMPUTE.getCode().equals(this.dbDialect));if (CollectionUtils.isEmpty(countMap)){return new PageResult<>(0L, new ArrayList<>());}long count = 0L;for (Object value : countMap.get(0).values()) {count = Long.parseLong(String.valueOf(value));}if (count == 0){return new PageResult<>(0L, new ArrayList<>());}// 执行分页查询 开启全表扫描final List> resultList = queryData(querySql, page, size);return new PageResult<>(count, resultList);}/*** 执行分页查询* @param querySql 分页查询sql* @param page 页码 从1开始 第n页传n* @param size 每页记录数* @return 分页查询结果*/public PageResult pageQuery(String querySql, Integer page, Integer size, Class clazz){final PageResult> result = pageQueryMap(querySql, page, size);List rows = new ArrayList<>();for (LinkedHashMap row : result.getRows()) {final T t = JSONObject.parseobject(JSONObject.toJSONString(row), clazz);rows.add(t);}return new PageResult<>(result.getTotal(), rows);}/*** 获取hive的表注释* @param result 结果* @param resultMaps show tables结果* @return List*/private List getHiveTableMetaInfos(List result, List> resultMaps) {if (dbDialect.equals(DbDialectEnum.HIVE.getCode())){for (LinkedHashMap resultMap : resultMaps) {final String tabName = String.valueOf(resultMap.get("tab_name"));final String descTableCommentSql = String.format(SELECT_TABLES_2_HIVE, tabName);List> resultMapsComments = querySql(descTableCommentSql);//col_name -> Detailed Table InformationString comments = resultMapsComments.stream().filter(m -> "Detailed Table Information".equals(m.get("col_name"))).map(m -> String.valueOf(m.get("data_type"))).findFirst().orElse("");comments = ReUtil.get("parameters:\{(?!.*?\().*transient_lastDdlTime.*?comment=(.*?)\}", comments,1);if (StringUtils.isBlank(comments)) {comments = "";}if (comments.contains(",")){comments = comments.substring(0, comments.lastIndexOf(","));}result.add(new TableMetaInfo(tabName, comments));log.info("===========>>>获取表{}的注释成功:{}", tabName, comments);resultMapsComments.clear();}return result;}return null;}/*** 执行SQL查询* @param sql sql语句* @return 数据列表，使用LinkedHashMap是为了防止HashMap序列化后导致顺序乱序*/public List> querySql(String sql){// 执行sqlStatement statement = null;ResultSet resultSet = null;try {statement = connection.createStatement();resultSet = statement.executeQuery(sql);return buildListMap(resultSet);} catch (SQLException e) {e.printStackTrace();throw new BizException(ResultCode.SQL_EXEC_ERR);}finally {// 关闭close(resultSet, statement);}}/*** 关闭对象 传入多个时注意顺序，需要先关闭哪个就传在参数前面* @param objs 对象动态数组*/public static void close(Object ...objs){if (objs == null || objs.length == 0){return;}for (Object obj : objs) {if (obj instanceof Statement){try {((Statement) obj).close();}catch (Exception e){e.printStackTrace();}}if (obj instanceof ResultSet){try {((ResultSet) obj).close();}catch (Exception e){e.printStackTrace();}}if (obj instanceof Connection){try {((Connection) obj).close();}catch (Exception e){e.printStackTrace();}}}}/*** @Description 功能描述：将resultSet构造为List* @Author itdl* @Date 2022/4/18 21:13* @Param {@link ResultSet} resultSet* @Return {@link List < Map


推荐阅读


上一篇：路由器自带的DDNS功能解决公网IP动态更新问题

下一篇：AWS携手Splunk等科技公司发布OCSF开源网络安全数据框架