Java Code Examples for java.sql.Connection#releaseSavepoint()

/**
 * Test 47 multiple tests for getSavepointId()
 */
public void xtestGetSavepoint() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint();
    Savepoint savepoint2 = con.setSavepoint();
    savepoint1.getSavepointId();
    savepoint2.getSavepointId();
    con.releaseSavepoint(savepoint2);
    savepoint2 = con.setSavepoint();
    savepoint2.getSavepointId();
    con.commit();
    savepoint2 = con.setSavepoint();
    savepoint2.getSavepointId();
    con.rollback();
    savepoint2 = con.setSavepoint();
    savepoint2.getSavepointId();
    con.rollback();
}
 

/**
 * Test43 - After releasing a savepoint, should be able to reuse it.
 */
public void xtestReuseNameAfterRelease() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("s1");
    try {
        con.setSavepoint("s1");
        fail("Should not be able to set two savepoints with the same name");
    } catch (SQLException se) {
        // Expected exception.
        if (usingEmbedded()) {
            assertSQLState("3B501", se);
        } else if (usingDerbyNetClient()) {
            assertSQLState("3B002", se);
        }
    }
    con.releaseSavepoint(savepoint1);
    con.setSavepoint("s1");
    con.rollback();
}
 

/**
 * Helper method which drops a table if it exists. Nothing happens if
 * the table doesn't exist.
 *
 * @param c the connection to use
 * @param table the table to drop
 * @throws SQLException if an unexpected database error occurs
 */
static void dropTable(Connection c, String table) throws SQLException {
    // Create a savepoint that we can roll back to if drop table fails.
    // This is not needed by Derby, but some databases (e.g., PostgreSQL)
    // don't allow more operations in a transaction if a statement fails,
    // and we want to be able to run these tests against other databases
    // than Derby.
    Savepoint sp = c.setSavepoint();
    Statement stmt = c.createStatement();
    try {
        stmt.executeUpdate("DROP TABLE " + table);
    } catch (SQLException e) {
        // OK to fail if table doesn't exist, roll back to savepoint
        c.rollback(sp);
    }
    stmt.close();
    c.releaseSavepoint(sp);
}
 

/** Test 18 */
public void testNoNestedSavepointsInsideJdbcSavepoint() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint();
    Statement s = getConnection().createStatement();
    // Following SQL savepoint will fail because we are trying to nest it
    // inside JDBC savepoint
    try {
        s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK"
                + " RETAIN CURSORS");
        fail("FAIL 18 shouldn't be able set SQL savepoint nested inside "
                + "JDBC savepoints");
    } catch (SQLException se) {
        // Expected exception.
        assertSQLState("3B002", se);
    }
    // rollback the JDBC savepoint. Now since there are no user defined
    // savepoints, we can define SQL savepoint
    con.releaseSavepoint(savepoint1);
    s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON "
            + "ROLLBACK RETAIN CURSORS");
    con.rollback();
}
 

/**
 * Helper method which drops a table if it exists. Nothing happens if
 * the table doesn't exist.
 *
 * @param c the connection to use
 * @param table the table to drop
 * @throws SQLException if an unexpected database error occurs
 */
static void dropTable(Connection c, String table) throws SQLException {
    // Create a savepoint that we can roll back to if drop table fails.
    // This is not needed by Derby, but some databases (e.g., PostgreSQL)
    // don't allow more operations in a transaction if a statement fails,
    // and we want to be able to run these tests against other databases
    // than Derby.
    Savepoint sp = c.setSavepoint();
    Statement stmt = c.createStatement();
    try {
        stmt.executeUpdate("DROP TABLE " + table);
    } catch (SQLException e) {
        // OK to fail if table doesn't exist, roll back to savepoint
        c.rollback(sp);
    }
    stmt.close();
    c.releaseSavepoint(sp);
}
 

/**
 * Test 13 shouldn't be able to use a savepoint from earlier transaction
 * after setting autocommit on and off
 */
public void testSavepointFromEarlierTransactionAfterToggleAutocommit()
        throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("MyName");
    con.setAutoCommit(true);
    con.setAutoCommit(false);
    Savepoint savepoint2 = con.setSavepoint("MyName1");
    try {// shouldn't be able to use savepoint from earlier tranasaction
        // after setting autocommit on and off
        con.releaseSavepoint(savepoint1);
        fail("FAIL 13 shouldn't be able to use a savepoint from earlier "
                + "transaction after setting autocommit on and off");
    } catch (SQLException se) {
        // Expected exception.
        assertSQLState("3B001", se);
    }
    con.releaseSavepoint(savepoint2);
    con.rollback();
}
 

/**
 * Test 7a: BUG 4468 - should not be able to pass a savepoint from a
 * different transaction for release/rollback
 */
