Using a database pool in an application
Once you have created a database pool, you can use it in your application to access the database that it provides connectivity with. Most applications other than the most trivial ones store data in databases and thereby require database access. In this section, we will walk through how we can configure an application to access a database through the database pool. We will look into how we can configure an application to use a server-scoped database pool as well as an application-scoped one. We will also see how we can configure an application to connect to the application-scoped database pool of a different application.
Accessing a server-scoped database pool
In order to access a server-scoped database pool from an application, you will need to give a dependency to the database pool in the application's Geronimo-specific deployment plan. We will illustrate this by using the JDBC sample application present in the directory of the samples for Chapter 3, Database Connectivity.
The application consists of a servlet that connects to a database and then creates a table called CUSTOMER that consists of four columns. These columns are Name, Address, Phone, and Email. The application then inserts a record into the table.
The application then retrieves that record and prints the details to your browser. The source code for the servlet is given as follows:
package com.packtpub.cust;
import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import javax.sql.DataSource;
import javax.annotation.Resource;
public class CustomerInfoServlet extends HttpServlet {
@Resource
private DataSource ds;
protected void service(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
Connection conn = null;
try {
conn = ds.getConnection();
Statement stmt = conn.createStatement();
String createSQL = "CREATE TABLE CUSTOMER(Name char(50),Address char(50),Phone char(20),Email char(50))";
stmt.executeUpdate(createSQL);
String insertSQL = "INSERT INTO TABLE CUSTOMER VALUES('Tom','Chapel Hill','234567','[email protected]')";
stmt.executeUpdate(insertSQL);
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERINFO");
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.print("<html>");
out.print("<head><title>Hello World Application</title> </head>");
out.println("<body>");
out.println("<table>");
out.println("<tr>");
out.print("<td><b>Name</b></td>");
out.print("<td><b>Address</b></td>");
out.print("<td><b>Phone</b></td>");
out.print("<td><b>Email</b></td>");
out.println("</tr>");
while (rs.next()) {
out.println("<tr>");
server-scoped database poolaccessingout.print("<td>");
String name = rs.getString(1);
out.print("</td>");
out.print("<td>");
String address = rs.getString(2);
out.print("</td>");
out.print("<td>");
String phoneNo = rs.getString(3);
out.print("</td>");
out.print("<td>");
String email = rs.getString(4);
out.print("</td>");
out.println("</tr>");
}
out.print("</body>");
out.print("</html>");
} catch (SQLException ex) {
closeConnection(conn);
} finally {
closeConnection(conn);
}
}
private void closeConnection(Connection conn){
try {
conn.close();
} catch (SQLException e) {
System.out.println("Cannot close connection");
e.printStackTrace();
}
}
}
The DataSource, which is a connection factory to the connections in the pool, is obtained by resource injection. The @Resource annotation is used to specify that an instance of DataSource should be injected into this variable, as shown:
@Resource private DataSource ds;
The connection is then obtained from the DataSource. A statement is created from the connection and then the executeUpdate method is called on the statement object to perform database operations, as shown:
conn = ds.getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate(createSQL);
The database plan that we will be using will be the same as the plan.xml file shown earlier. In case you have already deployed the plan, then you can deploy the application immediately. Otherwise, you will need to deploy the plan using the deployer, or the Deploy New portlet, as mentioned in the earlier sections on the deployment of database pools. After deploying the database pool, you will need to edit the geronimo-web.xml file of the application to have a dependency on the database pool, as well as a resource-ref element that maps the resource-ref-name to the database pool name. The plan is shown in the following illustration:
<web-app xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-2.0.1" xmlns:naming="http://geronimo.apache.org/xml/ns/naming-1.2" xmlns:sec="http://geronimo.apache.org/xml/ns/security-2.0" xmlns:sys="http://geronimo.apache.org/xml/ns/deployment-1.2"> <sys:environment> <sys:moduleId> <sys:groupId>com.packtpub.cust</sys:groupId> <sys:artifactId>CustomerInfo</sys:artifactId> <sys:version>1.0</sys:version> <sys:type>car</sys:type> </sys:moduleId> <sys:dependencies> <sys:dependency> <sys:groupId>console.dbpool</sys:groupId> <sys:artifactId>DerbyTestPool</sys:artifactId> </sys:dependency> </sys:dependencies> </sys:environment> <context-root>/CustomerInfo</context-root> <naming:resource-ref> <naming:ref-name>jdbc/DataSource</naming:ref-name> <naming:resource-link>DerbyTestPool</naming:resource-link> </naming:resource-ref> </web-app>
You can see the dependency on the database pool, as well as the resource-ref mapping, in this web application plan. On deploying this application, it will be able to utilize the database pool named DerbyTestPool to connect to and interact with a Derby database instance.
Accessing an application-scoped database pool from the same application
In the case of an application-scoped database pool, the only difference is that there is no dependency on a database pool configuration. Instead, the database pool is defined in the application's deployment descriptor as a separate module. The mechanism to access the database pool in the application and the resource mapping in the deployment plan is same, as illustrated earlier in this section. Also you can add an application-scoped database pool only in an enterprise application. You cannot add it to standalone web or EJB applications. There is a sample application provided at samplesChapter-3 Database ConnectivityJDBC Sample ear that is configured to use an application-scoped database pool. This application can be deployed by deploying the JDBCSample-ear-1.0.ear file, which is present in the modulesear arget directory.
Accessing an application-scoped database pool from a different application
In the case of accessing an application-scoped database pool from a different application, the only difference is that the application that wants to access the database pool of a different application should have the application defining the database pool as its parent application—that is, it should have a dependency to the application defining the database pool.