Struts 2 and Oracle Database Connectivity Example

By Atul Rai | Last Updated: July 21, 2018 Previous       Next

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.

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

Add Struts 2 Filter

Define the Struts 2 filter in 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.

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.

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.

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

<?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

Screen 2

Screen 3