Database Configuration


Resin
FAQ
Reference Guide
JavaDoc
Demo
Tutorial

Getting Started
Configuration
EJB
IDE
Topics
JSP
XTP/XSL

Basic Config
Resin Config
HTTP Config
App Config
Servlet Config
Filter Config
SSL
Login Config
DB Config
Taglib Config
Summary

Configuration

Taglib Config

resource-ref
Using Databases from a Servlet
Using Databases from a JavaScript JSP

resource-ref Servlet 2.2

Database configuration in the resin.conf uses resource-ref to put the DataSource in a JNDI context. The DataSource is the JDBC 2.0 structure 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
res-type	javax.sql.DataSource for database pools
driver-name	The Java classname of the driver.
url	The driver specific database url.
init-param	Extra parameters for the data source.

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

Reliability Parameters

Resin's database pool can configure a 'ping' query to test if the database is still alive. Normally when a database connection is placed in the pool it will wait there until the next request. It's possible (though unlikely) that the database might go down in the meantime. The ping configuration can test the database.

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 count(*) 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

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 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 = new 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>"); } %>

Configuration

Taglib Config