Recipe 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:
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. Follow the setAutoCommit( ) method call with the
database code that you want to treat as a single transaction. 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. In the area of Java code reserved for dealing with errors or
unexpected conditions, such as a catch block, call the
Connection's rollback(
)
method, which rolls back the SQL that was included in the
transaction.
Example 21-14 is a servlet that illustrates this
process.
Example 21-14. A servlet that uses a SQL transaction
package com.jspservletcookbook;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DbServletTrans extends HttpServlet {
DataSource pool;
/*Initialize the DataSource in the servlet's init( ) method
which the servlet container calls once when it creates an instance of
the servlet */
public void init( ) throws ServletException {
Context env = null;
try{
env = (Context) new InitialContext( ).lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
if (pool == null)
throw new ServletException(
"'oracle-8i-athletes' is an unknown DataSource");
} catch (NamingException ne) {
throw new ServletException(ne);
}
}//init
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
java.io.IOException {
Connection conn = null;
Statement stmt = null;
response.setContentType("text/html");
java.io.PrintWriter out = response.getWriter( );
out.println(
"<html><head><title>Using transactions</title></head><body>");
out.println(
"<h2>These SQL statements are part of a transaction</h2>");
out.println("CallableStatement.executeUpdate( )");
out.println("<br><br>");
out.println("Statement.executeUpdate( )");
out.println("<br><br>");
try{
//Get a connection from the pool
conn = pool.getConnection( );
//Display the default values for setAutoCommit( )
//and the isolation level
out.println("AutoCommit before setAutoCommit( ): " +
conn.getAutoCommit( ) + "<br><br>");
out.println("Transaction isolation level: ");
//just out of curiosity, display the existing transaction
// isolation level
witch(conn.getTransactionIsolation( )){
case 0 : out.println("TRANSACTION_NONE<br><br>"); break;
case 1 : out.println(
"TRANSACTION_READ_UNCOMMITTED<br><br>"); break;
case 2 : out.println(
"TRANSACTION_READ_COMMITTED<br><br>"); break;
case 4 : out.println(
"TRANSACTION_REPEATABLE_READ<br><br>"); break;
case 8 : out.println(
"TRANSACTION_SERIALIZABLE<br><br>"); break;
default: out.println("UNKNOWN<br><br>");
}//switch
//set Autocommit to false so that individual SQL statements will
//not be committed until Connection.commit( ) is called
conn.setAutoCommit(false);
//Transaction-related SQL begins...
CallableStatement cs = null;
//Create an instance of the CallableStatement
cs = conn.prepareCall( "{call addEvent (?,?,?)}" );
cs.setString(1,"Salisbury Beach 5-Miler");
cs.setString(2,"Salisbury MA");
cs.setString(3,"14-Aug-2003");
//Call the inherited PreparedStatement.executeUpdate( ) method
cs.executeUpdate( );
String sql = "update raceevent set racedate='13-Aug-2003' "+
"where name='Salisbury Beach 5-Miler'";
int res = 0;
stmt = conn.createStatement( );
res = stmt.executeUpdate(sql);
//commit the two SQL statements
conn.commit( );
} catch (Exception e){
try{
//rollback the transaction in case of a problem
conn.rollback( );
} catch (SQLException sqle){ }
throw new ServletException(e.getMessage( ));
} finally {
try{
if (stmt != null)
stmt.close( );
if (conn != null)
conn.close( );
} catch (SQLException sqle){ }
}
out.println("</table></body></html>");
} //doGet
}
The doGet( ) method in Example 21-14 displays the default values for
"auto committing" SQL statements
and the transaction isolation level (the level of database-locking
that occurs as the transactions within your Java code are initiated).
For example, if your SQL statements include the updating of database
fields, can other users of the database view the new column values
before your transaction is committed? If allowed, this type of
behavior is called a dirty read.
Table 21-2 shows the different types of
transaction isolation levels,
from the least to most restrictive level. Two other terms need
addressing before you inspect this
table:
A non-repeatable
read occurs when one transaction reads a row,
another transaction changes the same row, and the first transaction
reads the same row and receives the different value. A phantom read happens when one transaction obtains a
result set based on a WHERE condition and a second transaction
inserts a new row that satisfies this WHERE condition. The first
transaction then evaluates the same database table again with the
same WHERE condition and retrieves the new
"phantom" row.
Table 21-2. Transaction isolation levels|
TRANSACTION_NONE
|
0
|
The database driver does not support transactions.
|
TRANSACTION_READ_
UNCOMMITTED
|
1
|
Another transaction can see uncommitted changes;
"dirty reads" are allowed.
|
TRANSACTION_READ_
COMMITTED
|
2
|
Uncommitted changes are not visible to other transactions.
|
TRANSACTION_
REPEATABLE_READ
|
4
|
Uncommitted changes are not visible to other transactions;
nonrepeatable reads are also disallowed.
|
TRANSACTION_
SERIALIZABLE
|
8
|
Uncommitted changes are not visible to other transactions;
nonrepeatable reads and phantom reads are also disallowed.
|
|
Check your database vendor's specifications or
literature for how the database system you use handles transaction
isolation. Use the
Connection
object's
getTransactionIsolation( ) method to find out the
value associated with a particular database driver that JDBC-related
code is using. This method returns an int. For
example, a "2" return value means
that the Connection is associated with a
TRANSACTION_READ_COMMITTED transaction isolation
level.
|
|
Example 21-14 runs two SQL statements within a
transaction: it executes a stored procedure and initiates an
UPDATE statement. Then the code calls
commit( )
on the Connection object
to commit any database changes to the underlying data store. If this
SQL code throws an exception, the transaction is rolled back with a
call to Connection's rollback(
) method.
This method call prevents the prior SQL statements from having any
effect on the underlying database.
Figure 21-7 shows the output of the servlet in Example 21-14, as it would appear in a web browser.
See Also
The JDBC specification: http://java.sun.com/products/jdbc/download.html;
Recipe 21.1 on accessing a database from a
servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on
Tomcat; Recipe 21.4-Recipe 21.6 on using
DataSources with servlets and JSPs on WebLogic;
Recipe 21.7 and Recipe 21.8 on calling stored
procedures from servlets and JSPs; Recipe 21.11 on using transactions in JSPs; Recipe 21.12 on finding out information about a ResultSet.
| 
No comments:
Post a Comment