Basic Configuration

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.

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.

 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.

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:

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

Driver Configuration

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. The driver you choose depends on the options the vendor offers, and whether or not you need distributed transactions.

Set driver properties with init-param

init-param is used to set properties for java.sql.Driver database drivers. More modern drivers like ConnectionPoolDataSource will not use init-param, but will use IoC-style tags directly.

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

<database>
  <jndi-name>jdbc/mysql</jndi-name>
  <driver>
    <type>com.mysql.jdbc.Driver</type>
    <url>jdbc:mysql://localhost:3306/dbname</url>
    <user>username</user>
    <password>password</password>

    <init-param useUnicode="true"/>
  </driver>
  ...
</database>

<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>

    <useUnicode/>
  </driver>
  ...
</database>

Pooling Configuration

Reliability Configuration

ping

Resin's database pool can test if the pooled database connection is still alive by configuring a ping query. This is typically only necessary if the pooling parameters are changed from their default values.

If the pool is configured with a long max-idle-time the database connection may become stale if the database is restarted, or if the database is configured with a shorter connection timeout value than the configuration of the Resin pool. Normally when a database connection is returned to the pool it will wait there until the next request or the idle-time expires. If the database goes down in the meantime or closes the connection, 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 before returning the connection to the application. If the ping fails, the connection is assumed to be no good and a different connection from the pool is returned. For a ping-table of BROOMS, Resin will use the query select 1 from BROOMS where 1=0

  <database jndi-name="...">
    <driver type="...">
      ...
    </driver>

    <ping>true</ping>
    <ping-table>BROOMS</ping-table>
  </database>

You can test the ping using the following steps:

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

<driver> list

If there is a pool of database servers available that can be used for database operations, Resin can be configured with a list of <driver> tags. Resin uses a round robin algorithm to cycle through the list of drivers when obtaining connections. If a particular <driver> fails to provide a connection, Resin continues the attempt to obtain a connection. If all of the configured drivers fail to provide a connection the exception is propogated to the caller.

<database jndi-name="jdbc/hogwarts">
  <driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.110:3306/hogwarts</url>
    ...
  </driver>

  <driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.111:3306/hogwarts</url>
    ...
  </driver>
  ...
</database>

<backup-driver> list

Drivers in a driver list can be marked as backups. The drivers configured with <backup-driver> are used only if all of the drivers configured with <driver> have failed.

Each time a new connection is needed Resin goes through the process of first attempting to use one of the <driver> configured drivers to get a connection, and if that fails then the <backup-driver> are used. A new connection is needed from the driver if the pool of connections that is maintained by Resin does not contain an idle connection. The Pooling configuration and the usage pattern of the application determine how often a connection is obtained from a driver. The pooling configuration typically allows a single real connection to be reused by the application many times.

The lifetime of a connection obtained from a <backup-driver> is determined by the Pooling configuration, thus even if the main <driver> becomes available again a connection previously obtained from a <backup-driver> will continue to be used until it expires from the pool.

<database jndi-name="jdbc/hogwarts">
  <driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.110:3306/hogwarts</url>
    ...
  </driver>

  <driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.111:3306/hogwarts</url>
    ...
  </driver>

  <backup-driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.112:3306/hogwarts</url>
    ...
  </backup-driver>

  <backup-driver>
    <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type>
    <url>jdbc:mysql://192.168.0.113:3306/hogwarts</url>
    ...
  </backup-driver>
  ...
</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.

import javax.sql.*;
import javax.inject.*;

public class .... {
  @Named("jdbc/test") DataSource _pool;

  ...
}

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.

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.

package javax.inject.*;
package javax.sql.*;

public class MyBean()
{
  @In DataSource _pool;

  public void doStuff()
  {
    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) {
      }
    }
  }
}

Getting the underlying driver connection

The connection obtained by pool.getConnection() is an instance of com.caucho.sql.UserConnection.

UserConnection is a wrapper around the real driver connection, it allows Resin to intercept the close() call and manage the underlying driver connection.

In rare circumstances it is necessary to obtain the real connection returned by the driver. Typically this is a requirement for situations where the driver provides a specialized API that is not available with the standard JDBC API.

Connection driverConn = ((com.caucho.sql.UserConnection) connection).getConnection();

// never do this: driverConn.close()

Protecting the database password

Hibernate