Struts 2 and Oracle Database Connectivity Example


In this example, we will create a Struts 2 application that will connect to the Oracle database. To connect Struts 2 application with database, first of all, create a connection() method using Connection Interface and check we are able to communicate with the Oracle database or not. If connection established successfully then we will proceed for next.

Similar Post: Struts 2 and MySQL Database Connectivity Example

public static Connection connection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "websparrow", "system");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
}

localhost: Your host id or IP address.

1521: Oracle port number.

xe: Oracle database SID.

websparrow: Oracle database user name.

system: User password.

Block Diagram

Let’s try to understand with the help of DFD diagram.

Struts 2 and Oracle Database Connectivity Example

Software Used

In our example, we have used the following software.

  1. Eclipse IDE
  2. Tomcat 8
  3. JDK 8
  4. Oracle 11g

Required Dependencies/JARs

To communicate with Oracle database using Struts 2 application, we need the followings dependencies in the project.

  1. struts2 jars
  2. ojdbc14.jar

You can easily download all these JARs from Apache Struts and Oracle website.

Project Structure in Eclipse

Struts 2 and Oracle Database Connectivity Example

Add Struts 2 Filter

Define the Struts 2 filter in web.xml

web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	id="WebApp_ID" version="2.5">
	<display-name>Struts2OracleConnectivity</display-name>
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
	<filter>
		<filter-name>struts2</filter-name>
		<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
	</filter>
	<filter-mapping>
		<filter-name>struts2</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
</web-app>

Create DAO Class

Create a DAO class to test the connection is established or not and to retrieve the records from the database.

ConnectionDAO.java
package org.websparrow;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionDAO {

	// method to create the connection
	public static Connection connection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "websparrow", "system");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// method to retrieve the records from database
	public static ResultSet report() throws SQLException {
		ResultSet rs = null;
		try {
			Statement statement = connection().createStatement();
			rs = statement.executeQuery("select EMPNO,ENAME,JOB,to_char(HIREDATE,'DD-MON-YYYY') as HIREDATE,SAL from emp");
			return rs;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			connection().close();
		}
	}
}

Create Bean Class

Define all the parameters that you want to retrieve from the table and generates its getters and setters.

DataBean.java
package org.websparrow;

public class DataBean {
	private int empNo;
	private String empName;
	private String job;
	private String hireDate;
	private int sal;

	public int getEmpNo() {
		return empNo;
	}

	public void setEmpNo(int empNo) {
		this.empNo = empNo;
	}

	public String getEmpName() {
		return empName;
	}

	public void setEmpName(String empName) {
		this.empName = empName;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getHireDate() {
		return hireDate;
	}

	public void setHireDate(String hireDate) {
		this.hireDate = hireDate;
	}

	public int getSal() {
		return sal;
	}

	public void setSal(int sal) {
		this.sal = sal;
	}

}

Create Action Class

And finally, create an Action class to handle all request and response.

Struts2OracleAction.java
package org.websparrow;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.opensymphony.xwork2.ActionSupport;

public class Struts2OracleAction extends ActionSupport {
	private static final long serialVersionUID = 1L;
	DataBean bean = null;
	List<DataBean> list = null;

	// testing the connection whether it is established or not
	public String checkOracleConnection() {
		Connection conn = org.websparrow.ConnectionDAO.connection();
		if (conn == null) {
			return "ERROR";
		} else {
			return "SUCCESS";
		}
	}

	// display all the data on JSP page
	public String report() throws Exception {
		ResultSet rs = org.websparrow.ConnectionDAO.report();
		list = new ArrayList<>();
		if (rs != null) {
			while (rs.next()) {
				bean = new DataBean();
				bean.setEmpNo(rs.getInt("EMPNO"));
				bean.setEmpName(rs.getString("ENAME"));
				bean.setJob(rs.getString("JOB"));
				bean.setHireDate(rs.getString("HIREDATE"));
				bean.setSal(rs.getInt("SAL"));
				list.add(bean);
			}
			return "SUCCESS";
		} else {
			return "ERROR";
		}
	}

	public List<DataBean> getList() {
		return list;
	}

	public void setList(List<DataBean> list) {
		this.list = list;
	}
}

Create View Components

Create the JSP pages which are visible to the users.

index.jsp this page will test the connection.

<%@taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE>
<html>
<head>
<title>Struts 2 and Oracle Database Connectivity Example</title>
</head>
<body>
	<h2>Struts 2 and Oracle Database Connectivity Example</h2>
	<s:form action="CheckConnection.action">
		<s:submit value="Test Connection"></s:submit>
	</s:form>
</body>
</html>

connected.jsp If the connection is established successfully, you will be redirected to this page.

<%@taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE>
<html>
<head>
<title>Struts 2 and Oracle Database Connectivity Example</title>
</head>
<body>
	<h2>Struts 2 and Oracle Database Connectivity Example</h2>
	<p>Congratulation, You have successfully connected with Oracle database.</p>
	<s:form action="report.action">
		<s:submit value="Report"></s:submit>
	</s:form>
	<hr>
	<table style="margin-left: 20px;" border="1px solid;">
		<thead>
			<tr style="background-color: #E0E0E1;">
				<th>EMPNO</th>
				<th>ENAME</th>
				<th>JOB</th>
				<th>HIREDATE</th>
				<th>SAL</th>
			</tr>
		</thead>
		<s:iterator value="list">
			<tr>
				<td><s:property value="empNo" /></td>
				<td><s:property value="empName" /></td>
				<td><s:property value="job" /></td>
				<td><s:property value="hireDate" /></td>
				<td><s:property value="sal" /></td>
			</tr>
		</s:iterator>
	</table>
</body>
</html>

error.jsp if any exception occurred, then you are here.

<!DOCTYPE>
<html>
<head>
<title>Struts 2 and Oracle Database Connectivity Example</title>
</head>
<body>
	<h2>Struts 2 and Oracle Database Connectivity Example</h2>
	<p>There is some problem. Check the console log for more details.</p>
</body>
</html>

Map Request in Struts.xml

Map the action classes and result in the struts.xml

struts.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
	<constant name="struts.devMode" value="true" />
	<package name="default" extends="struts-default" namespace="/">
		<action name="CheckConnection" class="org.websparrow.Struts2OracleAction" method="checkOracleConnection">
			<result name="SUCCESS">/connected.jsp</result>
			<result name="ERROR">/error.jsp</result>
		</action>
		<action name="report" class="org.websparrow.Struts2OracleAction" method="report">
			<result name="SUCCESS">/connected.jsp</result>
			<result name="ERROR">/error.jsp</result>
		</action>
	</package>
</struts>

Output:

Now deploy the application inside Tomcat » webapps folder and start the server.

Screen 1

Struts 2 and Oracle Database Connectivity Example

Screen 2

Struts 2 and Oracle Database Connectivity Example

Screen 3

Struts 2 and Oracle Database Connectivity Example

Similar Posts

About the Author

Atul Rai
I love sharing my experiments and ideas with everyone by writing articles on the latest technological trends. Read all published posts by Atul Rai.