<document>
<s1>

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.DataSource` is the Factory object and `java.sql.Connection` is the factory's generated 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.

</s1> <deftable>
resin-web.xml configuration
The JDBC query servlet.
The JDBC initialization servlet.
</deftable> <example> 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) </example>

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:

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.

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

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.

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

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

<example> import javax.webbeans.Named; public class BasicServlet extends HttpServlet { @Named("jdbc/basic") private DataSource _ds; ... } </example> <servlet servlet-name="my-servlet" servlet-class="example.BasicServlet"> </servlet>

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

<example> 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(); } } }

- Database configuration reference
- Sample configurations for several database drivers
- Using Dependency Injection for Databases
- The Dependency Injection (or Assembly Line) pattern.

</document>