package com.skyline.energy.utils;
import java.io.StringWriter;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.skyline.energy.dataaccess.jdbc.ConnectionHolder;
import com.skyline.energy.dataaccess.jdbc.JdbcTransactionContext;
import com.skyline.energy.dataaccess.jdbc.JdbcTransactionManager;
import com.skyline.energy.dataaccess.jdbc.SqlExecuteContextHolder;
import com.skyline.energy.dataaccess.jdbc.SqlType;
import com.skyline.energy.datasource.DistributeDataSource;
import com.skyline.energy.exception.DataAccessException;
import com.skyline.energy.exception.TransactionException;
import com.skyline.energy.transaction.IsolationLevel;
import com.skyline.energy.transaction.TransactionContextHolder;
import com.skyline.energy.transaction.TransactionDefinition;
public class JdbcUtils {
private static final Log LOGGER = LogFactory.getLog(JdbcUtils.class);
public static Connection getConnection(DataSource dataSource) {
try {
if (dataSource instanceof DistributeDataSource) {
return doGetConnection(((DistributeDataSource) dataSource).getDataSource(), true);
} else {
return doGetConnection(dataSource, false);
}
} catch (SQLException ex) {
throw new DataAccessException("Could not get JDBC Connection", ex);
}
}
public static Connection doGetConnection(DataSource dataSource, boolean distribute) throws SQLException {
CommonUtils.assertNotNull(dataSource, "No DataSource specified");
JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
if (txObject == null) {// 不支持事务
return dataSource.getConnection();
}
boolean shouldAddCount = false;
Connection con = null;
ConnectionHolder conHolder = txObject.getConnectionHolder();
if (conHolder != null && conHolder.getCurrentConnection() != null) {// 已经存在事务
// 判断是否可以使用上次的connection
if (dataSource.equals(txObject.getTxDataSource())) {// 只对同一数据源进行事务处理
con = conHolder.getCurrentConnection();
shouldAddCount = true;
} else {
con = dataSource.getConnection();
}
} else { // 第一次开始事务
JdbcTransactionManager txManager = (JdbcTransactionManager) txObject.getTxManager();
if (distribute) {// 分布式数据源，只在第一此写操作时启动事务
if (needBeginTransaction()) {
beginNewTransaction(dataSource, txObject);
con = txObject.getConnectionHolder().getCurrentConnection();
shouldAddCount = true;
} else {// 创建独立于事务之外的connection
con = dataSource.getConnection();
}
} else if (txManager.isLazyBegin()) {// 仅懒启动式事务，第一次启动事务
beginNewTransaction(dataSource, txObject);
con = txObject.getConnectionHolder().getCurrentConnection();
shouldAddCount = true;
} else {
throw new TransactionException("not lazyBegin transaction bu no currentConnection exist");
}
conHolder = txObject.getConnectionHolder(); // 重新获取ConnectionHolder
}
if (shouldAddCount) {
conHolder.addCounter();
}
return con;
}
private static void beginNewTransaction(DataSource dataSource, JdbcTransactionContext txObject) {
Connection con = null;
try {
Connection newCon = dataSource.getConnection();
LOGGER.debug("Acquired Connection [" + newCon + "] for JDBC transaction");
ConnectionHolder conHolder = new ConnectionHolder(newCon);
txObject.setConnectionHolder(conHolder);
txObject.setTxDataSource(dataSource);
con = conHolder.getCurrentConnection();
JdbcUtils.prepareConnectionForTransaction(newCon, txObject.getCurrentDefinition());
} catch (Exception ex) {
releaseConnection(con);
throw new TransactionException("Could not open JDBC Connection for transaction", ex);
}
}
private static boolean needBeginTransaction() {
return (SqlExecuteContextHolder.getContext().getSqlType() == SqlType.UPDATE);
}
public static PreparedStatement createPreparedStatement(final Connection con, final String sql,
final boolean returnKeys, final Object... args) throws SQLException {
PreparedStatement ps = null;
if (returnKeys) {
ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
} else {
ps = con.prepareStatement(sql);
}
if (args != null) {
for (int i = 0; i < args.length; i++) {
Object arg = args[i];
setParameterValue(ps, i + 1, arg);
}
}
return ps;
}
public static void setParameterValue(PreparedStatement ps, int paramIndex, Object inValue) throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Setting SQL statement parameter value: column index " + paramIndex + ", parameter value ["
+ inValue + "], value class [" + (inValue != null ? inValue.getClass().getName() : "null") + "]");
}
if (inValue == null) {
setNull(ps, paramIndex);
} else {
setValue(ps, paramIndex, inValue);
}
}
private static void setNull(PreparedStatement ps, int paramIndex) throws SQLException {
boolean useSetObject = false;
int sqlType = Types.NULL;
try {
DatabaseMetaData dbmd = ps.getConnection().getMetaData();
String databaseProductName = dbmd.getDatabaseProductName();
String jdbcDriverName = dbmd.getDriverName();
if (databaseProductName.startsWith("Informix") || jdbcDriverName.startsWith("Microsoft SQL Server")) {
useSetObject = true;
} else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
|| jdbcDriverName.startsWith("SQLServer") || jdbcDriverName.startsWith("Apache Derby")) {
sqlType = Types.VARCHAR;
}
} catch (Throwable ex) {
LOGGER.debug("Could not check database or driver name", ex);
}
if (useSetObject) {
ps.setObject(paramIndex, null);
} else {
ps.setNull(paramIndex, sqlType);
}
}
private static void setValue(PreparedStatement ps, int paramIndex, Object inValue) throws SQLException {
if (isTypeString(inValue.getClass())) {
ps.setString(paramIndex, inValue.toString());
} else if (isTypeDate(inValue.getClass())) {
ps.setTimestamp(paramIndex, new Timestamp(((java.util.Date) inValue).getTime()));
} else if (inValue instanceof Calendar) {
Calendar cal = (Calendar) inValue;
ps.setTimestamp(paramIndex, new Timestamp(cal.getTime().getTime()), cal);
} else {
// Fall back to generic setObject call without SQL type specified.
ps.setObject(paramIndex, inValue);
}
}
public static void commitQuietly(Connection con) {
if (con != null) {
try {
if (!con.getAutoCommit()) {
con.commit();
}
} catch (SQLException ex) {
LOGGER.debug("Could not close JDBC Connection");
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
LOGGER.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
}
public static void prepareConnectionForTransaction(Connection con, TransactionDefinition definition)
throws SQLException {
CommonUtils.assertNotNull(con, "No Connection specified");
// Set read-only flag.
if (definition != null && definition.isReadOnly()) {
try {
LOGGER.debug("Setting JDBC Connection [" + con + "] read-only");
con.setReadOnly(true);
} catch (SQLException ex) {
Throwable exToCheck = ex;
while (exToCheck != null) {
if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
// Assume it's a connection timeout that would otherwise get lost: e.g. from JDBC 4.0
throw ex;
}
exToCheck = exToCheck.getCause();
}
// "read-only not supported" SQLException -> ignore, it's just a hint anyway
LOGGER.debug("Could not set JDBC Connection read-only", ex);
} catch (RuntimeException ex) {
Throwable exToCheck = ex;
while (exToCheck != null) {
if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
// Assume it's a connection timeout that would otherwise get lost: e.g. from Hibernate
throw ex;
}
exToCheck = exToCheck.getCause();
}
// "read-only not supported" UnsupportedOperationException -> ignore, it's just a hint anyway
LOGGER.debug("Could not set JDBC Connection read-only", ex);
}
}
if (definition != null && definition.getIsolationLevel() != IsolationLevel.ISOLATION_DEFAULT) {
LOGGER.debug("Changing isolation level of JDBC Connection [" + con + "] to "
+ definition.getIsolationLevel());
IsolationLevel currentIsolation = IsolationLevel.toIsolationLevel(con.getTransactionIsolation());
if (currentIsolation != definition.getIsolationLevel()) {
con.setTransactionIsolation(definition.getIsolationLevel().toJdbcIsolation());
}
}
if (con.getAutoCommit()) {
LOGGER.debug("Switching JDBC Connection [" + con + "] to manual commit");
con.setAutoCommit(false);
}
}
/**
* Close the given JDBC Connection and ignore any thrown exception. This is useful for typical finally blocks in
* manual JDBC code.
*
* @param con
* the JDBC Connection to close (may be <code>null</code>)
*/
public static void closeConnection(Connection con) {
if (con != null) {
try {
if (!con.isClosed()) { // 关闭没有关闭
con.close();
}
} catch (SQLException ex) {
LOGGER.debug("Could not close JDBC Connection", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
LOGGER.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
}
/**
* Close the given JDBC Statement and ignore any thrown exception. This is useful for typical finally blocks in
* manual JDBC code.
*
* @param stmt
* the JDBC Statement to close (may be <code>null</code>)
*/
public static void closeStatement(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
LOGGER.trace("Could not close JDBC Statement", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
LOGGER.trace("Unexpected exception on closing JDBC Statement", ex);
}
}
}
/**
* Close the given JDBC ResultSet and ignore any thrown exception. This is useful for typical finally blocks in
* manual JDBC code.
*
* @param rs
* the JDBC ResultSet to close (may be <code>null</code>)
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
LOGGER.trace("Could not close JDBC ResultSet", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
LOGGER.trace("Unexpected exception on closing JDBC ResultSet", ex);
}
}
}
/**
* Return whether the given JDBC driver supports JDBC 2.0 batch updates.
* <p>
* Typically invoked right before execution of a given set of statements: to decide whether the set of SQL
* statements should be executed through the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
* <p>
* Logs a warning if the "supportsBatchUpdates" methods throws an exception and simply returns <code>false</code> in
* that case.
*
* @param con
* the Connection to check
* @return whether JDBC 2.0 batch updates are supported
* @see java.sql.DatabaseMetaData#supportsBatchUpdates()
*/
public static boolean supportsBatchUpdates(Connection con) {
try {
DatabaseMetaData dbmd = con.getMetaData();
if (dbmd != null) {
if (dbmd.supportsBatchUpdates()) {
LOGGER.debug("JDBC driver supports batch updates");
return true;
} else {
LOGGER.debug("JDBC driver does not support batch updates");
}
}
} catch (SQLException ex) {
LOGGER.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
} catch (AbstractMethodError err) {
LOGGER.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
}
return false;
}
public static void releaseConnection(Connection con) {
if (con != null) {
JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
if (txObject == null) {// 不支持事务
closeConnection(con);
}
ConnectionHolder conHolder = txObject.getConnectionHolder();
if (conHolder != null && conHolder.getCurrentConnection() != null) {
Connection conInTx = conHolder.getCurrentConnection();
if (conInTx.equals(con)) {// 当前连接和事务处于同一事务，不关闭连接
conHolder.reduceCounter();
return;
}
}
// 不在同一事务中，直接关闭连接
closeConnection(con);
}
}
public static void commitTransaction(Connection con) throws SQLException {
JdbcTransactionContext txObject = (JdbcTransactionContext) TransactionContextHolder.getContext();
if (txObject == null) {// 不支持事务
throw new TransactionException("Transaction not exist.");
}
ConnectionHolder conHolder = txObject.getConnectionHolder();
if (conHolder.isOpen()) {// 还有后续事务，不提交
return;
}
if (con != null) {
LOGGER.debug("Transaction commit.");
try {
con.commit();
} catch (SQLException e) {
throw e;
} finally {
closeConnection(con);
}
}
}
public static void rollbackTransaction(Connection con) throws SQLException {
if (con != null) {
LOGGER.debug("Transaction rollback.");
try {
con.rollback();
} catch (SQLException e) {
throw e;
} finally {
closeConnection(con);
}
}
}
public static boolean isTypeString(Class<?> type) {
// Consider any CharSequence (including StringBuffer and StringBuilder) as a String.
return (CharSequence.class.isAssignableFrom(type) || StringWriter.class.isAssignableFrom(type));
}
/**
* Check whether the given value is a <code>java.util.Date</code> (but not one of the JDBC-specific subclasses).
*/
public static boolean isTypeDate(Class<?> type) {
return (java.util.Date.class.isAssignableFrom(type) && !(java.sql.Date.class.isAssignableFrom(type)
|| java.sql.Time.class.isAssignableFrom(type) || java.sql.Timestamp.class.isAssignableFrom(type)));
}
public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
String name = resultSetMetaData.getColumnLabel(columnIndex);
if (name == null || name.length() < 1) {
name = resultSetMetaData.getColumnName(columnIndex);
}
return name;
}
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
Object obj = rs.getObject(index);
String className = null;
if (obj != null) {
className = obj.getClass().getName();
}
if (obj instanceof Blob) {
obj = rs.getBytes(index);
} else if (obj instanceof Clob) {
obj = rs.getString(index);
} else if (className != null
&& ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className))) {
obj = rs.getTimestamp(index);
} else if (className != null && className.startsWith("oracle.sql.DATE")) {
String metaDataClassName = rs.getMetaData().getColumnClassName(index);
if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
obj = rs.getTimestamp(index);
} else {
obj = rs.getDate(index);
}
} else if (obj != null && obj instanceof java.sql.Date) {
if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
obj = rs.getTimestamp(index);
}
}
return obj;
}
}