Struts Database Connection Using Mysql Database

In this tutorial to use MySQL Database ,we will configure the Struts DataSource Manager and use the connection provided by Struts DataSource in action class.

Downloading and Installing Tomcat 5.5.9

Go to http://jakarta.apache.org/tomcat/. Down load the Tomcat 5.5.9 Install it into your machine.Test and run the pages that comes with tomcat.

Download Struts

You can download the Struts struts-1.2.7 from http://struts.apache.org/download.cgi and unzip it to your favorite directory. Go to struts-1.2.7\webapps directory and then unzip struts-blank.war file and We will use this file to write our tutorial also.

Download MySQL JDBC Driver

Download the mysql-connector-java-3.0.16-ga-bin.jar from here mysql-connector-java-3.0.16-ga-bin.jar or you can download and use latest version of mysql jdbc driver and copy JDBC driver file (mysql-connector-java-3.0.16-ga-bin.jar or latest version) to the jakarta-tomcat-5.5.9\common\lib directory of your tomcat installation. This will add automatically MySQL JDBC driver to the tomcat server.

Creating MySQL Database

I am using MySQL server installed on my local machine in this tutorial . You can install and download the MySQL on your local machine and use for this tutorial and if you already have the MySQL server then you can use the existing MySQL server.

To create test table,create database "strutsdatabase" on the MySQL server and then run following query.

CREATE TABLE `test` (
`username` varchar(20) NOT NULL default ''
) TYPE=MyISAM;

/*Data for the table `test` */

insert into `test` values ('rajesh'),('George'),('Vikas'),('Prakash'),('Mahesh');

Above query is used to create test table and then populates the table with the data.

Configuring Struts Application

In the strutsdatabase directory,create a directory "strutsdatabase" in jakarta-tomcat-5.5.9\webapps\ directory and copy the content of struts-blank application (unzipped above) .

Now start tomcat and by typing the url try to access the strutsdatabase application http://localhost:8080/strutsdatabase in browser. Your browser should display welcome page. After testing shutdown tomcat server.

Configuring Struts DataSource Manager

It is easy for your Action class get database connection using the Struts DataSource manager.In the struts-config.xml,to configure the Stuts DataSource Manager we will uncomment <data-sources> entry .

In struts-config.xml,uncomment <data-sources> and then change the line "org.apache.commons.dbcp.BasicDataSource" to "org.apache.tomcat.dbcp.dbcp.BasicDataSource". In tomcat 5.5.9 dbcp classes are packaged in the naming-factory-dbcp.jar archieve, so instead of "org.apache.commons.dbcp.BasicDataSource" we are using "org.apache.tomcat.dbcp.dbcp.BasicDataSource". After this in the <data-sources> tag,change database dirver, database url and passwor.

You <data-source> attributes are as follows:

<data-sources>
<data-source type="org.apache.tomcat.dbcp.dbcp.BasicDataSource">
<set-property
property="driverClassName"
value="com.mysql.jdbc.Driver" />
<set-property
property="url"
value="jdbc:mysql://localhost:3306/strutsdatabase?autoReconnect=true" />
<set-property
property="username"
value="root" />
<set-property
property="password"
value="" />
<set-property
property="maxActive"
value="10" />
<set-property
property="maxWait"
value="5000" />
<set-property
property="defaultAutoCommit"
value="false" />
<set-property
property="defaultReadOnly"
value="false" />
<set-property
property="validationQuery"
value="SELECT COUNT(*) FROM test" />
</data-source>
</data-sources>

Create action Class to Test the DataSource

To get the connection form DataSource, we will write the following code of the Action class :

package test;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import java.sql.*;

public class TestDataSource extends Action
{
public ActionForward execute(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception
{

javax.sql.DataSource dataSource;
java.sql.Connection myConnection=null;
try
{
dataSource = getDataSource(request);
myConnection = dataSource.getConnection();
Statement stmt=myConnection.createStatement();
ResultSet rst=stmt.executeQuery("select username from test");
System.out.println("******************************************");
System.out.println("********Out Put from TestDataSource ******");
while(rst.next())
{
System.out.println("User Name is: " + rst.getString("username"));
}
System.out.println("******************************************");
rst.close();
stmt.close();
// do what you wish with myConnection
}
catch (SQLException sqle)
{
getServlet().log("Connection.process", sqle);
}
finally
{
//enclose this in a finally block to make
//sure the connection is closed
try
{
myConnection.close();
}
catch (SQLException e)
{
getServlet().log("Connection.close", e);
}
}

return mapping.findForward("success");
}
}

To get the data source,following code is used and then connection from the Struts DataSource:

dataSource = getDataSource(request);
myConnection = dataSource.getConnection();

Go to the jakarta-tomcat-5.5.9\webapps\strutsdatabase\WEB-INF\src\java\test directory and then save this file(TestDataSource.java). Then add the servlet API into class path. Then open dos prompt and navigate to the jakarta-tomcat-5.5.9\webapps\strutsdatabase\WEB-INF\src\ directory and issue run ant and this will compile action class (TestDataSource.java) and copy it to the classes directory of the webapplication.

Creating Action Mapping struts-config.xml

In struts-config.xml,add the following action mapping :

Running and testing

Start the tomcat and then browse the url http://localhost:8080/strutsdatabase/DataSource.do.

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.

Keywords: struts database connection,data source connection,how to database,struts web xml,sql server database,ms access database,sql server connection,connecting to database,connect to database,java database connection,truts tutorial,java struts,jsp database connection,jndi database connection,tomcat database connection,websphere database connection,jboss database connection,struts tutorials,struts example,struts api,struts examples

HTML Quizes

XML Quizes