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

/**
 * 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 of validateConnection method, of class
 * com.gemstone.gemfire.internal.datasource.AbstractPoolCache.
 */
public void testValidateConnection() {
  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();
    Connection conn = poolConn.getConnection();
    if (!ds.validateConnection(conn)) fail("validate connection failed");
    conn.close();
    if (ds.validateConnection(conn)) fail("validate connection failed");
  }
  catch (Exception e) {
    fail("Exception occured in testValidateConnection due to " + e);
    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);
}
 

/**
 * Tests if the connection obtained from the PooledConnection can be used
 * and has expected defaults.
 * 
 * @throws SQLException
 */
@Test
public void testConnection() throws SQLException {
    PooledConnection pc = getPooledConnection();

    Connection con = pc.getConnection();

    assertTrue("Autocommit should be true", con.getAutoCommit());
    assertFalse("Read-only should be false", con.isReadOnly());
    assertEquals("Tx isolation level should be read committed.",
            Connection.TRANSACTION_READ_COMMITTED, con.getTransactionIsolation());

    Statement stmt = con.createStatement();

    try {
        ResultSet rs = stmt.executeQuery("SELECT cast(1 AS INTEGER) FROM rdb$database");

        assertTrue("Should select one row", rs.next());
        assertEquals("Selected value should be 1.", 1, rs.getInt(1));
    } finally {
        stmt.close();
    }
    con.close();
    assertTrue("Connection should report as being closed.", con.isClosed());
}
 

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

@Test(expected = SQLException.class)
public void testPooledConnectionClosed() throws Exception {
  ConnectionPoolDataSource ds =
      new MariaDbDataSource(hostname != null ? hostname : "localhost", port, database);
  PooledConnection pc = ds.getPooledConnection(username, password);
  Connection connection = pc.getConnection();
  MyEventListener listener = new MyEventListener();
  pc.addConnectionEventListener(listener);
  pc.addStatementEventListener(listener);
  connection.close();
  assertTrue(listener.closed);
  /* Verify physical connection is still ok */
  connection.createStatement().execute("select 1");

  /* close physical connection */
  pc.close();
  /* Now verify physical connection is gone */
  connection.createStatement().execute("select 1");
  fail("should never get there : previous must have thrown exception");
}
 

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

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

/**
 * Tests that the default password is not sent as an attribute string when
 * <code>attributesAsPassword</code> is <code>true</code>. The test is run
 * with a <code>ConnectionPoolDataSource</code>.
 */
public void embeddedTestAttributesAsPasswordWithoutPassword_pooled()
    throws Exception
{
    ConnectionPoolDataSource ds =
        J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(ds, "password",  "mypassword");
    JDBCDataSource.setBeanProperty(ds, "attributesAsPassword", Boolean.TRUE);
   // DERBY-1586 caused a malformed url error here
    PooledConnection pc = ds.getPooledConnection();
    Connection c = pc.getConnection();
    c.close();
}
 

/**
 * Test pooled connetion for chinese database name, user and password.
 * @throws SQLException
 */
public void testCPDSConnect() throws SQLException {
    // Test chinese database name.
    ConnectionPoolDataSource ds = J2EEDataSource.getConnectionPoolDataSource();
    J2EEDataSource.setBeanProperty(ds, "databaseName", "\u4e10");
    J2EEDataSource.setBeanProperty(ds, "createDatabase", "create");        

    PooledConnection poolConn = ds.getPooledConnection();
    Connection conn = poolConn.getConnection();
    conn.close();
    poolConn.close();
 
    // Chinese user
    J2EEDataSource.setBeanProperty(ds, "user", "\u4e10");
    poolConn = ds.getPooledConnection();
    conn = poolConn.getConnection();
    conn.close();
    poolConn.close();

    // Chinese password
    J2EEDataSource.setBeanProperty(ds, "password", "\u4e10");
    poolConn= ds.getPooledConnection();
    conn = poolConn.getConnection();
    conn.close();
    poolConn.close();
    
    /* Add the created database for cleanup by tearDown() */
    databasesForCleanup.add("\u4e10");
}
 

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

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

/**
 * Check setTransactioIsolation and with four connection in connection pool
 * for DERBY-4343 case
 * 
 * @throws SQLException
 */
public void testIsolationWithFourConnections()
        throws SQLException {
    ConnectionPoolDataSource ds = J2EEDataSource.getConnectionPoolDataSource();

    PooledConnection pc = ds.getPooledConnection();
    //First connection
    Connection conn = pc.getConnection();
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM SYS.SYSTABLES");
    rs.next();
    int ri = rs.getInt(1);
    rs.close();
    conn.rollback();
    conn.close();
    
    //Second connection
    conn = pc.getConnection();
    conn.close();
    
    //Third connection
    conn = pc.getConnection();
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    assertEquals(Connection.TRANSACTION_READ_COMMITTED, conn.getTransactionIsolation());
    conn.close();
    
    //Fourth connetion
    conn = pc.getConnection();
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    assertEquals(Connection.TRANSACTION_READ_UNCOMMITTED, conn.getTransactionIsolation());
    conn.close();

}
 

