javax.sql.PooledConnection Java Examples

/**
 * 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();
}
 

@Override
public synchronized PooledObject<PooledConnectionAndInfo> makeObject() {
    PooledConnectionAndInfo pci;
    try {
        PooledConnection pc = null;
        if (userName == null) {
            pc = cpds.getPooledConnection();
        } else {
            pc = cpds.getPooledConnection(userName, Utils.toString(userPassword));
        }

        if (pc == null) {
            throw new IllegalStateException("Connection pool data source returned null from getPooledConnection");
        }

        // should we add this object as a listener or the pool.
        // consider the validateObject method in decision
        pc.addConnectionEventListener(this);
        pci = new PooledConnectionAndInfo(pc, userName, userPassword);
        pcMap.put(pc, pci);
    } catch (final SQLException e) {
        throw new RuntimeException(e.getMessage());
    }
    return new DefaultPooledObject<>(pci);
}
 

@Override
public synchronized PooledObject<PooledConnectionAndInfo> makeObject() {
    PooledConnectionAndInfo pci;
    try {
        PooledConnection pc = null;
        if (userName == null) {
            pc = cpds.getPooledConnection();
        } else {
            pc = cpds.getPooledConnection(userName, Utils.toString(userPassword));
        }

        if (pc == null) {
            throw new IllegalStateException("Connection pool data source returned null from getPooledConnection");
        }

        // should we add this object as a listener or the pool.
        // consider the validateObject method in decision
        pc.addConnectionEventListener(this);
        pci = new PooledConnectionAndInfo(pc, userName, userPassword);
        pcMap.put(pc, pci);
    } catch (final SQLException e) {
        throw new RuntimeException(e.getMessage());
    }
    return new DefaultPooledObject<>(pci);
}
 

/**
 * If a fatal error occurs, close the underlying physical connection so as not to be returned in the future
 */
@Override
public void connectionErrorOccurred(final ConnectionEvent event) {
    final PooledConnection pc = (PooledConnection) event.getSource();
    if (null != event.getSQLException()) {
        System.err.println("CLOSING DOWN CONNECTION DUE TO INTERNAL ERROR (" + event.getSQLException() + ")");
    }
    pc.removeConnectionEventListener(this);

    final PooledConnectionAndInfo pci = pcMap.get(pc);
    if (pci == null) {
        throw new IllegalStateException(NO_KEY_MESSAGE);
    }
    try {
        pool.invalidateObject(pci);
    } catch (final Exception e) {
        System.err.println("EXCEPTION WHILE DESTROYING OBJECT " + pci);
        e.printStackTrace();
    }
}
 

/**
 * Test that isolation is reset on PooledConnection.getConnection()
 * @param pooledConnType   Descripiton of the type of pooled connection
 * @param pc               PooledConnection or XAConnection  
 * @throws SQLException
 */
private void assertPooledConnIso(
        String pooledConnType, PooledConnection pc) throws SQLException {
    Connection conn = pc.getConnection();

    setupDerby1144Table(conn);

    // *** Test isolation level reset on conntype.getConnection()          
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    assertIsoLocks(conn, Connection.TRANSACTION_READ_UNCOMMITTED);

    conn.close();
    //Get a new connection with pooledConnType.getConnection()
    // Isolation level should be reset to READ_COMMITTED
    Connection newconn = pc.getConnection();
    assertIsoLocks(newconn, Connection.TRANSACTION_READ_COMMITTED);
}
 

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

private void obtainThenReleaseCxnAndAssessIdleValidationWithOutcome(ValidationOutcome validationOutcome, String validationQuery, Boolean autoCommit)
        throws SQLException, InterruptedException {
    datasource.getPoolProperties().setTestWhileIdle(true);
    datasource.getPoolProperties().setValidationInterval(1);
    datasource.getPoolProperties().setDefaultAutoCommit(autoCommit);
    datasource.getPoolProperties().setValidationQuery(validationQuery);
    datasource.setUrl(MockDriver.getUrlWithValidationOutcomes(validationOutcome));

    PooledConnection cxn1 = getPooledConnection();
    MockConnection mockCxn1 = getMock(cxn1);
    Assert.assertFalse("No transaction must be running after connection is obtained", mockCxn1.isRunningTransaction());

    cxn1.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();

    if (validationOutcome == ValidationOutcome.SUCCESS) {
        Assert.assertEquals("Pool must contain 1 idle connection at this time", datasource.getIdle(), 1);
    }
    else {
        Assert.assertEquals("Pool must not contain any idle connection at this time", datasource.getIdle(), 0);
    }
}
 

