caucho
Resin
FAQ
Reference Guide
Demo
Tutorial

Getting Started
Configuration
IDE
Topics
JSP
XML/XSLT

Basic Config
Directory
Servlets
Filters
Resources
Databases
Messaging
Security Config
Log Config
Taglib Config
Misc resin.conf
Host resin.conf
Port resin.conf
App resin.conf
Summary
Glossary
Index
 Database Configuration

Resources
Configuration
Messaging

  1. Configuration
    1. Core Configuration
    2. Common Databases
    3. Pooling Configuration
    4. Transactions
    5. Reliability Parameters
    6. Caucho MySql Driver
  2. Example Uses
    1. Using Databases from a Servlet
    2. Using Databases from a JSP
    3. Using Databases from a JavaScript JSP

ConfigurationServlet 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
AttributeMeaning
res-ref-nameJNDI path attribute to store the pool. The path is relative to java:comp/env.
res-typejavax.sql.XADataSource for transaction-aware database pools and javax.sql.DataSource for non-transactional pools.
init-paramExtra parameters for the data source.

init-param values
AttributeMeaning
driver-nameThe Java classname of the driver.
urlThe driver specific database url.
data-sourceUse 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.

Sample resin.conf fragment
<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-namecom.caucho.jdbc.mysql.Driver
urljdbc:mysql-caucho://localhost:3306/test
MySql (mm.mysql driver)
driver-nameorg.gjt.mm.mysql.Driver
urljdbc:mysql://localhost:3306/test
Oracle (thin driver)
driver-nameoracle.jdbc.driver.OracleDriver
urljdbc:oracle:thin:@localhost:1521:test
Postgres
driver-nameorg.postgresql.Driver
urljdbc:postgresql://localhost/test

Pooling Configuration

Pooling Parameters
AttributeMeaningDefault
max-connectionsMaximum number of allowed connections20
max-idle-timeMaximum time an idle connection is kept in the pool30 sec
max-active-timeMaximum time a connection allowed to be active 6 hours
max-pool-timeMaximum time a connection is kept in the pool24 hours
connection-wait-timeHow long to wait for an idle connection (Resin 1.2.3)10 minutes
max-overflow-connectionsHow 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
sseconds
mminutes
hhours
Ddays

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
AttributeMeaningDefault
ping-tableThe database table used to "ping", checking that the connection is still live.n/a
ping-on-reuseTest for live connections before allocating them from the pool.false
ping-on-freeTest for live connections before replacing them in the pool.false
ping-on-idlePeriodically test connections in the poolfalse
ping-intervalHow often to ping for ping-on-idle60s

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
PropertyMeaningDefault
encodingcharacter encodingISO-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.

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

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

test.jsp
<%@ 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>");
}
%>

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