Transactions

RIFE2 makes it easy and natural to work with database transactions.

Transactions group multiple database queries as a single unit and isolate them depending on your isolation level.

Grouping and isolating queries

For example, imagine that we want to expand our previous example and report the number of names in the database each time a new name is added.

This select query will perform the count:

    // ... add after selectQuery ...
    Select countQuery = new Select(datasource)
        .from(createQuery.getTable()).field("count(*)");

Now we can change the add route and perform the count right after inserting the name:

    Route add = post("/add", c -> {
        var name = c.parameter("name");
        manager.executeUpdate(insertQuery,
            statement -> statement.setString("name", name));
        var count = manager.executeGetFirstInt(countQuery);
        c.print("Added " + name + " (#" + count+ ")<br><br>");
        c.print("<a href='" + c.urlFor(addForm) + "'>Add more</a><br>");
        c.print("<a href='" + c.urlFor(list) + "'>List names</a><br>");
    });

There's problem in the code above though, several people could be adding names at the same time through the web interface, potentially increasing the count multiple times before countQuery is executed. Transactions provide an easy solution to treat those two queries as a single unit.

For example:

    Route add = post("/add", c -> {
        var name = c.parameter("name");
        manager.inTransaction(() -> {
            manager.executeUpdate(insertQuery,
                statement -> statement.setString("name", name));
            var count = manager.executeGetFirstInt(countQuery);
            c.print("Added " + name + " (#" + count+ ")<br><br>");
        });
        c.print("<a href='" + c.urlFor(addForm) + "'>Add more</a><br>");
        c.print("<a href='" + c.urlFor(list) + "'>List names</a><br>");
    });

The DbQueryManager class provides the inTransaction(TransactionUser) method. It ensures that all the instructions in the provided TransactionUser instance are executed inside a transaction and committed afterwards.

Nested transactions

RIFE2's transaction support allows for nested transactions. If a transaction is already active in the current thread, it will simply be re-used.

The commit will also only take place if a new transaction has actually been started by the active inTransaction invocation, otherwise it's the responsibility of the enclosing code to execute the commit. If a runtime exception occurs during the execution and a new transaction has been started beforehand, it will be automatically rolled back.

Rolling back

If you need to explicitly roll back an active transaction, it's recommended to create an anonymous inner class that extends DbTransactionUserWithoutResult which provides additional transaction-related methods.

For instance, let's assume that only a maximum of 5 names can be added in our example:

        // ...
        manager.inTransaction(new DbTransactionUserWithoutResult<>() {
            public void useTransactionWithoutResult()
            throws InnerClassException {
                manager.executeUpdate(insertQuery,
                    statement -> statement.setString("name", name));
                var count = manager.executeGetFirstInt(countQuery);
                if (count > 5) {
                    c.print("Maximum number of names reached<br><br>");
                    rollback();
                }
                c.print("Added " + name + " (#" + count+ ")<br><br>");
            }
        });
        // ...

The rollback method of the DbTransactionUserWithoutResult class backs out of the transaction and undoes all the changes that were made.

NOTE : Be careful to not use the regular JDBC rollback method. You might be inside a nested RIFE2 transaction that has other logic executing after the rollback. Using DbTransactionUser.rollback(), stops the execution of the active DbTransactionUser and breaks out of any number of them nesting.

The examples above are assuming that no data will be returned from within the transaction through TransactionUserWithoutResult and DbTransactionUserWithoutResult. If you need to return any data, the classes TransactionUser and DbTransactionUser are available for that purpose.

RIFE2 transactions instead of raw JDBC transactions

I recommended to always use transactions through RIFE's inTransaction method since it ensures that transactional code can be re-used and enclosed within other transactional code. Correctly using the regular JDBC transaction-related methods requires great care and planning and often results in error-prone code that is not reusable.

IMPORTANT : RIFE2's nested transactions rely on custom exceptions to cleanly roll back transactions in the case of other exceptions. It's strongly discouraged to have catch-all exception statements inside a RIFE2 DbTransactionUser since you might prevent transactions from properly rolling back.

---

Next learn more about Generic Query Manager