/**
 * Regression test for a NullPointerException when trying to use the LOB
 * stored procedures after closing and then getting a new logical
 * connection. The problem was that the LOB stored procedure objects on the
 * server side were closed and not reprepared.
 * See Jira issue DERBY-3799.
 */
public void testDerby3799() throws SQLException {
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    PooledConnection pc = cpDs.getPooledConnection();
    // Get first logical connection.
    Connection con1 = pc.getConnection();
    Statement stmt = con1.createStatement();
    ResultSet rs = stmt.executeQuery("select dClob from derby3799");
    assertTrue(rs.next());
    rs.getString(1);
    rs.close();
    con1.close();
    // Get second logical connection.
    Connection con2 = pc.getConnection();
    stmt = con2.createStatement();
    rs = stmt.executeQuery("select dClob from derby3799");
    assertTrue(rs.next());
    rs.getString(1); // NPE happened here.
    con2.close();
}
 

public void run() {
      String threadName = Thread.currentThread().getName();
      //System.out.println(" Inside Run method of " + threadName);
      int numConn2 = 0;
//      int display = 0;
      while (numConn2 < maxPoolSize) {
        try {
          PooledConnection conn = (PooledConnection) poolCache
              .getPooledConnectionFromPool();
          poolConnlist.add(conn);
          numConn2++;
          //	System.out.println(" ********** Got connection " + numConn2+ "from
          // " + threadName);
        }
        catch (Exception ex) {
          fail("Exception occured in trying to getPooledConnectionFromPool due to "
              + ex);
          ex.printStackTrace();
        }
      }
      if (numConn2 != maxPoolSize)
          fail("#### Error in getting all connections from the " + threadName);
      //System.out.println(" ****************GOT ALL connections "+ threadName
      // + "***********");
    }
 

/**
 * Tests that the statement cache is able to throw out prepared statements
 * when it reaches maximum capacity.
 *
 * @throws SQLException if something goes wrong...
 */
public void testCacheOverflow()
        throws SQLException {
    final int stmtCount = 150;
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(11));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    Connection con = pc.getConnection();
    for (int i=0; i < stmtCount; i++) {
        // Yes, the "values + i" is intended here.
        PreparedStatement pStmt = con.prepareStatement("values " + i);
        ResultSet rs = pStmt.executeQuery();
        JDBC.assertSingleValueResultSet(rs, Integer.toString(i));
        pStmt.close();
    }
    con.close();
    pc.close();
}
 

/**
 * check whether commit without statement will flow by checking its transaction id
 * on client. This test is run only for client where commits without an
 * active transactions will not flow to the server.
 * DERBY-4653
 *
 * @throws SQLException
 **/
public void testConnectionFlowCommit()
        throws SQLException {
    ConnectionPoolDataSource ds = J2EEDataSource.getConnectionPoolDataSource();

    PooledConnection pc = ds.getPooledConnection();
    Connection conn = pc.getConnection();

    testConnectionFlowCommitWork(conn);
    conn.close();

    //Test for XADataSource
    XADataSource xs = J2EEDataSource.getXADataSource();
    XAConnection xc = xs.getXAConnection();
    conn = xc.getConnection();
    testConnectionFlowCommitWork(conn);
    conn.close();

    //Test for DataSource
    DataSource jds = JDBCDataSource.getDataSource();
    conn = jds.getConnection();
    testConnectionFlowCommitWork(conn);
    conn.close();
}
 

/**
 * Tests that the statement cache is able to throw out prepared statements
 * when it reaches maximum capacity.
 *
 * @throws SQLException if something goes wrong...
 */
public void testCacheOverflow()
        throws SQLException {
    final int stmtCount = 150;
    ConnectionPoolDataSource cpDs =
            J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(11));
    J2EEDataSource.setBeanProperty(cpDs, "createDatabase", "create");
    PooledConnection pc = cpDs.getPooledConnection();
    Connection con = pc.getConnection();
    for (int i=0; i < stmtCount; i++) {
        // Yes, the "values + i" is intended here.
        PreparedStatement pStmt = con.prepareStatement("values " + i);
        ResultSet rs = pStmt.executeQuery();
        JDBC.assertSingleValueResultSet(rs, Integer.toString(i));
        pStmt.close();
    }
    con.close();
    pc.close();
}
 

