Now that we have the struts application up and running we can keep adding to the framework as per the ask of the project.
In the first version of the getquote project we have built a basic form where it will ask for a stock code and return the price of that stock code.
Here the execute method in LookupAction (which is called from the RequestProcessor ) simply checks if the stock code symbol value is "SUNW", and if so it returns a predefined value of the stock which is displayed in the forwarding page quote.jsp.
If we want to make this application a bit more usable it would be useful if we get maintain a database of stock code with their price and date. Now if a user wants to query the historical value of a stock he can simply enter the stock code and the date for which he want the price and the application should return him the price.
The below post illustrates the changes that are required to be made in the current application to to connect to the mysql database.
For installing and connecting to mysql you can refer to the post link below
MYSQL database installation
For creating the stocks table in the stocks database you can refer to the link below
Creating the Sample Stocks Database
Once your mysql installation and connection is tested you are ready to make the required code changes in your getquote application to connect the database.
There can be n number of ways of achieving this, but in the example which I describe below
There are 3 important steps for accomplishing this
- Changes to the struts config file for configuring the DataSource
- Getting the correct version of the mysql connector jar and the Jakarta Commons Database Connection pool (DBCP) jar file
- Changes to the LookupAction controller to connect the the configured database.
Changes to the struts-config.xml
A sample part of the struts-config.xml with changes for setting up a suitable datasource is below
<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="root" />
<set-property property="password"
value="mysql" />
</data-source>
</data-sources>
This datasource has the following main attributes
The type of datasource - org.apache.commons.dbcp.BasicDataSource
A fully qualified class name of the configuration object for the instance of the DataSource -
driverClassName which which in this case is a mysql driver - com.mysql.jdbc.Driver
the url for the datasource - jdbc:mysql://localhost/stocks
and a username/password to connect to the datasource.
This <data-sources> element must be the first element in your struts-config.xml file prior to the <form-beans> and the <action-mappings> elements.
Changes to LookupAction.java
We import some JDBC packages required to perform DataSource operations
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
In the getQuote method in LookupAction.java we add the following lines of code in a try-catch-finally block to use the DBCP datasource.
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);
} //end rs.next
else {
System.err.println("Symbol not found returning null");
}
} //end try
catch (SQLException sqle) {
System.err.println (sqle.getMessage());
}
finally {
...
}
In this code the first thing we do to retrieve the DBCP datasource is to call getDataSource() with the request parameter. This method exists in the org.apache.struts.action.Action class and is used to retrieve the defined DataSource from the ServletContext. For this you pass a reference of the request to getDataSource() which pulls the DBCP DataSource from the ServletContext. Next the dataSource.getConnection() method requests a Connection from the DBCP DataSource's pool of connections and retrieves the required Connection to the calling action. Once the database Connection object is got, we execute the sql statement to fetch the required data and store it in a ResultSet object. Once the resultset is got we close the ResultSet, Statement and the Connection.
dataSource = getDataSource(request);
Finally we add the correct version of the .jar files in the lib folder of the getquote project for the version of Struts, Java, Tomcat server and mysql datababase - namely .
mysql.jar
commons-pool-1.4.jar
commons-dbcp-1.2.2.jar
For the version of struts I am using I have taken the corresponding version of the jar files from the links below
For the complete code changes that are required in your struts application you can refer to
Testing the Database connection
http://localhost:8080/dynagetquote/index.jsp
When we test the application we can enter the stock codes we have entered in the database and get the corresponding prices.
References God's Word for the day
What does a wolf have in common with a lamb?
No more has a sinner with the devout.
What peace is there between a hyena and a dog?
And what peace between the rich and the poor?
Wild asses in the wilderness are the prey of lions;
likewise the poor are the feeding grounds for the rich.
Sirach 13:17-19
Concerning Prayer
"And whenever you pray do not be like the hypocrites;
for they love to stand and pray in the synagogues
and at the street corners, so that they may be seen by others.
Truly I tell you, they have received thier reward.
But whenever you pray, go into your room and shut the door
and pray to your Father who is in secret;
And your Fahter who sees in secret will reward you."
Mathew 6:5-6
