How to Fetch data from Database in JSP using Struts 2

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

This Struts 2 tutorials will explain how to fetch data from MySQL database using struts 2. In this demo, we are going to use Java Collection List to store all the data and display the collection of data using Struts 2 iterator tag.

Software Used

1. Eclipse
2. MySQL Database
3. JDK 8
4. Tomcat 8

In this demo, we need to create these pages…

index.jps for invoking the action and display the records.

FetchDataAction.java for storing the incoming data in List.

FetchDataDAO.java for connecting with the database and retrieve the data.

FetchDataBean.java represents table data.

web.xml used for defining the filter and welcome page.

struts.xml used for defining the action and result.

Note this demo has been designed on MVC Architecture.

JARS Required

To use Struts 2, we need the Struts core jars and MySQL connector jar. To download Struts jars click here.

1. commons-fileupload-1.3.1.jar
2. commons-io-2.2.jar
3. commons-lang-2.4.jar
4. commons-lang3-3.2.jar
5. commons-logging-api-1.1.jar
6. freemarker-2.3.19.jar
7. javassist-3.11.0.GA.jar
8. ognl-3.0.6.jar
9. struts2-core-2.3.20.1.jar
10. xwork-core-2.3.20.1.jar
11. mysql-connector-java-5.1.38-bin

Project Structure in Eclipse

Define the Struts 2 filter

Before starting the code we need define struts 2 filter in web.xml.

web.xml

<web-app>
	<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 Data Access Object and Bean

Create the DAO and Bean class that communicates with the database and retrieve the data from the table.

FetchDataDAO.java

package org.websparrow;

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

public class FetchDataDAO {

	public ResultSet fetchData() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "");
			String query = "SELECT NAME,  EMAIL ,  MOBILE ,  COLLEGE ,  COURSE,  SKILLS ,  MARKS1 ,  MARKS2 ,  MARKS3 FROM pdfreport";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			return rs;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}

FetchDataBean.java

package org.websparrow;

public class FetchDataBean {
	String name, email, mobile, college, course, skills;
	int marks1, marks2, marks3;
	
	// Generates Getters and Setters...
 }

Create the Action

Action class contains ArrayList object as the data member and execute method.

FetchDataAction.java

package org.websparrow;

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

import com.opensymphony.xwork2.ActionSupport;

public class FetchDataAction extends ActionSupport {
	List<FetchDataBean> dataList = null;
	ResultSet rs = null;
	String fetchData = null;
	FetchDataDAO daoObj = null;
	// Generate Getters and Setters...

	public String execute() throws Exception {
		try {
			if (fetchData.equals("FetchRecords")) {
				dataList = new ArrayList<FetchDataBean>();
				FetchDataBean dataBean = null;
				rs = new FetchDataDAO().fetchData();
				if (rs != null) {
					while (rs.next()) {
						dataBean = new FetchDataBean();
						dataBean.setName(rs.getString("NAME"));
						dataBean.setEmail(rs.getString("EMAIL"));
						dataBean.setMobile(rs.getString("MOBILE"));
						dataBean.setCollege(rs.getString("COLLEGE"));
						dataBean.setCourse(rs.getString("COURSE"));
						dataBean.setSkills(rs.getString("SKILLS"));
						dataBean.setMarks1(rs.getInt("MARKS1"));
						dataBean.setMarks2(rs.getInt("MARKS2"));
						dataBean.setMarks3(rs.getInt("MARKS3"));
						dataList.add(dataBean);

					}

				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "SUCCESS";
	}
}                    

Map the Action and Result

Finally, define the action and result in 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="FetchRecords" class="org.websparrow.FetchDataAction">
			<result name="SUCCESS">/index.jsp</result>			
		</action>
	</package>
</struts&gt

Create User Interface

Now create the page that interacts with the user.

<%@taglib prefix="s" uri="/struts-tags"%>
<html>
<head>
<style>
table, td, th {
	border: 1px solid black;
}

table {
	border-collapse: collapse;
	width: 100%;
}

th {
	height: 50px;
}

button {
	background-color: #008CBA;
	border: none;
	color: white;
	padding: 15px 32px;
	text-align: center;
	text-decoration: none;
	display: inline-block;
	font-size: 16px;
	border-radius: 12px;
}
</style>
</head>
<body style="text-align: center;">
	<s:form action="FetchRecords.action" method="POST">
		<button type="submit" name="fetchData" value="FetchRecords">Fetch
			Records</button>
	</s:form>
	<s:if test="fetchData=='FetchRecords'">
		<div style="margin-top: 40px; margin-right: 150px; margin-left: 150px;">
			<table>
				<thead>
					<tr style="background-color: #E0E0E1;">
						<th>NAME</th>
						<th>EMAIL</th>
						<th>MOBILE</th>
						<th>COLLEGE</th>
						<th>COURSE</th>
						<th>SKILLS</th>
						<th>MARKS 1</th>
						<th>MARKS 2</th>
						<th>MARKS 3</th>
					</tr>
				</thead>
				<s:iterator value="dataList">
					<tr>
						<td><s:property value="name" /></td>
						<td><s:property value="email" /></td>
						<td><s:property value="mobile" /></td>
						<td><s:property value="college" /></td>
						<td><s:property value="course" /></td>
						<td><s:property value="skills" /></td>
						<td><s:property value="marks1" /></td>
						<td><s:property value="marks2" /></td>
						<td><s:property value="marks3" /></td>
					</tr>
				</s:iterator>
			</table>
		</div>
	</s:if>
</body>
</html>

Result of Demo

For the test, the application hit this URL in your browser http://localhost:8080/Struts2FetchData/. We will get an output as given below.

Screen 1

Screen 2