Resin provides a robust and very well tested connection pool that is used to obtain connections to databases.

Core Concepts

Connection

A database connection is used to allow the Java program, running in a JVM, to communicate with a database server.

Connection Pool

Connection pools are used to reduce the overhead of using a database. Establishing a connection to the database is a costly operation. A connection pool keeps a pool of open connections, each connection can be used for a time as needed, and then released back to the pool. A connection that has been released back to the pool can then be reused.

Connection pooling is especially important in server applications. The overhead of opening a new connection for each new client request is too costly. Instead, the database pool allows for a connection to be opened once and then reused for many requests.

DataSource

Resin provides an implementation of DataSource. Resin's implementation of DataSource is a connection pool.

Driver

A Driver provides an interface and is responsible for the communication with the database. Every different database (i.e Oracle, MySQL) has their own means of enabling communication from the client (in this case Resin and you applications) and the database. The Driver provides a common interface that hides the details of that communication.

Transaction

Transactions are especially important in server applications where many threads of processing may be interacting with the database at the same time.

For a simple example, imagine a set of operations that reads a value, calculates a new value, and then updates the database.

Simple set of database operations

read value A=1 calculate A=A+1 update A=2 read value A=2 calculate A=A+1 update A=3

Imagine if one thread is performing this operation, and in the middle of this read/calculate/update, another thread performs an update. The data that the first thread obtained from the read and is using for the calculation and update is no longer valid.

2 Threads with a conflicting set of database operations

Thread 1 Thread 2 -------- -------- read value A=1 read value A=1 calculate A=A+1 calculate A=A+1 update A=2 update A=2

Placing the read/calculate/update operations in a transactions guarantees that only one thread can perform those operations at a time, if a second thread comes along and tries to perform the operation, it will have to wait for the first thread to finish before it can begin.

2 Threads and a database protected with transactions

Thread1 Thread 2 ------- -------- read value A=1 calculate A=A+1 (tries to read A, but has to wait for thread 1) update A=2 read value A=2 calculate A=A+1 update A=3

Distributed Transaction

If the guarantees that transactions apply need to apply to operations that occur on two databases within the same transaction, distributed transactions are needed.

If A and B in the following example are in two different databases, then a distributed transaction is needed:

Simple set of database operations

read value db1.A=1 read value db2.B=99 calculate A=A+1 calculate B=B-A update db1.A=2 update db2.B=97

Distributed transactions are rarely needed, and few databases really support them.

Core Configuration

database

Resin 3.0

Driver Configuration

driver

Resin 3.0

Choosing a driver class for <type>

Database vendors usually provide many different classes that are potential candidates for type. The JDBC api has developed over time, and is now being replaced by the more general JCA architecture. The driver you choose depends on the options the vendor offers, and whether or not you need distributed transactions.

JCA drivers

JCA is replacing JDBC as the API for database drivers. JCA is a much more flexible approach that defines an API that can be used for any kind of connection, not just a connection to a database. If a database vendor provides a JCA interface, it is the best one to use.

A JCA driver implements ManagedConnectionFactory. When you specify such a class for type, Resin will notice that it is a JCA driver and take advantage of the added functionality that the JCA interface provides.

The same JCA driver is used for both non-distributed and distributed transactions

JDBC 2.0 - ConnectionPoolDataSource

JDBC 2.0 defined the interface ConnectionPoolDataSource. A ConnectionPoolDataSource is not a connection pool, but it does provide some extra information that helps Resin to pool the connection more effectively.

A driver that implements ConnectionPoolDataSource is better than a JDBC 1.0 driver that implements Driver.

JDBC 2.0 - XADataSource

JDBC 2.0 defined the interface XADataSource for connections that can participate in distributed transactions. A distributed transaction is needed when transactions involve multiple connections. For example, with two different database backends, if the guarantees that transactions apply need to apply to operations that occur on both databases within the same transaction, distributed transactions are needed.

Distributed transactions are rarely needed, and few databases really support them. Some vendors will provide XADataSource drivers even though the database does not really support distributed transactions. Often, XADataSource drivers are slower than their ConnectionPoolDataSource counterparts.

XADataSource should only be used if distributed transactions are really needed, and can probably be safely ignored for most applications.

JDBC 1.0 - Driver

Driver is the original JDBC interface, and is the least desirable kind of driver to use. Resin can still pool database connections using these drivers, but it will not be as efficient as the newer drivers.

Set driver properties with init-param

init-param is used to set properties of the database driver that are specific to the driver and are not generic enough for resin to provide a named configuration tag.

For example, MySQL drivers accept the useUnicode parameter, if true the driver will use Unicode characeter encodings when handling strings.

