Connection and Transaction Handling

To read data from or write data to the database, a connection to the database is required. To be able to connect to a database, you need to add the relevant information to Torque's configuration, see the section about initialisation and configuration.

Connection and Transaction handling cannot be treated separately from each other. Some databases, e.g. oracle, may automatically start a transaction on an sql command, so you might not be able to get a database connection without a transaction. Then, transactions live in database connections, so it is not possible to get a transaction without a database connection.

There are two ways of connection and transaction handling:

• Let Torque take care of it.
• Do it yourself.

Torque's Connection and Transaction Handling

When you use Torque, you need not bother about connections and transactions. The only thing you have to do is to configure Torque correctly, then Torque is able to create a database connection whenever it needs one. For example, if you call the save() method of some object, Torque retrieves a connection from its internal connection pool, uses it to save the connection, and returns it to the pool.

If the database supports transactions and autocommit is turned off, one Transaction is used for every method which needs a database connection. See the source code for details.

Handle Connections and Transactions yourself

If you want to manipulate the database in some way which is not incorporated into Torque, or want to execute several commands in one transaction, you need to handle connections and transaction yourself. This is potentially dangerous, as incorrect handling of connections and transaction may lead to connection leaks (resulting in errors because no more connections can be opened to the database) or blocked connections (because some transaction has been left open). Typically, these conditions appear if a database operation fails, because error handling is insufficient.

The following code is recommended if you need to handle connections and/or transactions yourself:

Connection connection = null;
try
{
    connection = Transaction.begin();

    // do something with connection, e.g.
    // someObject.save(connection);

    Transaction.commit(connection);
    connection = null;
}
finally
{
    if (connection != null)
    {
        Transaction.safeRollback(connection);
    }
}

This code ensures that if any error occurs within the try block, an attempt is made to rollback the transaction and return it into the pool.

As of Torque 5.0 and with the advent of the try-with-resources construct, this can be simplified further. Simply use TorqueConnection instead, which handles the automatic closing and possibly rollback internally:

try (TorqueConnection connection = Transaction.begin())
{
    // do something with connection, e.g.
    // someObject.save(connection);

    Transaction.commit(connection);
}

If the database supports transactions and autocommit is turned off, all database operations are executed in a single transaction. This has the following effect: For example, you execute two saves in the try block, and the second save fails. The first save operation will be rolled back, i.e. the database reverts the changes made by the first save. If you do not like this behavior, the safe way is to wrap everything in its own try ... finally block.

If you use more than one database, you might want to use Transaction.begin(String databaseName) instead of Transaction.begin().

The following code is NOT recommended, as some databases (like Oracle) start transactions automatically, and some pools (like dbcp, Torque's default pool) do not rollback automatically if a connection is returned to the pool. So under certain conditions, you might have leftovers from the last transaction in a "fresh" connection from the pool, or, even worse, the connection blocks because the pool attempts to set some transaction property which needs to be set as first command in a transaction.

Connection connection = null;
try
{
    // Bad ! No defined transaction state afterwards !
    connection = Torque.getConnection();

    // do something with connection, e.g.
    // someObject.save(connection);

    Transaction.commit(connection);
    connection = null;
}
finally
{
    if (connection != null)
    {
        // Bad ! No rollback or commit !
        Torque.closeConnection(connection);
    }
}

Supplying your own Transaction Manager

In some situations, e.g. if you use external transaction control from another framework, it is not desirable that Torque attempts to manipulate transaction state. If this is the case, you can configure Torque to use another transaction manager by implementing a org.apache.torque.util.TransactionManager, and then use the Transaction.setTransactionManager() method at application startup time to tell Torque to use your own TransactionManager instead of the built-in one.

Note that transaction management errors typically occur only under load and are difficult to find, so use this only if you know what you are doing.