O'Reilly logo

Java Servlet & JSP Cookbook by Bruce W. Perry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

21.10. Executing Several SQL Statements Within a Single Transaction

Problem

You want to execute more than one SQL statement within a single transaction.

Solution

Use the java.sql.Connection API and the setAutoCommit( ), commit( ), and rollback( ) methods to create a transaction.

Discussion

Some SQL statements, such as those that update customer information in two different database tables, are meant to be executed only as a group. If one of them does not succeed, the database is returned to its previous state. This is the purpose of using a transaction in your Java code. A transaction is a logical unit of database operations that can be "rolled back" or canceled as a group if something goes wrong with one of the operations.

Once you have a database connection (an instance of java.sql.Connection), you can call various Connection methods to create a transaction. Here are the steps for executing a transaction:

  1. Call the Connection object's setAutoCommit( ) method with false as the parameter. This turns off the default behavior for JDBC code, which is to commit each separate SQL statement instead of automatically grouping sequential statements as a single transaction.

  2. Follow the setAutoCommit( ) method call with the database code that you want to treat as a single transaction.

  3. Call the Connection's commit( ) method to commit the SQL statements, which writes any database changes associated with the SQL (such as a DELETE or UPDATE statement) to the underlying database file.

  4. In the area of ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required