Struts 2 and MySQL Database Connectivity Example

By Atul Rai | Last Updated: November 10, 2018 Next

This Struts 2 tutorial explains how to connect with the database using the Struts 2 Framework and save the details in the database table. We are going to use Struts 2 UI tag to make application simple and reliable.

Software Required

Eclipse / NetBeans IDE
MySQL Database
JDK 1.5 or Higher
Tomcat 7/8

JARS Required

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

commons-fileupload-1.3.1.jar
commons-io-2.2.jar
commons-lang-2.4.jar
commons-lang3-3.2.jar
commons-logging-api-1.1.jar
freemarker-2.3.19.jar
javassist-3.11.0.GA.jar
ognl-3.0.6.jar
struts2-core-2.3.20.1.jar
xwork-core-2.3.20.1.jar
mysql-connector-java-5.1.38-bin

Database Schema

Creating a table in MySQL database to store the data is very first step. Here is the table script.

CREATE TABLE `struts2db` (
  `ID` INT(5) DEFAULT NULL,
  `NAME` VARCHAR(20) DEFAULT NULL,
  `EMAIL` VARCHAR(50) DEFAULT NULL,
  `CITY` VARCHAR(30) DEFAULT NULL
);

Project Structure in Eclipse

Struts 2 and MySQL Database Connectivity Example

How to Start Coding

Step 1- Before starts the entire coding we will define the filter tag for org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter in web.xml

web.xml

<web-app>
	<welcome-file-list>
		<welcome-file>register.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>

Step 2- Now create the Data Access Object and Action class.

Admin.java

package com.admin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import com.action.RegisterAction;

public class Admin {
	public static Connection conn() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "");
	}

	public static int register(RegisterAction ra) {
		int flag = 0;
		try {
			PreparedStatement ps = conn().prepareStatement("INSERT INTO STRUTS2DB VALUES(?,?,?,?)");
			ps.setInt(1, ra.getId());
			ps.setString(2, ra.getName());
			ps.setString(3, ra.getEmail());
			ps.setString(4, ra.getCity());
			flag = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
}

RegisterAction.java

package com.action;

import com.admin.Admin;
import com.opensymphony.xwork2.ActionSupport;

public class RegisterAction extends ActionSupport {
	int id;
	String name, email, city;

	public String execute() throws Exception {

		int var = Admin.register(this);
		if (var > 0) {
			return "SUCCESS";
		} else {
			return "input";
		}
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

}

Step 3- Create the UI page

register.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<html>
<head>
<title>Struts2 and MySQL database connectivity</title>
</head>
<body style="background-color: lightyellow;">
	<div style="margin-left: 25%; margin-top: 50px;">
		<h1>Struts2 and MySQL database connectivity example</h1>
		 <s:form action="Register.action">
			<s:textfield label="ID" name="id" />
			<s:textfield label="Name" name="name" />
			<s:textfield label="Email" name="email" />
			<s:textfield label="City" name="city" />
			<s:submit value="Register" />
		</s:form>
</div>
</body>
</html>

success.jsp

<html>
<head>
<title>Success</title>
</head>
<body style="text-align: center; color: green; font-size: 20px; margin-top: 200px;">
	Records registered successfully.
	<br>
	<br>
	<a href="register.jsp">Add New Records</a>
</body>
</html>

error.jsp

<html>
<head>
<title>Error</title>
</head>
<body style="text-align: center; color: red; font-size: 20px; margin-top: 200px;">
Some error occurred.
</body>
</html>

Step 4-Define the action and results 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="Register" class="com.action.RegisterAction">
			<result name="SUCCESS">/success.jsp</result>
			<result name="input">/error.jsp</result>
		</action>
	</package>
</struts>

Download Source Code – struts2-and-mysql-database-connectivity-example.zip

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.