9.4 Transactions

Java Servlet Programming, 2nd Edition > 9. Database Connectivity > 9.4 Transactions

 
< BACKCONTINUE >

9.4 Transactions

So far, we have failed to mention one important feature of modern relational database systems: transactions. Most service-oriented web sites need to do more than run SELECT statements and insert single pieces of data. Let's look at an online banking application. To perform a transfer of $50,000 between accounts, your program needs to perform an operation that consists of two separate but related actions: credit one account and debit another. Now, imagine that for some reason or another, the SQL statement for the credit succeeds but the one for the debit fails. One account holder is $50,000 richer, but the other account has not been debited to match.

SQL failure is not the only potential problem. If another user checks the account balance in between the credit and the debit, she will see the original balance. The database is shown in an invalid state (more money is represented than actually exists). Granted, this kind of thing is unlikely to occur often, but in a universe of infinite possibilities, it will almost certainly happen sometime. This kind of problem is similar to the synchronization issues we discussed back in Chapter 3. This time, instead of concerning ourselves with the validity of data stored in a servlet, we are concerned with the validity of an underlying database. Simple synchronization is not enough to solve this problem: multiple servlets may be accessing the same database. For systems like banking software, chances are good that the database is being used by a number of entirely non-Java applications as well.

Sounds like a fairly tricky problem, right? Fortunately, it was a problem long before Java came along, so it has already been solved. Most major RDMBS systems support the concept of transactions. A transaction allows you to group multiple SQL statements together. Using a transaction-aware RDBMS, you can begin a transaction, perform any number of actions, and either commit the results to the database or roll back all of your SQL statements. If we build our online banking application with a transaction-based system, the credit will automatically be canceled if the debit fails.

A transaction is isolated from the rest of the database until finished. As far as the rest of the database is concerned, everything takes place at once (in other words, transactions are atomic). This means that other users accessing the database will always see a valid view of the data, although not necessarily an up-to-date view. If a user requests a report on widgets sold before your widget sales transaction is completed, the report will not include the most recent sale.

9.4.1 Using Transactions with JDBC

Transaction management with JDBC takes place via the Connection object. By default, new connections start out in autocommit mode. This means that every SQL statement is executed as an individual transaction that is immediately committed to the database. To control commitment yourself, thereby allowing you to group SQL statements into transactions, you call setAutoCommit(false) on the Connection object. You can check the status of autocommit with the getAutoCommit( ) method. Once you have completed all of your SQL statements, you call commit( ) to permanently record the transaction in the database. Or, if you encountered an error, you call rollback( ) to undo it.

Example 9-7 shows a servlet that uses transactions to do basic order processing. It assumes two tables in an ODBC database INVENTORY (containing the product ID and amount in stock) and SHIPPING (containing a product ID, an order number, and the amount shipped). The servlet uses an unshown chargeCard( ) method that handles billing and throws an exception if the customer's credit card is invalid.

