<document>
<header>
<product>resin</product>
<title>Using a database from a Servlet</title>
<description>
<p>
Obtain and use a database connection from a Servlet.
</p>
</description>
</header>

<body>
<summary/>

<section name="webxml" title="web.xml">

<p>
A <a config-tag="database"/> configuration in <code>WEB-INF/web.xml</code> 
makes a datasource available to the application.
</p>

<p>
The following example configures a PostgreSQL driver, more example
configurations are in the <a href="doc|db|thirdparty">Third-party</a> section
of the documentation.
</p>

<p>
<a config-tag="servlet"/> and <a config-tag="servlet-mapping"/> are used to 
enable the servlet.
</p>

<example title="WEB-INF/web.xml">
&lt;web-app xmlns="http://caucho.com/ns/resin"
         xmlns:resin="http://caucho.com/ns/resin/core"&gt;

  &lt;database jndi-name="jdbc/HogwartsStore"/&gt;
    &lt;driver&gt;
      &lt;type&gt;org.postgresql.Driver&lt;/type&gt;
      &lt;url&gt;jdbc:postgresql://127.0.0.1:5432/hogwarts_store&lt;/url&gt;
      &lt;user&gt;web&lt;/user&gt;
      &lt;password&gt;i19web&lt;/password&gt;
    &lt;/driver&gt;
  &lt;/database&gt;

  ...

  &lt;servlet servlet-name="example.TestDatabase" servlet-class="example.TestDatabase"/&gt;
  &lt;servlet-mapping url-pattern="/TestDatabase" servlet-name="example.TestDatabase"/&gt;

  ...

&lt;/web-app&gt;

</example>

</section>

<section name="init" title='Servlet initialization'>
    
<p>The <code>DataSource</code> is obtained using the <a
config-tag="database">jndi-name</a> specified when configuring the database
resource.  The JNDI lookup of <code/DataSource/> is done only once, the
<code/DataSource/> is stored in the <code>_dataSource</code> member variable so
that it can be used for each request. If it was not stored, there would be an
impact on performance
from having to do the lookup each time.</p>

<example title="TestDatabase.java">
package example;

import java.io.*;

import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;
import javax.sql.*;

public class TestDatabase
  extends HttpServlet
{
  private final static Logger log = Logger.getLogger(TestDatabase.class.getName());

  private final static String DATASOURCE_NAME = "jdbc/HogwartsStore";

  private DataSource _dataSource;

  public void setDataSource(DataSource dataSource)
  {
    _dataSource = dataSource;
  }

  public DataSource getDataSource()
  {
    return _dataSource;
  }

  public void init()
    throws ServletException
  {
    if (_dataSource == null) {
      try {
        Context env = (Context) new InitialContext().lookup("java:comp/env");

        _dataSource = (DataSource) env.lookup(DATASOURCE_NAME);

        if (_dataSource == null)
          throw new ServletException("`" + DATASOURCE_NAME + "' is an unknown DataSource");
      } catch (NamingException e) {
        throw new ServletException(e);
      }
    }
  }

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException
  {
    ...
  }
}
</example>

</section> <!-- init -->

<section name="use" title='Using the DataSource to get a Connection'>

<p>The DataSource is a factory that is used to obtain connections when they are
needed.  The following is a sample design pattern for getting new database
connections from within a servlet..  The <var/try ... finally/> block is very
important.  Without the close in the finally block, Resin's database pool can
lose connections.
</p>  

<p>This is so important, it is worth repeating.  The <var/try
... finally/> block is very important.  Without the close in the finally block,
Resin's database pool can lose connections.  If you fail to close() a
connection, Resin cannot know that it is not being used anymore, and cannot
allocate it for another request.  Eventually, you may run out of connections.
</p>

<example title="TestDatabase.java">

...

public class TestDatabase
  extends HttpServlet
{
  public void setDataSource(DataSource dataSource) ...

  public DataSource getDataSource() ...

  public void init() ...

  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException
  {
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();

    Connection conn = null;

    try {
      conn = getDataSource().getConnection();

      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("select NAME, PRICE from BROOMS");

      out.println("Brooms:&lt;br>");

      while (rs.next()) {
        out.print(rs.getString(1));
        out.print(" ");
        out.print(rs.getInt(2));
        out.println("&lt;br>");
      }

      rs.close();
      stmt.close();
    }
    catch (SQLException e) {
      throw new ServletException(e);
    }
    finally {
      try {
        if (conn != null)
          conn.close();
      }
      catch (SQLException e) {
        log.log(Level.WARNING, ex.toString(), ex);
      }
    }
  }
}
</example>
</section> <!-- use -->
    
<section name="inject" title='Variation: Dependency Injection'>

<p>
The example as it stands is tied to a single data-source name,
"jdbc/HogwartsStore".  This limits configuration options, using a different
DataSource requires a change to the code.
</p>

<p>
Fortunately, the code as it stands supports 
<a href="doc|resource|tutorial/injection/index.xtp">Dependency Injection</a>
on servers (like Resin) that support it.
</p>

<p>
For example, to have the servlet use the "jdbc/DiagonAlley" DataSource requires
only a small change in web.xml.
</p>

<example title="web.xml - DataSource injection">
  &lt;servlet servlet-name="example.TestDatabase" servlet-class="example.TestDatabase"&gt;
    &lt;init&gt;
      &lt;data-source&gt;${'${'}jndi:lookup("jdbc/DiagonAlley")}&lt;/data-source&gt;
    &lt;/init&gt;
  &lt;/servlet&gt;

  &lt;servlet-mapping url-pattern="/TestDatabase" servlet-name="example.TestDatabase"/&gt;
</example>

<p>
The <var/init/> block causes Resin to call <code>setDataSource</code> before
the <code>init()</code> method is called on the servlet.  The servlet does
not do a jndi lookup for the DataSource, because it already has one.
</p>

</section> <!-- inject -->

</body>
</document>