public void testSwapSavepointsAcrossConnectionAndRelease()
        throws SQLException {
    Connection con = getConnection();
    Connection con2 = openDefaultConnection();
    con2.setAutoCommit(false);
    Savepoint savepoint1 = con2.setSavepoint("s1");
    Statement s = createStatement();
    s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
    con.setSavepoint("s1");
    try {
        con.releaseSavepoint(savepoint1);
        fail("FAIL 7a - releasing a another transaction's savepoint did "
                + "not raise error");
    } catch (SQLException se) {
        // Expected exception.
        if (usingEmbedded()) {
            assertSQLState("3B502", se);
        } else if (usingDerbyNetClient()) {
            assertSQLState("XJ097", se);
        }

    }
    con.commit();
    con2.commit();
}
 

/**
 * Test 6c: TEST case just for bug 4467 // Test 10 - create a named
 * savepoint with the a generated name savepoint1 =
 * con2.setSavepoint("SAVEPT0"); // what exactly is the correct behaviour
 * here? try { savepoint2 = con2.setSavepoint(); } catch (SQLException se) {
 * System.out.println("Expected Exception is " + se.getMessage()); }
 * con2.commit();
 */
public void testReleaseSavepointFromOtherTransaction() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("s1");
    Statement s = createStatement();
    s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
    Connection con2 = openDefaultConnection();
    try {
        con2.releaseSavepoint(savepoint1);
        fail("FAIL 6c - releasing another transaction's savepoint did "
                + "not raise error");
    } catch (SQLException se) {
        // Expected exception.
        if (usingEmbedded()) {
            assertSQLState("XJ010", se);
        } else if (usingDerbyNetClient()) {
            assertSQLState("XJ008", se);
        }
    }
    con.commit();
    con2.commit();
}
 

/**
 * test 6a - create a savepoint release it and then create another with the
 * same name. and release the first one
 */
public void testReleaseReleasedSavepoint() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("s1");
    con.releaseSavepoint(savepoint1);
    // The following savepoint was earlier named s1. Changed it to s2 while
    // working on DRDA support
    // for savepoints. The reason for that is as follows
    // The client translates all savepoint jdbc calls to equivalent sql and
    // hence
    // if the 2 savepoints in
    // a transaction are named the same, then the release savepoint below
    // will get converted to
    // RELEASE TO SAVEPOINT s1 and that succeeds because there is a valid
    // savepoint named s1.
    con.setSavepoint("s2");
    Statement s = createStatement();
    s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
    try {
        con.releaseSavepoint(savepoint1);
        fail("FAIL 6a - releasing a released savepoint did not raise error");
    } catch (SQLException se) {
        // Expected exception.
        assertSQLState("3B001", se);
    }
    con.commit();
}
 

/**
 * Test 12 releasing a savepoint multiple times - should not work
 */
public void testReleaseMultipleTimes() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("MyName");
    con.releaseSavepoint(savepoint1);
    try {
        con.releaseSavepoint(savepoint1);
        fail("FAIL 12 releasing a savepoint multiple times should fail");
    } catch (SQLException se) {
        // Expected exception.
        assertSQLState("3B001", se);
    }
    con.rollback();
}
 

/**
 * test 6b - create a savepoints release it and then create another with the
 * same name. and rollback the first one
 */
public void testRollbackReleasedSavepoint() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("s1");
    con.releaseSavepoint(savepoint1);
    // The following savepoint was earlier named s1. Changed it to s2 while
    // working on DRDA support
    // for savepoints. The reason for that is as follows
    // The client translates all savepoint jdbc calls to equivalent sql and
    // hence
    // if the 2 savepoints in
    // a transaction are named the same, then the rollback savepoint below
    // will get converted to
    // ROLLBACK TO SAVEPOINT s1 and that succeeds because there is a valid
    // savepoint named s1.
    con.setSavepoint("s2");
    Statement s = createStatement();
    s.executeUpdate("INSERT INTO T1 VALUES(2,1)");
    try {
        con.rollback(savepoint1);
        fail("FAIL 6b - rollback a released savepoint did not raise error");
    } catch (SQLException se) {
        // Expected exception.
        assertSQLState("3B001", se);
    }
    con.commit();
}
 

/**
 * Test2 - After releasing a savepoint, should be able to reuse it.
 */
public void testReusingSavepoints() throws SQLException {
    Connection con = getConnection();
    Savepoint savepoint1 = con.setSavepoint("s1");
    con.releaseSavepoint(savepoint1);
    con.setSavepoint("s1");
    con.rollback();
}
 

public static void doConnectionReleaseSavepoint() throws Throwable
{
	Connection conn = DriverManager.getConnection("jdbc:default:connection");
	conn.releaseSavepoint((Savepoint) null);
	Statement s = conn.createStatement();
	s.executeUpdate("insert into t2 values(1)");
}
 

public static void doConnectionReleaseSavepoint() throws Throwable
{
	Connection conn = DriverManager.getConnection("jdbc:default:connection");
	conn.releaseSavepoint((Savepoint) null);
	Statement s = conn.createStatement();
	s.executeUpdate("insert into t2 values(1)");
}
 

