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, he 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, "The Servlet Life Cycle". 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.
Transaction management with JDBC takes place via the Connectionobject. By default, new connections start out in auto-commit 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 auto-commit 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-5 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.
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 initialization, 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.
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.
Create a single Connection object in the init() method and have the servlet implement SingleThreadModel, so the web server creates a pool of servlet instances with a Connection for each, as shown in Figure 9-4. This has the same effect as synchronizing doPost(), but because the web server has a number of servlet instances to choose from, the performance hit for the user is not as great. This approach is easy to implement, but is less robust than using a separate connection pool because the servlet has no control over how many servlet instances are created and how many connections are used. When creating single-threaded database servlets, be especially sure to have the destroy() method close any open database connections.
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.
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), rather than the entire servlet. 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, such as the pool drivers package available from WebLogic at http://www.weblogic.com, 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, have a little more operational overhead than standard drivers because every JDBC class needs to be wrapped by another class. This is transparent to the programmer and won't make much of a difference with most Java applications--but with a high-performance, high-volume servlet application, every little performance gain helps.
Example 9-6 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.
import java.sql.*;
import java.util.*;
public class ConnectionPool {
private Hashtable connections;
private int increment;
private String dbURL, user, password;
public ConnectionPool(String dbURL,
String user,
String password,
String driverClassName,
int initialConnections,
int increment)
throws SQLException, ClassNotFoundException {
// Load the specified driver class
Class.forName(driverClassName);
this.dbURL = dbURL;
this.user = user;
this.password = password;
this.increment = increment;
connections = new Hashtable();
// Put our pool of Connections in the Hashtable
// The FALSE value indicates they're unused
for(int i = 0; i < initialConnections; i++) {
connections.put(DriverManager.getConnection(dbURL, user, password),
Boolean.FALSE);
}
}
public Connection getConnection() throws SQLException {
Connection con = null;
Enumeration cons = connections.keys();
synchronized (connnections) {
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.
con = DriverManager.getConnection(dbURL, user, password);
}
// 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.
// We've got to make more.
for(int i = 0; i < increment; i++) {
connections.put(DriverManager.getConnection(dbURL, user, password),
Boolean.FALSE);
}
// Recurse to get one of the new connections.
return getConnection();
}
public void returnConnection(Connection returned) {
Connection con;
Enumeration cons = connections.keys();
while (cons.hasMoreElements()) {
con = (Connection)cons.nextElement();
if (con == returned) {
connections.put(con, Boolean.FALSE);
break;
}
}
}
}
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-7 shows a revised version of the order processing servlet that uses the pooling class.
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class OrderHandlerPool extends HttpServlet {
private ConnectionPool pool;
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
pool = new ConnectionPool("jdbc:oracle:oci7:orders", "user", "passwd",
"oracle.jdbc.driver.OracleDriver", 10, 5);
}
catch (Exception e) {
throw new UnavailableException(this, "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);
}
}
}
Session tracking, which we examined in detail back in Chapter 7, "Session Tracking", 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-8 demonstrates by showing a ConnectionPer-Client 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 life cycle.
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(ServletConfig config) throws ServletException {
super.init(config);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e) {
throw new UnavailableException(this, "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);
// Try getting the connection holder for this client
ConnectionHolder holder =
(ConnectionHolder) session.getValue("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.putValue("servletapp.connection", holder);
}
catch (SQLException e) {
getServletContext().log(e, "Couldn't get db connection");
}
}
// Get the actual connection from the holder
Connection 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("/servlet/CreditCardHandler"));
}
catch (Exception e) {
// Any error is grounds for rollback
try {
con.rollback();
session.removeValue("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 HttpSessionBindingListner 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 rollback any uncommitted changes. If a user leaves our hypothetical online shopping system before checking out, her transaction is rolled back when the session expires.
Storing connections in sessions requires careful analysis of your application's needs. Most low-end and mid-range database servers can max out at about 100 connections; desktop databases like Microsoft Access saturate even more quickly.
 |  |  |
| 9.3. Reusing Database Objects |  | 9.5. Advanced JDBC Techniques |
Copyright © 2001 O'Reilly & Associates. All rights reserved.