江苏龙网

  1. 主页 > 生活百科 > >

JDBC连接池封装MaxCompute/Hive/Oracle/Mysql

JDBC

有时候需要连接第三方的各种数据源，总是要去写不同的代码，于是将MaxCompute, Hive, Oracle, MySQL等JDBC连接封装起来，只需要传入不同的参数即可创建一个不同类型的连接池 。
连接参数基础类封装
封装了JDBC基础的连接参数，如果不需要这些属性可以继承该类，增加新的属性即可 。
@Datapublic class BaseJdbcConnParam implements Serializable {/*** driver name*/private String driverName;/*** IP*/private String ip;/*** db server port*/private Integer port;/*** db name*/private String dbName;/*** db connection username*/private String username;/*** db connection password*/private String password;}抽象连接工具类封装
功能如下：

  • 1、构造函数：根据连接参数不同构建不同的连接对象
  • 2、构建具体的连接，子类实现buildConnection()
  • 3、获取连接，构建好之后直接获取getConnection()
/** * @Description 抽象连接工具类父类 * @Author itdl * @Date 2022/08/15 09:54 */public abstract class AbstractConnUtil<P extends BaseJdbcConnParam> {/*** connection params*/protected final P connParam;/*** jdbc connection object*/protected final Connection connection;/*** 构造函数，构造工具类对象* @param connParam 连接参数*/public AbstractConnUtil(P connParam) {this.connParam = connParam;this.connection = buildConnection();}/*** 构建连接对象* @return 连接对象*/protected abstract Connection buildConnection();/*** 获取连接*/public Connection getConnection() {return connection;}}连接池管理
功能如下：
  • 1、根据不同的连接参数，和最大连接数去创建一个对应类型的连接池 。
  • 2、获取连接方法，如果连接没有了，等待其他线程释放（最多等待十分钟）
  • 3、释放连接方法，将连接放回连接池，然后唤醒等待的线程
  • 4、关闭连接池所有的连接
/** * @Description 连接池管理 * @Author itdl * @Date 2022/08/16 09:42 */@Slf4jpublic class DbConnPool<T extends BaseJdbcConnParam> {/*** 用于存放连接*/private final LinkedList<Connection> connPool = new LinkedList<Connection>();/*** 最大连接池数量*/private final Integer maxPoolSize;private final T connParam;/*** 构造函数* @param connParam 连接参数* @param maxPoolSize 连接池大小*/public DbConnPool(T connParam, Integer maxPoolSize){this.maxPoolSize = maxPoolSize;this.connParam = connParam;// 初始化连接池for (int i = 0; i < maxPoolSize; i++) {connPool.addLast(this.createConnection());}}/*** 创建数据库连接* @return 连接*/private Connection createConnection() {if (connParam instanceof OracleJdbcConnParam){final OracleConnUtil util = new OracleConnUtil((OracleJdbcConnParam) connParam);return util.getConnection();}if (connParam instanceof HiveJdbcConnParam){final HiveConnUtil util = new HiveConnUtil((HiveJdbcConnParam) connParam);return util.getConnection();}if (connParam instanceof MysqlJdbcConnParam){final MysqlConnUtil util = new MysqlConnUtil((MysqlJdbcConnParam) connParam);return util.getConnection();}if (connParam instanceof MaxComputeJdbcConnParam){final MaxComputeJdbcUtil util = new MaxComputeJdbcUtil((MaxComputeJdbcConnParam) connParam);return util.getConnection();}throw new BizException(ResultCode.CONN_TYPE_NOT_SUPPORT);}/*** 获取连接* @return 连接*/public synchronized Connection getConnection(){if (connPool.size() == 0){//throw new BizException(ResultCode.CONN_POOL_EMPTY_ERR);// 最长等待十分钟try {log.info("==========连接池已经空了，请等待其他线程释放==========");wait(10 * 60 * 1000);} catch (InterruptedException e) {log.info("==========连接池已经空了，等待了10分钟还没有释放，抛出异常==========");e.printStackTrace();throw new BizException(ResultCode.CONN_POOL_EMPTY_ERR);}}// 去除最上面一个连接 如果没有连接了，将会抛出异常return connPool.removeFirst();}/*** 用完后释放连接* @param conn 要释放的连接*/public synchronized void freeConnection(Connection conn){// 通知连接已经释放notifyAll();this.connPool.addLast(conn);}/*** 关闭连接池*/public synchronized void close(){for (Connection connection : connPool) {SqlUtil.close(connection);}}}SQL操作工具类
根据连接对象Connection和数据库房源，封装不同的sql执行 。执行SQL核心功能封装 。
/** * @Description SQL操作工具类 * @Author itdl * @Date 2022/08/10 17:13 */@Slf4jpublic class SqlUtil {/**查询mysql表注释sql*/public static final String SELECT_TABLES_MYSQL = "select table_name, table_comment from information_schema.tables where TABLE_SCHEMA = '%s'";/**查询MaxCompute表注释sql*/public static final String SELECT_TABLES_MAX_COMPUTE = "select table_name, table_comment from information_schema.tables where TABLE_SCHEMA = '%s'";/**查询oracle表注释sql*/public static final String SELECT_TABLES_ORACLE = "SELECT t2.TABLE_NAME as table_name, t2.COMMENTS as table_comment FROM user_tables t1 inner join user_tab_comments t2 on t1.TABLE_NAME = t2.TABLE_NAME";/**查询hive表注释sql, 先查询表名，根据表名获取建表语句，正则提取表注释*/public static final String SELECT_TABLES_HIVE = "show tables";public static final String SELECT_TABLES_2_HIVE = "describe extended %s";/**分页数量统计Mysql*/private static final String SELECT_COUNT_MYSQL = "select count(1) from (%s) z";/**分页数量统计MaxCompute*/private static final String SELECT_COUNT_MAX_COMPUTE = "select count(1) from (%s) z;";/**分页数量统计Hive*/private static final String SELECT_COUNT_ORACLE = "select count(1) from (%s) z";/**分页数量统计Oracle*/private static final String SELECT_COUNT_HIVE = "select count(1) from (%s) z";/**maxCompute开启全表扫描sql*/private static final String FULL_SCAN_MAX_COMPUTE = "set odps.sql.allow.fullscan=true;";/**分页查询sql-Mysql*/private static final String SELECT_PAGE_MYSQL = "select z.* from (%s) z limit %s, %s";/**分页查询sql-MaxCompute*/private static final String SELECT_PAGE_MAX_COMPUTE = "select z.* from (%s) z limit %s, %s;";/**分页查询sql-Hive*/private static final String SELECT_PAGE_HIVE = "select * from (select row_number() over () as row_num_01,u.* from (%s) u) mm where mm.row_num_01 between %s and %s";/**分页查询sql-Oracle*/private static final String SELECT_PAGE_ORACLE = "select * from (SELECT ROWNUM as row_num_01,z.* from (%s) z) h where h.row_num_01 > %s and h.row_num_01 <= %s";/**数据库连接*/private final Connection connection;/**数据库方言*/private final Integer dbDialect;/**支持的方言列表*/private static final List<Integer> supportDbTypes =Arrays.asList(DbDialectEnum.ORACLE.getCode(), DbDialectEnum.HIVE.getCode(), DbDialectEnum.MYSQL.getCode(), DbDialectEnum.MAX_COMPUTE.getCode());public SqlUtil(Connection connection, Integer dbDialect) {if (!supportDbTypes.contains(dbDialect)){throw new BizException(ResultCode.CONN_TYPE_NOT_SUPPORT);}this.connection = connection;this.dbDialect = dbDialect;}/*** 根据connection获取所有的表和对应的注释*/public List<TableMetaInfo> getTables(String schemaName){List<TableMetaInfo> result = new ArrayList<>();String sql = "";switch (this.dbDialect){case 1:sql = SELECT_TABLES_ORACLE;break;case 2:sql = SELECT_TABLES_HIVE;break;case 3:if (StringUtils.isBlank(schemaName)){throw new BizException(ResultCode.SELECT_TABLES_SCHEMA_NOT_NULL_ERR);}sql = String.format(SELECT_TABLES_MYSQL, schemaName);break;case 4:if (StringUtils.isBlank(schemaName)){throw new BizException(ResultCode.SELECT_TABLES_SCHEMA_NOT_NULL_ERR);}sql = String.format(SELECT_TABLES_MAX_COMPUTE, schemaName);default:break;}if (StringUtils.isBlank(sql)){throw new BizException(ResultCode.CONN_TYPE_NOT_SUPPORT);}// 执行SQL语句final List<LinkedHashMap<String, Object>> resultMaps = querySql(sql);if (ObjectUtils.isEmpty(resultMaps)){return Lists.newArrayList();}// hive单独处理List<TableMetaInfo> result1 = getHiveTableMetaInfos(result, resultMaps);if (result1 != null) return result1;// 转换结果return resultMaps.stream().map(m->{final TableMetaInfo info = new TableMetaInfo();Object tableNameObj = m.get("table_name");String tableName = tableNameObj == null ? m.get("TABLE_NAME") == null ? "" : String.valueOf(m.get("TABLE_NAME")) : String.valueOf(tableNameObj);Object tableCommentObj = m.get("table_comment");String tableComment = tableCommentObj == null ? m.get("TABLE_COMMENT") == null ? "" : String.valueOf(m.get("TABLE_COMMENT")) : String.valueOf(tableCommentObj);info.setTableName(tableName);info.setComment(tableComment);return info;}).collect(Collectors.toList());}/*** 根据schemeName，表名获取字段列表* @param tableName 一般是数据库 oracle是用户名*/public List<TableColumnMetaInfo> getColumnsByTableName(String tableName){try {List<TableColumnMetaInfo> list = new ArrayList<>();final DatabaseMetaData metaData = https://www.isolves.com/it/cxkf/yy/JAVA/2022-08-17/connection.getMetaData();final ResultSet columns = metaData.getColumns(null, null, tableName, null);while (columns.next()){String columnName = columns.getString("COLUMN_NAME");String remarks = columns.getString("REMARKS");remarks = StringUtils.isBlank(remarks) ? "" : remarks;final TableColumnMetaInfo metaInfo = new TableColumnMetaInfo(tableName, columnName, remarks);list.add(metaInfo);}return list;} catch (SQLException e) {e.printStackTrace();return Lists.newArrayList();}}/*** 执行sql查询* @param querySql 查询sql* @return List


推荐阅读


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

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