Example 9-7. Transaction-Based Order Management
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class OrderHandler extends HttpServlet {   public void doPost(HttpServletRequest req, HttpServletResponse res)                                 throws ServletException, IOException {     res.setContentType("text/plain");     PrintWriter out = res.getWriter();     Connection con = null;     try {       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");       con = DriverManager.getConnection("jdbc:odbc:ordersdb", "user", "passwd");       // Turn on transactions       con.setAutoCommit(false);       Statement stmt = con.createStatement();       stmt.executeUpdate(         "UPDATE INVENTORY SET STOCK = (STOCK - 10) WHERE PRODUCTID = 7");       stmt.executeUpdate(         "UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10) WHERE PRODUCTID = 7");       chargeCard();  // method doesn't actually exist...       con.commit();       out.println("Order successful!  Thanks for your business!");     }     catch (Exception e) {       // Any error is grounds for rollback       try {         con.rollback();       }       catch (SQLException ignored) { }       out.println("Order failed. Please contact technical support.");     }     finally {       // Clean up.       try {         if (con != null) con.close();       }       catch (SQLException ignored) { }     }   } }

Here are a few notes on this example. First, the order transaction logic is in doPost( ) since the client's action is definitely not safely repeatable. Second, because the example demonstrates transaction logic more than servlet logic, the servlet simply assumes the user is buying 10 units of item 7, rather than bothering to actually parse a form for credit card and order information. Finally, as the servlet runs, any exception thrown during driver initializing, connecting to the database, executing SQL, or charging the credit card causes execution to jump to the catch( ) block, where the rollback( ) method is called, undoing all our work.

9.4.2 Optimized Transaction Processing

Note that in the previous example the Connection object was created inside the doPost( ) method, giving up the performance improvements we gained earlier in the chapter by moving the creation up to init( ). This is done because transactions are linked to connections and, therefore, connections using transactions cannot be shared. Imagine what would happen if another invocation of this servlet invoked the commit( ) method when our order had reached only the second SQL statement. Our INVENTORY table would be short 10 units!

So, how do we use transactions without having to connect to the database every time a page is requested? There are several possibilities:

  • Synchronize the doPost( ) method. This means that each instance of the servlet deals with only one request at a time. This works well for very low traffic sites, but it does slow things down for your users because every transaction has to finish before the next can start. If you need to perform database-intensive updates and inserts, the delay will probably be unacceptable.

  • Leave things as they are, but create a new Connection object for each transaction. If you need to update data only once in every few thousand page requests, this might be the simplest route.

  • Create a pool of Connection objects in the init( ) method and hand them out as needed, as shown in Figure 9-3. This is probably the most efficient way to handle the problem, if done right. It can, however, become very complicated very quickly without third-party support classes.

  • Implement session tracking in the servlet and use the HttpSession object to hold onto a Connection for each user. This allows you to go one step beyond the other solutions and extend a transaction across multiple page requests or even multiple servlets.

Figure 9-3. Servlets using a database connection pool

9.4.3 Connection Pooling

For a complicated servlet, creating a connection pool is the ideal approach. With a connection pool, we can duplicate only the resources we need to duplicate (that is, Connection objects). A connection pool can also intelligently manage the size of the pool and make sure each connection remains valid. A number of connection pool packages are currently available. Some, such as the DbConnectionBroker that is freely available from Java Exchange at http://javaexchange.com, work by creating an object that dispenses connections and connection IDs on request. Others, often called pool drivers, implement a new JDBC driver that handles a pool of connections to another JDBC driver. Using a pooling driver like this is the easiest way to implement connection pooling in your servlets. Pooling drivers, however, can have a tiny bit more operational overhead than standard drivers because every JDBC class needs to be wrapped by another class.

Example 9-8 demonstrates a simple connection pooling system. A number of connections are created at startup and are handed out to methods as needed. If all the connections are in use, the servlet creates a new one. While our ConnectionPool class is fully functional, mission-critical deployments might benefit from one of the more complete third-party packages.

Example 9-8. The ConnectionPool Class
import java.sql.*; import java.util.*; public class ConnectionPool {   private Hashtable connections = new Hashtable();   private Properties props;   public ConnectionPool(Properties props, int initialConnections)                    throws SQLException, ClassNotFoundException {     this.props = props;     initializePool(props, initialConnections);   }   public ConnectionPool(String driverClassName, String dbURL,                         String user, String password,                         int initialConnections)                    throws SQLException, ClassNotFoundException {     props = new Properties();     props.put("connection.driver", driverClassName);     props.put("connection.url", dbURL);     props.put("user", user);     props.put("password", password);     initializePool(props, initialConnections);   }   public Connection getConnection() throws SQLException {     Connection con = null;     Enumeration cons = connections.keys();     synchronized (connections) {       while(cons.hasMoreElements()) {         con = (Connection)cons.nextElement();         Boolean b = (Boolean)connections.get(con);         if (b == Boolean.FALSE) {           // So we found an unused connection.           // Test its integrity with a quick setAutoCommit(true) call.           // For production use, more testing should be performed,           // such as executing a simple query.           try {             con.setAutoCommit(true);           }           catch(SQLException e) {             // Problem with the connection, replace it.             // http://www.servlets.com/jservlet2/errata.html             // The ConnectionPool class should try to close an invalid             // connection before replacing it, to support databases             // like Oracle which require explicit closing.             try {               con.close();             }             catch (SQLException ignored) {             }             connections.remove(con);             con = getNewConnection();           }           // Update the Hashtable to show this one's taken           connections.put(con, Boolean.TRUE);           // Return the connection           return con;         }       }       // If we get here, there were no free connections.  Make one more.       // A more robust connection pool would have a maximum size limit,       // and would reclaim connections after some timeout period       con = getNewConnection();       connections.put(con, Boolean.TRUE);       return con;     }   }   public void returnConnection(Connection returned) {     if (connections.containsKey(returned)) {       connections.put(returned, Boolean.FALSE);     }   }   private void initializePool(Properties props, int initialConnections)                    throws SQLException, ClassNotFoundException {     // Load the driver     Class.forName(props.getProperty("connection.driver"));     // Put our pool of Connections in the Hashtable     // The FALSE value indicates they're unused     for(int i = 0; i < initialConnections; i++) {       Connection con = getNewConnection();       connections.put(con, Boolean.FALSE);     }   }   private Connection getNewConnection() throws SQLException {     return DriverManager.getConnection(       props.getProperty("connection.url"), props);   } }

The ConnectionPool class maintains a Hashtable, using Connection objects as keys and Boolean objects as stored values. The Boolean value indicates whether a connection is in use. A program calls the getConnection( ) method of ConnectionPool to be assigned a Connection object it can use; it calls returnConnection( ) to give the connection back to the pool. This is a fairly simple model of a connection pool. For deployment, you probably want something that does a better job of maintaining the quality of the pool and does more verification of integrity than a simple call to setAutoCommit( ).

Example 9-9 shows a revised version of the order processing servlet that uses the pooling class.

Example 9-9. Connection Pooling Transaction Servlet
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class OrderHandlerPool extends HttpServlet {   private ConnectionPool pool;   public void init() throws ServletException {     try {       pool = new ConnectionPool("oracle.jdbc.driver.OracleDriver",                              "jdbc:oracle:oci7:orders", "user", "passwd", 5);     }     catch (Exception e) {       throw new UnavailableException("Couldn't create connection pool");     }   }   public void doPost(HttpServletRequest req, HttpServletResponse res)                                 throws ServletException, IOException {     Connection con = null;     res.setContentType("text/plain");     PrintWriter out = res.getWriter();     try {       con = pool.getConnection();       // Turn on transactions       con.setAutoCommit(false);       Statement stmt = con.createStatement();       stmt.executeUpdate(         "UPDATE INVENTORY SET STOCK = (STOCK - 10) WHERE PRODUCTID = 7");       stmt.executeUpdate(         "UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10) WHERE PRODUCTID = 7");       chargeCard();  // method doesn't actually exist...       con.commit();       out.println("Order successful!  Thanks for your business!");     }     catch (Exception e) {       // Any error is grounds for rollback       try {         con.rollback();       }       catch (Exception ignored) { }       out.println("Order failed. Please contact technical support.");     }     finally {       if (con != null) pool.returnConnection(con);     }   } } 

9.4.4 Connections as Part of a Session

Session tracking, which we examined in detail back in Chapter 7, gives us another way of handling transactions. Using sessions, we can create or allocate a dedicated database connection for individual users of a web site or intranet application. Example 9-10 demonstrates by showing a ConnectionPerClient servlet that associates a unique Connection with each client HttpSession. It wraps the Connection with a ConnectionHolder that is responsible for managing the connection's lifecycle.

Example 9-10. Associating a Connection with a Session
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; class ConnectionHolder implements HttpSessionBindingListener {   private Connection con = null;   public ConnectionHolder(Connection con) {     // Save the Connection     this.con = con;     try {       con.setAutoCommit(false);  // transactions can extend between web pages!     }     catch(SQLException e) {       // Perform error handling     }   }   public Connection getConnection() {     return con;  // return the cargo   }   public void valueBound(HttpSessionBindingEvent event) {     // Do nothing when added to a Session   }   public void valueUnbound(HttpSessionBindingEvent event) {     // Roll back changes when removed from a Session     // (or when the Session expires)     try {       if (con != null) {         con.rollback();  // abandon any uncomitted data         con.close();       }     }     catch (SQLException e) {       // Report it     }   } } /* Actual Servlet */ public class ConnectionPerClient extends HttpServlet {   public void init() throws ServletException {     try {       Class.forName("oracle.jdbc.driver.OracleDriver");     }     catch (ClassNotFoundException e) {       throw new UnavailableException("Couldn't load OracleDriver");     }   }   public void doGet(HttpServletRequest req, HttpServletResponse res)                                throws ServletException, IOException {     res.setContentType("text/plain");     PrintWriter out = res.getWriter();     HttpSession session = req.getSession(true);     Connection con;     // Synchronize: Without this two holders might be created for one client     synchronized (session) {       // Try getting the connection holder for this client       ConnectionHolder holder =         (ConnectionHolder) session.getAttribute("servletapp.connection");       // Create (and store) a new connection and holder if necessary       if (holder == null) {         try {           holder = new ConnectionHolder(DriverManager.getConnection(             "jdbc:oracle:oci7:ordersdb", "user", "passwd"));           session.setAttribute("servletapp.connection", holder);         }         catch (SQLException e) {           log("Couldn't get db connection", e);         }       }       // Get the actual connection from the holder       con = holder.getConnection();     }     // Now use the connection     try {       Statement stmt = con.createStatement();       stmt.executeUpdate(         "UPDATE INVENTORY SET STOCK = (STOCK - 10) WHERE PRODUCTID = 7");       stmt.executeUpdate(         "UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10) WHERE PRODUCTID = 7");       // Charge the credit card and commit the transaction in another servlet       res.sendRedirect(res.encodeRedirectURL(         req.getContextPath() + "/servlet/CreditCardHandler"));     }     catch (Exception e) {       // Any error is grounds for rollback       try {         con.rollback();         session.removeAttribute("servletapp.connection");       }       catch (Exception ignored) { }       out.println("Order failed. Please contact technical support.");     }   } }

Rather than directly binding a connection to the session, we've created a simple holder class that implements the HttpSessionBindingListener interface. We do this because database connections are the most limited resource in a JDBC application and we want to make sure that they will be released properly when no longer needed. The wrapper class also allows us to roll back any uncommitted changes. If a user leaves our hypothetical online shopping system before checking out, his transaction is rolled back when the session expires.

Storing connections in sessions requires careful analysis of your application's needs. Most low-end and midrange database servers can max out at about 100 connections; desktop databases like Microsoft Access saturate even more quickly.


Last updated on 3/20/2003
Java Servlet Programming, 2nd Edition, © 2001 O'Reilly

< BACKCONTINUE >


Java servlet programming
Java Servlet Programming (Java Series)
ISBN: 0596000405
EAN: 2147483647
Year: 2000
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net