package com.product.datasource.connection;
import com.product.common.lang.StringUtils;
import com.product.core.config.Global;
import com.product.core.exception.BaseException;
import com.product.datasource.config.DataBaseType;
import com.product.datasource.config.ErrorCode;
import com.product.datasource.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.getDataBaseType() == null) {
throw new BaseException(ErrorCode.UNKNOWN_DATABASE_TYPE);
}
//瑁呰浇椹卞姩
Class.forName(dbe.getDataBaseType().getDriver());
if (DataBaseType.MYSQL.equals(dbe.getDataBaseType())) {
// MYSQL
return getMysqlConnection(dbe);
} else if (DataBaseType.ORACLE.equals(dbe.getDataBaseType())) {
// ORACLE
return getOracleConnection(dbe);
} else if (DataBaseType.SQLSERVER.equals(dbe.getDataBaseType())) {
// SQLSERVER
return getSqlServerConnection(dbe);
} else if (DataBaseType.INFORMIX.equals(dbe.getDataBaseType())) {
// INFORMIX
return getInformixConnection(dbe);
} else if (DataBaseType.PSQL.equals(dbe.getDataBaseType())) {
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 (DriverTypeEnum.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 (DriverTypeEnum.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.getDataBaseType())) {
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 DriverTypeEnum
* @return
*/
public static boolean connectionValidity(Connection connection, DataBaseType DriverTypeEnum) {
try {
String validationQuery = DriverTypeEnum.getValidationQuery();
try (PreparedStatement pst = connection.prepareStatement(validationQuery)) {
boolean execute = pst.execute();
return execute;
}
} catch (Exception e) {
log.error("楠岃瘉杩炴帴鏈夋晥鎬уけ璐�", e);
return false;
}
}
}