![]() | ![]() | ![]() | ||||||||||||||
![]() | ||||||||||||||||
documentation examples changes amber (jpa) ejb database ioc jmx jsf messaging quercus remoting servlet security jdbc basic jdbc ioc | 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:
JDBC database access is based around the Factory pattern.
With JDBC,
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) 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:
<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.ConnectionFactoryThe specific driver for this example,
The servlet needs to locate the Because the servlet only needs to look up the
import javax.webbeans.Named; public class BasicServlet extends HttpServlet { @Named("jdbc/basic") private DataSource _ds; ... } Standard configurationresin-web.xml <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
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 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(); } } }
|