/**
 * Test if obtaining a new logical connection while one is open does not produce errors 
 * when the older logical connection is closed with statements open.
 * <p>
 * See JDBC-250
 * </p>
 */
@Test
public void testStatementOnConnectionReuse() throws SQLException {
    PooledConnection pc = getPooledConnection();
    Connection con = pc.getConnection();
    // Cast to FirebirdStatement to make this work with Java 5 / JDBC 3
    FirebirdStatement stmt = (FirebirdStatement)con.createStatement();

    pc.getConnection();
    assertTrue("Statement should be closed", stmt.isClosed());
    assertTrue("Connection should be closed", con.isClosed());
}
 

/**
 * Tests that PacketTooLargeException doesn't clober the connection.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testPacketTooLargeException() throws Exception {
    final ConnectionEventListener conListener = new ConnectionListener();
    PooledConnection pc = null;

    pc = this.cpds.getPooledConnection();

    pc.addConnectionEventListener(conListener);

    createTable("testPacketTooLarge", "(field1 LONGBLOB)");

    Connection connFromPool = pc.getConnection();
    PreparedStatement pstmtFromPool = ((ConnectionWrapper) connFromPool).clientPrepare("INSERT INTO testPacketTooLarge VALUES (?)");

    this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'max_allowed_packet'");
    this.rs.next();

    int maxAllowedPacket = this.rs.getInt(2);

    int numChars = (int) (maxAllowedPacket * 1.2);

    pstmtFromPool.setBinaryStream(1, new BufferedInputStream(new FileInputStream(newTempBinaryFile("testPacketTooLargeException", numChars))), numChars);

    try {
        pstmtFromPool.executeUpdate();
        fail("Expecting PacketTooLargeException");
    } catch (PacketTooBigException ptbe) {
        // We're expecting this one...
    }

    // This should still work okay, even though the last query on the same connection didn't...
    this.rs = connFromPool.createStatement().executeQuery("SELECT 1");

    assertTrue(this.connectionErrorEventCount == 0);
    assertTrue(this.closeEventCount == 0);
}
 

/**
 * Check setTransactioIsolation and with four connection in connection pool
 * for DERBY-4343 case
 *
 * @throws SQLException
 */
public void testIsolationWithFourConnections()
        throws SQLException {
    ConnectionPoolDataSource ds = J2EEDataSource.getConnectionPoolDataSource();

    PooledConnection pc = ds.getPooledConnection();
    //First connection
    Connection conn = pc.getConnection();
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM SYS.SYSTABLES");
    rs.next();
    int ri = rs.getInt(1);
    rs.close();
    conn.rollback();
    conn.close();

    //Second connection
    conn = pc.getConnection();
    conn.close();

    //Third connection
    conn = pc.getConnection();
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    assertEquals(Connection.TRANSACTION_READ_COMMITTED, conn.getTransactionIsolation());
    conn.close();

    //Fourth connetion
    conn = pc.getConnection();
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    assertEquals(Connection.TRANSACTION_READ_UNCOMMITTED, conn.getTransactionIsolation());
    conn.close();

}
 

@Override
public Connection getConnection(String name, long maxWait) throws SQLException {
  PooledConnection pooledCon = this.getPooledConnection(name, maxWait);
  return pooledCon == null ? null : pooledCon.getConnection();
}
 

@Override
public Connection getConnection() throws SQLException {
    PooledConnection connection = dataSource.getPooledConnection();
    return connection.getConnection();
}
 

/**
 * Test that global temporary tables declared in a connection handle to pooled connection are dropped at connection handle close time
 * and are not available to next connection handle to the same pooled connection 
 *
 * @return	true if it succeeds, false if it doesn't
 *
 * @exception SQLException	Thrown if some unexpected error happens
 */