/**
 * Test of getPooledConnectionFromPool method, of class
 * com.gemstone.gemfire.internal.datasource.AbstractPoolCache.
 */
public void testGetPooledConnectionFromPool() {
  try {
    Context ctx = cache.getJNDIContext();
    GemFireConnPooledDataSource ds = (GemFireConnPooledDataSource) ctx
        .lookup("java:/PooledDataSource");
    GemFireConnectionPoolManager provider = (GemFireConnectionPoolManager) ds
        .getConnectionProvider();
    ConnectionPoolCacheImpl poolCache = (ConnectionPoolCacheImpl) provider
        .getConnectionPoolCache();
    PooledConnection poolConn = (PooledConnection) poolCache
        .getPooledConnectionFromPool();
    if (poolConn == null)
        fail("getPooledConnectionFromPool failed to get a connection from pool");
  }
  catch (Exception e) {
    fail("Exception occured in testGetPooledConnectionFromPool due to " + e);
    e.printStackTrace();
  }
}
 

/**
 * If a fatal error occurs, close the underlying physical connection so as not to be returned in the future
 */
@Override
public void connectionErrorOccurred(final ConnectionEvent event) {
    final PooledConnection pc = (PooledConnection) event.getSource();
    if (null != event.getSQLException()) {
        System.err.println("CLOSING DOWN CONNECTION DUE TO INTERNAL ERROR (" + event.getSQLException() + ")");
    }
    pc.removeConnectionEventListener(this);

    final PooledConnectionAndInfo info = pcMap.get(pc);
    if (info == null) {
        throw new IllegalStateException(NO_KEY_MESSAGE);
    }
    try {
        pool.invalidateObject(info.getUserPassKey(), info);
    } catch (final Exception e) {
        System.err.println("EXCEPTION WHILE DESTROYING OBJECT " + info);
        e.printStackTrace();
    }
}
 

private synchronized Connection getConnection3() throws SQLException {
	if (isDisposed) {
		throw new IllegalStateException("Connection pool has been disposed.");
	} // test again with lock
	PooledConnection pconn;
	if (!recycledConnections.isEmpty()) {
		pconn = recycledConnections.remove();
	} else {
		pconn = dataSource.getPooledConnection();
		pconn.addConnectionEventListener(poolConnectionEventListener);
	}
	Connection conn = pconn.getConnection();
	activeConnections++;
	assertInnerState();
	return conn;
}
 

/**
 * Tests that the <code>attributesAsPassword</code> property of a
 * <code>ConnectionPoolDataSource</code> causes an explicitly specified
 * password to be sent as a property string.
 */
public void embeddedTestAttributesAsPasswordWithPassword_pooled()
    throws Exception
{
    ConnectionPoolDataSource ds =
        J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(ds, "attributesAsPassword", Boolean.TRUE);
    try {
        PooledConnection pc =
            ds.getPooledConnection("username", "mypassword");
        fail("Expected getPooledConnection to fail.");
    } catch (SQLException e) {
        // expect error because of malformed url
        assertSQLState("XJ028", e);
    }
}
 

/**
 * Test that isolation is reset on PooledConnection.getConnection()
 * @param pooledConnType   Descripiton of the type of pooled connection
 * @param pc               PooledConnection or XAConnection  
 * @throws SQLException
 */
private void assertPooledConnIso(
    String pooledConnType, PooledConnection pc) throws SQLException {
    Connection conn = pc.getConnection();

    setupDerby1144Table(conn);

    // *** Test isolation level reset on conntype.getConnection()          
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    assertIsoLocks(conn, Connection.TRANSACTION_READ_UNCOMMITTED);

    conn.close();
    //Get a new connection with pooledConnType.getConnection()
    // Isolation level should be reset to READ_COMMITTED
    Connection newconn = pc.getConnection();
    assertIsoLocks(newconn, Connection.TRANSACTION_READ_COMMITTED);
}
 

/**
 * Executes a test sequence to make sure the schema is reset between
 * logical connections.
 *
 * @param pc pooled connection to get logical connections from
 * @param userSchema name of the default schema for the connection (user)
 * @throws SQLException if something goes wrong...
 */
