Optimizing JDBC

This section provides some ideas for improving your coding practices when accessing databases.

Using database connection pooling

With connection pooling, database connections are borrowed and returned to a pool of reusable objects, rather than created and destroyed for each request. JRun has its own database connection pooling mechanism built in and it is enabled by default for each data source you add in the JMC.

If you do not use JRun data sources, you must implement your own custom connection pooling mechanism.

You can manipulate the JRun data sources using optional settings that are not exposed in the JMC. All of these settings are optional. The following table describes these settings:

Parameter	Description	Default
native-results	Set to true to use the cacheable, scrollable, updatable JRun ResultSet implementation. Set to false to use the ResultSet returned by the underlying JDBC driver.	true
initial-connections	Set the number of connections that JRun creates when the pool is instantiated.	1
pool-statements	Set to true to pool PreparedStatements instead of recreating them with each invocation.	true
minimum-size	Set the minimum number of objects that JRun retains in the pool.	0
maximum-size	Set the maximum number of objects that JRun maintains in the pool.	2147483647
maximum-soft	Set to true to have the connection pool create new emergency objects if the maximum size of a pool is reached but requests are still waiting on objects. This temporarily increases the size of the pool, but the pool shrinks back down to an acceptable size automatically when the skimmer activates. Set to false to force the requests to wait until an object is available. For information about the skimmer, see the description of the skimmer-frequency setting.	true
connection-timeout	Set the length of time in seconds that JRun lets a connection remain dormant before destroying it.	1200
user-timeout	Set the length of time in seconds that a user must keep a connection before it is automatically returned to the pool.	20
skimmer-frequency	Set the length of time in seconds that the pool skimmer waits between reap cycles. During each reap cycle, the skimmer evaluates all connections (both checked in and checked out) to determine whether to automatically return them to the pool or to destroy them if they have timed out.	420
shrink-by	Set the maximum number of objects that can be removed from the pool in any one reap cycle. JRun checks this value each time the pool is sized down by the skimmer. It prevents JRun's connection pooling mechanism from backing off the pool too quickly at peak times.	5
debugging	Set to true to enable verbose logging information.	false
cache-enabled	Set to true to enable the query/ResultSet cache.	false
cache-size	Set the maximum number of query/ResultSet pairs that the cache can contain.	5
cache-refresh-interval	Set the interval, in seconds, between the cache's reloading of its ResultSets from the database.	30
remove-on-exceptions	Set to true to remove a Connection from the pool if there is a SQLException.	false

To change connection pool settings, edit the properties of the appropriate data source in the jrun-resources.xml file. For example, the following setting overrides the default skimmer-frequency value of 420 and replaces it with 100:

<datasource>

...
 <skimmer-frequency>100</skimmer-frequency>
...
</datasource>

Using prepared statements

Whenever you query a database, JRun compiles the query and sends it to the database through the database connection. For frequently used queries, you can use PreparedStatements. PreparedStatements are precompiled and stored on the server. They can contain any number of variables, indicated with question marks, which are inserted into the query at runtime.

The following code example shows how to build a PreparedStatement with three variables:

String sqlstmt = "INSERT INTO SESSIONS VALUES(?,?,?)";

try {
 InitialContext ctx = new InitialContext();
 DataSource ds = (DataSource) ctx.lookup(dsName);
 conn = ds.getConnection();
 ps = conn.prepareStatement(sqlstmt);
 ps.setString(1,sid);
 ps.setLong(2,time);
 ps.setInt(3,1);
 rs = ps.executeQuery();
} catch (SQLException sqle) {
...
}
...

For more information on using the PreparedStatement object, see the Java 2 API documentation.

Closing connections, statements and resultsets

After you use Connection, Statement, and ResultSet objects, close them to free up the resources that they claimed, which returns unused connections to the pool. Add a finally block, as shown in the following example:

...
} finally {
 try {
  ps.close();
  rs.close();
 } catch (Exception e) {
  e.printStackTrace();
 }
}

Limiting fetches and number of rows

The ResultSet and Statement objects expose a number of methods that are useful for tweaking database operations, including the following:

• Statement.setMaxRows
• setFetchSize
• setFetchDirection

Developers often confuse the setFetchSize and setMaxRows methods. You can invoke them on either the ResultSet or Statement objects. To understand the difference between these methods, first you must understand what happens when a database returns the result of a query. The following sections describe querying a database, and the differences between the setFetchSize and setMaxRows methods.

Database query overview

When you submit a query to the database, the database processes the query and stores the resulting data in its cache. The ResultSet object holds references to the records in the database's cache, the number of which being equal to the fetch size. In other words, the ResultSet object is only an open connection to a database, and does not contain the data from the returned rows.

The JDBC driver, which provides the connection between the JRun server and the database, gets some of the rows from the database cache and returns them to the client-side (that is, the JRun server is the database's client) for processing. The driver gets the number of rows equal to getFetchSize.

When the client finishes processing the rows provided by the drivers, the JDBC driver prefetches the next getFetchSize rows, and so on.

Using setMaxRows

To limit the number of rows that the database returns from the query, use setMaxRows, as shown in the following example:

Statement stmt = conn.createStatement();

stmt.setMaxRows(10);
ResultSet rs = stmt.executeQuery("SELECT * from SESSIONS");

To limit the number of rows the database returns, you can also use the LIMIT command in your SQL statement. The following example shows a ResultSet being limited to 100 rows:

rs = ps.executeQuery("SELECT * FROM tablename LIMIT 100");

Using setFetchSize

To set the number of rows that the JDBC driver gets from the database cache and returns to JRun, use setFetchSize. Set it to lower numbers if the data requires a great deal of processing.

Note:   The setFetchSize method is only a hint to the database on how to fetch data. Not all database drivers support this method.

When determining an appropriate setFetchSize, consider the user interaction with the front end and how users will navigate the results of their queries. For example, if the user interface lets users interact with 10 rows at a time, set your fetch size to 10.

Using setFetchDirection

Use the setFetchDirection method if you are processing ResultSets and want to return the last rows first. This can speed up processing time, especially when working with a large amount of data.

For example, if you have a ResultSet of transactions that is sorted by date, you might want the most recent transactions in the first fetch.

Note:   The setFetchDirection method is only a hint to the database on how to fetch data. Not all database drivers support this method.

The following code shows an example of reversing the fetch direction:

ResultSet rs = null;

Connection conn = null;
String sql = "SELECT * FROM employee";
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = stmt.executeQuery(sql);

Testing the JDBC drivers

Not all JDBC drivers are alike. Test different drivers with different databases under a variety of loads to see which combination works best with your web application.

Use the JDBC-to-ODBC bridge only if absolutely necessary.

For more information on JDBC drivers, see the Sun website at http://industry.java.sun.com/products/jdbc/drivers

Caching static data

The fastest way to access a database is to not access it at all. Where possible, you should try to cache static data rather than obtain it dynamically through calls to a database. Limit access to the database to a single query during initialization in the init and jspInit methods of servlets and JSPs. Then cache the results and allow the remaining requests to access that cached data. For more information, see the following sections:

"Caching static data in the init method"

"Caching static data in jspInit"