<document>
  <header>
    <product>resin</product>
    <type>tutorial</type>
    <tutorial-startpage>basic</tutorial-startpage>
    <title>Using Dependency-Injection with JDBC Databases</title>
    <description>
    <p>This tutorial describes the standard pattern
for using a database in Resin, following the Dependency-Injection/Inversion-of-Control pattern.</p>
    </description>
  </header>

  <body>
    <summary/>

<p>Using a JDBC database is a three step process:</p>

<ul>
<li>Configuring the &lt;database> in the resin-web.xml or resin.conf
<li>Retrieving the <code>DataSource</code> from the global JNDI
resource map.
<li>Using a <code>Connection</code> from the <code>DataSource</code>
to execute the SQL.
</ul>

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

<section title="Files in this tutorial">
<deftable>
<tr><td><viewfile-link file="WEB-INF/resin-web.xml"/>
    <td>resin-web.xml configuration
<tr><td><viewfile-link file="WEB-INF/classes/example/BasicServlet.java"/>
    <td>The JDBC query servlet.
<tr><td><viewfile-link file="WEB-INF/classes/example/InitServlet.java"/>
    <td>The JDBC initialization servlet.
</deftable>
</section>

<section title="Database Schema">

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

</section>

<section title="Database Configuration">

<p>In Resin 3.0, the &lt;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.</p>

<p>The &lt;driver> tag configures the database driver.  The database
vendor will make the driver classes available and describe the
configuration variables.  The <a
href="../../thirdparty.xtp">thirdparty database page</a> describes
several important database configurations.</p>

<p>The &lt;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:</p>

<ol>
<li>JCA - Java Connection Architecture - this is a common driver
interface for more than just JDBC.  If possible, it's generally the
best to choose.
<li>ConnectionPoolDataSource - JDBC driver which has extra hooks to
help Resin pool the connections.
<li>Driver - old-style JDBC driver.  Its main benefit is that it's
generally always available as a fallback.
</ol>

<example>
&lt;web-app xmlns="http://caucho.com/ns/resin">
  &lt;database jndi-name="jdbc/basic">
    &lt;driver type="com.caucho.db.jca.ConnectionFactory">
      &lt;url>resin:WEB-INF/db&lt;/url>
    &lt;/driver>
  &lt;/database>
&lt;/web-app>
</example>

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

<section title="com.caucho.db.jca.ConnectionFactory">

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

</section>

</section>

<section title="Servlet Initialization">

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

<example>
public class BasicServlet extends HttpServlet {
  private DataSource _ds;

  public void setDataSource(DataSource ds)
  {
    _ds = ds;
  }

  public void init()
    throws IOException, ServletException
  {
    if (_ds == null) {
      // the servlet could also create an assemble() method as
      // in the jdbc-basic tutorial
      throw new ServletException("data-source must be assigned");
    }
  }

  ...
}
</example>

<section title="Dependency Injection configuration">

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

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

<p>Enabling the Dependency Injection pattern is trivial: just
add a <code>setDataSource</code> method as in the example above.</p>

<example title="resin-web.xml">
&lt;servlet servlet-name="my-servlet"
         servlet-class="example.BasicServlet">
  &lt;init>
    &lt;data-source>\${jndi("jdbc/basic")}&lt;/data-source>
  &lt;/init>
&lt;/servlet>
</example>

</section>

<section title="Standard configuration">

<example title="web.xml">
...
&lt;servlet servlet-name="my-servlet"
         servlet-class="example.BasicServlet">
  &lt;init-param data-source="java:comp/env/jdbc/basic"/>
&lt;/servlet>
...
</example>

</section>

</section>

<section title="Using the Database">

<p>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
<code>conn.close()</code> must be in a finally block.</p>

<example title="Connection try ... finally block">
Connection conn = _ds.getConnection();
try {
  ...
} finally {
  conn.close();
}
</example>

<p>The full example splits the database access into two methods to
clarify the roles.  The <code>service</code> retrieves the output
writer from the servlet response and wraps any checked exceptions
in a <code>ServletException</code>.  Splitting the servlet method
simplifies the <code>doQuery</code> method, so it can concentrate
on the database access.</p>

<example>
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("&lt;table border='3'>");
      
    while (rs.next()) {
      out.println("&lt;tr>&lt;td>" + rs.getString(1));
      out.println("    &lt;td>" + rs.getString(2));
    }

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

</section>

<section title="See also">

<ul>
<li><a href="db|config">Database configuration</a> reference
<li><a href="db|thirdparty">Sample configurations</a> for several database drivers
<li>Using <a href="doc|db|tutorial/jdbc-ioc/index.xtp">Dependency Injection for Databases</a>
<li>The <a href="doc|resource|tutorial/injection/index.xtp">Dependency Injection</a> (or Assembly Line) pattern.
</ul>

</section>

  </body>
</document>
