Integrating the DBCP into Your Struts Application | Professional Jakarta Struts (Programmer to Programmer)

< Day Day Up >

Integrating the DBCP project into a Struts application is an extremely easy task. It involves a few simple steps that we describe in this chapter. At the end of this section, you will have a Struts application that stores and retrieves its persistent data to and from a MySQL database using the DBCP project.

Creating a Sample Database

Before we can leverage the DBCP, we must have a database to connect to. The database we use in our Struts application is a MySQL database named stocks that contains a single table, also named stocks. This table contains a list of stock symbols and prices; its layout appears in Table 11.1. Table 11.2 shows the data that populates the stocks table.

Table 11.1: The stocks Table Structure
Column	Description
symbol	Contains a unique key identifying each stock. It is a varchar(15).
price	Contains the current price of the stock symbol being looked up. It is a double.

Table 11.2: The Contents of the stocks Table
Symbol	Price
SUNW	78.00
YHOO	24.45
MSFT	3.24

For our example, we use the MySQL database. To prepare, you must have an instance of the MySQL database server installed and running on your host machine. You can find the MySQL server at www.mysql.com. You should also download the latest JDBC driver for MySQL, which you can also find at this Web site.

Once you have MySQL installed, complete these steps to create and configure the MySQL database:

Start the MySQL client found in the <MYSQL_HOME>/bin/ directory by typing the following command:
```
 mysql 
```
Create the stocks database by executing this command:
```
 create database stocks; 
```
Make sure you are modifying the correct database by using this command:
```
 use stocks; 
```

Create the stocks table using the following command:

 create table stocks (   symbol varchar(15) not null primary key,   price double not null );

Insert the user data into the stocks table by executing these commands:

 insert into stocks values("SUNW", 78.00); insert into stocks values("YHOO", 24.45); insert into stocks values("MSFT", 3.24);

You now have a MySQL database of stocks. To test your installation, make sure you are still running the MySQL client and enter these two commands:

 use stocks; select * from stocks;

If everything was installed correctly, you should see results similar to the following:

 +--------+-------+ | symbol | price | +--------+-------+ | SUNW   |    78 | | YHOO   | 24.45 | | MSFT   |  3.24 | +--------+-------+ 3 rows in set (0.00 sec)

Using the DBCP in Your Struts Application

Now that we have a live database, let's work on the actual integration of the Commons DBCP into our Struts application. For our example, we revisit our wroxapp application, but this time let's use the previously defined database to look up the current stock price, as opposed to the hard-coded response that we are currently using.

The ways you can integrate the DBCP into a Struts application may be limitless, but one existing method leverages the functionality of the org.apache.struts.action.ActionServlet. For our example, let's take advantage of this built-in functionality.

To begin, add a new entry to the struts-config.xml file. This entry, <data-sources>, describes a collection of DataSource components that are managed by the ActionServlet. Table 11.3 describes the attributes of a <data-source> entry.

Table 11.3: The Attributes of a <data-source> Entry
Property	Description
type	The fully qualified class name of the DataSource object being used in this <data-source> definition. This class must extend javax.sql.DataSource and it must be completely configurable using <set-property> sub-elements (all configurable data members must satisfy the JavaBean specification).
className	The fully qualified class name of the configuration object for the instance of this DataSource. The implementation of this object must extend org.apache.struts.config.DataSourceConfig.
key	A unique key identifying a DataSource instance stored in the ServletContext. If this property is not used, the key defaults to Action.DATA_SOURCE_KEY. If you intend to use more than one DataSource in your application, you must include a key for each one.

To initialize the DBCP, you must add a new <data-source> element to the struts-config.xml file. Listing 11.1 contains our new <data-source> element, describing a DBCP DataSource.

Listing 11.1: Code added to struts-config.xml to initialize the DataSource.

 <?xml version="1.0" encoding="ISO-8859-1" ?> <!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.1//EN" "http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd"> <struts-config>   <data-sources>     <data-source       type="org.apache.commons.dbcp.BasicDataSource">       <set-property property="driverClassName"         value="com.mysql.jdbc.Driver" />       <set-property property="url"         value="jdbc:mysql://localhost/stocks" />       <set-property property="username"         value="YOUR USERNAME" />       <set-property property="password"         value="YOUR PASSWORD" />     </data-source>   </data-sources>   <form-beans>     <form-bean name="lookupForm"       type="ch03.LookupForm"/>     <form-bean name="dynamicLookupForm"       type="org.apache.struts.action.DynaActionForm">       <form-property name="symbol"         type="java.lang.String"         initial="MSFT"/>     </form-bean>   </form-beans>   <action-mappings>     <action className="ch08.WROXActionMapping"       path="/Lookup"       type="ch03.LookupAction"       name="lookupForm"       validate="true"       input="/index.jsp">       <set-property property="logResults"  value="true"/>       <forward name="success" path="/quote.jsp"/>       <forward name="failure" path="/index.jsp"/>     </action>     <action path="/DynamicLookup"       type="ch06.DynamicLookupAction"       name="dynamicLookupForm" >       <forward name="success" path="/quote.jsp"/>       <forward name="failure" path="/dynamicindex.jsp"/>     </action>   </action-mappings>   <controller     processor />   <plug-in className="ch04.WROXPlugin"/> </struts-config>

