Configuration

Servlet 2.2

Database configuration in the resin.conf uses resource-ref to put the DataSource in a JNDI context. The JNDI configuration page gives a more general description of using JNDI in Resin. The DataSource is the JDBC 2.0 factory to get new database connections. Each web-app can configure its own database pool. Hosts can share a database pool by putting the resource-ref in the <host> block and the entire server can share a database pool by putting the resource-ref in the <http-server> block.

Core Configuration

The driver classes can be in WEB-INF/lib or WEB-INF/classes, although it's a better idea to put it in the global classpath or resin2.0/lib.

Basic Init Parameters

Attribute	Meaning
res-ref-name	JNDI path attribute to store the pool. The path is relative to java:comp/env.
res-type	javax.sql.XADataSource for transaction-aware database pools and javax.sql.DataSource for non-transactional pools.
init-param	Extra parameters for the data source.

init-param values

Attribute	Meaning
driver-name	The Java classname of the driver.
url	The driver specific database url.
data-source	Use a defined PooledDataSource or XADataSource instead of using the driver directly.

Here's a sample minimal resin.conf fragment to bind a DBPool-based database to the JNDI path "java:comp/env/jdbc/test". The examples below show how that JNDI path will be used.

<resource-ref>
  <res-ref-name>jdbc/test</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <init-param driver-name="com.caucho.jdbc.mysql.Driver"/>
  <init-param url="jdbc:mysql-caucho://localhost:3306/test"/>
</resource-ref>

Common Databases

MySql (Caucho driver)
driver-name	com.caucho.jdbc.mysql.Driver
url	jdbc:mysql-caucho://localhost:3306/test
MySql (mm.mysql driver)
driver-name	org.gjt.mm.mysql.Driver
url	jdbc:mysql://localhost:3306/test
Oracle (thin driver)
driver-name	oracle.jdbc.driver.OracleDriver
url	jdbc:oracle:thin:@localhost:1521:test
Postgres
driver-name	org.postgresql.Driver
url	jdbc:postgresql://localhost/test

Pooling Configuration

Pooling Parameters

Attribute	Meaning	Default
max-connections	Maximum number of allowed connections	20
max-idle-time	Maximum time an idle connection is kept in the pool	30 sec
max-active-time	Maximum time a connection allowed to be active	6 hours
max-pool-time	Maximum time a connection is kept in the pool	24 hours
connection-wait-time	How long to wait for an idle connection (Resin 1.2.3)	10 minutes
max-overflow-connections	How many "overflow" connection are allowed if the connection wait times out.	0

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

Time suffixes

s	seconds
m	minutes
h	hours
D	days

Transactions

Some applications, including any applications using EJB or Resin-CMP, need transaction-aware database pools. A transaction-aware database pool will participate in any transaction, either handled by EJB or using the UserTransactoin API. A non-transaction-aware database will ignore any transaction.

Transaction-aware databases use XADataSource for their configuration. Non-transaction-aware databases use DataSource for the configuration.

Reliability Parameters

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.

Reliability Parameters

Attribute	Meaning	Default
ping-table	The database table used to "ping", checking that the connection is still live.	n/a
ping-on-reuse	Test for live connections before allocating them from the pool.	false
ping-on-free	Test for live connections before replacing them in the pool.	false
ping-on-idle	Periodically test connections in the pool	false
ping-interval	How often to ping for ping-on-idle	60s

If the database had a table my_table, you could configure the pool to check idle connections as follows:

<resource-ref> <res-ref-name>jdbc/test</res-ref-name> <res-type>javax.sql.DataSource</res-type> <init-param driver-name="com.caucho.jdbc.mysql.Driver"/> <init-param url="jdbc:mysql-caucho://localhost:3306/test"/> <init-param ping-table="my_table"/> <init-param ping-on-idle="true"/> </resource-ref>

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.

Caucho MySql Driver

The experimental Caucho MySql driver includes one special init-param to configure the character encoding:

Properties

Property	Meaning	Default
encoding	character encoding	ISO-8859-1

Example Uses

Using Databases from a Servlet

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

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 {
  DataSource pool;

  public void init()
    throws ServletException
  {
    try {
      Context env = (Context) new InitialContext().lookup("java:comp/env");

      pool = (DataSource) env.lookup("jdbc/test");

      if (pool == null)
        throw new ServletException("`jdbc/test' 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) {
      }
    }
  }
}

Using Databases from a JSP

The following is a sample design pattern for using database using JSP.

<%@ page import='java.sql.*, javax.sql.*, javax.naming.*' %>
<%
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/test");

Connection conn = ds.getConnection();

try {
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery("select NAME, PRICE from BROOMS");

  %><h2>Brooms:</h2><%
    while (rs.next()) { %>
<%= rs.getString(1) %> <%= rs.getString(2) %><br><%
  }
} finally {
  conn.close();
}
%>

In many cases, it will be easier to use a tag library to simplify the JSP.

Using Databases from a JavaScript JSP

The following is a sample design pattern for using database using javascript and JSP. Resin will automatically close the connection, so there's no need for a finally block.

<%@ page language='javascript' %>
<%
var conn = Database("jdbc/test");

var rs = conn.query("select NAME, PRICE from BROOMS");

out.writeln("<h2>Brooms:</h2>");
while (rs.next()) {
  out.write(rs.get(1));
  out.write(" ");
  out.write(rs.get(2));
  out.writeln("<br>");
}
%>

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