Basic JDBC Database Pattern

JDBC database access is based around the Factory pattern. With JDBC, javax.sql.DataSource is the Factory object. The <database> configures the DataSource and stores it in the JNDI resource map. The servlet will retrieve the DataSource and use it as a factory to obtain Connection objects, the main workhorse for using databases.

Files in this tutorial

WEB-INF/web.xml web.xml configuration

WEB-INF/classes/example/BasicServlet.java The JDBC query servlet.

WEB-INF/classes/example/InitServlet.java The 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. The thirdparty database page describes 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:

JCA - Java Connection Architecture - this is a common driver interface for more than just JDBC. If possible, it's generally the best to choose.
ConnectionPoolDataSource - JDBC driver which has extra hooks to help Resin pool the connections.
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"> <path>WEB-INF/db</path> </driver> </database> </web-app>

In the above example, the <path> is a driver-specific parameter, set using bean-style initialization , i.e. the ConnectionFactory class has a setPath method.

com.caucho.db.jca.ConnectionFactory

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

Servlet Initialization

The servlet is configured with a DataSource to access JDBC. Resin allows two styles of configuration: Dependency Injection using bean-style setters and standard servlet <init-param> configuration. The Dependency Injection style is simpler, while the <init-param> style will work on all servlet engines. By creating a separate assemble() method, a servlet can take advantage of Resin's Dependency Injection and still be fully compatible with other servlet engines.

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 the DataSource once, it will generally look it up in the init() method and store it as an instance variable. Because the DataSource is designed to be thread-safe, it can be used simultaneously by any of the requesting threads.

public class BasicServlet extends HttpServlet { private DataSource _ds; public void setDataSource(DataSource ds) { _ds = ds; } public void init() throws IOException, ServletException { if (_ds == null) assemble(); } private void assemble() throws ServletException { try { String name = getInitParameter("data-source"); Context ic = new InitialContext(); _ds = (DataSource) ic.lookup("java:comp/env/" + name); } catch (Exception e) { throw new ServletException(e); } ... }

When the servlet initializes, it checks to see if the DataSource is already configured. If not, the assemble() method configures the DataSource itself using JNDI. The assemble() method performs the Assembler function in the Dependency Injection (Assembly Line) pattern.

If you are using Resin's Dependency Injection capability, the assemble() method is not needed. You can eliminate the JNDI code and rely on the setDataSource. You'll need the assemble() method if you're porting your servlet to a different servlet engine.

Dependency Injection configuration

Using dependency injection to configure servlets has some advantages over the init-param method:

The servlet initialization code is simpler. The servlet doesn't need JNDI code.
The configured values can be more complicated than the string-limitation of <init-param>.
The DataSource itself isn't tied to JNDI, although JNDI will certainly remain the primary registry.

Enabling the Dependency Injection pattern is trivial: just add a setDataSource method as in the example above.

web.xml

<servlet servlet-name="my-servlet" servlet-class="example.BasicServlet"> <init> <data-source>${jndi:lookup("jdbc/basic")}</data-source> </init> </servlet>

Standard configuration

web.xml

<servlet servlet-name="my-servlet" servlet-class="example.BasicServlet"> <init-param data-source="java:comp/env/jdbc/basic"/> </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 the conn.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. The service retrieves the output writer from the servlet response and wraps any checked exceptions in a ServletException. Splitting the servlet method simplifies the doQuery method, so it can concentrate on the database access.

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

The Dependency Injection (or Assembly Line) pattern.

Try the Tutorial

Tutorials

Scrapbook