The <data-sources> element, which acts as the parent to all <data-source> elements, must be added prior to the <form-beans> and <action-mappings> elements. The <data-sources> element must be the first element in a struts-config.xml file.

As you look over the previous addition, notice that our new <data-source> element uses only one of the previously described attributes and several <set-property> sub-elements. First we use the type attribute to define the DataSource implementation--we are describing the Commons DBCP org.apache.commons.dbcp.BasicDataSource in this instance.

Next, we define several <set-property> sub-elements. These elements are used to configure the data members of the Commons DBCP. This is why all DataSource implementations must implement their configurable properties using the JavaBean specification. Table 11.4 defines each of the properties that we are using.

Table 11.4: The <set-property> Sub-elements of a <data-source> Entry
Property	Description
driverClassName	The fully qualified class name of the JDBC driver that we are using to connect to our database.
url	The JDBC URL that identifies the instance of the database used by our application.
username	The username that the DataSource will use to connect to the database.
password	The password that the DataSource will use to connect to the database.

The <set-property> sub-elements used in this example are specific to the Commons DBCP. When configuring other DataSources, consult the appropriate documentation to determine the available property data.

Now that you have looked over the strut-config.xml changes, let's modify the ch03.LookupAction to use the new DataSource. First add the following import statements. These statements represent JDBC packages required to perform most DataSource operations:

 import javax.sql.DataSource; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException;

The second change you must make is to the getQuote() method. This method must be modified to use the DBCP DataSource. These changes are shown in Listing 11.2.

Listing 11.2: Modifying the getQuote() method to use a DataSource object.

 protected Double getQuote(HttpServletRequest request,   String symbol) {   Double price = null;   Connection conn = null;   Statement stmt = null;   ResultSet rs = null;   DataSource dataSource = null;   try {     dataSource = getDataSource(request);     conn = dataSource.getConnection();     stmt = conn.createStatement();     rs = stmt.executeQuery("select * from stocks where "       + "symbol='" + symbol +"'");     if ( rs.next() ) {     double tmp = 0;     tmp = rs.getDouble("price");     price = new Double(tmp);     System.err.println("price : "       + price);   }   else {     System.err.println("Symbol not found returning null");   } } catch (SQLException e) {   System.err.println(e.getMessage()); } finally {   if (rs != null) {     try {       rs.close();     }     catch (SQLException sqle) {       System.err.println(sqle.getMessage());     }     rs = null;   }   if (stmt != null) {     try {       stmt.close();     }     catch (SQLException sqle) {       System.err.println(sqle.getMessage());     }     stmt = null;   }   if (conn != null) {     try {       conn.close();     }     catch (SQLException sqle) {       System.err.println(sqle.getMessage());     }     conn = null;     }   }   return price; }

When reviewing the new getQuote() method, notice that the first thing we do is retrieve the DBCP DataSource using the getDataSource() method. This method is a convenience method that exists in the base org,apache.struts.action.Action class and is used to retrieve the defined DataSource from the ServletContext. The code snippet that retrieves the DataSource is shown here:

 dataSource = getDataSource(request);

As you can see, retrieving the DataSource is a simple process: You pass the getDataSource() method a reference to the request and it pulls the DBCP DataSource out of the ServletContext.

If you have defined more than one DataSource, you must use the key property in the <data-source> element. If we had defined our DataSource using the key property, we would have to pass the same key as the second parameter of the getDataSource(HttpServletRequest request, String key) method.

Once you have a reference to the DBCP DataSource, you can get a Connection object from it and continue with normal JDBC processing, as shown here:

 conn = dataSource.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("select * from stocks where "   + "symbol='" + symbol + "'");

There is nothing special about this code except for the retrieval of the Connection from the dataSource.getConnection() method. This method requests a Connection from the DBCP DataSource's pool of connections and returns the retrieved Connection to the calling action. While the returned Connection looks and acts just like any other JDBC Connection object, it is actually a wrapper object around a java.sql.Connection object. The purpose of this wrapped Connection is to allow the DataSource to manage the Connections stored in its pool.

After the getQuote() method has completed its inspection of the ResultSet, it goes through the normal process of resource cleanup and closes the ResultSet, Statement, and Connection. The only thing notable about this section involves the use of the Connection object. In the normal definition of the Connection class, this method would close the connection to the database and thus render it useless for later processes. Because this Connection object is an instance of a wrapper object, however, the Connection is returned to the pool for later use instead of being closed.

That's all there is to using the DataSource in a Struts application. You can now test your changes by completing these steps:

Download the Commons DBCP archive from:
- http://jakarta.apache.org/builds/jakarta-commons/release/commons-dbcp/v1.0/
Extract the archive to a convenient location and copy the resulting commons-dbcp.jar file to the <CATALINA_HOME>/webapps/wroxapp/WEB-INF/lib directory.
Copy the MySQL JDBC driver into the <CATALINA_HOME>/webapps/wroxapp/WEB-INF/lib directory.
Restart Tomcat.
Open your browser to the following URL and enter a stock symbol contained in the database:
- http://localhost:8080/wroxapp/
If everything went according to plan, then you should see the quote.jsp with the price of the symbol that you entered.

As you may have noticed, we did not copy any of the Commons JAR files into our project. This is because the current version of Struts is prepackaged with several of the Commons libraries, including the DBCP. If you want to use a specific version of the DBCP, you need to identify that version and copy it to the <WEB-INF>/lib directory of your Web application.

< Day Day Up >