private void doTestSchemaIsReset(PooledConnection pc, String userSchema)
        throws SQLException {
    Connection con1 = pc.getConnection();
    JDBC.assertCurrentSchema(con1, userSchema);
    Statement stmt1 = con1.createStatement();
    // Change the schema.
    stmt1.execute("set schema APP");
    stmt1.close();
    JDBC.assertCurrentSchema(con1, "APP");
    // Close the logical connection and get a new one.
    con1.close();
    Connection con2 = pc.getConnection();
    // Make sure the schema has been reset from APP to the user name.
    JDBC.assertCurrentSchema(con2, userSchema);
    con2.close();
    // Try a third time, but don't change the schema now.
    Connection con3 = pc.getConnection();
    JDBC.assertCurrentSchema(con3, userSchema);
    con3.close();
    pc.close();
}
 

/**
 * check whether commit without statement will flow by checking its transaction id
 * on client. This test is run only for client where commits without an
 * active transactions will not flow to the server.
 * DERBY-4653
 * 
 * @throws SQLException
 **/
public void testConnectionFlowCommit()
        throws SQLException {
    ConnectionPoolDataSource ds = J2EEDataSource.getConnectionPoolDataSource();

    PooledConnection pc = ds.getPooledConnection();
    Connection conn = pc.getConnection();

    testConnectionFlowCommitWork(conn, 1);
    conn.close();
    
    //Test for XADataSource
    XADataSource xs = J2EEDataSource.getXADataSource();
    XAConnection xc = xs.getXAConnection();
    conn = xc.getConnection();
    testConnectionFlowCommitWork(conn, 1);
    conn.close();
    
    //Test for DataSource
    DataSource jds = JDBCDataSource.getDataSource();
    conn = jds.getConnection();
    testConnectionFlowCommitWork(conn, 1);
    conn.close();       
}
 

/**
 * Implementation of call back function from ConnectionEventListener
 * interface. This callback will be invoked on connection close event.
 * 
 * @param event
 */
public void connectionClosed(ConnectionEvent event) {
  if (isActive) {
    try {
      PooledConnection conn = (PooledConnection) event.getSource();
      provider.returnConnection(conn);
    }
    catch (Exception ex) {
      String exception = "GemFireConnPooledDataSource::connectionclosed:Exception ="
          + ex;
      LogWriterI18n writer = TransactionUtils.getLogWriterI18n();
      if (writer.fineEnabled()) writer.fine(exception, ex);
    }
  }
}
 

/**
 * Invalidates the PooledConnection in the pool. The KeyedCPDSConnectionFactory closes the connection and pool
 * counters are updated appropriately. Also clears any idle instances associated with the user name that was used to
 * create the PooledConnection. Connections associated with this user are not affected and they will not be
 * automatically closed on return to the pool.
 */
@Override
public void invalidate(final PooledConnection pc) throws SQLException {
    final PooledConnectionAndInfo info = pcMap.get(pc);
    if (info == null) {
        throw new IllegalStateException(NO_KEY_MESSAGE);
    }
    final UserPassKey key = info.getUserPassKey();
    try {
        pool.invalidateObject(key, info); // Destroy and update pool counters
        pool.clear(key); // Remove any idle instances with this key
    } catch (final Exception ex) {
        throw new SQLException("Error invalidating connection", ex);
    }
}
 

/**
 When a connection is being pooled, the underlying JDBC embedded
 connection object is re-used. As each application gets a new
 Connection object, that is really a wrapper around the old connection
 it should reset any connection spoecific state on the embedded connection
 object.
 */
private static void PoolReset(String type, PooledConnection pc) throws SQLException
{
    PoolResetWork("1", "C", pc.getConnection());
    PoolResetWork("2", "", pc.getConnection());
    PoolResetWork("3", "D", pc.getConnection());

    pc.close();
}
 

/**
 * @since 2.4.0
 */
PooledConnectionAndInfo(final PooledConnection pc, final String userName, final char[] userPassword) {
    this.pooledConnection = pc;
    this.userName = userName;
    this.userPassword = userPassword;
    this.upKey = new UserPassKey(userName, userPassword);
}
 

/**
 * Tests for equals() on connections. 
 */
@Test
public void testConnectionEquals() throws SQLException {
    PooledConnection pc1 = getPooledConnection();
    PooledConnection pc2 = getPooledConnection();
    Connection con1 = pc1.getConnection();
    Connection con2 = pc2.getConnection();
    
    assertTrue("A connection should be equal to itself", con1.equals(con1));
    assertFalse("A connection should not be equal to a different connection", con1.equals(con2));
    Connection con1_2 = pc1.getConnection();
    assertFalse("A connection from the same pooled connection should be different", con1.equals(con1_2));
}
 

