Pool configuration

From Resin 3.0

 <product>resin</product>
 <title>Database Configuration</title>
 <version>Resin 3.0</version>

 <description>

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

 </description>

</header>

<a href="../doc/db-thirdparty.xtp">Sample configurations</a> for several database drivers
<a href="../examples/db-jdbc/index.xtp">Basic JNDI/JDBC usage tutorial</a>
Using <a href="../examples/db-jdbc-ioc/index.xtp">Java Injection for Databases</a>

</s1>

A basic <database> configuration specifies the following:

The JNDI name where the configured DataSource will be stored
The database driver's main class
The driver-specific url for the database.
Any user and password information.

 <driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
   <url>jdbc:mysql://localhost:3306/test</url>
   <user></user>
   <password></password>
 </driver>

</database>

</web-app> </example>

This <database> will configure a javax.sql.DataSource and store it in JNDI at java:comp/env/jdbc/test_mysql. To use the data source, follow the database use pattern in the <a href="../examples/db-jdbc/index.xtp">DataSource tutorial.</a>

Sample <database> configurations are available in the <a href="../doc/db-thirdparty.xtp">thirdparty driver page.</a>

Although some deployments will specify driver and

connection pool parameters, the default values will be fine for most

applications.

</s1>

<s2 title="Connection"> <glossary title="connection" type="inline"> An established channel of communication between a client and a server. The client and the server may be on separate machines, on the same machine, or even running in the same JVM. Often the connection is established using TCP/IP as the transport of communication. </glossary>

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

</s2>

<s2 title="Connection Pool"> <glossary title="connection pool" type="inline"> A set of connections maintained so that the connections can be reused when there is a future need for the conneciton. </glossary>

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.

</s2>

<s2 title="DataSource"> <glossary title="DataSource" type="inline"> A JDBC term (and interface name) used for a factory that is used to obtain connections. </glossary>

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

</s2>

<glossary title="driver" type="inline"> An implemetation of a defined interface that hides the details of communication with a device or other resource, such as a database. </glossary>

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.

</s2>

<glossary title="transaction" type="inline"> A transaction is used to mark a group of operations and provide a guarantee that all of the operations happen, or none of them happen. Transactions protect the integrity of the database. </glossary>

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. <example title="Example: 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 </example>

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.

<example title="Example: 2 Threads with database race condition"> 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 </example>

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

</example>

</s2>

<glossary title="distributed transaction" type="inline"> A distributed transaction is a transaction that involves more than one connection. </glossary>

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:

<example title="Example: 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 </example>

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

</s2>

</s1>

<s2 title="database" version="Resin 3.0" type="defun"> <parents>server, host-default, host, web-app-default, web-app</parents>

Configure a database resource, which is a database pool that manages and provides connections to a database.

<deftable-childtags> <tr>

 <th>attribute</th>
 <th>Description</th>
 <th>Default</th>

</tr> <tr>

 <td>jndi-name</td>
 <td>JNDI name to store the pool under.  Servlets, jsp, and

other java code use this name. The path is relative to java:comp/env</td>

 <td> </td>

</tr> <tr>

 <td>driver</td>
 <td>Configure the database <a href="#driver">driver</a>.</td>
 <td></td>

</tr> <tr>

 <td>backup-driver</td>
 <td>Configure a backup <a href="#driver">driver</a>.</td>
 <td></td>

</tr> <tr>

 <td>max-connections</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum number of allowed connections</td>
 <td>1024</td>

</tr> <tr>

 <td>max-idle-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time an idle connection is kept in

the pool</td>

 <td>30 sec</td>

</tr> <tr>

 <td>max-active-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time a connection allowed to be active</td>
 <td>6 hours</td>

</tr> <tr>

 <td>max-pool-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time a connection is kept in the pool</td>
 <td>24 hours</td>

</tr> <tr>

 <td>connection-wait-time</td>
 <td><a href="#pooling">Pooling parameter</a> - how long to wait for an idle connection (Resin 1.2.3)</td>
 <td>30 sec</td>

</tr> <tr>

 <td>max-overflow-connections</td>
 <td><a href="#pooling">Pooling parameter</a> - how many "overflow" connection are allowed if the connection wait times out.</td>
 <td>1024</td>

</tr> <tr>

<td>ping-table</td>
<td><a href="#reliability">Reliability parameter</a> - The database table used to "ping", checking that the connection is still live.</td>
<td>n/a</td>

</tr> <tr>

 <td>ping</td>
 <td><a href="#reliability">Reliability parameter</a> - test for live connections before allocating them from the pool.</td>
 <td>false</td>

</tr> <tr>

 <td>ping-interval</td>
 <td><a href="#reliability">Reliability parameter</a> - set the minimum interval for pings, instead of doing a ping every time</td>
 <td>1s</td>

</tr> <tr>

 <td>prepared-statement-cache-size</td>
 <td>A cache that holds prepared statements, a reused prepared statement avoids the overhead of the driver making the prepared statement</td>
 <td>0</td>

</tr> <tr>

 <td>spy</td>
 <td>A debugging aid, if true, generate info level log events that reveal the SQL that is used with the connections.</td>
 <td>false

</td></tr></deftable-childtags>

All times default to seconds, but can use longer time periods:

<th>Suffix</th>
<th>Description</th>

</tr> <tr><td>s</td><td>seconds </td></tr><tr><td>m</td><td>minutes </td></tr><tr><td>h</td><td>hours </td></tr><tr><td>D</td><td>days </td></tr></deftable>

The class that corresponds to <database> is <a href="javadoc|com.caucho.sql.DBPool|"/>

</s2>

<s2 title="connection" version="Resin 3.0" type="defun"> <parents>database</parents>

Initialize java.sql.Connection object managed by database.

<deftable-childtags> <tr>

 <th>attribute</th>
 <th>Description</th>
 <th>Default</th>

</tr> <tr>

 <td>catalog</td>
 <td>Sets catalog property on java.sql.Connection object.</td>
 <td> </td>

</tr> <tr>

 <td>read-only</td>
 <td>Sets readOnly property on java.sql.Connection object.</td>
 <td>false</td>

</tr> <tr>

 <td>transaction-isolation</td>
 <td>Sets transaction isolation property on java.sql.Connection object.
   All levels defined in java.sql.Connection.TRANSACTION_*
   are supported via value set:
   none, read-committed, read-uncommitted, repeatable-read, serializable.
 </td>
 <td> unspecified </td>

</tr> </deftable-childtags>

The class that corresponds to <connection> is <a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html">Connection (Java 2 Platform SE 5.0)</a>

</s2>

</s1>

<s2 title="driver" version="Resin 3.0" type="defun"> <parents>database</parents>

Configure a database driver. The driver is a class provided by the database vendor, it is responsible for the communication with the database.

The jar file with the driver in it can be placed in WEB-INF/lib, although it is often best to place your datbase driver's jar file in $RESIN_HOME/lib/local/, which makes the driver available to all of your web applications.

Examples of common driver configurations are in <a href="../doc/db-thirdparty.xtp">Third-party Database Configuration</a>.

The class that corresponds to <driver> is <a href="javadoc|com.caucho.sql.DriverConfig"/>

<deftable-childtags> <tr>

 <th>Attribute</th>
 <th>Description</th>

</tr> <tr><td>type</td><td>The Java class name of the database driver.</td><td> </td></tr><tr><td>url</td><td>The driver specific database url.</td><td> </td></tr><tr><td>user</td><td>The username to give the database driver.</td><td> </td></tr><tr><td>password</td><td>The password to give the database driver.</td><td> </td></tr><tr><td>init-param</td><td>Set <a href="#init-param">driver specific properties</a> not known to Resin.</td><td> </td></tr></deftable-childtags>

</s2>

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

</s3>

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.

</s3>

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.

</s3>

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.

</s3> </s2>

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 character encodings when handling strings.

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

</s2>

</s1>

Pooling configuration controls the behaviour of Resin's pooling of database connections. For most applications and databases the only needed change is to increase the max-connections value to meet high demand. Other pooling parameters have defaults that are based on our years of experience with many different databases in many different applications. Changes from the defaults should only be done in response to specific problems, and with a good understanding of how pooling works.

</s1>

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 <a href="#pooling">pooling</a> 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

<example title="Example: <ping> configuration">

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

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

</example>

You can test the ping using the following steps:

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

</s2>

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.

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

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 <a href="#pooling">Pooling configuration</a> 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 <a href="#pooling">Pooling configuration</a>, 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.

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

</s3>

</s2>

</s1>

The DataSource is a factory that is used to obtain a connection. The DataSource is obtained using the <a config-tag="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.

<example title="Example: Obtaining a DataSource"> import javax.sql.*; import javax.webbeans.*;

public class .... {

 @Named("jdbc/test") DataSource _pool;

...

} </example> </s2>

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.

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

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.

<example title="Example: Getting a connection from the DataSource"> package javax.webbeans.*; 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) {
     }
   }
 }

} </example>

</s2>

The connection obtained by pool.getConnection() is an instance of <a href="javadoc|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.

<example title="Example: Getting the underlying driver connection"> Connection driverConn = ((com.caucho.sql.UserConnection) connection).getConnection();

// never do this: driverConn.close() </example>

</s2>

</s1>

Resin provides facilities that allow you to plugin your own custom code that returns a password to Resin. However any solution is vulnerable, unless you require a person to type in a password every time Resin starts (or restarts). Typically the security of the machine hosting Resin, and proper permissions on the readability of the resin.xml file, are sufficient to protect your database password.

The solution shown below is not really secure because you can disassemble the Password code to get the decryption key, but it may be marginally better than plaintext.

 <password resin:type="com.hogwarts.Password">mX9aN9M==</password> 
 ...

</example>

You will need to provide com.hogwarts.Password:

<example title="Example: Password class"> package com.hogwarts;

public class Password {

 private String _value; 

 public void addText(String value) 
 { 
   _value = value; 
 } 

 public Object replaceObject() 
 { 
   return decrypt(_value); 
 }

 private String decrypt(String encrypted)
 {
   ... custom code ...
 }

} </example>

This solution is completely general, you can use resin:type anywhere in the configuration files where a string value is allowed.

Resin does not provide the equivalent of com.hogwarts.Password because it's not really secure. Providing that kind of solution would lead some to believe it was a secure solution.

</s1>

</body> </document>

 <product>resin</product>
 <title>Database Configuration</title>
 <version>Resin 3.0</version>

 <description>

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

 </description>

</header>

<a href="db-thirdparty.xtp">Sample configurations</a> for several database drivers
<a href="../examples/db-jdbc/index.xtp">Basic JNDI/JDBC usage tutorial</a>
Using <a href="../examples/db-jdbc-ioc/index.xtp">Dependency Injection for Databases</a>

</s1>

A basic <database> configuration specifies the following:

The JNDI name where the configured DataSource will be stored
The database driver's main class
The driver-specific url for the database.
Any user and password information.

 <driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
   <url>jdbc:mysql://localhost:3306/test</url>
   <user></user>
   <password></password>
 </driver>

</database> </example>

This <database> will configure a javax.sql.DataSource and store it in JNDI at java:comp/env/jdbc/test_mysql. To use the data source, follow the database use pattern in the <a href="../examples/db-jdbc/index.xtp">DataSource tutorial.</a>

Sample <database> configurations are available in the <a href="db-thirdparty.xtp">thirdparty driver page.</a>

Although some deployments will specify driver and

connection pool parameters, the default values will be fine for most

applications.

</s1>

<s2 title="Connection"> <glossary title="connection" type="inline"> An established channel of communication between a client and a server. The client and the server may be on separate machines, on the same machine, or even running in the same JVM. Often the connection is established using TCP/IP as the transport of communication. </glossary>

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

</s2>

<s2 title="Connection Pool"> <glossary title="connection pool" type="inline"> A set of connections maintained so that the connections can be reused when there is a future need for the conneciton. </glossary>

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.

</s2>

<s2 title="DataSource"> <glossary title="DataSource" type="inline"> A JDBC term (and interface name) used for a factory that is used to obtain connections. </glossary>

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

</s2>

<glossary title="driver" type="inline"> An implemetation of a defined interface that hides the details of communication with a device or other resource, such as a database. </glossary>

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.

</s2>

<glossary title="transaction" type="inline"> A transaction is used to mark a group of operations and provide a guarantee that all of the operations happen, or none of them happen. Transactions protect the integrity of the database. </glossary>

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. <example title="Example: 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 </example>

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.

<example title="Example: 2 Threads with database race condition"> 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 </example>

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

</example>

</s2>

<glossary title="distributed transaction" type="inline"> A distributed transaction is a transaction that involves more than one connection. </glossary>

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:

<example title="Example: 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 </example>

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

</s2>

</s1>

<s2 title="database" version="Resin 3.0" type="defun"> <parents>server, host-default, host, web-app-default, web-app</parents>

Configure a database resource, which is a database pool that manages and provides connections to a database.

<deftable-childtags> <tr>

 <th>attribute</th>
 <th>Description</th>
 <th>Default</th>

</tr> <tr>

 <td>jndi-name</td>
 <td>JNDI name to store the pool under.  Servlets, jsp, and

other java code use this name. The path is relative to java:comp/env</td>

 <td> </td>

</tr> <tr>

 <td>driver</td>
 <td>Configure the database <a href="#driver">driver</a>.</td>
 <td></td>

</tr> <tr>

 <td>backup-driver</td>
 <td>Configure a backup <a href="#driver">driver</a>.</td>
 <td></td>

</tr> <tr>

 <td>max-connections</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum number of allowed connections</td>
 <td>1024</td>

</tr> <tr>

 <td>max-idle-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time an idle connection is kept in

the pool</td>

 <td>30 sec</td>

</tr> <tr>

 <td>max-active-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time a connection allowed to be active</td>
 <td>6 hours</td>

</tr> <tr>

 <td>max-pool-time</td>
 <td><a href="#pooling">Pooling parameter</a> - maximum time a connection is kept in the pool</td>
 <td>24 hours</td>

</tr> <tr>

 <td>connection-wait-time</td>
 <td><a href="#pooling">Pooling parameter</a> - how long to wait for an idle connection (Resin 1.2.3)</td>
 <td>30 sec</td>

</tr> <tr>

 <td>max-overflow-connections</td>
 <td><a href="#pooling">Pooling parameter</a> - how many "overflow" connection are allowed if the connection wait times out.</td>
 <td>1024</td>

</tr> <tr>

<td>ping-table</td>
<td><a href="#reliability">Reliability parameter</a> - The database table used to "ping", checking that the connection is still live.</td>
<td>n/a</td>

</tr> <tr>

 <td>ping</td>
 <td><a href="#reliability">Reliability parameter</a> - test for live connections before allocating them from the pool.</td>
 <td>false</td>

</tr> <tr>

 <td>ping-interval</td>
 <td><a href="#reliability">Reliability parameter</a> - set the minimum interval for pings, instead of doing a ping every time</td>
 <td>1s</td>

</tr> <tr>

 <td>prepared-statement-cache-size</td>
 <td>A cache that holds prepared statements, a reused prepared statement avoids the overhead of the driver making the prepared statement</td>
 <td>0</td>

</tr> <tr>

 <td>spy</td>
 <td>A debugging aid, if true, generate info level log events that reveal the SQL that is used with the connections.</td>
 <td>false

</td></tr></deftable-childtags>

All times default to seconds, but can use longer time periods:

<th>Suffix</th>
<th>Description</th>

</tr> <tr><td>s</td><td>seconds </td></tr><tr><td>m</td><td>minutes </td></tr><tr><td>h</td><td>hours </td></tr><tr><td>D</td><td>days </td></tr></deftable>

The class that corresponds to <database> is <a href="javadoc|com.caucho.sql.DBPool|"/>

</s2>

</s1>

<s2 title="driver" version="Resin 3.0" type="defun"> <parents>database</parents>

Configure a database driver. The driver is a class provided by the database vendor, it is responsible for the communication with the database.

The jar file with the driver in it can be placed in WEB-INF/lib, although it is often best to place your datbase driver's jar file in $RESIN_HOME/lib/local/, which makes the driver available to all of your web applications.

Examples of common driver configurations are in <a href="db-thirdparty.xtp">Third-party Database Configuration</a>.

The class that corresponds to <driver> is <a href="javadoc|com.caucho.sql.DriverConfig"/>

<deftable-childtags> <tr>

 <th>Attribute</th>
 <th>Description</th>

</tr> <tr><td>type</td><td>The Java class name of the database driver.</td><td> </td></tr><tr><td>url</td><td>The driver specific database url.</td><td> </td></tr><tr><td>user</td><td>The username to give the database driver.</td><td> </td></tr><tr><td>password</td><td>The password to give the database driver.</td><td> </td></tr><tr><td>init-param</td><td>Set <a href="#init-param">driver specific properties</a> not known to Resin.</td><td> </td></tr></deftable-childtags>

</s2>

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

</s3>

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.

</s3>

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.

</s3>

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.

</s3> </s2>

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 character encodings when handling strings.

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

</s2>

</s1>

Pooling configuration controls the behaviour of Resin's pooling of database connections. For most applications and databases the only needed change is to increase the max-connections value to meet high demand. Other pooling parameters have defaults that are based on our years of experience with many different databases in many different applications. Changes from the defaults should only be done in response to specific problems, and with a good understanding of how pooling works.

</s1>

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 <a href="#pooling">pooling</a> 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

<example title="Example: <ping> configuration">

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

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

</example>

You can test the ping using the following steps:

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

</s2>

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.

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

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 <a href="#pooling">Pooling configuration</a> 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 <a href="#pooling">Pooling configuration</a>, 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.

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

</s3>

</s2>

</s1>

The DataSource is a factory that is used to obtain a connection. The DataSource is obtained using the <a config-tag="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.

<example title="Example: Obtaining a DataSource"> import javax.sql.*; import javax.webbeans.*;

public class .... {

 @Named("jdbc/test") DataSource _pool;

...

} </example> </s2>

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.

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

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.

<example title="Example: Getting a connection from the DataSource"> package javax.webbeans.*; 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) {
     }
   }
 }

} </example>

</s2>

The connection obtained by pool.getConnection() is an instance of <a href="javadoc|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.

<example title="Example: Getting the underlying driver connection"> Connection driverConn = ((com.caucho.sql.UserConnection) connection).getConnection();

// never do this: driverConn.close() </example>

</s2>

</s1>

Resin provides facilities that allow you to plugin your own custom code that returns a password to Resin. However any solution is vulnerable, unless you require a person to type in a password every time Resin starts (or restarts). Typically the security of the machine hosting Resin, and proper permissions on the readability of the resin.xml file, are sufficient to protect your database password.

The solution shown below is not really secure because you can disassemble the Password code to get the decryption key, but it may be marginally better than plaintext.

 <password resin:type="com.hogwarts.Password">mX9aN9M==</password> 
 ...

</example>

You will need to provide com.hogwarts.Password:

<example title="Example: Password class"> package com.hogwarts;

public class Password {

 private String _value; 

 public void addText(String value) 
 { 
   _value = value; 
 } 

 public Object replaceObject() 
 { 
   return decrypt(_value); 
 }

 private String decrypt(String encrypted)
 {
   ... custom code ...
 }

} </example>

This solution is completely general, you can use resin:type anywhere in the configuration files where a string value is allowed.

Resin does not provide the equivalent of com.hogwarts.Password because it's not really secure. Providing that kind of solution would lead some to believe it was a secure solution.

</s1>

</body> </document>

 <title>database: Database tag configuration</title>
 <version>Resin 3.1</version>
 <description>

The database tag configures a database as a javax.sql.DataSource with numerous options. Resin offers robust database connection pooling.

 </description>

</header> <body>

See <a href="config-env.xtp">Environment</a> configuration for resources: classloader, databases, connectors, and resources.
See <a href="config-database.xtp">Database Configuration</a> for a detailed overview of databases and Resin.

</s1>

<defun title="<connection-wait-time>" version="Resin 3.1"> <parents>database</parents>

<connection-wait-time> configures the time a getConnection call should wait when then pool is full before trying to create an overflow connection.

</defun>

<defun title="<close-dangling-connections>" version="Resin 3.1.1"> <parents>database</parents>

<close-dangling-connections> closes open connections at the end of a request and logs a warning and stack trace.

</defun>

<defun title="<driver>" version="Resin 3.1"> <parents>database</parents>

<driver> configures a database driver for a connection pool. The individual driver information is available from the driver vendor or in the <a href="db-thirdparty.xtp">database driver</a> page.

The content of the driver tag configures bean properties of the driver class, e.g. url, user, password.

<def title="driver schema"> element driver {

 type,
 *

} </def>

</defun>

<defun title="<database>" version="Resin 3.1"> <parents>resin, cluster, host, web-app</parents>

<database> configures a database as a javax.sql.DataSource and stores it in jndi with the given jndi-name.

<deftable-childtags> <tr>

 <td>connection-wait-time</td>
 <td>When the pool it full, how long to wait before opening a new connection anyway.</td>
 <td>10m</td>

</tr> </deftable-childtags>

 <database jndi-name="jdbc/test">
   <driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
     <url>jdbc:mysql://localhost:3306/test</url>
     <user></user>
     <password></password>
   </driver>
 </database>

</web-app> </example>

<def title="database schema"> element database {

 jndi-name
 & connection-Database?
 & driver+
 & connection-wait-time
 & max-active-time
 & max-close-statements
 & max-connections
 & max-create-connections
 & max-idle-time
 & max-overflow-connections
 & max-pool-time
 & password
 & ping
 & ping-table
 & ping-query
 & ping-interval
 & prepared-statement-cache-size
 & save-allocation-stack-trace
 & spy
 & transaction-timeout
 & user
 & xa
 & xa-forbid-same-rm
 & wrap-statements

} </def>

</defun>

<defun title="<max-active-time>" version="Resin 3.1"> <parents>database</parents>

<max-active-time> configures the maximum time a connection can be active before Resin will automatically close it. Normally, the max-active-time should not be configured, since Resin will also automatically close a connection at the end of a request.

Sites should generally leave max-active-time at the default.

<defun title="<max-close-statements>" version="Resin 3.1"> <parents>database</parents>

<max-close-statements> configures how many open statements Resin should save to for the connection close. Since the JDBC Connection.close() call automatically closes any open statements, Resin's database pool needs to keep track of any open statements to close them in case the application has forgotten. The <max-close-statements> is primarily needed for older database drivers implementing the java.sql.Driver interface.

</defun>

<defun title="<max-connections>" version="Resin 3.1"> <parents>database</parents>

<max-connections> configures the maximum number of open connections allowed for Resin's database pool. Sites can use <max-connections> to throttle the number of database connections for an overloaded server. When max-connections is reached and an application calls getConnection, Resin will wait <a href="#connection-wait-time">connection-wait-time</a> or until a connection is freed before allocating a new connection.

<defun title="<max-create-connections>" version="Resin 3.1"> <parents>database</parents>

<max-create-connections> configures the maximum number of simultaneous connection creations. Since connection creation is slow and database access can be spiky, Resin's pool limits the number of new connections to the database at any time. Once a connection has succeeded, a new connection can proceed.

