9.3 Reusing Database Objects

Java Servlet Programming, 2nd Edition > 9. Database Connectivity > 9.3 Reusing Database Objects

 
< BACKCONTINUE >

9.3 Reusing Database Objects

In the introduction, we mentioned that the servlet lifecycle allows for extremely fast database access. After you've used JDBC for a short time, it will become evident that the major performance bottleneck often comes right at the beginning, when you are opening a database connection. This is rarely a problem for most applications and applets because they can afford a few seconds to create a Connection that is used for the life of the program. With servlets this bottleneck is more serious because we are creating and tearing down a new Connection for every page request. Luckily, the servlet lifecycle allows us to reuse the same connection for multiple requests, even concurrent requests, as Connection objects are required to be thread safe.

9.3.1 Reusing Database Connections

A servlet can create one or more Connection objects in its init( ) method and reuse them in its service( ) , doGet( ), and doPost( ) methods. To demonstrate, Example 9-6 shows the phone lookup servlet rewritten to create its Connection object in advance. It also uses the HtmlSQLResult class from Example 9-5 to display the results. Note that this servlet uses the Sybase JDBC driver.

Example 9-6. An Improved Directory Servlet
import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class DBPhoneLookupReuse extends HttpServlet {   private Connection con = null;   public void init() throws ServletException {     try {       // Load (and therefore register) the Sybase driver       Class.forName("com.sybase.jdbc.SybDriver");       con = DriverManager.getConnection(         "jdbc:sybase:Tds:dbhost:7678", "user", "passwd");     }     catch (ClassNotFoundException e) {       throw new UnavailableException("Couldn't load database driver");     }     catch (SQLException e) {       throw new UnavailableException("Couldn't get db connection");     }   }   public void doGet(HttpServletRequest req, HttpServletResponse res)                                throws ServletException, IOException {     res.setContentType("text/html");     PrintWriter out = res.getWriter();     out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");     out.println("<BODY>");     HtmlSQLResult result =       new HtmlSQLResult("SELECT NAME, PHONE FROM EMPLOYEES", con);     // Display the resulting output     out.println("<H2>Employees:</H2>");     out.println(result);     out.println("</BODY></HTML>");   }   public void destroy() {     // Clean up.     try {       if (con != null) con.close();     }     catch (SQLException ignored) { }   } }

9.3.2 Reusing Prepared Statements

With a little care, you can speed servlet performance even more by creating other database-related objects ahead of time. The PreparedStatement object is an ideal candidate because it can precompile a SQL statement. This usually saves only a few milliseconds, but if your site gets a few hundred thousand hits a day, that can add up pretty quickly.

Note, however, that sharing objects other than connections poses a problem. Servlets must be thread safe, and accessing a PreparedStatement might require three or four method calls. If one thread calls the clearParameters( ) method of PreparedStatement right before another thread calls execute( ) , the results of execute( ) will be disastrous. Also, there's the limitation that a Statement can support only one query (and any associated result sets) at a time. One solution is to synchronize the sections of your code that use shared objects, as discussed in Chapter 3 and shown here:

synchronized (pstmt) {   pstmt.clearParameters();   pstmt.setInt(1, 2);   pstmt.setInt(2, 4);   pstmt.setDouble(3, 53.43);   pstmt.executeUpdate(); }

Unfortunately, this solution is not without drawbacks. Entering a synchronization block on some platforms takes extra time, and synchronized objects can be used by only one thread at a time. However, some servlets already require a synchronization block, and in these cases the drawback is less of an issue. A good rule of thumb, then, is to create your connections ahead of time, along with any frequently used objects (such as PreparedStatement objects) that can be quickly used inside preexisting synchronization blocks.

For servlets written using the SingleThreadModel interface, these issues do not apply. On the other hand, you will have a number of copies of your servlet loaded at once, which could be just as detrimental to performance.


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