Connecting through JSP | Mastering Resin

Database Reliability

In a production setting or even in a development shop, it's important to know the database is alive. Resin can test whether a database is alive by using a very simple SQL query called a ping query. You can use the ping test with objects in a connection pool. It is represented by the following SQL:

 select 1 FROM <defined table>

This SQL attempts a select on a specific table defined in resin.conf for the database driver. The JNDI configuration options available are as follows:

ping-table— The database table used for pinging; note that the table is in the database specified in the configuration.
ping-on-reuse— A pool object tests for a live connection before being used or allocated in an application; default is false.
ping-on-free— A pool object tests for a live connection before being put back into the pool; default is false.
ping-on-idle— A pool object tests for a live connection randomly; default is false.

In all cases, if the connection attempt fails, the object shuts down its current connection and reattempts to connect with the database. To see database reliability in use, consider the following JNDI configuration:

 <database>     <jndi-name>jdbc/mysql</jndi-name>     <driver type="org.gjt.mm.mysql.Driver">     <url>       jdbc:mysql://localhost:3306/products     </url>     <user>spider</user>     <password>spider</password>     </driver>     <max-connections>20</max-connections>     <max-idle-time>30s</max-idle-time>     <connection-wait-time>15</connection-wait-time>     <ping-table>product</ping-table>     <ping-on-reuse>true</ping-on-reuse>   </database>

This configuration tells the Resin server to perform a ping query on the product table found in the products database. The pool connection objects ping just before they are allocated for use by the server. You can test the ping query by executing the servlets shown in any of the listings. Once the results are presented to the client browser, shut down the database where the code is making connections. This action will cause all the pool connection objects to have stale information. If the object attempted to execute SQL, they would fail if the ping query wasn't in place. Because ping query attempts are made just before the pool objects are allocated to an application, a new connection is made with the database, and the code executes without error.