Using a database transaction with JDBC

Do you need help with your Java programming?

Click here for instant help with your Java code.

This example demonstrates how to use a transaction with JDBC.
The only thing really that differs from ordinary database operations is that you set a boolean value on the Connection object.
The Connection object has a method called setAutoCommit which sets a value of the object that decides whether to execute the query immediately or not.
Default that value is true, so in order to utilize a transaction you need to set it to false. In the example a new connection is created and finally also closed.
It is though more common to have a pool of connections from which you borrow a connection when you need it and then return it to the pool once you are finished with it.
If that were the case here we would have to call the setAutoCommit method again in the finally block, this time with false as the argument, before returning it to the pool.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
*
* @author javadb.com
*/
public class Main {

    /**
     * Updates tables using a transaction
     */
    public void updateDatabaseWithTransaction() {

        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("[nameOfDriver]");

            connection = DriverManager.getConnection("[databaseURL]",
                    "[userid]",
                    "[password]");

            //Here we set auto commit to false so no changes will take
            //effect immediately.
            connection.setAutoCommit(false);

            statement = connection.createStatement();

            //Execute the queries
            statement.executeUpdate("UPDATE Table1 SET Value = 1 WHERE Name = 'foo'");
            statement.executeUpdate("UPDATE Table2 SET Value = 2 WHERE Name = 'bar'");

            //No changes has been made in the database yet, so now we will commit
            //the changes.
            connection.commit();


        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();

            try {
                //An error occured so we rollback the changes.
                connection.rollback();
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

        new Main().updateDatabaseWithTransaction();

    }
}

Do you know your Java?
Take a Ten-Question-Java-Quiz!

Search for code examples on this site

Need help with your Java code? It's secure and confidential.

This is how it works:

Send a detailed description of what you need help with, the more details the better. Also provide a deadline for when it has to be finished. More time means better chance of putting your request into the schedule.

If the request is serious you will shortly receive an email with the price, to which you have to respond if you accept.

Once you have accepted, the work will begin on developing your code by an experienced Java developer. When the code is finished a link to a secure payment will be sent to you.

The source code is then sent to you once the payment is completed.

IMPORTANT! The request needs to be very detailed, else it may be ignored.

Write your detailed request here:

E-mail address: