<title>Database Configuration</title>

<summarylist/>

<section title="resource-ref" version="Servlet 2.2">
Database configuration in the resin.conf uses <var/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 &lt;host> block and
the entire server can share a database pool by putting the
resource-ref in the &lt;http-server> block.

<subsection title='Core Configuration'>

<p/>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.

<deftable title='Basic Init Parameters'>
<tr><th>Attribute<th>Meaning
<tr><td>res-ref-name<td>JNDI path attribute to store the pool.  The path is relative to java:comp/env.
<tr><td>res-type<td>javax.sql.DataSource for database pools
<tr><td>init-param<td>Extra parameters for the data source.
</deftable>

<deftable title='init-param values'>
<tr><th>Attribute<th>Meaning
<tr><td>driver-name<td>The Java classname of the driver.
<tr><td>url<td>The driver specific database url.
</deftable>

<p/>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.

<example title="Sample resin.conf fragment">
&lt;resource-ref>
  &lt;res-ref-name>jdbc/test&lt;/res-ref-name>
  &lt;res-type>javax.sql.DataSource&lt;/res-type>
  &lt;init-param driver-name="com.caucho.jdbc.mysql.Driver"/>
  &lt;init-param url="jdbc:mysql-caucho://localhost:3306/test"/>
&lt;/resource-ref>
</example>

</subsection>

<subsection title='Common Databases'>

<deftable>
<tr><th colspan=2>MySql (Caucho driver)
<tr><td>driver-name<td>com.caucho.jdbc.mysql.Driver
<tr><td>url<td>jdbc:mysql-caucho://localhost:3306/test
<tr><th colspan=2>MySql (mm.mysql driver)
<tr><td>driver-name<td>org.gjt.mm.mysql.Driver
<tr><td>url<td>jdbc:mysql://localhost:3306/test
<tr><th colspan=2>Oracle (thin driver)
<tr><td>driver-name<td>oracle.jdbc.driver.OracleDriver
<tr><td>url<td>jdbc:oracle:thin:@localhost:1521:db
<tr><th colspan=2>Postgres
<tr><td>driver-name<td>org.postgresql.Driver
<tr><td>url<td>jdbc:postgresql://localhost/test
</deftable>

</subsection>

<subsection title='Pooling Configuration'>

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

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

<deftable title="Time suffixes">
<tr><td>s<td>seconds
<tr><td>m<td>minutes
<tr><td>h<td>hours
<tr><td>D<td>days
</deftable>

</subsection>

<subsection title='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.

<p/>When pinging, Resin's DBPool will test a table specified with the
<var/ping-table/> parameter.  For a ping-table of my_table, Resin will
use a query like the following:

<example>
SELECT count(*) FROM my_table
</example>

<p/>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.

<deftable title="Reliability Parameters">
<tr><th>Attribute<th>Meaning<th>Default
<tr><td>ping-table<td>The database table used to "ping", checking that
the connection is still live.<td>n/a
<tr><td>ping-on-reuse<td>Test for live connections before allocating
them from the pool.<td>false
<tr><td>ping-on-free<td>Test for live connections before replacing
them in the pool.<td>false
<tr><td>ping-on-idle<td>Periodically test connections in the pool<td>false
</deftable>

</subsection>

</section>

<section title='Using Databases from a Servlet'>

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

<example title="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:&lt;br>");
      while (rs.next()) {
        out.print(rs.getString(1));
        out.print(" ");
        out.print(rs.getInt(2));
        out.println("&lt;br>");
      }

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      throw new ServletException(e);
    } finally {
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException e) {
      }
    }
  }
}
</example>

</section>

<section title='Using Databases from a JavaScript JSP'>

<p/>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.

<example title="test.jsp">
&lt;%@ page language='javascript' %>
&lt;%
var conn = new Database("jdbc/test");

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

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

</section>
