Resin 4 Database Setup
From Resin 4.0 Wiki
Contents
|
Basic JDBC Database Pattern
This tutorial describes the standard pattern for using a database in Resin.
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 JNDIresource map.
- Using a
Connection
from theDataSource
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.
Files in this tutorial
File | Description |
---|---|
WEB-INF/resin-web.xml |
resin-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 [doc|db-thirdparty.xtp 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"> <url>resin:WEB-INF/db</url> </driver> </database> </web-app>
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.
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 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.
==
import javax.inject.Inject; import javax.inject.Named; public class BasicServlet extends HttpServlet { @Inject @Named("jdbc/basic") private DataSource _ds; ... }
Standard configuration
resin-web.xml
<servlet servlet-name="my-servlet" servlet-class="example.BasicServlet"> </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.
==
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(); } } }
See also
- [doc|database-tags.xtp Database configuration] reference
- [doc|db-thirdparty.xtp Sample configurations] for several database drivers
- Using [examples|db-jdbc-ioc/index.xtp Dependency Injection for Databases]
- The [examples|ioc-injection/index.xtp Dependency Injection] (or Assembly Line) pattern.
Using Dependency-Injection with JDBC Databases
This tutorial describes the standard pattern for using a database in Resin, following the Dependency-Injection/Inversion-of-Control pattern using Java Injection annotations.
Using a JDBC database is a three step process:
- Configuring the <database> in the resin-web.xml or resin.conf
- Injecting the
DataSource
to a field with a@com.caucho.config.Name
annotation. - Using a
Connection
from theDataSource
to execute the SQL.
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 CDI registry. 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
File | Description |
---|---|
WEB-INF/resin-web.xml |
resin-web.xml configuration |
WEB-INF/classes/example/BasicServlet.java |
The JDBC query 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 4.0, the <database> tag configures the database pool and driver and saves the connection factory (DataSource) in the Java Injection manager. Java Injection is an IoC (inversion-of-control) configuration system, 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 [doc|db-thirdparty.xtp 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" xmlns:example="urn:java:example"> <database jndi-name="jdbc/basic"> <example:Demo/> <driver type="com.caucho.db.jca.ConnectionFactory"> <url>resin:WEB-INF/db</url> </driver> </database> </web-app>
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.
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 CDI injection and standard servlet <init-param>
configuration with JNDI lookups. The Dependency Injection style is
simpler, cleaner and makes the application more testable and robust.
==
package example; import javax.inject.Inject; public class BasicServlet extends HttpServlet { @Inject @Demo private DataSource _ds; ... }
Dependency Injection configuration
Using dependency injection to configure servlets has some advantages over the init-param method:
- The configuration is type-safe. Resin's dependency injection will check the DataSource type before injecting the value. Resin will also inform you of any conflicts, e.g. configuration of multiple matching databases.
- 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 the @javax.inject.Inject
annotation to your
DataSource
field.
resin-web.xml
<servlet servlet-name="my-servlet" servlet-class="example.BasicServlet"> </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
- [doc|config-database.xtp Database configuration] reference
- [doc|db-thirdparty.xtp Sample configurations] for several database drivers
- Using [../db-jdbc-ioc/index.xtp Dependency Injection for Databases]
- The [../ioc-injection/index.xtp Dependency Injection] (or Assembly Line) pattern.