static boolean testPooledConnectionClose()
				throws SQLException {
	boolean passed = true;
	Connection con1 = null, con2 = null;

	try
	{
		System.out.println("TEST5 : Temporary tables declared in a pooled connection should get dropped when that pooled connection is closed");
		ConnectionPoolDataSource dsp;
		if (isDerbyNet) {
		/* following would require the IBM universal jdbc driver to be available during build...This section needs to be reworked for networkserver
			com.ibm.db2.jcc.DB2ConnectionPoolDataSource ds = new com.ibm.db2.jcc.DB2ConnectionPoolDataSource();
			ds.setDatabaseName("wombat");
			ds.setUser("cs");
			ds.setPassword("cs");
			hostName = TestUtil.getHostName();
			ds.setServerName(hostName);
			ds.setPortNumber(1527);
			ds.setDriverType(4);
			dsp = ds;
		*/
			System.out.println("test will not build without universal driver");
			return passed;
		
		} else {
			EmbeddedConnectionPoolDataSource dscsp = new EmbeddedConnectionPoolDataSource();
			dscsp.setDatabaseName("wombat");
			//dscsp.setConnectionAttributes("unicode=true");
			dsp = dscsp;
		}

		PooledConnection pc = dsp.getPooledConnection();
		con1 = pc.getConnection();
		con1.setAutoCommit(false);
		Statement s = con1.createStatement();

		System.out.println(" In the first connection handle to the pooled connection, create physical session schema, create table t1 in it");
		System.out.println(" Insert some rows in physical SESSION.t1 table. Inspect the data.");
		s.executeUpdate("CREATE schema SESSION");
		s.executeUpdate("CREATE TABLE SESSION.t1(c21 int)");
		s.executeUpdate("insert into SESSION.t1 values(11)");
		s.executeUpdate("insert into SESSION.t1 values(12)");
		s.executeUpdate("insert into SESSION.t1 values(13)");
		ResultSet rs1 = s.executeQuery("select * from SESSION.t1"); //should return 3 rows for the physical table
		dumpRS(rs1);

		System.out.println(" Next declare a temp table with same name as physical table in SESSION schema.");
		System.out.println(" Insert some rows in temporary table SESSION.t1. Inspect the data");
		s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
		s.executeUpdate("insert into SESSION.t1 values(11,1)");
		rs1 = s.executeQuery("select * from SESSION.t1"); //should return 1 row for the temporary table
		dumpRS(rs1);
		System.out.println(" Now close the connection handle to the pooled connection");
		con1.commit();
		con1.close();
		con1=null;

		System.out.println(" Do another getConnection() to get a new connection handle to the pooled connection");
		con2 = pc.getConnection();
		s = con2.createStatement();
		System.out.println(" In this new handle, a select * from SESSION.t1 should be looking at the physical session table");
		rs1 = s.executeQuery("select * from SESSION.t1");
		dumpRS(rs1);

		s.executeUpdate("DROP TABLE SESSION.t1");
		if (isDerbyNet)
			s.executeUpdate("DROP TABLE SESSION.t1");

		s.executeUpdate("DROP schema SESSION restrict");
		con2.commit();
		con2.close();
		System.out.println("TEST5 PASSED");
	} catch (Throwable e)
	{
		System.out.println("Unexpected message: "+ e.getMessage());
		if (con1 != null) con1.rollback();
		if (con2 != null) con2.rollback();
		passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
		System.out.println("TEST5 FAILED");
	}

	return passed;
}
 

@Test
public void createDataSourceTest() throws SQLException {
  CloudSpannerConnectionPoolDataSource subject = new CloudSpannerConnectionPoolDataSource();
  subject.setProjectId("helpful-adroit-123456");
  subject.setInstanceId("test-instance");
  subject.setDatabase("test");
  subject
      .setPvtKeyPath("C:\\Users\\MyUserName\\Documents\\CloudSpannerKeys\\cloudspanner-key.json");
  subject.setPvtKeyPath(null);
  subject.setOauthAccessToken("TEST");
  subject.setSimulateProductName("PostgreSQL");
  subject.setAllowExtendedMode(true);
  subject.setLoginTimeout(10);
  subject.setDefaultAutoCommit(false);
  subject.setLogWriter(new PrintWriter(System.out));
  Assert.assertEquals(
      "ConnectionPoolDataSource from " + nl.topicus.jdbc.CloudSpannerDriver.getVersion(),
      subject.getDescription());
  PooledConnection con = subject.getPooledConnection();
  Assert.assertNotNull(con);
  ICloudSpannerConnection connection = (ICloudSpannerConnection) con.getConnection();
  Assert.assertEquals("jdbc:cloudspanner://localhost", connection.getUrl());
  Assert.assertEquals("PostgreSQL", connection.getProductName());
  Assert.assertEquals("helpful-adroit-123456",
      connection.getSuppliedProperties().getProperty("Project"));
  Assert.assertEquals("test-instance",
      connection.getSuppliedProperties().getProperty("Instance"));
  Assert.assertEquals("test", connection.getSuppliedProperties().getProperty("Database"));
  Assert.assertEquals("TEST", connection.getSuppliedProperties().getProperty("OAuthAccessToken"));
  Assert.assertTrue(connection.isAllowExtendedMode());
  Assert.assertEquals(subject.isDefaultAutoCommit(), connection.getAutoCommit());

  PooledConnection con2 = subject.getPooledConnection("TEST", "TEST");
  Assert.assertNotNull(con2);
  boolean exception = false;
  try {
    subject.getParentLogger();
  } catch (SQLFeatureNotSupportedException e) {
    exception = true;
  }
  Assert.assertTrue(exception);
}