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;
|
}
|
|
}
|
|
|
}
|
