<s1 title="Database Configuration">

<summarylist/>

<s2 title="Configuration" version="Servlet 2.2">

<p>Database configuration in the resin.conf uses <var/resource-ref/> to put
the DataSource in a JNDI context.  The <a href="jndi.xtp">JNDI
configuration</a> 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 &lt;host> block and the entire server can share a database pool
by putting the resource-ref in the &lt;http-server> block.</p>

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

<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.XADataSource for transaction-aware database pools and javax.sql.DataSource for non-transactional 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.
<tr><td>data-source<td>Use a defined PooledDataSource or XADataSource
instead of using the driver directly.
</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.</p>

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

</s3>

<s3 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:test
<tr><th colspan=2>Postgres
<tr><td>driver-name<td>org.postgresql.Driver
<tr><td>url<td>jdbc:postgresql://localhost/test
</deftable>

</s3>

<s3 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>

<p>All times default to seconds, but can use longer time periods:</p>

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

</s3>

<s3 title='Transactions'>

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

<p>Transaction-aware databases use XADataSource for their
configuration.  Non-transaction-aware databases use DataSource
for the configuration.</p>

</s3>

<s3 title='Reliability Parameters'>

<p>Resin's database pool can test if the pooled database connection
is still alive by configuring a <var/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 <var/ping/> configuration can test
the database connection.</p>

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

<example>
SELECT 1 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.</p>

<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
<tr><td>ping-interval<td>How often to ping for ping-on-idle<td>60s
</deftable>

<p>If the database had a table <var/my_table/>, you could
configure the pool to check idle connections as follows:</p>

<example>
&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;init-param ping-table="my_table"/>
  &lt;init-param ping-on-idle="true"/>
&lt;/resource-ref>
</example>

<p>You can test the database reliability using the following steps:
</p>

<ol>
<li>Configure the database with ping-table and ping-on-idle.
<li>Execute some servlet that queries the database.
<li>Restart the database server.
<li>Execute another servlet that queries the database.
</ol>

</s3>

<s3 title='Caucho MySql Driver'>

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

<deftable title='Properties'>
<tr><th>Property<th>Meaning<th>Default
<tr><td>encoding<td>character encoding<td>ISO-8859-1
</deftable>

</s3>

</s2>

<s2 title='Example Uses'>

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

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

</s3>

<s3 title='Using Databases from a JSP'>

<p>The following is a sample design pattern for using database using
JSP.</p>

<example title="test.jsp">
&lt;%@ page import='java.sql.*, javax.sql.*, javax.naming.*' %>
&lt;%
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");

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

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

</s3>

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

<example title="test.jsp">
&lt;%@ page language='javascript' %&gt;
&lt;%
var conn = 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>");
}
%&gt;
</example>

</s3>
</s2>
</s1>