</defun>

<defun title="<max-idle-time>" version="Resin 3.1"> <parents>database</parents>

<max-idle-time> configures the maximum time a connection can remain idle before Resin automatically closes it. Since idle databases tie up resources, Resin will slowly close idle connections that are no longer needed.

Higher values of <max-idle-time> will connections to remain in the idle pool for a longer time. Lower values will close idle connections more quickly.

</defun>

<defun title="<max-overflow-connections>" version="Resin 3.1"> <parents>database</parents>

<max-overflow-connections> extends <connection-max> temporarily in case of overflow. After the <connection-wait-time> expires, Resin can create an overflow connection to handle unforseen load spikes.

</defun>

<defun title="<max-pool-time>" version="Resin 3.1"> <parents>database</parents>

<max-pool-time> configures the maximum time the connection can remain open. A connection could theoretically remain open, switching between active and idle, for an indefinite time. The <max-pool-time> allows a site to limit to total time of that connection.

Most sites will leave <max-pool-time> at the default.

</defun>

<defun title="<password>" version="Resin 3.1"> <parents>database</parents>

<password> configures the database connection password. Sites requiring additional security for their passwords can use the <a href="env-tags.xtp#resin:type">resin:type</a> attribute to configure a password decoder.

</defun>

<defun title="<ping>" version="Resin 3.1"> <parents>database</parents>

<ping> enables connection validation. When <ping> is enabled, Resin will test the connection with <a href="#ping-query"><ping-query></a> or <a href="#ping-table"><ping-table></a> before returning a connection to the user. If the connection fails the test, Resin will close it and return a new connection.

For efficiency, Resin will only validate the connection if it has been idle for longer than <a href="#ping-interval"><ping-interval></a>.

<default>false</default>

</defun>

<defun title="<ping-table>" version="Resin 3.1"> <parents>database</parents>

<ping-table> configures the database table Resin should use to verify if a connection is still valid when returned from the pool.

</defun>

<defun title="<ping-query>" version="Resin 3.1"> <parents>database</parents>

<ping-query> specifies the query to use for validating if a database connection is still valid when returned from the idle pool.

</defun>

<defun title="<ping-interval>" version="Resin 3.1"> <parents>database</parents>

<ping-interval> configures when Resin should validate an idle connection. Connections which have been idle for less than <ping-interval> are assumed to be still valid without validation. Connections idle for longer than <ping-interval> are validated.

Sites can force a validation by setting <ping-interval> to 0.

</defun>

<defun title="<prepared-statement-cache-size>" version="Resin 3.1"> <parents>database</parents>

<prepared-statement-cache-size> configures how many prepared statements Resin should cache for each connection. Caching prepared statement can improve performance for some database drivers by avoiding repeated parsing of the query SQL.

</defun>

<defun title="<save-allocation-stack-trace>" version="Resin 3.1"> <parents>database</parents>

<save-allocation-stack-trace> helps debugging application with a missing Connection.close() by saving the stack trace where the Connection.getConnection() was called. When Resin detects that the connection has failed to close, it can then print the allocation stack trace, which is more informative for tracking down errors.

</defun>

<defun title="<spy>" version="Resin 3.1"> <parents>database</parents>

The <spy> tag is a very useful logging tag for debugging database problems. If <spy> is enabled, all database queries will be logged at the "fine" level. Applications can use <spy> to debug unexpected database queries, or to improve query performance.

<default>false</default>

<example title="Example: spy output"> 0.6:setString(1,1) 0.6:executeQuery(select o.DATA from my_bean o where o.ID=?)

</example>

</defun>

<defun title="<transaction-timeout>" version="Resin 3.1"> <parents>database</parents>

<transaction-timeout> configures the maximum time a transaction can be alive before a mandatory rollback.

</defun>

</body> </document>

Pool configuration

From Resin 3.0

Views

Personal tools

Navigation

Search

Toolbox