package com.product.data.connection; import com.alibaba.druid.proxy.jdbc.ClobProxyImpl; import com.product.common.lang.StringUtils; import com.product.core.config.Global; import com.product.core.exception.BaseException; import com.product.data.config.DatabaseType; import com.product.data.config.ErrorCode; import com.product.data.entity.DatabaseEntity; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.sql.*; /** * @Author cheng * @Date 2022/2/5 17:05 * @Desc */ public class ConnectionManager { static Logger log = LoggerFactory.getLogger(ConnectionManager.class); public static void main(String[] args) { DatabaseEntity databaseEntity = new DatabaseEntity(DatabaseType.MySql.getValue()); databaseEntity.setDbName("product_db_v2.0.0"); databaseEntity.setUserName("root"); databaseEntity.setIp("127.0.0.1"); databaseEntity.setPassWord("root123"); databaseEntity.setPort("3306"); databaseEntity.setCustomParams("&serverTimezone=Asia/Shanghai"); getConnection(databaseEntity); } /** * 获取系统连接 * * @return */ public static Connection getSystemConnection() throws BaseException { String url = Global.getSystemConfig("data.source.url", ""); String userName = Global.getSystemConfig("data.source.user", ""); String userPassword = Global.getSystemConfig("data.source.password", ""); //批量提交参数是否开启 if (url.indexOf("rewriteBatchedStatements=true") == -1) { url += "&rewriteBatchedStatements=true"; } DatabaseEntity databaseEntity = new DatabaseEntity(DatabaseType.MySql.getValue()); databaseEntity.setUserName(userName); databaseEntity.setPassWord(userPassword); return getConnection(url, databaseEntity); } /** * 获取连接 * * @param dbe * @return * @throws BaseException */ public static Connection getConnection(DatabaseEntity dbe) throws BaseException { try { if (dbe.getDbType() == null) { throw new BaseException(ErrorCode.UNKNOWN_DATABASE_TYPE); } //装载驱动 Class.forName(dbe.getDbType().getDriver()); if (DatabaseType.MySql.equals(dbe.getDbType())) { // MYSQL return getMysqlConnection(dbe); } else if (DatabaseType.Oracle.equals(dbe.getDbType())) { // ORACLE return getOracleConnection(dbe); } else if (DatabaseType.SqlServer.equals(dbe.getDbType())) { // SQLSERVER return getSqlServerConnection(dbe); } else if (DatabaseType.Informix.equals(dbe.getDbType())) { // INFORMIX return getInformixConnection(dbe); } else if (DatabaseType.PSQL.equals(dbe.getDbType())) { return getPSQLConnection(dbe); } throw new BaseException(ErrorCode.GET_CONNECTION_FAIL); } catch (BaseException e) { e.printStackTrace(); throw e; } catch (Exception e) { e.printStackTrace(); throw new BaseException(ErrorCode.GET_CONNECTION_FAIL); } } public static Connection getPSQLConnection(DatabaseEntity dbe) { String templateUrl = "jdbc:postgresql://%s:%s/%s%s"; //替换URL模板 String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams()); return getConnection(url, dbe); } public static long getConnectionSystemTime(Connection connection, DatabaseType dbType) throws BaseException { String queryTimeSql; if (DatabaseType.MySql.equals(dbType)) { queryTimeSql = "select current_timestamp(3) `timestamp` "; } else if (DatabaseType.Oracle.equals(dbType)) { queryTimeSql = "select systimestamp as timestamp from dual"; } else if (DatabaseType.SqlServer.equals(dbType)) { queryTimeSql = "SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 00:00:00.000', GETUTCDATE())) * 60000 + DATEPART(S,GETUTCDATE()) * 1000 + DATEPART(MS, GETUTCDATE()) as timestamp"; } else if (DatabaseType.Informix.equals(dbType)) { queryTimeSql = "select current as timestamp from sysmaster:sysshmvals"; } else { throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL); } try (PreparedStatement pst = connection.prepareStatement(queryTimeSql); ResultSet resultSet = pst.executeQuery();) { while (resultSet.next()) { final Object timestamp = resultSet.getObject("timestamp"); if (timestamp != null) { if (timestamp instanceof Time) { return ((Time) timestamp).getTime(); } else if (timestamp instanceof Timestamp) { return ((Timestamp) timestamp).getTime(); } else { return resultSet.getTimestamp("timestamp").getTime(); } } return resultSet.getLong("timestamp"); } throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL); } catch (BaseException e) { throw e; } catch (Exception e) { e.printStackTrace(); throw new BaseException(ErrorCode.GET_CONNECTION_TIME_FAIL); } } /** * 获取MySql连接 * * @param dbe * @return * @throws SQLException */ private static Connection getMysqlConnection(DatabaseEntity dbe) { String templateUrl = "jdbc:mysql://%s:%s/%s%s"; //替换URL模板 String url = String.format(templateUrl, dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getCustomParams()); return getConnection(url, dbe); } /** * 获取Sql Server连接 * * @param dbe * @return * @throws SQLException */ private static Connection getSqlServerConnection(DatabaseEntity dbe) { String dbInstance = dbe.getDbInstance(); String url; if (StringUtils.isEmpty(dbInstance)) { url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbName()); } else { // 有实例名 url = String.format("jdbc:sqlserver://%s:%s;instanceName=%s;databaseName=%s;", dbe.getIp(), dbe.getPort(), dbe.getDbInstance(), dbe.getDbName()); } return getConnection(url, dbe); } /** * 获取Oracle连接 * * @param dbe * @return * @throws SQLException */ private static Connection getOracleConnection(DatabaseEntity dbe) { String url; if (!StringUtils.isEmpty(dbe.getSid())) { url = String.format("jdbc:oracle:thin:@%s:%s:%s", dbe.getIp(), dbe.getPort(), dbe.getSid()); } else if (!StringUtils.isEmpty(dbe.getServerName())) { url = String.format("jdbc:oracle:thin:@//%s:%s/%s", dbe.getIp(), dbe.getPort(), dbe.getServerName()); } else { throw new BaseException(ErrorCode.GET_ORACLE_SID_SERVERNAME_EMPTY); } return getConnection(url, dbe); } /** * 获取Informix 连接 * * @param dbe * @return * @throws SQLException */ private static Connection getInformixConnection(DatabaseEntity dbe) throws BaseException { String url = String.format("jdbc:informix-sqli://%s:%s/%s:INFORMIXSERVER=%s;%s", dbe.getIp(), dbe.getPort(), dbe.getDbName(), dbe.getDbInstance(), dbe.getCustomParams()); return getConnection(url, dbe); } /** * 获取连接 * * @param url jdbc连接url * @param dbe 数据库基本数据 * @return * @throws SQLException */ private static Connection getConnection(String url, DatabaseEntity dbe) throws BaseException { try { //获获取连接 Connection connection = DriverManager.getConnection(url, dbe.getUserName(), dbe.getPassWord()); //测试连接 if (!connectionValidity(connection, dbe.getDbType())) { throw new BaseException(ErrorCode.GET_CONNECTION_FAIL); } return connection; } catch (BaseException e) { throw e; } catch (Exception e) { e.printStackTrace(); log.error("获取链接失败", e); log.error(url); throw new BaseException(ErrorCode.GET_CONNECTION_FAIL); } } /** * 验证连接有效性 * * @param connection * @param databaseType * @return */ public static boolean connectionValidity(Connection connection, DatabaseType databaseType) { try { String validationQuery = databaseType.getValidationQuery(); try (PreparedStatement pst = connection.prepareStatement(validationQuery)) { boolean execute = pst.execute(); return execute; } } catch (Exception e) { log.error("验证连接有效性失败", e); return false; } } }