| Using a database from a Servlet |
| Cookbook |
| Tutorials |
Obtain and use a database connection from a Servlet.
| web.xml |
A <database> configuration in WEB-INF/web.xml makes a datasource available to the application.
The following example configures a PostgreSQL driver, more example configurations are in the Third-party section of the documentation.
<servlet> and <servlet-mapping> are used to enable the servlet.
<web-app xmlns="http://caucho.com/ns/resin"
xmlns:resin="http://caucho.com/ns/resin/core">
<database jndi-name="jdbc/HogwartsStore"/>
<driver>
<type>org.postgresql.Driver</type>
<url>jdbc:postgresql://127.0.0.1:5432/hogwarts_store</url>
<user>web</user>
<password>i19web</password>
</driver>
</database>
...
<servlet servlet-name="example.TestDatabase" servlet-class="example.TestDatabase"/>
<servlet-mapping url-pattern="/TestDatabase" servlet-name="example.TestDatabase"/>
...
</web-app>
|
| Servlet initialization |
The DataSource is obtained using the jndi-name specified when configuring the database resource. The JNDI lookup of DataSource is done only once, the DataSource is stored in the _dataSource 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.
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
{
...
}
}
|
| Using the DataSource to get a Connection |
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 block is very important. Without the close in the finally block, Resin's database pool can lose connections.
This is so important, it is worth repeating. The 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.
...
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:<br>");
while (rs.next()) {
out.print(rs.getString(1));
out.print(" ");
out.print(rs.getInt(2));
out.println("<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);
}
}
}
}
|
| Variation: Dependency Injection |
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.
Fortunately, the code as it stands supports Dependency Injection on servers (like Resin) that support it.
For example, to have the servlet use the "jdbc/DiagonAlley" DataSource requires only a small change in web.xml.
<servlet servlet-name="example.TestDatabase" servlet-class="example.TestDatabase">
<init>
<data-source>${jndi:lookup("jdbc/DiagonAlley")}</data-source>
</init>
</servlet>
<servlet-mapping url-pattern="/TestDatabase" servlet-name="example.TestDatabase"/>
|
The block causes Resin to call setDataSource before the init() method is called on the servlet. The servlet does not do a jndi lookup for the DataSource, because it already has one.
| Cookbook |
| Tutorials |
| Copyright © 1998-2005 Caucho Technology, Inc. All rights reserved. Resin® is a registered trademark, and HardCoretm and Quercustm are trademarks of Caucho Technology, Inc. |  |