Examples of java.sql.Savepoint

java.sql.Savepoint
A Savepoint is an instant during the current transaction that can be utilised by a Rollback from the Connection.rollback method. Rolling back to a particular Savepoint means that all changes that occurred after that Savepoint are removed.

  @Test
  public void testTransactionManagerRollback() throws Exception {
    ConnectionSource connectionSource = createMock(ConnectionSource.class);
    DatabaseConnection conn = createMock(DatabaseConnection.class);
    expect(conn.isAutoCommitSupported()).andReturn(false);
    Savepoint savePoint = createMock(Savepoint.class);
    expect(savePoint.getSavepointName()).andReturn("name").anyTimes();
    expect(conn.setSavePoint(isA(String.class))).andReturn(savePoint);
    conn.rollback(savePoint);
    expect(connectionSource.getDatabaseType()).andReturn(databaseType);
    expect(connectionSource.getReadWriteConnection()).andReturn(conn);
    expect(connectionSource.saveSpecialConnection(conn)).andReturn(true);

View Full Code Here

  @Test
  public void testTransactionManagerRollbackOtherException() throws Exception {
    ConnectionSource connectionSource = createMock(ConnectionSource.class);
    DatabaseConnection conn = createMock(DatabaseConnection.class);
    expect(conn.isAutoCommitSupported()).andReturn(false);
    Savepoint savePoint = createMock(Savepoint.class);
    expect(savePoint.getSavepointName()).andReturn("name").anyTimes();
    expect(conn.setSavePoint(isA(String.class))).andReturn(savePoint);
    conn.rollback(savePoint);
    expect(connectionSource.getDatabaseType()).andReturn(databaseType);
    expect(connectionSource.getReadWriteConnection()).andReturn(conn);
    expect(connectionSource.saveSpecialConnection(conn)).andReturn(true);

View Full Code Here

  public void testTransactionManagerAutoCommitSupported() throws Exception {
    ConnectionSource connectionSource = createMock(ConnectionSource.class);
    DatabaseConnection conn = createMock(DatabaseConnection.class);
    expect(conn.isAutoCommitSupported()).andReturn(true);
    expect(conn.getAutoCommit()).andReturn(false);
    Savepoint savePoint = createMock(Savepoint.class);
    expect(savePoint.getSavepointName()).andReturn("name").anyTimes();
    expect(conn.setSavePoint(isA(String.class))).andReturn(savePoint);
    conn.commit(savePoint);
    expect(connectionSource.getDatabaseType()).andReturn(databaseType);
    expect(connectionSource.getReadWriteConnection()).andReturn(conn);
    expect(connectionSource.saveSpecialConnection(conn)).andReturn(true);

View Full Code Here

    ConnectionSource connectionSource = createMock(ConnectionSource.class);
    DatabaseConnection conn = createMock(DatabaseConnection.class);
    expect(conn.isAutoCommitSupported()).andReturn(true);
    expect(conn.getAutoCommit()).andReturn(true);
    conn.setAutoCommit(false);
    Savepoint savePoint = createMock(Savepoint.class);
    expect(savePoint.getSavepointName()).andReturn("name").anyTimes();
    expect(conn.setSavePoint(isA(String.class))).andReturn(savePoint);
    conn.commit(savePoint);
    conn.setAutoCommit(true);
    expect(connectionSource.getDatabaseType()).andReturn(databaseType);
    expect(connectionSource.getReadWriteConnection()).andReturn(conn);

View Full Code Here

            jrs.rollback(null);
            fail("Shuld throw NullPointerException since jrs has not been executed.");
        } catch (NullPointerException e) {
            // Expected.
        }
        Savepoint savepoint = new MockSavepoint(1, "mock savepoint 1");
        try {
            jrs.rollback(savepoint);
            fail("Shuld throw NullPointerException since jrs has not been executed.");
        } catch (NullPointerException e) {
            // Expected.

View Full Code Here

     */
    public void testGetKeyAfterSavepointRollback() throws SQLException
    {
        Connection conn = getConnection();
        Statement s = createStatement();
        Savepoint savepoint1 = conn.setSavepoint();


        int expected=1;


        s.execute("insert into t11_AutoGen(c11) values(99)", 
            Statement.RETURN_GENERATED_KEYS);

View Full Code Here

          // Try with named save points
          this.conn.setAutoCommit(false);
          this.stmt
              .executeUpdate("INSERT INTO testSavepoints VALUES (1)");


          Savepoint afterInsert = this.conn
              .setSavepoint("afterInsert");
          this.stmt
              .executeUpdate("UPDATE testSavepoints SET field1=2");


          Savepoint afterUpdate = this.conn
              .setSavepoint("afterUpdate");
          this.stmt.executeUpdate("DELETE FROM testSavepoints");


          assertTrue("Row count should be 0",
              getRowCount("testSavepoints") == 0);

View Full Code Here

        //logic to scan all the records to find another rcord for 
        //comparison.
        con.setAutoCommit(false);
        assertEquals (499, stmt.executeUpdate (
                "delete from constraintest where val1 != '0'"));
        Savepoint deleted = con.setSavepoint("deleted");
        ps.setString(1, "0");
        ps.setString (2, "test");
        try {
            ps.execute();
            fail ("managed to insert a duplicate");

View Full Code Here

    {
      System.out.println("TEST3A : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      PreparedStatement pStmt = con.prepareStatement("insert into SESSION.t1 values (?, ?)");
      pStmt.setInt(1, 11);
      pStmt.setInt(2, 1);
      pStmt.execute();
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint 2, drop temp table t1, rollback savepoint 2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      con.rollback(savepoint2);


      System.out.println("  select should pass, rollback savepoint 1, select should fail");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");


      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3A FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3A PASSED");
    }


    try
    {
      System.out.println("TEST3B : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and declare temp table t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");


      System.out.println("  Release savepoint 1 and select from temp table t1 & t2");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Drop temp table t2(explicit drop), rollback transaction(implicit drop of t1)");
      s.executeUpdate("drop table SESSION.t2");
      con.rollback();


      System.out.println("  Select from temp table t1 and t2 will fail");
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      try {
        rs1 = s.executeQuery("select * from SESSION.t2");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      con.commit();
      System.out.println("TEST3B PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3B FAILED");
    }


    try
    {
      System.out.println("TEST3C : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and declare temp table t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");


      System.out.println("  Release savepoint 1 and select from temp table t1 and t2");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint3 and rollback savepoint3(should not touch t1 and t2)");
      Savepoint savepoint3 = con.setSavepoint();
      con.rollback(savepoint3);


      System.out.println("  select from temp tables t1 and t2 should pass");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback transaction and select from temp tables t1 and t2 should fail");
      con.rollback();
      try {
        rs1 = s.executeQuery("select * from SESSION.t1");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }
      try {
        rs1 = s.executeQuery("select * from SESSION.t2");
      } catch (Throwable e)
      {
        System.out.println("Expected message: "+ e.getMessage());
      }


      con.commit();
      System.out.println("TEST3C PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3C FAILED");
    }


    try
    {
      System.out.println("TEST3D : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and drop temp table t1");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");


      System.out.println("  Rollback savepoint2 and select temp table t1");
      con.rollback(savepoint2);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println(" Commit transaction and select temp table t1");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3D PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3D FAILED");
    }


    try
    {
      System.out.println("TEST3E : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and declare temp table t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");


      System.out.println("  Create savepoint2 and drop temp table t1");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");


      System.out.println("  Rollback savepoint 1 and select from temp table t1 should fail");
      con.rollback(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3E FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3E PASSED");
    }


    try
    {
      System.out.println("TEST3F : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and drop temp table t1");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      s.executeUpdate("drop table SESSION.t1");
      System.out.println("  rollback, select on t1 should fail");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3F FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3F PASSED");
    }


    try
    {
      System.out.println("TEST3G : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println(" In the transaction:");
      System.out.println("  drop temp table t1 and rollback, select on t1 should pass");
      s.executeUpdate("drop table SESSION.t1");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3G PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3G FAILED");
    }


    try
    {
      System.out.println("TEST3H : Savepoint and commit behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println(" In the transaction:");
      System.out.println("  drop temp table t1 and commit, select on t1 should fail");
      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3H FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3H PASSED");
    }


    try
    {
      System.out.println("TEST3I : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 and rollback");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");


      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3I FAILED");
    } catch (Throwable e)
    {
      System.out.println("Expected message: "+ e.getMessage());
      con.commit();
      System.out.println("TEST3I PASSED");
    }


    try
    {
      System.out.println("TEST3J : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 with 2 columns and commit");
      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, 11)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      con.commit();
      System.out.println("  Create savepoint1 and drop temp table t1 with 2 columns");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      System.out.println("  declare temp table t1 but this time with 3 columns");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int, c13 int not null) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(22, 22, 22)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  Create savepoint2 and drop temp table t1 with 3 columns");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("drop table SESSION.t1");
      con.rollback();
      System.out.println("  select from temp table t1 here should have 2 columns");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      s.executeUpdate("drop table SESSION.t1");


      con.commit();
      System.out.println("TEST3J PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3J FAILED");
    }


    try
    {
      System.out.println("TEST3K : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into session.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and update some rows in t1 and inspect the data");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
      s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>2");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  temp table t2 should also have no rows because attempt was made to modify it (even though nothing actually got modified in t2 in the savepoint)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Commit the transaction and should see no data in t1 and t2");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3K PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3K FAILED");
    }


    try
    {
      System.out.println("TEST3L : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into session.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into session.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and update some rows in t1 and inspect the data");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("UPDATE SESSION.t1 SET c12 = 3 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  update t2 with where clause such that no rows get modified in t2 and inspect the data");
      s.executeUpdate("UPDATE SESSION.t2 SET c22 = 3 where c22>3");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback to savepoint1 and we should loose all the rows in t1");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      System.out.println("  temp table t2 should also have no rows because attempt was made to modfiy it (even though nothing actually got modified in t2 in the savepoint)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3L PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3L FAILED");
    }


    try
    {
      System.out.println("TEST3M : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      s.executeUpdate("insert into SESSION.t3 values(31, 1)");
      s.executeUpdate("insert into SESSION.t4 values(41, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 & t2 & t3 and ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into session.t2 values(22, 2)");
      s.executeUpdate("insert into session.t3 values(32, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Release savepoint2 and now savepoint1 should keep track of changes made to t1 and t2, inspect the data in t1 & t2");
      con.releaseSavepoint(savepoint2);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback savepoint1 and should see no data in t1 and t2, inspect the data");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Should see data in t3 since it was not touched in the savepoint that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      s.executeUpdate("drop table SESSION.t3");
      s.executeUpdate("drop table SESSION.t4");
      con.commit();
      System.out.println("TEST3M PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3M FAILED");
    }


    try
    {
      System.out.println("TEST3N : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2 & t3 & t4, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t4(c41 int, c42 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      s.executeUpdate("insert into SESSION.t3 values(31, 1)");
      s.executeUpdate("insert into SESSION.t4 values(41, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t3 ");
      s.executeUpdate("insert into SESSION.t3 values(31, 2)");
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and delete some rows from t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t1 where c12>1");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  delete from t2 with where clause such that no rows are deleted from t2 and inspect the data in t2");
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>3");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and delete some rows from t2 this time and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      s.executeUpdate("DELETE FROM SESSION.t2 where c22>1");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should see no data in t1 and t2 and t3");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t3");
      dumpRS(rs1);


      System.out.println("  Should see data in t4 since it was not touched in the transaction that was rolled back");
      rs1 = s.executeQuery("select * from SESSION.t4");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      s.executeUpdate("drop table SESSION.t3");
      s.executeUpdate("drop table SESSION.t4");
      con.commit();
      System.out.println("TEST3N PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3N FAILED");
    }


    try
    {
      System.out.println("TEST3O : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1 & t2, insert few rows and commit");
      s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged on rollback delete rows");
      s.executeUpdate("insert into SESSION.t1 values(11, 1)");
      s.executeUpdate("insert into SESSION.t2 values(21, 1)");
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);
      con.commit();


      System.out.println(" In the next transaction, insert couple more rows in t1 ");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Create savepoint1 and insert one row in t2 and inspect the data in t2");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("insert into SESSION.t2 values(22, 2)");
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Rollback savepoint1 and should see no data in t2 but t1 should have data, inspect the data");
      con.rollback(savepoint1);
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Commit the transaction and should see no data in t2 but t1 should have data");
      con.commit();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      s.executeUpdate("drop table SESSION.t2");
      con.commit();
      System.out.println("TEST3O PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3O FAILED");
    }


    try
    {
      System.out.println("TEST3P : Savepoint and Rollback behavior");


      System.out.println(" In the transaction:");
      System.out.println("  declare temp table t1, insert few rows and commit");
      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)");
      s.executeUpdate("insert into SESSION.t1 values(12, 2)");
      con.commit();


      System.out.println(" In the transaction:");
      System.out.println("  Create savepoint1 and insert some rows into t1 and inspect the data in t1");
      Savepoint savepoint1 = con.setSavepoint();
      s.executeUpdate("insert into SESSION.t1 values(13, 3)");


      System.out.println("  Release savepoint1 and now transaction should keep track of changes made to t1, inspect the data in t1");
      con.releaseSavepoint(savepoint1);
      ResultSet rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      System.out.println("  Rollback the transaction and should still see no data in t1");
      con.rollback();
      rs1 = s.executeQuery("select * from SESSION.t1");
      dumpRS(rs1);


      s.executeUpdate("drop table SESSION.t1");
      con.commit();
      System.out.println("TEST3P PASSED");
    } catch (Throwable e)
    {
      System.out.println("Unexpected message: "+ e.getMessage());
      con.rollback();
      passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
      System.out.println("TEST3P FAILED");
    }


    try
    {
      System.out.println("TEST3Q : Prepared statement test - DML and rollback behavior");
      System.out.println(" In the transaction:");
      System.out.println("  Declare temp table t2, insert / update / delete data using various prepared statements and commit");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) not logged on commit preserve rows");
      PreparedStatement pStmtInsert = con.prepareStatement("insert into SESSION.t2 values (?, ?)");
      pStmtInsert.setInt(1, 21);
      pStmtInsert.setInt(2, 1);
      pStmtInsert.execute();
      pStmtInsert.setInt(1, 22);
      pStmtInsert.setInt(2, 2);
      pStmtInsert.execute();
      pStmtInsert.setInt(1, 23);
      pStmtInsert.setInt(2, 2);
      pStmtInsert.execute();
      PreparedStatement pStmtUpdate = con.prepareStatement("UPDATE SESSION.t2 SET c22 = 3 where c21=?");
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      PreparedStatement pStmtDelete = con.prepareStatement("DELETE FROM SESSION.t2 where c21 = ?");
      pStmtDelete.setInt(1, 23);
      pStmtDelete.execute();


      con.commit();
      ResultSet rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println(" In the next transaction:");
      System.out.println("  Create savepoint1 and insert some rows into t2 using prepared statement and inspect the data in t2");
      Savepoint savepoint1 = con.setSavepoint();
      pStmtInsert.setInt(1, 23);
      pStmtInsert.setInt(2, 2);
      pStmtInsert.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint2 and update row inserted in savepoint1 using prepared statement and inspect the data in t2");
      Savepoint savepoint2 = con.setSavepoint();
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  rollback savepoint2 and should loose all the data from t2");
      con.rollback(savepoint2);
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint3 and insert some rows into t2 using prepared statement and inspect the data in t2");
      Savepoint savepoint3 = con.setSavepoint();
      pStmtInsert.setInt(1, 21);
      pStmtInsert.setInt(2, 1);
      pStmtInsert.execute();
      pStmtInsert.setInt(1, 22);
      pStmtInsert.setInt(2, 2);
      pStmtInsert.execute();
      pStmtInsert.setInt(1, 23);
      pStmtInsert.setInt(2, 333);
      pStmtInsert.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);


      System.out.println("  Create savepoint4 and update row inserted in savepoint3 using prepared statement and inspect the data in t2");
      Savepoint savepoint4 = con.setSavepoint();
      pStmtUpdate.setInt(1, 23);
      pStmtUpdate.execute();
      rs1 = s.executeQuery("select * from SESSION.t2");
      dumpRS(rs1);

View Full Code Here

    MockControl conControl = MockControl.createControl(Connection.class);
    final Connection con = (Connection) conControl.getMock();
    MockControl mdControl = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData md = (DatabaseMetaData) mdControl.getMock();
    MockControl spControl = MockControl.createControl(Savepoint.class);
    Savepoint sp = (Savepoint) spControl.getMock();


    pmf.getPersistenceManager();
    pmfControl.setReturnValue(pm, 1);
    pm.currentTransaction();
    pmControl.setReturnValue(tx, 3);

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.Savepoint

arjdbc.jdbc.RubyJdbcConnection

br.com.objectos.comuns.relational.jdbc.TransactionsGuice

br.com.objectos.way.relational.TransactionsGuice

com.alibaba.druid.bvt.pool.DruidPooledConnectionTest

com.alibaba.druid.bvt.proxy.BasicTypeTest

com.alibaba.druid.bvt.proxy.ConnectionTest

com.alibaba.druid.filter.logging.LogFilter

com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl

com.cloud.utils.db.Merovingian

com.cloud.utils.db.Transaction

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.