Java Code Examples for javax.sql.ConnectionPoolDataSource#getPooledConnection()

public void testJira95pds() throws Exception {
    try {
        ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
        JDBCDataSource.setBeanProperty(pds, "databaseName", "jdbc:derby:boo");
        pds.getPooledConnection();
        fail ("expected an SQLException!");
    } catch (SQLException sqle) {
        // DERBY-2498 - when fixed, remove if
        if (usingEmbedded())
            assertSQLState("XCY00", sqle);
    } catch (Exception e) {
        // DERBY-2498 - when fixed, remove if
        if (usingEmbedded())
            throw e;
    }
}
 

protected void assertShutdownWOUPOK(
    String dbName, String user, String password)
throws SQLException {
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    JDBCDataSource.setBeanProperty(pds, "user", user);
    JDBCDataSource.setBeanProperty(pds, "password", password);
    JDBCDataSource.setBeanProperty(pds, "shutdownDatabase","shutdown");
    try {
        pds.getPooledConnection();
        fail ("expected a failed shutdown connection");
    } catch (SQLException e) {
        // expect 08006 on successful shutdown
        assertSQLState("08006", e);
    }
}
 

protected void assertConnectionWOUPFail(
    String expectedSqlState, String dbName, String user, String password)
throws SQLException
{
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    JDBCDataSource.setBeanProperty(pds, "user", user);
    JDBCDataSource.setBeanProperty(pds, "password", password);
    try {
        pds.getPooledConnection();
        fail("Connection should've been refused/failed");
    }
    catch (SQLException e) {
            assertSQLState(expectedSqlState, e);
    }
}
 

protected void assertShutdownWOUPOK(
    String dbName, String user, String password)
throws SQLException {
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    JDBCDataSource.setBeanProperty(pds, "user", user);
    JDBCDataSource.setBeanProperty(pds, "password", password);
    JDBCDataSource.setBeanProperty(pds, "shutdownDatabase","shutdown");
    try {
        pds.getPooledConnection();
        fail ("expected a failed shutdown connection");
    } catch (SQLException e) {
        // expect 08006 on successful shutdown
        assertSQLState("08006", e);
    }
}
 

protected void assertShutdownWOUPOK(
    String dbName, String user, String password)
throws SQLException {
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    JDBCDataSource.setBeanProperty(pds, "user", user);
    JDBCDataSource.setBeanProperty(pds, "password", password);
    JDBCDataSource.setBeanProperty(pds, "shutdownDatabase","shutdown");
    try {
        pds.getPooledConnection();
        fail ("expected a failed shutdown connection");
    } catch (SQLException e) {
        // expect 08006 on successful shutdown
        assertSQLState("08006", e);
    }
}
 

protected void assertShutdownWOUPFail(
    String expectedSqlState, String dbName, String user, String password) 
throws SQLException
{
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "shutdownDatabase", "shutdown");
    JDBCDataSource.setBeanProperty(pds, "user", user);
    JDBCDataSource.setBeanProperty(pds, "password", password);
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    try {
        pds.getPooledConnection();
        fail("expected failed shutdown");
    } catch (SQLException e) {
        assertSQLState(expectedSqlState, e);
    }
}
 

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

public void testJira95pds() throws Exception {
    try {
        ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
        JDBCDataSource.setBeanProperty(pds, "databaseName", "jdbc:derby:boo");
        pds.getPooledConnection();
        fail ("expected an SQLException!");
    } catch (SQLException sqle) {
        // DERBY-2498 - when fixed, remove if
        if (usingEmbedded())
            assertSQLState("XCY00", sqle);
    } catch (Exception e) {
        // DERBY-2498 - when fixed, remove if
        if (usingEmbedded())
            throw e;
    }
}
 

public void assertConnectionFail(String dbName) throws SQLException {
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    // Reset to no user/password though client requires
    // a valid name, so reset to the default
    if (usingDerbyNetClient())
        JDBCDataSource.setBeanProperty(pds, "user", "APP");
    else
        JDBCDataSource.clearStringBeanProperty(pds, "user");
    JDBCDataSource.clearStringBeanProperty(pds, "password");
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    try {
        pds.getPooledConnection();
        fail("expected connection to fail");
    } catch (SQLException e) {
        assertSQLState("08004", e);
    }
}
 

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

}
 

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

protected void assertShutdownFail(
    String expectedSqlState, String dbName, String user, String password) 
throws SQLException
{
    ConnectionPoolDataSource pds = J2EEDataSource.getConnectionPoolDataSource();
    JDBCDataSource.setBeanProperty(pds, "shutdownDatabase", "shutdown");
    JDBCDataSource.setBeanProperty(pds, "databaseName", dbName);
    try {
        pds.getPooledConnection(user, password);
        fail("expected failed shutdown");
    } catch (SQLException e) {
        assertSQLState(expectedSqlState, e);
    }
}
 

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

public void shutEngine() throws SQLException {
    ConnectionPoolDataSource tmpDs =
            singleUseDS("shutdownDatabase", "shutdown");
    JDBCDataSource.setBeanProperty(tmpDs, "databaseName", "");
    tmpDs.getPooledConnection();
}
 

private PooledConnection    getPooledConnection
    ( ConnectionPoolDataSource cpDs, String userName ) throws Exception
{
    return cpDs.getPooledConnection( userName, getTestConfiguration().getPassword( userName ) );
}