Java Code Examples for javax.sql.PooledConnection#close()

/**
 * Do some basic verification on a connection obtained from the data source.
 *
 * @param cDs data source to get connection from
 * @throws SQLException if a JDBC operation fails
 */
private void verifyConnection(ClientConnectionPoolDataSource cDs)
        throws SQLException {
    J2EEDataSource.setBeanProperty(cDs, "createDatabase", "create");
    PooledConnection pc = cDs.getPooledConnection();
    // Get a connection and make sure we can access the database.
    Connection con = pc.getConnection();
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select * from sys.systables");
    JDBC.assertDrainResultsHasData(rs);
    PreparedStatement ps1 = con.prepareStatement("values 31");
    JDBC.assertSingleValueResultSet(ps1.executeQuery(), "31");
    ps1.close();
    PreparedStatement ps2 = con.prepareStatement("values 31");
    // The physical statement is supposed to be the same, but not the
    // logical prepared statements (if pooling is used).
    assertNotSame(ps1, ps2);
    JDBC.assertSingleValueResultSet(ps2.executeQuery(), "31");
    // Close everything
    stmt.close();
    ps2.close();
    con.close();
    pc.close();
}
 

/**
 * Closes all unused pooled connections.
 */
public synchronized void dispose() throws SQLException {
	if (isDisposed) {
		return;
	}
	isDisposed = true;
	SQLException e = null;
	while (!recycledConnections.isEmpty()) {
		PooledConnection pconn = recycledConnections.remove();
		try {
			pconn.close();
		} catch (SQLException e2) {
			if (e == null) {
				e = e2;
			}
		}
	}
	if (e != null) {
		throw e;
	}
}
 

/**
 * Test sequence for testing if the connection holdability is reset.
 *
 * @param closeConnection determines if the logical connection is
 *      explicitly closed before a new one is obtained
 * @throws SQLException if something goes wrong...
 */
private void doTestHoldabilityIsReset(final boolean closeConnection)
        throws SQLException {
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(7));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    // Keep track of our own connection, the framework currently creates
    // a new pooled connection and then obtains a connection from that.
    // Statement pooling only works within a single pooled connection.
    Connection con = pc.getConnection();
    assertEquals("Unexpected default holdability",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    con.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
    assertEquals("Holdability not updated",
            ResultSet.CLOSE_CURSORS_AT_COMMIT, con.getHoldability());
    if (closeConnection) {
        con.close();
    }
    con = pc.getConnection();
    assertEquals("Holdability not reset",
            ResultSet.HOLD_CURSORS_OVER_COMMIT, con.getHoldability());
    pc.close();
}
 

/**
 * Test that notification of an error event doesn't fail when the
 * listener is null.
 */
private void subtestErrorEventWithNullListener(PooledConnection pc)
        throws SQLException
{
    pc.addConnectionEventListener(null);
    Connection c = pc.getConnection();
    // Shut down the database to invalidate all connections
    getTestConfiguration().shutdownDatabase();
    try {
        // Should trigger an error event since the connection is no
        // longer valid
        c.prepareStatement("VALUES 1");
        fail("Statement should fail after database shutdown");
    } catch (SQLException e) {
        if (usingEmbedded()) {
            // No current connection is expected on embedded
            assertSQLState("08003", e);
        } else {
            // The client driver reports communication error
            assertSQLState("08006", e);
        }
    }
    c.close();
    pc.close();
}
 

/**
 * Test that notification of an error event doesn't fail when the
 * listener is null.
 */
private void subtestErrorEventWithNullListener(PooledConnection pc)
    throws SQLException
{
    pc.addConnectionEventListener(null);
    Connection c = pc.getConnection();
    // Shut down the database to invalidate all connections
    getTestConfiguration().shutdownDatabase();
    try {
        // Should trigger an error event since the connection is no
        // longer valid
        c.prepareStatement("VALUES 1");
        fail("Statement should fail after database shutdown");
    } catch (SQLException e) {
        if (usingEmbedded()) {
            // No current connection is expected on embedded
            assertSQLState("08003", e);
        } else {
            // The client driver reports communication error
            assertSQLState("08006", e);
        }
    }
    c.close();
    pc.close();
}
 

/**
 * Test that notification of an error event doesn't fail when the
 * listener is null.
 */
private void subtestErrorEventWithNullListener(PooledConnection pc)
    throws SQLException
{
    pc.addConnectionEventListener(null);
    Connection c = pc.getConnection();
    // Shut down the database to invalidate all connections
    getTestConfiguration().shutdownDatabase();
    try {
        // Should trigger an error event since the connection is no
        // longer valid
        c.prepareStatement("VALUES 1");
        fail("Statement should fail after database shutdown");
    } catch (SQLException e) {
        if (usingEmbedded()) {
            // No current connection is expected on embedded
            assertSQLState("08003", e);
        } else {
            // The client driver reports communication error
            assertSQLState("08006", e);
        }
    }
    c.close();
    pc.close();
}
 

@Test(expected = SQLException.class)
public void testPooledConnectionException() throws Exception {
  Assume.assumeTrue(System.getenv("MAXSCALE_VERSION") == null && System.getenv("SKYSQL") == null);

  ConnectionPoolDataSource ds =
      new MariaDbDataSource(hostname != null ? hostname : "localhost", port, database);
  PooledConnection pc = null;
  try {
    pc = ds.getPooledConnection(username, password);
    MyEventListener listener = new MyEventListener();
    pc.addConnectionEventListener(listener);
    MariaDbConnection connection = (MariaDbConnection) pc.getConnection();

    /* Ask server to abort the connection */
    try {
      connection.createStatement().execute("KILL CONNECTION_ID()");
    } catch (Exception e) {
      /* exception is expected here, server sends query aborted */
    }

    /* Try to read  after server side closed the connection */
    connection.createStatement().execute("SELECT 1");

    fail("should never get there");
  } finally {
    if (pc != null) {
      pc.close();
    }
  }
}
 

/**
 * Closes the PooledConnection and stops listening for events from it.
 */
@Override
public void destroyObject(final UserPassKey key, final PooledObject<PooledConnectionAndInfo> p) throws Exception {
    final PooledConnection pc = p.getObject().getPooledConnection();
    pc.removeConnectionEventListener(this);
    pcMap.remove(pc);
    pc.close();
}
 

/**
 * Tests if the connection isolation level is reset when a new connection
 * is obtained.
 * <p>
 * The two arguments are introduced to test different scenarios; explicit
 * and implicit connection closing, and session data caching (piggybacked
 * information).
 *
 * @param closeConnection tells if the connection is explicitly closed
 *      before a new one is obtained
 * @param executeQuery tells if a query is executed on the connection before
 *      a new connection is obtained.
 * @throws SQLException if something goes wrong...
 */
private void doTestIsolationLevelIsReset(final boolean closeConnection,
                                         final boolean executeQuery)
        throws SQLException {
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(7));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    // Keep track of our own connection, the framework currently creates
    // a new pooled connection and then obtains a connection from that.
    // Statement pooling only works within a single pooled connection.
    Connection con = pc.getConnection();
    assertEquals("Unexpected default isolation level",
            Connection.TRANSACTION_READ_COMMITTED,
            con.getTransactionIsolation());
    con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    assertEquals("Isolation level not updated",
            Connection.TRANSACTION_REPEATABLE_READ,
            con.getTransactionIsolation());
    if (executeQuery) {
        PreparedStatement ps = con.prepareStatement("values 2");
        JDBC.assertSingleValueResultSet(ps.executeQuery(), "2");
        ps.close();
    }
    if (closeConnection) {
        con.close();
    }
    con = pc.getConnection();
    assertEquals("Isolation level not reset",
            Connection.TRANSACTION_READ_COMMITTED,
            con.getTransactionIsolation());
    pc.close();
}
 

/**
 * Tests that a temporary table crated in one logical connection is gone
 * in the next logical connection.
 *
 * @throws SQLException if the test fails for some reason
 */
public void testTemporaryTablesAreDeletedInNewLogicalConnection()
        throws SQLException {
    ConnectionPoolDataSource cpds =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpds, "maxStatements", new Integer(3));
    J2EEDataSource.setBeanProperty(cpds, "createDatabase", "create");
    PooledConnection pc = cpds.getPooledConnection();
    Connection lcOne = pc.getConnection();

    // Create the first logical connection and the temporary table.
    Statement stmt = lcOne.createStatement();
    stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE cpds_temp_table " +
            "(id int) ON COMMIT PRESERVE ROWS NOT LOGGED");
    // The temporary table is created in SESSION.
    JDBC.assertEmpty(
            stmt.executeQuery("select * from SESSION.cpds_temp_table"));
    stmt.executeUpdate("insert into SESSION.cpds_temp_table values 1");
    lcOne.commit();
    lcOne.close();

    // Create the second logical connection and try to query the temp table.
    Connection lcTwo = pc.getConnection();
    stmt = lcTwo.createStatement();
    try {
        stmt.executeQuery("select * from SESSION.cpds_temp_table");
        fail("Temporary table still existing in new logical connection.");
    } catch (SQLException sqle) {
        // Expect syntax error.
        assertSQLState("42X05", sqle);
    }
    lcTwo.rollback();
    lcTwo.close();
    pc.close();
}
 

private void closePooledConnections(Set<PooledCassandraConnection> usedConnections2)
{
	for (PooledConnection connection : usedConnections2)
	{
		try
		{
			connection.close();
		}
		catch (SQLException e)
		{
			logger.error(e.getMessage());
		}
	}
}
 

/**
 * Test that notification of a close event doesn't fail when the
 * listener is null.
 */
private void subtestCloseEventWithNullListener(PooledConnection pc)
    throws SQLException
{
    pc.addConnectionEventListener(null);
    // Trigger a close event
    pc.getConnection().close();
    pc.close();
}
 

/**
 * Tests fix for BUG#4808- Calling .close() twice on a PooledConnection
 * causes NPE.
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug4808() throws Exception {
    MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
    ds.setURL(BaseTestCase.dbUrl);
    PooledConnection closeMeTwice = ds.getPooledConnection();
    closeMeTwice.close();
    closeMeTwice.close();

}
 

/**
 * Tests fix for BUG#4808- Calling .close() twice on a PooledConnection
 * causes NPE.
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug4808() throws Exception {
    MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
    ds.setURL(BaseTestCase.dbUrl);
    PooledConnection closeMeTwice = ds.getPooledConnection();
    closeMeTwice.close();
    closeMeTwice.close();

}
 

/**
 * Test that connections retrieved from {@code ConnectionPoolDataSource}
 * behave as expected when {@code close()} is called and the transaction is
 * active.
 */
public void testCloseActiveConnection_CP() throws SQLException {
    ConnectionPoolDataSource ds =
            J2EEDataSource.getConnectionPoolDataSource();
    PooledConnection pc = ds.getPooledConnection();
    testCloseActiveConnection(pc.getConnection(), true, false);
    Connection c = pc.getConnection();
    c.setAutoCommit(false);
    testCloseActiveConnection(c, false, false);
    pc.close();
}
 

/**
 * Test a deferred connection reset. When connection pooling is done
 * and connection is reset, the client sends EXCSAT,ACCSEC and followed
 * by SECCHK and ACCRDB. Test if the security mechanism related information
 * is correctly reset or not. This method was added to help simulate 
 * regression test for DERBY-1080. It is called from testDerby1080.   
 * @param user username 
 * @param password password for connection
 * @param secmec security mechanism for datasource
 * @throws Exception
 */
