Digital Marriage Invitation Card

How to Fetch data from Database in JSP using Struts 2


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 database and retrieve the data.

FetchDataBean.java represents table data.

web.xml used for defining 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


How to Fetch data from Database in JSP using Struts 2

Define the Struts 2 filter

Before staring 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 communicate with data base and retrieve the data from 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>

Create User Interface

Now create the page that interact with 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 test the application hit this URL in your browser http://localhost:8080/Struts2FetchData/. We will get output as given below.

Screen 1 How to Fetch data from Database in JSP using Struts 2 Screen 2 How to Fetch data from Database in JSP using Struts 2
Share this article on: