Caucho Technology

basic jdbc database pattern


This tutorial describes the standard pattern for using a database in Resin.

Demo

Using a JDBC database is a three step process:

  • Configuring the <database> in the resin-web.xml or resin.conf
  • Retrieving the DataSource from the global JNDI resource map.
  • Using a Connection from the DataSource to execute the SQL.

JDBC database access is based around the Factory pattern. With JDBC,javax.sql.DataSourceis the Factory object andjava.sql.Connectionis the factory's generated object. The <database> configures theDataSourceand stores it in the JNDI resource map. The servlet will retrieve theDataSourceand use it as a factory to obtainConnectionobjects, the main workhorse for using databases.

Files in this tutorial

FILEDESCRIPTION
WEB-INF/resin-web.xmlresin-web.xml configuration
WEB-INF/classes/example/BasicServlet.javaThe JDBC query servlet.
WEB-INF/classes/example/InitServlet.javaThe JDBC initialization servlet.

Database Schema

CREATE TABLE jdbc_basic_brooms (
  id INTEGER PRIMARY KEY auto_increment,

  name VARCHAR(128),

  cost INTEGER
);

INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('firebolt', 4000)
INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('nimbus 2001', 500)
INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('nimbus 2000', 300)
INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('cleansweep 7', 150)
INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('cleansweep 5', 100)
INSERT INTO jdbc_basic_brooms (name, cost) VALUES ('shooting star', 50)

Database Configuration

In Resin 3.0, the <database> tag configures the database pool and driver and saves the connection factory (DataSource) in JNDI. JNDI is just a global lookup tree available to all classes, making it straightforward to separate resource configuration from the application code.

The <driver> tag configures the database driver. The database vendor will make the driver classes available and describe the configuration variables. Thethirdparty database pagedescribes several important database configurations.

The <type> tag is the most important driver configuration item. It specifies the main Java driver class. For many drivers, you will have a choice of different drivers following different internal JDBC APIs. If you have a choice, you should try the drivers in the following order, after checking your database vendor's recommendations:

  1. JCA - Java Connection Architecture - this is a common driver interface for more than just JDBC. If possible, it's generally the best to choose.
  2. ConnectionPoolDataSource - JDBC driver which has extra hooks to help Resin pool the connections.
  3. Driver - old-style JDBC driver. Its main benefit is that it's generally always available as a fallback.
<web-app xmlns="http://caucho.com/ns/resin">

  <database jndi-name="jdbc/basic">
    <driver type="com.caucho.db.jca.ConnectionFactory">
      <url>resin:WEB-INF/db</url>
    </driver>
  </database>

</web-app>

The <url> specifies the location of the database. Each database driver will have a unique URL formal. In this case, the <url> specifies a directory for the database files. Other databases may specify a host and port.

com.caucho.db.jca.ConnectionFactory

The specific driver for this example,com.caucho.db.jca.ConnectionFactoryis a simple database intended for examples and testing.

Servlet Initialization

The servlet needs to locate theDataSourceto use JDBC. The servlet needs to lookup the database pool's DataSource using JNDI. In the configuration above, the name "jdbc/basic" is shorthand for "java:comp/env/jdbc/basic". "java:comp/env" is a context containing configured resources. For example, "java:comp/env/jdbc/basic" is a JDBC resource in that context.

Because the servlet only needs to look up theDataSourceonce, it will generally look it up in theinit()method and store it as an instance variable. TheDataSourceis thread-safe, so it can be used simultaneously by any of the requesting threads.

import javax.inject.Inject;
import javax.inject.Named;

public class BasicServlet extends HttpServlet {
  @Inject @Named("jdbc/basic")
  private DataSource _ds;

  ...
}

Standard configuration

resin-web.xml
<servlet servlet-name="my-servlet"
         servlet-class="example.BasicServlet">
</servlet>

Using the Database

The most important pattern when using JDBC is the following try/finally block. All database access should follow this pattern. Because connections are pooled, it's vital to close the connection no matter what kind of exceptions may be thrown So theconn.close()must be in a finally block.

Connection try ... finally block
Connection conn = _ds.getConnection();
try {
  ...
} finally {
  conn.close();
}

The full example splits the database access into two methods to clarify the roles. Theserviceretrieves the output writer from the servlet response and wraps any checked exceptions in aServletException. Splitting the servlet method simplifies thedoQuerymethod, so it can concentrate on the database access.

package example;

public class BasicServlet extends HttpServlet {
  @Resource(name="jdbc/basic")
  private DataSource _ds;

  public void service(HttpServletRequest req,
                      HttpServletResponse res)
    throws java.io.IOException, ServletException
  {
    PrintWriter out = res.getWriter();

    try {
      doQuery(out);
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private void doQuery(PrintWriter out)
    throws IOException, SQLException
  {
    Connection conn = _ds.getConnection();

    try {
      String sql = "SELECT name, cost FROM jdbc_basic_brooms"
                   + " ORDER BY cost DESC";
      
      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery(sql);

      out.println("<table border='3'>");
      
      while (rs.next()) {
        out.println("<tr><td>" + rs.getString(1));
        out.println("    <td>" + rs.getString(2));
      }

      out.println("</table>");
      
      rs.close();
      stmt.close();
    } finally {
      conn.close();
    }
  }
}

See also

Demo


Copyright © 1998-2011 Caucho Technology, Inc. All rights reserved.
Resin ® is a registered trademark, and Quercustm, Ambertm, and Hessiantm are trademarks of Caucho Technology.