<database> <jndi-name>jdbc/mysql</jndi-name> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://localhost:3306/dbname</url> <user>username</user> <password>password</password> <init-param useUnicode="true"/> </driver> ... </database>

Pooling Configuration

Reliability Configuration

Resin's database pool can test if the pooled database connection is still alive by configuring a ping query. The database connection may become stale if the database is restarted, possibly for maintenance. Normally when a database connection is returned to the pool it will wait there until the next request. If the database goes down in the meantime, the connection will become stale. The ping configuration can test the database connection.

When pinging, Resin's DBPool will test a table specified with the ping-table parameter. For a ping-table of my_table, Resin will use a query like the following:

SELECT 1 FROM my_table

There are three ping modes: ping-on-free, ping-on-idle, and ping-on-reuse. ping-on-free tests the database when the connection is returned to the database pool, ping-on-idle tests the connection when it's in the idle pool, and ping-on-reuse tests the connection just before using the connection.

You can test the database reliability using the following steps:

1. Configure the database with ping-table and ping-on-idle.
2. Execute some servlet that queries the database.
3. Restart the database server.
4. Execute another servlet that queries the database.

Obtaining and using a database connection

Getting the DataSource

The DataSource is a factory that is used to obtain a connection. The DataSource is obtained using the <jndi-name> specified when configuring the database resource.

Ideally, the JNDI lookup of DataSource is done only once, the DataSource obtained from the lookup can be stored in a member variable or other appropriate place. The stored DataSource can then be used each time a connection is needed. If it is not stored, there will be an impact on performance from having to do the lookup each time you want to get a connection.

Obtaining a DataSource

public class .... { private final static String DATASOURCE_NAME = "jdbc/test"; DataSource _pool; ... void init() { try { Context env = (Context) new InitialContext().lookup("java:comp/env"); _pool = (DataSource) env.lookup(DATASOURCE_NAME); if (_pool == null) throw new ServletException("`" + DATASOURCE_NAME + "' is an unknown DataSource"); } catch (NamingException e) { throw new ServletException(e); } } ... }

Getting a Connection

A connection is obtained from the DataSource. The connection is used as needed, and then released with a call to close() so that Resin knows it is available for a subsequent request.

It is very important that the close() is always called, even if there as an exception. Without the close(), Resin's database pool can loose connections. If you fail to close() a connection, Resin does not know that it is available for reuse, and cannot allocate it for another request. Eventually, Resin may run out of connections.

Always put a close() in a finally block, to guarantee that it is called.

The following example shows the use of a finally block that contains the close(). Because the close() is in a finally block, it will happen even if the code using the connection throws an exception.

Getting a connection from the DataSource

Connection conn = null; try { conn = pool.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(" ... "); ... rs.close(); stmt.close(); } catch (SQLException e) { throw new ServletException(e); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } }

Using Databases from a Servlet

The following is a sample design pattern for getting new database connections from within a servlet.. The try ... finally block is very important. Without the close in the finally block, Resin's database pool can loose connections.

This is so importaant, it is worth repeating. The try ... finally block is very important. Without the close in the finally block, Resin's database pool can loose connections. If you fail to close() a connection, Resin cannot know that it is not being used anymore, and cannot allocate it for another request. Eventually, you may run out of connections.

The DataSource is obtained using the <jndi-name> specified when configuring the database resource. The JNDI lookup of DataSource is done only once, the DataSource is stored in the _pool member variable so that it can be used for each request. If it was not stored, there would be an impact on performance from having to do the lookup each time.

TestDatabase.java

package test; import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; import javax.naming.*; import javax.sql.*; public class TestDatabase extends HttpServlet { private final static String DATASOURCE_NAME = "jdbc/test"; DataSource _pool; public void init() throws ServletException { try { Context env = (Context) new InitialContext().lookup("java:comp/env"); _pool = (DataSource) env.lookup(DATASOURCE_NAME); if (_pool == null) throw new ServletException("`" + DATASOURCE_NAME + "' is an unknown DataSource"); } catch (NamingException e) { throw new ServletException(e); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); Connection conn = null; try { conn = _pool.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select NAME, PRICE from BROOMS"); out.println("Brooms:<br>"); while (rs.next()) { out.print(rs.getString(1)); out.print(" "); out.print(rs.getInt(2)); out.println("<br>"); } rs.close(); stmt.close(); } catch (SQLException e) { throw new ServletException(e); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } } }

Copyright © 1998-2003 Caucho Technology, Inc. All rights reserved. Resin® is a registered trademark, and HardCoretm and Quercustm are trademarks of Caucho Technology, Inc.