@Test
public void testConnection() throws Exception {
Connection conn = getConnection();
assertNull(conn.getMetaData());
assertFalse(conn.getAutoCommit());
conn.setAutoCommit(true);
assertTrue(conn.getAutoCommit());
conn.setAutoCommit(false);
assertTrue(conn.isReadOnly());
conn.setReadOnly(true);
assertTrue(conn.isReadOnly());
assertEquals(Connection.TRANSACTION_NONE, conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
assertEquals(Connection.TRANSACTION_READ_UNCOMMITTED, conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
assertNull(conn.getWarnings());
conn.clearWarnings();
assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability());
conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());
conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
conn.commit();
conn.rollback();
assertTrue(conn.isValid(0));
conn.setClientInfo("prop1", "value1");
Properties props = new Properties();
props.setProperty("prop2", "value2");
props.setProperty("prop3", "value3");
conn.setClientInfo(props);
assertEquals("value1", conn.getClientInfo("prop1"));
assertEquals("value2", conn.getClientInfo("prop2"));
assertEquals("value3", conn.getClientInfo("prop3"));
Properties props2 = conn.getClientInfo();
assertEquals("value1", props2.getProperty("prop1"));
assertEquals("value2", props2.getProperty("prop2"));
assertEquals("value3", props2.getProperty("prop3"));
assertNotNull(((JcrJdbcConnection) conn).getJcrSession());
assertFalse(conn.isClosed());
conn.close();
assertTrue(conn.isClosed());
}
/**
* {@inheritDoc}
*
* @see org.hibernate.service.jdbc.connections.spi.ConnectionProvider#getConnection()
*/
public Connection getConnection() throws SQLException {
Connection connection = this.pool.getConnection();
// set the Transaction Isolation if defined
try {
// set the Transaction Isolation if defined
if ((this.isolation != null)
&& (connection.getTransactionIsolation() != this.isolation.intValue())) {
connection.setTransactionIsolation(this.isolation.intValue());
}
// toggle autoCommit to false if set
if (connection.getAutoCommit() != this.autocommit) {
connection.setAutoCommit(this.autocommit);
}
return connection;
} catch (SQLException e) {
try {
connection.close();
} catch (Exception e2) {
logger.warn(
"Setting connection properties failed and closing this connection failed again", e);
}
throw e;
}
}
@Override
public void run() {
Connection conn = null;
try {
Driver d = new SimpleDriver();
conn = d.connect("jdbc:simpledb://localhost", null);
// conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = conn.createStatement();
// try {
// Thread.sleep(5000);
// } catch (InterruptedException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
for (int i = 0; i < 3; i++) {
String cmd;
if (i % 2 == 0) cmd = "update STUDENT set MajorId=30 " + "where SName = 'amy'";
else cmd = "update STUDENT set MajorId=20 " + "where SName = 'amy'";
stmt.executeUpdate(cmd);
System.out.println("Amy's major changed.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
System.out.println(conn.getTransactionIsolation());
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public C3P0PooledConnection(
Connection con,
ConnectionTester connectionTester,
boolean autoCommitOnClose,
boolean forceIgnoreUnresolvedTransactions,
ConnectionCustomizer cc,
String pdsIdt)
throws SQLException {
try {
if (cc != null) cc.onAcquire(con, pdsIdt);
} catch (Exception e) {
throw SqlUtils.toSQLException(e);
}
this.physicalConnection = con;
this.connectionTester = connectionTester;
this.autoCommitOnClose = autoCommitOnClose;
this.forceIgnoreUnresolvedTransactions = forceIgnoreUnresolvedTransactions;
this.supports_setTypeMap =
C3P0ImplUtils.supportsMethod(con, "setTypeMap", new Class[] {Map.class});
this.supports_setHoldability =
C3P0ImplUtils.supportsMethod(con, "setHoldability", new Class[] {int.class});
this.dflt_txn_isolation = con.getTransactionIsolation();
this.dflt_catalog = con.getCatalog();
this.dflt_holdability =
(supports_setHoldability ? con.getHoldability() : ResultSet.CLOSE_CURSORS_AT_COMMIT);
}
/**
* Execute transaction.
*
* @param config the Config object
* @param transactionLevel the transaction level
* @param atom the atom operation
* @return true if transaction executing succeed otherwise false
*/
boolean tx(Config config, int transactionLevel, IAtom atom) {
Connection conn = config.getThreadLocalConnection();
if (conn != null) { // Nested transaction support
try {
if (conn.getTransactionIsolation() < transactionLevel)
conn.setTransactionIsolation(transactionLevel);
boolean result = atom.run();
if (result) return true;
throw new NestedTransactionHelpException(
"Notice the outer transaction that the nested transaction return false"); // important:can not return false
} catch (SQLException e) {
throw new ActiveRecordException(e);
}
}
Boolean autoCommit = null;
try {
conn = config.getConnection();
autoCommit = conn.getAutoCommit();
config.setThreadLocalConnection(conn);
conn.setTransactionIsolation(transactionLevel);
conn.setAutoCommit(false);
boolean result = atom.run();
if (result) conn.commit();
else conn.rollback();
return result;
} catch (NestedTransactionHelpException e) {
if (conn != null)
try {
conn.rollback();
} catch (Exception e1) {
LogKit.error(e1.getMessage(), e1);
}
LogKit.logNothing(e);
return false;
} catch (Throwable t) {
if (conn != null)
try {
conn.rollback();
} catch (Exception e1) {
LogKit.error(e1.getMessage(), e1);
}
throw t instanceof RuntimeException ? (RuntimeException) t : new ActiveRecordException(t);
} finally {
try {
if (conn != null) {
if (autoCommit != null) conn.setAutoCommit(autoCommit);
conn.close();
}
} catch (Throwable t) {
LogKit.error(
t.getMessage(),
t); // can not throw exception here, otherwise the more important exception in previous
// catch block can not be thrown
} finally {
config.removeThreadLocalConnection(); // prevent memory leak
}
}
}
/**
* Get a new connection from the datasource, check/set autocommit == false and isolation level
* according to {@link #DEFAULT_ISOLATION_LEVEL}
*
* @throws SQLException
*/
protected Connection getConnection() throws SQLException {
Connection con = getDataSource().getConnection();
if (con.getAutoCommit()) con.setAutoCommit(false);
if (con.getTransactionIsolation() != DEFAULT_ISOLATION_LEVEL)
con.setTransactionIsolation(DEFAULT_ISOLATION_LEVEL);
return con;
}
public TransactionIsolationLevel getTransactionIsolationLevel() {
try {
return TransactionIsolationLevel.valueOf(connection.getTransactionIsolation());
} catch (SQLException e) {
throw new UnableToManipulateTransactionIsolationLevelException(
"unable to access current setting", e);
}
}
public int getTransactionIsolation() throws SQLException {
String methodCall = "getTransactionIsolation()";
try {
return reportReturn(methodCall, realConnection.getTransactionIsolation());
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
}
/**
* FIXME Skipped for Postgres Test method for {@link org.melati.poem.dbms.Dbms#
* getConnection(java.lang.String, java.lang.String, java.lang.String)}.
*
* @throws Exception
*/
public void testGetConnection() throws Exception {
Connection c = PoemThread.transaction().getDatabase().getCommittedConnection();
if (c.getClass().getName().indexOf("postgresql") == -1) {
// System.err.println(c.getTransactionIsolation() + ">=" +
// Connection.TRANSACTION_READ_COMMITTED);
assertTrue(
c.getTransactionIsolation()
+ " is not >= "
+ Connection.TRANSACTION_READ_COMMITTED
+ " for database "
+ PoemThread.transaction().getDatabase()
+ " using "
+ PoemThread.transaction().getDatabase().getDbms()
+ " for connection "
+ c.getClass().getName(),
c.getTransactionIsolation() >= Connection.TRANSACTION_READ_COMMITTED);
}
}
public void setTransactionIsolation(int level) {
try {
if (connection.getTransactionIsolation() == level) {
// already set, noop
return;
}
connection.setTransactionIsolation(level);
} catch (SQLException e) {
throw new UnableToManipulateTransactionIsolationLevelException(level, e);
}
}
public DruidConnectionHolder(DruidAbstractDataSource dataSource, Connection conn)
throws SQLException {
this.dataSource = dataSource;
this.conn = conn;
this.connectTimeMillis = System.currentTimeMillis();
this.lastActiveTimeMillis = connectTimeMillis;
this.underlyingAutoCommit = conn.getAutoCommit();
{
boolean initUnderlyHoldability = !holdabilityUnsupported;
if (JdbcConstants.SYBASE.equals(dataSource.getDbType()) //
|| JdbcConstants.DB2.equals(dataSource.getDbType()) //
) {
initUnderlyHoldability = false;
}
if (initUnderlyHoldability) {
try {
this.underlyingHoldability = conn.getHoldability();
} catch (UnsupportedOperationException e) {
holdabilityUnsupported = true;
LOG.warn("getHoldability unsupported", e);
} catch (SQLFeatureNotSupportedException e) {
holdabilityUnsupported = true;
LOG.warn("getHoldability unsupported", e);
} catch (SQLException e) {
// bug fixed for hive jdbc-driver
if ("Method not supported".equals(e.getMessage())) {
holdabilityUnsupported = true;
}
LOG.warn("getHoldability error", e);
}
}
}
this.underlyingReadOnly = conn.isReadOnly();
try {
this.underlyingTransactionIsolation = conn.getTransactionIsolation();
} catch (SQLException e) {
// compartible for alibaba corba
if (!"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException"
.equals(e.getClass().getName())) {
throw e;
}
}
this.defaultHoldability = underlyingHoldability;
this.defaultTransactionIsolation = underlyingTransactionIsolation;
this.defaultAutoCommit = underlyingAutoCommit;
this.defaultReadOnly = underlyingReadOnly;
}
private void checkAndAdjustConnection(ConnectionType type, Connection connection) {
// Set autocommit to off
// When using datasource with sharable connection , it could throw exception or log warning
try {
if (connection.getAutoCommit()) {
log.info("connection.setAutoCommit(false)");
connection.setAutoCommit(false);
}
} catch (SQLException e) {
throw new ConnectorException("Cannot check or adjust connection autocommit flag", e);
}
// Set Transaction Isolation
// When using datasource with sharable connection , it could throw exception or log warning
try {
if (connection.getTransactionIsolation() == Connection.TRANSACTION_NONE
|| connection.getTransactionIsolation() == Connection.TRANSACTION_READ_UNCOMMITTED) {
log.info("connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED)");
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
}
} catch (SQLException e) {
throw new ConnectorException("Cannot check or adjust transaction isolation settings", e);
}
}
private Connection getRawConnectionForReadOnly(Options options) {
try {
Connection conn = target.getConnection(options);
conn.setAutoCommit(true);
conn.setReadOnly(true);
if (session.getTransactionIsolation() != 0) {
if (conn.getTransactionIsolation() != session.getTransactionIsolation()) {
conn.setTransactionIsolation(session.getTransactionIsolation());
}
}
return conn;
} catch (SQLException e) {
throw DbException.convert(e);
}
}
/**
* @param options
* @return
* @throws SQLException
*/
private Connection getRawConnection(Options options) throws DbException {
Connection conn = target.getConnection(options);
try {
if (conn.getAutoCommit() != session.getAutoCommit()) {
conn.setAutoCommit(session.getAutoCommit());
}
if (session.getTransactionIsolation() != 0) {
if (conn.getTransactionIsolation() != session.getTransactionIsolation()) {
conn.setTransactionIsolation(session.getTransactionIsolation());
}
}
if (conn.isReadOnly() != session.isReadOnly()) {
conn.setReadOnly(session.isReadOnly());
}
} catch (Exception e) {
throw DbException.convert(e);
}
return conn;
}
public void backup(int month, int year, String description) throws Exception {
int trans = Connection.TRANSACTION_READ_COMMITTED;
try {
connection.setAutoCommit(false);
trans = connection.getTransactionIsolation();
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
// content
// 1: create master
java.util.Date date = new java.util.Date();
createMaster(month, year, description, date);
long masterId = getMaxIndex(IDBConstants.TABLE_PAYROLL_CATEGORY_HISTORY_MASTER);
// payroll category
IHRMSQL iSql = new HRMSQLSAP();
PayrollCategory[] categories = iSql.getAllPayrollCategory(connection);
saveCategory(masterId, categories);
// payroll category employee
saveCategoryEmployee(masterId, categories);
// payroll category component
saveCategoryComponent(masterId, categories);
// employee component
saveEmployeeComponent(masterId, categories);
connection.commit();
connection.setAutoCommit(true);
connection.setTransactionIsolation(trans);
} catch (SQLException e) {
connection.rollback();
connection.setAutoCommit(true);
connection.setTransactionIsolation(trans);
e.printStackTrace();
throw new Exception("Failed to backup. ", e);
}
}
/**
* Prepare the given Connection with the given transaction semantics.
*
* @param con the Connection to prepare
* @param definition the transaction definition to apply
* @return the previous isolation level, if any
* @throws SQLException if thrown by JDBC methods
* @see #resetConnectionAfterTransaction
*/
public static Integer prepareConnectionForTransaction(
Connection con, TransactionDefinition definition) throws SQLException {
Assert.notNull(con, "No Connection specified");
// Set read-only flag.
if (definition != null && definition.isReadOnly()) {
try {
if (logger.isDebugEnabled()) {
logger.debug("Setting JDBC Connection [" + con + "] read-only");
}
con.setReadOnly(true);
} catch (Throwable ex) {
// SQLException or UnsupportedOperationException
// -> ignore, it's just a hint anyway.
logger.debug("Could not set JDBC Connection read-only", ex);
}
}
// Apply specific isolation level, if any.
Integer previousIsolationLevel = null;
if (definition != null
&& definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
if (logger.isDebugEnabled()) {
logger.debug(
"Changing isolation level of JDBC Connection ["
+ con
+ "] to "
+ definition.getIsolationLevel());
}
previousIsolationLevel = con.getTransactionIsolation();
con.setTransactionIsolation(definition.getIsolationLevel());
}
return previousIsolationLevel;
}
@Override
public int getTransactionIsolation() throws SQLException {
return _wrapped.getTransactionIsolation();
}
private void testTableLevelLocking() throws SQLException {
deleteDb("transactionIsolation");
conn1 = getConnection("transactionIsolation");
assertEquals(Connection.TRANSACTION_READ_COMMITTED, conn1.getTransactionIsolation());
conn1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
assertEquals(Connection.TRANSACTION_SERIALIZABLE, conn1.getTransactionIsolation());
conn1.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
assertEquals(Connection.TRANSACTION_READ_UNCOMMITTED, conn1.getTransactionIsolation());
assertSingleValue(conn1.createStatement(), "CALL LOCK_MODE()", 0);
conn1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
assertSingleValue(conn1.createStatement(), "CALL LOCK_MODE()", 3);
assertEquals(Connection.TRANSACTION_READ_COMMITTED, conn1.getTransactionIsolation());
conn1.createStatement().execute("SET LOCK_MODE 1");
assertEquals(Connection.TRANSACTION_SERIALIZABLE, conn1.getTransactionIsolation());
conn1.createStatement().execute("CREATE TABLE TEST(ID INT)");
conn1.createStatement().execute("INSERT INTO TEST VALUES(1)");
conn1.setAutoCommit(false);
conn2 = getConnection("transactionIsolation");
conn2.setAutoCommit(false);
conn1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
// serializable: just reading
assertSingleValue(conn1.createStatement(), "SELECT * FROM TEST", 1);
assertSingleValue(conn2.createStatement(), "SELECT * FROM TEST", 1);
conn1.commit();
conn2.commit();
// serializable: write lock
conn1.createStatement().executeUpdate("UPDATE TEST SET ID=2");
try {
assertSingleValue(conn2.createStatement(), "SELECT * FROM TEST", 1);
fail("Expected lock timeout");
} catch (SQLException e) {
assertKnownException(e);
}
conn1.commit();
conn2.commit();
conn1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
// read-committed: #1 read, #2 update, #1 read again
assertSingleValue(conn1.createStatement(), "SELECT * FROM TEST", 2);
conn2.createStatement().executeUpdate("UPDATE TEST SET ID=3");
conn2.commit();
assertSingleValue(conn1.createStatement(), "SELECT * FROM TEST", 3);
conn1.commit();
// read-committed: #1 read, #2 read, #2 update, #1 delete
assertSingleValue(conn1.createStatement(), "SELECT * FROM TEST", 3);
assertSingleValue(conn2.createStatement(), "SELECT * FROM TEST", 3);
conn2.createStatement().executeUpdate("UPDATE TEST SET ID=4");
try {
conn1.createStatement().executeUpdate("DELETE FROM TEST");
fail("Expected lock timeout");
} catch (SQLException e) {
assertKnownException(e);
}
conn2.commit();
conn1.commit();
assertSingleValue(conn1.createStatement(), "SELECT * FROM TEST", 4);
assertSingleValue(conn2.createStatement(), "SELECT * FROM TEST", 4);
conn1.close();
conn2.close();
deleteDb("transactionIsolation");
}
/**
* Pass thru method to the wrapped jdbc 1.x {@link java.sql.Connection}.
*
* @exception SQLException if this connection is closed or an error occurs in the wrapped
* connection.
*/
public int getTransactionIsolation() throws SQLException {
assertOpen();
return connection.getTransactionIsolation();
}
public int getTransactionIsolation() throws SQLException {
return con.getTransactionIsolation();
}
/**
* Get Cached Connection
*
* @param connection info
* @param autoCommit true if autocommit connection
* @param transactionIsolation Connection transaction level
* @return connection or null
* @throws Exception
*/
@Override
public Connection getCachedConnection(
CConnection connection, boolean autoCommit, int transactionIsolation) throws Exception {
Connection conn = null;
Exception exception = null;
try {
if (m_ds == null) getDataSource(connection);
//
try {
conn = m_ds.getConnection();
if (conn != null) {
if (conn.getTransactionIsolation() != transactionIsolation)
conn.setTransactionIsolation(transactionIsolation);
if (conn.getAutoCommit() != autoCommit) conn.setAutoCommit(autoCommit);
// conn.setDefaultRowPrefetch(20); // 10 default - reduces round
// trips
}
} catch (Exception e) {
exception = e;
conn = null;
if (DBException.isInvalidUserPassError(e)) {
// log might cause infinite loop since it will try to acquire database connection again
/*
log.severe("Cannot connect to database: "
+ getConnectionURL(connection)
+ " - UserID=" + connection.getDbUid());
*/
System.err.println(
"Cannot connect to database: "
+ getConnectionURL(connection)
+ " - UserID="
+ connection.getDbUid());
}
}
if (conn == null && exception != null) {
// log might cause infinite loop since it will try to acquire database connection again
/*
log.log(Level.SEVERE, exception.toString());
log.fine(toString()); */
System.err.println(exception.toString());
}
} catch (Exception e) {
exception = e;
}
try {
if (conn != null) {
int numConnections = m_ds.getNumBusyConnections();
if (numConnections >= m_maxbusyconnections && m_maxbusyconnections > 0) {
log.warning(getStatus());
// hengsin: make a best effort to reclaim leak connection
Runtime.getRuntime().runFinalization();
}
}
} catch (Exception ex) {
}
if (exception != null) throw exception;
return conn;
} // getCachedConnection
@Override
public int getTransactionIsolation() throws SQLException {
checkState();
return conn.getTransactionIsolation();
}
@Test
public void testConnectionWhenClosed() throws Exception {
Connection conn = getConnection();
conn.close();
try {
conn.createStatement();
fail();
} catch (SQLException ignore) {
}
try {
conn.createStatement(1, 1);
fail();
} catch (SQLException ignore) {
}
try {
conn.createStatement(1, 1, 1);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null, 1, 1);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null, 1, 1, 1);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null, 1);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null, (int[]) null);
fail();
} catch (SQLException ignore) {
}
try {
conn.prepareStatement(null, (String[]) null);
fail();
} catch (SQLException ignore) {
}
try {
conn.nativeSQL(null);
fail();
} catch (SQLException ignore) {
}
try {
conn.setAutoCommit(true);
fail();
} catch (SQLException ignore) {
}
try {
conn.getAutoCommit();
fail();
} catch (SQLException ignore) {
}
try {
conn.commit();
fail();
} catch (SQLException ignore) {
}
try {
conn.rollback();
fail();
} catch (SQLException ignore) {
}
try {
conn.setReadOnly(true);
fail();
} catch (SQLException ignore) {
}
try {
conn.isReadOnly();
fail();
} catch (SQLException ignore) {
}
try {
conn.setTransactionIsolation(1);
fail();
} catch (SQLException ignore) {
}
try {
conn.getTransactionIsolation();
fail();
} catch (SQLException ignore) {
}
try {
conn.setHoldability(1);
fail();
} catch (SQLException ignore) {
}
try {
conn.getHoldability();
fail();
} catch (SQLException ignore) {
}
try {
conn.setClientInfo("var1", "value1");
fail();
} catch (SQLException ignore) {
}
try {
conn.getClientInfo("var1");
fail();
} catch (SQLException ignore) {
}
try {
conn.setClientInfo(null);
fail();
} catch (SQLException ignore) {
}
try {
conn.getClientInfo();
fail();
} catch (SQLException ignore) {
}
}