@Override
public void releaseSavepoint(Savepoint savepoint)
{
    try
    {
        Connection connection = template.getConnection();
        connection.releaseSavepoint(savepoint);
    }
    catch (SQLException e)
    {
        throw new RuntimeException("Failed to create SAVEPOINT: " + savepoint, e);
    }
}
 

@Override
public TableDataInfo getOriginValue(List<Object> whereParamsList, SQLSelectStatement parseSqlStatement,
    Connection connection, TableMetaInfo tableMetaInfo) throws SQLException {
    Savepoint sp = null;
    TableDataInfo txcTable = new TableDataInfo();
    txcTable.setTableName(getTableName(parseSqlStatement));
    Set<String> primaryKeyNameSet = tableMetaInfo.getPrimaryKeyName();
    String selectSql = selectSql(parseSqlStatement, primaryKeyNameSet);
    LockRetryExecutor lockRetryExecutor = new LockRetryExecutor();
    Connection conn = ((ConnectionAdapter)connection).getSourceConnection();
    boolean originalAutoCommit = conn.getAutoCommit();
    try {
        if (originalAutoCommit) {
            conn.setAutoCommit(false);
        }
        sp = conn.setSavepoint();
        while (true) {

            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = conn.prepareStatement(selectSql);
                if (whereParamsList != null && !whereParamsList.isEmpty()) {
                    for (int i = 1; i <= whereParamsList.size(); i++) {
                        preparedStatement.setObject(i, whereParamsList.get(i - 1));
                    }
                }
                resultSet = preparedStatement.executeQuery();
                List<TableDataInfo.TxcLine> txcLines =
                    ResultConvertUtils.convertWithPrimary(resultSet, primaryKeyNameSet, getSqlType());
                txcTable.setLine(txcLines);
                boolean allLocked = true;
                for (TableDataInfo.TxcLine txcLine : txcLines) {
                    String row_key = ResourceRowLockHelper.buildRowKey(txcLine.getPrimaryKeyValues());
                    boolean locked = ResourceRowLockHelper.rowLocked(((ConnectionAdapter)connection),
                        ((ConnectionAdapter)connection).getConnectionRuntimeContext(), txcTable.getTableName(),
                        row_key);
                    if (locked) {
                        conn.rollback(sp);
                        lockRetryExecutor.sleep();
                        allLocked = false;
                        break;
                    }
                }
                if (allLocked) {
                    break;
                }
            } catch (Throwable e) {
                logger.error("Global lock for select failed", e);
                conn.rollback(sp);
                throw e;
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            }
        }

    } finally {
        if (sp != null) {
            conn.releaseSavepoint(sp);
        }
        if (originalAutoCommit) {
            conn.setAutoCommit(true);
        }
    }
    return null;
}
 

/**
 * Updates a nation's endorsements in nationstates based on the available NationData. The nation's existing endorsements are cleared, and the endorsements from the
 * NationData are set. The total number of endorsements and the current time is also updated in the endorsement trends table. If any part of this process fails, 
 * roll back, leaving the database in the same state as it was previously.
 * 
 * @param conn
 * @param access 
 * @param data to use to update endorsements with
 * @param nationId of the nation to update
 * @throws SQLException
 */
public static void updateEndorsements(final Connection conn, final DatabaseAccess access, final NationData data, final int nationId) throws SQLException {
	conn.setAutoCommit(false);
	Savepoint save =  conn.setSavepoint();
	try {
		try (PreparedStatement endorsements = conn.prepareStatement("INSERT INTO assembly.endorsements (endorser, endorsed) VALUES (?, ?)")) {
			for (String endorsed : data.endorsements) {
				if (endorsed.trim().length() > 0) {
					endorsements.setInt(1, access.getNationId(endorsed));
					endorsements.setInt(2, nationId);
					endorsements.addBatch();
				}
			}

			try (PreparedStatement hasEndorsement = conn.prepareStatement("DELETE FROM assembly.endorsements WHERE endorsed = ?")) {
				hasEndorsement.setInt(1, nationId);
				hasEndorsement.executeUpdate();
			}

			endorsements.executeBatch();
			// NOTE: hasEndorsement is executed before endorsements --
			//       first the nation's endos are reset, then the new ones 
			//       are added.
		}

		try (PreparedStatement updateEndorsementTrends = conn.prepareStatement("INSERT INTO assembly.nation_endorsement_trends (nation, endorsements, timestamp) VALUES (?, ?, ?)")) {
			updateEndorsementTrends.setInt(1, nationId);
			updateEndorsementTrends.setInt(2, data.endorsements.length);
			updateEndorsementTrends.setLong(3, System.currentTimeMillis());
			updateEndorsementTrends.executeUpdate();
		}
		
		conn.commit();
		conn.releaseSavepoint(save);
	} catch (SQLException e) {
		conn.rollback(save);
		Logger.warn("Rolling back endorsement transaction");
		throw e;
	} finally {
		conn.setAutoCommit(true);
	}
}
 

@Override
public void releaseSavepoint(final Savepoint savepoint) throws SQLException {
    Connection conn = this.checkConn(this.getConnection());
    conn.releaseSavepoint(savepoint);
}