/**
 * Create and return an EmbedPooledConnection from the received instance
 * of EmbeddedDataSource.
 */
protected PooledConnection getNewPooledConnection(
    EmbeddedDataSource eds, String user, String password,
    boolean requestPassword) throws SQLException
{
    return new EmbedPooledConnection(
        eds, user, password, requestPassword);
}
 

/**
 * Invalidates the PooledConnection in the pool. The CPDSConnectionFactory closes the connection and pool counters
 * are updated appropriately. Also closes the pool. This ensures that all idle connections are closed and
 * connections that are checked out are closed on return.
 */
@Override
public void invalidate(final PooledConnection pc) throws SQLException {
    final PooledConnectionAndInfo pci = pcMap.get(pc);
    if (pci == null) {
        throw new IllegalStateException(NO_KEY_MESSAGE);
    }
    try {
        pool.invalidateObject(pci); // Destroy instance and update pool counters
        pool.close(); // Clear any other instances in this pool and kill others as they come back
    } catch (final Exception ex) {
        throw new SQLException("Error invalidating connection", ex);
    }
}
 

/**
 * Check that traceFile connection attribute functions correctly.
 * tracefile was tested in checkDriver, but not for DataSources.
 * tracefile= was used in datasourcepermissions_net, but that's 
 * incorrect syntax. Note that we're not checking the contents of
 * the tracefile.
 *
 * Note also that this test cannot run against a remote server.
 * <p>
 * This is also a regression test for DERBY-4717.
 *
 * @throws SQLException
 */
public void testClientTraceFileDSConnectionAttribute() throws SQLException
{
    String traceFile;

    // with ConnectionPoolDataSource
    ConnectionPoolDataSource cpds = J2EEDataSource.getConnectionPoolDataSource();

    traceFile = "trace3.out";
    JDBCDataSource.setBeanProperty(cpds, "connectionAttributes",
            "traceFile="+traceFile);
    // DERBY-2468 - trace3.out does not get created
    ((PooledConnection)getPhysicalConnection(cpds)).close();
    JDBCDataSource.clearStringBeanProperty(cpds, "connectionAttributes");

    traceFile = "trace4.out";
    JDBCDataSource.setBeanProperty(cpds, "traceFile", traceFile);
    ((PooledConnection)getPhysicalConnection(cpds)).close();
    cpds = null;

    // now with XADataSource
    XADataSource xads = J2EEDataSource.getXADataSource();

    traceFile = "trace5.out";
    JDBCDataSource.setBeanProperty(xads, "connectionAttributes",
            "traceFile="+traceFile);
    ((XAConnection)getPhysicalConnection(xads)).close();
    // DERBY-2468 - trace5.out does not get created
    JDBCDataSource.clearStringBeanProperty(xads, "connectionAttributes");

    traceFile = "trace6.out";
    JDBCDataSource.setBeanProperty(xads, "traceFile", traceFile);
    ((XAConnection)getPhysicalConnection(xads)).close();

    assertTraceFilesExistAndCanBeDeleted();
}
 

@Test
public void testOnConnectValidationWithValidationSQLDoesNotOccurWhenDisabled() throws SQLException {
    this.datasource.setUrl(MockDriver.getUrlWithValidationOutcomes(ValidationOutcome.FAILURE));
    datasource.getPoolProperties().setTestOnConnect(false);
    datasource.getPoolProperties().setDefaultAutoCommit(Boolean.FALSE);
    datasource.getPoolProperties().setValidationQuery("SELECT 1");
    PooledConnection cxn = getPooledConnection();
    Assert.assertFalse("No transaction must be running after connection is obtained", getMock(cxn).isRunningTransaction());
}
 

/**
 * 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();
}
 

@Test
public void testOnBorrowValidationWithoutValidationQueryDoesNotOccurWhenDisabled() throws SQLException {
    datasource.getPoolProperties().setTestOnBorrow(false);
    datasource.getPoolProperties().setDefaultAutoCommit(Boolean.FALSE);
    PooledConnection cxn = getPooledConnection();
    Assert.assertFalse("No transaction must be running after connection is obtained", getMock(cxn).isRunningTransaction());
}