You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by kr...@apache.org on 2008/02/29 14:31:14 UTC
svn commit: r632317 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java
Author: kristwaa
Date: Fri Feb 29 05:31:13 2008
New Revision: 632317
URL: http://svn.apache.org/viewvc?rev=632317&view=rev
Log:
DERBY-3329: Enable statement pooling in the client JDBC driver.
Adds some tests for statement pooling. The tests are not yet enabled.
Patch file: derby-3329-3b_stmtpooling_tests.diff
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java (with props)
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java?rev=632317&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java Fri Feb 29 05:31:13 2008
@@ -0,0 +1,773 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.StatementPoolingTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+*/
+package org.apache.derbyTesting.functionTests.tests.jdbcapi;
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import javax.sql.ConnectionPoolDataSource;
+import javax.sql.PooledConnection;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
+import org.apache.derbyTesting.junit.J2EEDataSource;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * A set of tests specifically targeted at connections that support statement
+ * pooling.
+ */
+public class StatementPoolingTest
+ extends BaseJDBCTestCase {
+
+ public StatementPoolingTest(String name) {
+ super(name);
+ }
+
+ /**
+ * 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));
+ 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();
+ }
+
+ /**
+ * Verifies that statement pooling is enabled by checking the names of the
+ * implementation classes in Derby.
+ *
+ * @throws SQLException if creating the JDBC objects fail
+ */
+ public void testPoolingEnabledByCheckingImplementationDetails()
+ throws SQLException {
+ final String conClass = "CachingLogicalConnection";
+ final String psClass = "LogicalPreparedStatement";
+ final String csClass = "LogicalCallableStatement";
+ Connection con = getConnection();
+ assertClassName(con, conClass);
+ assertClassName(con.prepareStatement("values 1"), psClass);
+ assertClassName(con.prepareStatement("values 1",
+ Statement.RETURN_GENERATED_KEYS),
+ psClass);
+ assertClassName(con.prepareStatement("values 1",
+ Statement.NO_GENERATED_KEYS),
+ psClass);
+ assertClassName(con.prepareStatement("values 1",
+ ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_READ_ONLY),
+ psClass);
+ assertClassName(con.prepareStatement("values 1",
+ ResultSet.TYPE_SCROLL_INSENSITIVE,
+ ResultSet.CONCUR_UPDATABLE),
+ psClass);
+ assertClassName(con.prepareStatement("values 1",
+ (String[])null),
+ psClass);
+ assertClassName(con.prepareStatement("values 1",
+ new String[] {}),
+ psClass);
+ assertClassName(con.prepareCall("values 1"), csClass);
+ assertClassName(con.prepareCall("values 1",
+ ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_READ_ONLY),
+ csClass);
+ assertClassName(con.prepareCall("values 1",
+ ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_READ_ONLY,
+ ResultSet.CLOSE_CURSORS_AT_COMMIT),
+ csClass);
+ }
+
+ /**
+ * Assert that the name of the class of the object is what is expected.
+ * <p>
+ * The assert does not consider package names, only the name passed in as
+ * {@code expectedName} and the passed in name concatenated with "40".
+ *
+ * @param obj object to check
+ * @param expectedName the expected name of the class
+ * @throws AssertionFailedError if the class name is not as expected
+ */
+ private static void assertClassName(Object obj, String expectedName) {
+ assertNotNull("The expected name cannot be <null>", expectedName);
+ assertNotNull("The object cannot be <null>", obj);
+ String[] names = obj.getClass().getName().split("\\.");
+ String simpleName = names[names.length -1];
+ if (!simpleName.equals(expectedName)) {
+ if (!simpleName.equals(expectedName + "40")) {
+ fail("Expected class name " + expectedName + " or " +
+ expectedName + "40, got " + simpleName);
+ }
+ }
+ }
+
+ /**
+ * This test merley checks that creating a logical prepared statement does
+ * not fail.
+ *
+ * @throws SQLException if creating the prepared statement fails
+ */
+ public void testPrepareStatementPath()
+ throws SQLException {
+ PreparedStatement ps = prepareStatement("values 9708");
+ ps.close();
+ }
+
+ /**
+ * This test merley checks that creating a logical callable statement does
+ * not fail.
+ *
+ * @throws SQLException if creating the callable statement fails
+ */
+ public void testPrepareCallPath()
+ throws SQLException {
+ CallableStatement cs = prepareCall(
+ "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+ cs.close();
+ }
+
+ /**
+ * This test merley checks that creating a logical callable statement, which
+ * is not really a call, does not fail.
+ *
+ * @throws SQLException if creating the callable statement fails
+ */
+ public void testPrepareCallWithNoCallPath()
+ throws SQLException {
+ CallableStatement cs = prepareCall("values 1");
+ cs.close();
+ }
+
+ /**
+ * Tests that closing the prepared statement also closes the result set.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testClosingPSClosesRS()
+ throws SQLException {
+ PreparedStatement ps = prepareStatement("values 99");
+ ResultSet rs = ps.executeQuery();
+ ps.close();
+ try {
+ rs.next();
+ fail("Result set should have been closed");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL16", sqle);
+ }
+ }
+
+ /**
+ * Tests that the connection holdability is reset, when it is first
+ * modified, the connection closed and a new logical connection obtained.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testHoldabilityIsResetExplicitClose()
+ throws SQLException {
+ doTestHoldabilityIsReset(true);
+ }
+
+ /**
+ * Tests that the connection holdability is reset, when it is first
+ * modified, and a new logical connection obtained without first explicitly
+ * closing the previous one.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testHoldabilityIsResetNoExplicitClose()
+ throws SQLException {
+ doTestHoldabilityIsReset(false);
+ }
+
+ /**
+ * 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));
+ 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();
+ }
+
+ public void testIsolationLevelIsResetExplicitCloseQuery()
+ throws SQLException {
+ doTestIsolationLevelIsReset(true, true);
+ }
+
+ public void testIsolationLevelIsResetExplicitCloseNoQuery()
+ throws SQLException {
+ doTestIsolationLevelIsReset(true, false);
+ }
+
+ public void testIsolationLevelIsResetNoExplicitCloseNoQuery()
+ throws SQLException {
+ doTestIsolationLevelIsReset(false, false);
+ }
+
+ public void testIsolationLevelIsResetNoExplicitCloseQuery()
+ throws SQLException {
+ doTestIsolationLevelIsReset(false, true);
+ }
+
+ /**
+ * 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));
+ 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 closing the connection closes the logical prepared statement,
+ * but not the physical statement.
+ * <p>
+ * Since there are not public interface methods to test this, the approcah
+ * taken will be this:
+ * <ol> <li>Create a new table</li>
+ * <li>Prepare a statement selecting from the table.</li>
+ * <li>Close the statement, putting it into the cache.</li>
+ * <li>Delete the table.</li>
+ * <li>Prepare the selecting statement again.</li>
+ * <li>Execute the statement.</li>
+ * </ol>
+ * If the physical statement was closed when closing the caching logical
+ * connection, the prepare will fail. If it was left open, the prepare will
+ * succeed because the statement is fetched from the cache, but the
+ * execution will fail becuase the table no longer exists.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testCachingLogicalConnectionCloseLeavesStatementsOpen()
+ throws SQLException {
+ ConnectionPoolDataSource cpDs =
+ J2EEDataSource.getConnectionPoolDataSource();
+ J2EEDataSource.setBeanProperty(cpDs, "maxStatements", new Integer(7));
+ 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();
+ con.setAutoCommit(false);
+ Statement stmt = createStatement();
+ stmt.executeUpdate("create table clcclso (id int)");
+ PreparedStatement ps = con.prepareStatement("select * from clcclso");
+ commit();
+ con.close();
+ try {
+ ps.execute();
+ } catch (SQLException sqle) {
+ // Already closed.
+ assertSQLState("XJ012", sqle);
+ }
+ stmt = createStatement();
+ stmt.executeUpdate("drop table clcclso");
+ commit();
+ // If an exception is thrown here, statement pooling is disabled or not
+ // working correctly.
+ con = pc.getConnection();
+ ps = con.prepareStatement("select * from clcclso");
+ try {
+ ps.execute();
+ fail("Execution should have failed");
+ } catch (SQLException sqle) {
+ assertSQLState("42X05", sqle);
+ }
+ ps.close();
+ // Make sure the connection is still valid.
+ ps = con.prepareStatement("values 976");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "976");
+ ps.close();
+ con.close();
+ }
+
+ /**
+ * Checks if a reset of one statement affects other open statement on the
+ * connection.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void resTestCloseDoesNotAffectOtherStatement()
+ throws SQLException {
+ final String sql = "select * from stmtpooltest where val > 0 and val " +
+ "<= 7 order by val";
+ PreparedStatement psForward = prepareStatement(sql);
+ ResultSet rsForward = psForward.executeQuery();
+ assertTrue(rsForward.next());
+ assertEquals("1", rsForward.getString(1));
+ assertTrue(rsForward.next());
+ assertEquals("2", rsForward.getString(1));
+ PreparedStatement psScrollable = prepareStatement(sql,
+ ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ ResultSet rsScrollable = psScrollable.executeQuery();
+ // Read seven rows from the scrollable rs, position at last row.
+ for (int val=1; val <= 7; val++) {
+ assertTrue(rsScrollable.next());
+ assertEquals(val, rsScrollable.getInt(1));
+ }
+
+ // Create a statement, then close it.
+ PreparedStatement psToClose = prepareStatement(
+ "select val from stmtpooltest where val = 5");
+ JDBC.assertSingleValueResultSet(psToClose.executeQuery(), "5");
+ psToClose.close();
+ assertTrue(rsForward.next());
+ assertEquals("3", rsForward.getString(1));
+ assertTrue(rsScrollable.first());
+ assertEquals("1", rsScrollable.getString(1));
+ // Should fetch a cached statement.
+ psToClose = prepareStatement(
+ "select val from stmtpooltest where val = 5");
+ JDBC.assertSingleValueResultSet(psToClose.executeQuery(), "5");
+ psToClose.close();
+ assertTrue(rsScrollable.last());
+ assertEquals("7", rsScrollable.getString(1));
+ assertFalse(rsScrollable.next());
+ rsScrollable.close();
+ assertTrue(rsForward.next());
+ assertEquals("4", rsForward.getString(1));
+ rsForward.close();
+ }
+
+ /**
+ * Tests that closing a logical prepared statement referring a non-existing
+ * table works.
+ * <p>
+ * In this test, the prepared statement that will be made invalid by the
+ * delete is held open by the user.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testDeleteReferringTableWhenOpen()
+ throws SQLException {
+ getConnection().setAutoCommit(false);
+ // Create a table, insert a row, then create a statement selecting it.
+ Statement stmt = createStatement();
+ stmt.executeUpdate("create table testdeletewhenopen (id int)");
+ stmt.executeUpdate("insert into testdeletewhenopen values 1");
+ PreparedStatement ps = prepareStatement(
+ "select * from testdeletewhenopen");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+ // Now delete the table and logically close the prepared statement.
+ stmt.executeUpdate("drop table testdeletewhenopen");
+ stmt.close();
+ ps.close();
+ // If running without statement pooling, you will get exception here.
+ ps = prepareStatement("select * from testdeletewhenopen");
+ // If we get this far, there is a big change we have fetched an
+ // invalid statement from the cache, but we won't get the exception
+ // until we try to execute it.
+ try {
+ ps.executeQuery();
+ fail("Prepared statement not valid, referring non-existing table");
+ } catch (SQLException sqle) {
+ assertSQLState("42X05", sqle);
+ }
+ }
+
+ /**
+ * Tests that closing a logical prepared statement referring a non-existing
+ * table works.
+ * <p>
+ * In this test, the prepared statement that will be made invalid by the
+ * delete is in the statement cache when the delete happens.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void testDeleteReferringTableWhenInCache()
+ throws SQLException {
+ getConnection().setAutoCommit(false);
+ // Create a table, insert a row, then create a statement selecting it.
+ Statement stmt = createStatement();
+ stmt.executeUpdate("create table testdeletewhenopen (id int)");
+ stmt.executeUpdate("insert into testdeletewhenopen values 1");
+ PreparedStatement ps = prepareStatement(
+ "select * from testdeletewhenopen");
+ JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
+ // Put the statement into the cache.
+ ps.close();
+ // Now delete the table and fetch the cached prepared statement.
+ stmt.executeUpdate("drop table testdeletewhenopen");
+ stmt.close();
+ // If running without statement pooling, you will get exception here.
+ ps = prepareStatement("select * from testdeletewhenopen");
+ // If we get this far, there is a big change we have fetched an
+ // invalid statement from the cache, but we won't get the exception
+ // until we try to execute it.
+ try {
+ ps.executeQuery();
+ fail("Prepared statement not valid, referring non-existing table");
+ } catch (SQLException sqle) {
+ assertSQLState("42X05", sqle);
+ }
+ }
+
+ public void resTestCloseCursorsAtCommit()
+ throws SQLException {
+ doTestResultSetCloseForHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
+ }
+
+ public void resTestHoldCursorsOverCommit()
+ throws SQLException {
+ doTestResultSetCloseForHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ }
+
+ /**
+ * Tests if the holdability settings is taking effect, and also that the
+ * result set is closed when the connection is closed.
+ *
+ * @param holdability result set holdability as specfied by
+ * {@link java.sql.ResultSet}
+ * @throws SQLException if something goes wrong...
+ */
+ private void doTestResultSetCloseForHoldability(int holdability)
+ throws SQLException {
+ getConnection().setAutoCommit(false);
+ PreparedStatement ps = prepareStatement(
+ "select * from stmtpooltest order by val",
+ ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_READ_ONLY,
+ holdability);
+ ResultSet rs = ps.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ commit();
+ if (holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT) {
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ }
+ getConnection().close();
+ try {
+ rs.next();
+ fail("Should have thrown exception");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL16", sqle);
+ }
+ }
+
+ /**
+ * Checks if closing the logical connection closes the logical statement.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void resTestLogicalConnectionCloseInvalidatesLogicalStatement()
+ throws SQLException {
+ Connection con = getConnection();
+ PreparedStatement ps =
+ con.prepareStatement("select * from stmtpooltest");
+ // Don't execute the statement.
+ assertNotNull(ps.getMetaData());
+ con.close();
+ try {
+ ps.getMetaData();
+ fail("Logical statement should be closed and throw exception");
+ } catch (SQLException sqle) {
+ assertSQLState("XJ012", sqle);
+ }
+ con = getConnection();
+ ps = con.prepareStatement("select * from stmtpooltest order by val");
+ // Execute the statement this time.
+ ResultSet rs = ps.executeQuery();
+ assertTrue(rs.next());
+ assertNotNull(ps.getMetaData());
+ rs.close();
+ con.close();
+ try {
+ ps.getMetaData();
+ fail("Logical statement should be closed and throw exception");
+ } catch (SQLException sqle) {
+ assertSQLState("XJ012", sqle);
+ }
+ }
+
+ /**
+ * Tests that nothing is committed on the connection when autocommit is
+ * disabled.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void resTestNoCommitOnReuse()
+ throws SQLException {
+ // Make sure the table is empty.
+ cleanTableExceptedToBeEmpty();
+
+ // Start test
+ final String sql = "insert into stmtpooldata (val) values ?";
+ getConnection().setAutoCommit(false);
+ PreparedStatement ps = prepareStatement(sql);
+ ps.setInt(1, 68);
+ assertEquals(1, ps.executeUpdate());
+ ps.close();
+ ps = prepareStatement(sql);
+ ps.setInt(1, 77);
+ assertEquals(1, ps.executeUpdate());
+ Statement stmt = createStatement();
+ ResultSet rs =stmt.executeQuery(
+ "select val from stmtpooldata order by val");
+ JDBC.assertFullResultSet(rs, new String[][] {{"68"},{"77"}});
+ rollback();
+ rs = stmt.executeQuery("select val from stmtpooldata order by val");
+ JDBC.assertEmpty(rs);
+ }
+
+ /**
+ * Tests that autocommit is working.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void resTestCommitOnReuse()
+ throws SQLException {
+ // Make sure the table is empty.
+ cleanTableExceptedToBeEmpty();
+
+ // Start test
+ final String sql = "insert into stmtpooldata (val) values ?";
+ getConnection().setAutoCommit(true);
+ PreparedStatement ps = prepareStatement(sql);
+ ps.setInt(1, 68);
+ assertEquals(1, ps.executeUpdate());
+ ps.close();
+ ps = prepareStatement(sql);
+ ps.setInt(1, 77);
+ assertEquals(1, ps.executeUpdate());
+ Statement stmt = createStatement();
+ ResultSet rs =stmt.executeQuery(
+ "select val from stmtpooldata order by val");
+ JDBC.assertFullResultSet(rs, new String[][] {{"68"},{"77"}});
+ rollback();
+ rs = stmt.executeQuery("select val from stmtpooldata order by val");
+ JDBC.assertFullResultSet(rs, new String[][] {{"68"},{"77"}});
+ // Clean up
+ assertEquals(2, stmt.executeUpdate("delete from stmtpooldata"));
+ }
+
+ /**
+ * Tests that nothing is committed on the connection when autocommit is
+ * disabled.
+ *
+ * @throws SQLException if something goes wrong...
+ */
+ public void resTestNoDataCommittedOnInvalidTransactionState()
+ throws SQLException {
+ // Make sure the table is empty.
+ cleanTableExceptedToBeEmpty();
+
+ // Start test
+ final String sql = "insert into stmtpooldata (val) values ?";
+ getConnection().setAutoCommit(false);
+ PreparedStatement ps = prepareStatement(sql);
+ ps.setInt(1, 68);
+ assertEquals(1, ps.executeUpdate());
+ ps.close();
+ ps = prepareStatement(sql);
+ ps.setInt(1, 77);
+ assertEquals(1, ps.executeUpdate());
+ Statement stmt = createStatement();
+ ResultSet rs = stmt.executeQuery(
+ "select val from stmtpooldata order by val");
+ JDBC.assertFullResultSet(rs, new String[][] {{"68"},{"77"}});
+ try {
+ getConnection().close();
+ // We should not get here, but let's see what has happened.
+ // Possible symptoms:
+ // - lock timeout: connection resources has not been freed.
+ // - no rows: rollback was issued.
+ // - two rows: commit was issued.
+ stmt = createStatement();
+ rs = stmt.executeQuery("select val from stmtpooldata order by val");
+ int rows = 0;
+ while (rs.next()) {
+ rows++;
+ }
+ fail("Connection should not be allowed to close. Rows in table: " +
+ rows);
+ } catch (SQLException sqle) {
+ assertSQLState("25001", sqle);
+ rollback();
+ }
+ stmt = createStatement();
+ rs = stmt.executeQuery("select val from stmtpooldata order by val");
+ JDBC.assertEmpty(rs);
+ }
+
+ /**
+ * Deletes row from a test table that is expected to be empty.
+ *
+ * @throws SQLException if a database operation fails
+ */
+ private void cleanTableExceptedToBeEmpty()
+ throws SQLException {
+ Statement stmt = createStatement();
+ ResultSet rs = stmt.executeQuery("select * from stmtpooldata");
+ int rowCount = 0;
+ while (rs.next()) {
+ rowCount++;
+ }
+ rs.close();
+ // Delete rows if any, and print a warning if verbosity is on.
+ if (rowCount > 0) {
+ println("Expected empty table, got " + rowCount + " rows.");
+ assertEquals(rowCount,
+ stmt.executeUpdate("delete from stmtpooldata"));
+ }
+ }
+
+ public static Test suite() {
+ TestSuite suite = new TestSuite("StatementPoolingTest suite");
+ TestSuite baseSuite = new TestSuite(StatementPoolingTest.class);
+ // Statement pooling is not yet enabled for XA.
+ //suite.addTest(TestConfiguration.connectionXADecorator(baseSuite));
+ suite.addTest(TestConfiguration.connectionCPDecorator(baseSuite));
+
+ // Add tests that require data from the database.
+ TestSuite reqDataSuite = new TestSuite("Requires data suite");
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestCloseDoesNotAffectOtherStatement"));
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestLogicalConnectionCloseInvalidatesLogicalStatement"));
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestHoldCursorsOverCommit"));
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestCloseCursorsAtCommit"));
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestNoCommitOnReuse"));
+ reqDataSuite.addTest(new StatementPoolingTest(
+ "resTestCommitOnReuse"));
+ // This test fails, DERBY-3319 is probably the cause.
+ //reqDataSuite.addTest(new StatementPoolingTest(
+ // "resTestNoDataCommittedOnInvalidTransactionState"));
+ suite.addTest(TestConfiguration.connectionCPDecorator(
+ new BaseJDBCTestSetup(reqDataSuite) {
+ public void setUp() throws Exception {
+ // Generate some data we can use in the tests.
+ Statement stmt = getConnection().createStatement();
+ try {
+ stmt.executeUpdate("drop table stmtpooltest");
+ } catch (SQLException sqle) {
+ assertSQLState("42Y55", sqle);
+ }
+ stmt.executeUpdate("create table stmtpooltest (" +
+ "id int generated always as identity," +
+ "val int)");
+ PreparedStatement ps = getConnection().prepareStatement(
+ "insert into stmtpooltest values (DEFAULT, ?)");
+ // Insert data with val in range [1,7].
+ for (int val=1; val <= 7; val++) {
+ ps.setInt(1, val);
+ ps.addBatch();
+ }
+ ps.executeBatch();
+
+ try {
+ stmt.executeUpdate("drop table stmtpooldata");
+ } catch (SQLException sqle) {
+ assertSQLState("42Y55", sqle);
+ }
+ stmt.executeUpdate("create table stmtpooldata (" +
+ "id int generated always as identity," +
+ "val int)");
+ // Leave this table empty.
+ }
+ }));
+ return TestConfiguration.clientServerDecorator(suite);
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/StatementPoolingTest.java
------------------------------------------------------------------------------
svn:eol-style = native