Database configuration in the resin.conf uses 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. 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. 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. 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. <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> 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 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: sseconds mminutes hhours Ddays 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. Resin's database pool can test if the pooled database connection is still alive by configuring a 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 configuration can test the database connection. When pinging, Resin's DBPool will test a table specified with the 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. 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 , 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: Configure the database with ping-table and ping-on-idle. Execute some servlet that queries the database. Restart the database server. Execute another servlet that queries the database. The experimental Caucho MySql driver includes one special init-param to configure the character encoding: PropertyMeaningDefault encodingcharacter encodingISO-8859-1 The following is a sample design pattern for getting new database connections. The 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) { } } } } 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. 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>"); } %>
