Struts 2 Login Example using MySQL database Stored Procedure

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

This Struts 2 tutorial will explain how to create a login application using Stored Procedure. Before creating this application some basic requirements are necessary to known.

What is Stored Procedure?

A stored procedure is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary.
Wikipedia

How to Call Stored Procedure in Java?

In Java programming language the CallableStatement interface used to execute SQL stored procedures.

IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

Syntax:

CallableStatement cs = connection().prepareCall("{call <procedureName>(arg1, arg1, arg1, ……)}");

Software Used

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

Project Structure in Eclipse

Create Table and Procedure

Now create a table in your database to store the user information

add_user.sql

CREATE TABLE add_user (
  user_name varchar(50) DEFAULT NULL,
  user_email varchar(50) NOT NULL,
  user_pass varchar(20) DEFAULT NULL,
  PRIMARY KEY (user_email)
);

Create the procedure to insert the user information into the table.

addUser.sql

DELIMITER $$

USE `websparrow`$$

DROP PROCEDURE IF EXISTS `addUser`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `addUser`(
  vuser_name VARCHAR (50),
  vuser_email VARCHAR (50),
  vuser_pass VARCHAR (20),
  OUT msg VARCHAR (100)
)
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062 #
  SET msg = 'Sorry ! Email already exist.' ;
  SET msg = 'User successfully created. You can login now' ;
  INSERT INTO add_user (user_name, user_email, user_pass) 
  VALUES
    (
      vuser_name,
      vuser_email,
      vuser_pass
    ) ;
  COMMIT ;
END$$

DELIMITER ;

Create the procedure to get the user information from the table.

getUser.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS `getUser` $$

CREATE PROCEDURE `websparrow`.`getUser` (
  vuser_email VARCHAR (50),
  vuser_pass VARCHAR (20),
  OUT msg VARCHAR (100)
) 
BEGIN
  DECLARE CONTINUE HANDLER FOR 1329 #
  SET msg = "Sorry ! Invalid email and password..." ;
  SELECT 
    user_name INTO msg 
  FROM
    add_user 
  WHERE user_email = vuser_email 
    AND user_pass = vuser_pass ;
END $$

DELIMITER ;

Add Struts 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>Struts2MySQLStoredProcedureExp</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 and Action Class

Admin.java

package org.websparrow;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class Admin {
	public Connection connection() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection("jdbc:mysql://localhost:3306/websparrow", "root", "");
	}

	public String getUser(String user_email, String user_pass) {
		try {
			CallableStatement cs = connection().prepareCall("{call getUser(?,?,?)}");
			cs.setString(1, user_email);
			cs.setString(2, user_pass);
			cs.registerOutParameter(3, Types.VARCHAR);
			cs.executeQuery();
			return cs.getString(3);
		} catch (Exception e) {
			return e.getMessage();
		}
	}

	public String addUser(String user_name, String user_email, String user_pass) {
		try {
			CallableStatement cs = connection().prepareCall("{call addUser(?,?,?,?)}");
			cs.setString(1, user_name);
			cs.setString(2, user_email);
			cs.setString(3, user_pass);
			cs.registerOutParameter(4, Types.VARCHAR);
			cs.executeQuery();
			return cs.getString(4);
		} catch (Exception e) {
			return e.getMessage();
		}

	}

}

RegisterAction.java

package org.websparrow;

import com.opensymphony.xwork2.ActionSupport;

public class RegisterAction extends ActionSupport {
	private String name, email, pass, msg;
	Admin adm = new Admin();

	@Override
	public String execute() throws Exception {
		try {
			setMsg(adm.addUser(name, email, pass));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "SUCCESS";
	}

	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 getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

}

LoginAction.java

package org.websparrow;

import com.opensymphony.xwork2.ActionSupport;

public class LoginAction extends ActionSupport {
	private String msg;
	private String email, pass;
	Admin adm = new Admin();

	@Override
	public String execute() throws Exception {
		msg = adm.getUser(email, pass);
		if (msg.startsWith("Sorry")) {
			return "FAILURE";
		} else {
			return "SUCCESS";
		}
	}

	public String getEmail() {
		return email;
	}

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

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

}

Create the JSP

index.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE html>
<html>
<head>
<title>Login-WebSparrow.org</title>
</head>
<body style="text-align: center;">
	<h3>Login Please</h3>
	<form action="login" method="post">
		<pre>
<input type="text" name="email" placeholder="Enter Email" required />

<input type="password" name="pass" placeholder="Enter Password" required />

<input type="submit" name="sub" value="Login" />
		</pre>
		<%
			String str = (String) request.getAttribute("msg");
			if (str != null) {
				out.print(str);
			}
		%>
	</form>
	<a href="register.jsp">Register Now</a>
</body>
</html>

register.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE html>
<html>
<head>
<title>Register- WebSparrow.org</title>
</head>
<body style="text-align: center;">
	<h3>Register Please</h3>
	<form action="register" method="post">
		<pre>
		<input type="text" name="name" placeholder="Full Name" required />
		
		<input type="text" name="email" placeholder="Enter Email" required />
		
		<input type="password" name="pass" placeholder="Enter Password" required />
		
		<input type="submit" name="sub" value="Register" />
		</pre>
		<%
			String str = (String) request.getAttribute("msg");
			if (str != null) {
				out.print(str);
			}
		%>
	</form>
	<a href="index.jsp">Login Now</a>
</body>
</html>

welcome.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<!DOCTYPE html>
<html>
<head>
<title>Welcome</title>
</head>
<body>
	<h1>Welcome,<s:property value="msg" /></h1>
</body>
</html>

Map Action Class 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="org.websparrow.RegisterAction">
			<result name="SUCCESS">/register.jsp</result>
		</action>
		<action name="login" class="org.websparrow.LoginAction">
			<result name="SUCCESS">/welcome.jsp</result>
			<result name="FAILURE">/index.jsp</result>
		</action>
	</package>
</struts>

Output

To run your application hit this URL in your browser localhost:8080/Struts2MySQLStoredProcedureExp
Screen 1