private void assertSecMecWithConnPoolingOK(
    String user, String password, Short secmec) throws Exception
{
    Connection conn;
    String securityMechanismProperty = "SecurityMechanism";
    Class[] argType = { Short.TYPE };
    String methodName = getSetterName(securityMechanismProperty);
    Object[] args = new Short[1];
    args[0] = secmec;
    
    ConnectionPoolDataSource cpds = getCPDS(user,password);
    
    // call setSecurityMechanism with secmec.
    Method sh = cpds.getClass().getMethod(methodName, argType);
    sh.invoke(cpds, args);
    
    // simulate case when connection will be re-used by getting 
    // a connection, closing it and then the next call to
    // getConnection will re-use the previous connection.  
    PooledConnection pc = cpds.getPooledConnection();
    conn = pc.getConnection();
    conn.close();
    conn = pc.getConnection();
    assertConnectionOK(conn);
    pc.close();
    conn.close();
}
 

private void testBug62452WithConnection(PooledConnection con) throws Exception {
    this.pstmt = con.getConnection().prepareStatement("SELECT 1");
    this.rs = this.pstmt.executeQuery();
    con.close();

    // If PooledConnection is already closed by some reason a NullPointerException was thrown on the next line
    // because the closed connection has nulled out the list that it synchronises on when the closed event is fired.
    this.pstmt.close();
}
 

@Test
public void testWhileIdleReturnValidationWithoutValidationQueryDoesNotOccurWhenDisabled() throws SQLException, InterruptedException {
    datasource.setUrl(MockDriver.getUrlWithValidationOutcomes(ValidationOutcome.FAILURE));
    datasource.getPoolProperties().setTestWhileIdle(false);
    datasource.getPoolProperties().setDefaultAutoCommit(Boolean.FALSE);
    datasource.getPoolProperties().setValidationInterval(1);

    PooledConnection cxn = getPooledConnection();
    cxn.close();
    Assert.assertEquals("Pool must contain 1 idle connection at this time", datasource.getIdle(), 1);

    Thread.sleep(1200); // Nasty - instrument PooledConnection to drive time measurement instead of hard-coded System.currentTimeMillis()
    datasource.testIdle();
    Assert.assertEquals("Pool must contain 1 idle connection at this time", datasource.getIdle(), 1);
}
 

/**
 * Test that a connection to PoolDataSource can be established
 * successfully while creating a database using setDatabaseName()
 * with create=true
 *
 * @throws SQLException
 */

public void testCreateInDatabaseName_Pooled() throws SQLException
{
    //test with PooledConnection
    ConnectionPoolDataSource cpds = J2EEDataSource.getConnectionPoolDataSource();
    PooledConnection pc = cpds.getPooledConnection();
    String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName();
    J2EEDataSource.setBeanProperty(cpds, "databaseName",dbName +";create=true");
    Connection c;
    c = pc.getConnection();
    c.setAutoCommit(false);
    c.close();
    pc.close();
}
 

/**
 * Test a deferred connection reset. When connection pooling is done
 * and connection is reset, the client sends EXCSAT,ACCSEC and followed
 * by SECCHK and ACCRDB. Test if the security mechanism related information
 * is correctly reset or not. This method was added to help simulate 
 * regression test for DERBY-1080. It is called from testDerby1080.   
 * @param user username 
 * @param password password for connection
 * @param secmec security mechanism for datasource
 * @throws Exception
 */
private void assertSecMecWithConnPoolingOK(
    String user, String password, Short secmec) throws Exception
{
    Connection conn;
    String securityMechanismProperty = "SecurityMechanism";
    Class[] argType = { Short.TYPE };
    String methodName = getSetterName(securityMechanismProperty);
    Object[] args = new Short[1];
    args[0] = secmec;
    
    ConnectionPoolDataSource cpds = getCPDS(user,password);
    
    // call setSecurityMechanism with secmec.
    Method sh = cpds.getClass().getMethod(methodName, argType);
    sh.invoke(cpds, args);
    
    // simulate case when connection will be re-used by getting 
    // a connection, closing it and then the next call to
    // getConnection will re-use the previous connection.  
    PooledConnection pc = cpds.getPooledConnection();
    conn = pc.getConnection();
    conn.close();
    conn = pc.getConnection();
    assertConnectionOK(conn);
    pc.close();
    conn.close();
}