Sunday 25 October 2020

JSP Servlet Mysql CRUD Application




Project Structure





Create ActionHelper

package com.ramsis;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;



public class ActionHelper
{
public boolean addEmployee(Employee objEmployee) throws Exception{
Connection con = null;
ResultSet rs = null;
Statement stmt = null;
StringBuffer sbfQuery = null;
try
{
con = DBConnection.getConnect();
con.setAutoCommit(false);
stmt = con.createStatement();
sbfQuery = new StringBuffer();

sbfQuery.append("SELECT COUNT(*) FROM EMP ");
sbfQuery.append("WHERE emp_id = '"+objEmployee.getEmpId()+"'");
rs = stmt.executeQuery(sbfQuery.toString());
rs.next();
if(rs.getInt(1) > 0){
return false;
}

sbfQuery = new StringBuffer();
sbfQuery.append("INSERT INTO EMP(emp_id, name, design, salary)");
sbfQuery.append("VALUES('");
sbfQuery.append(objEmployee.getEmpId()+"','");
sbfQuery.append(objEmployee.getName()+"','");
sbfQuery.append(objEmployee.getDesign()+"','");
sbfQuery.append(objEmployee.getSalary()+"' )");

int iRowCount = stmt.executeUpdate(sbfQuery.toString());

if(iRowCount != 1){
return false;
}
con.commit();
}catch(Exception ex){
throw ex;
}finally{
DBConnection.closeResources(con,rs,stmt,null);
}
return true;
}



public ArrayList<Employee> getAllEmployees() throws Exception{
ArrayList<Employee> aEmployeeList = null;
Connection con = null;
ResultSet rs = null;
Statement stmt = null;
StringBuffer sbfQuery = null;
Employee objEmployee = null;
try
{
con = DBConnection.getConnect();
stmt = con.createStatement();
aEmployeeList = new ArrayList();
sbfQuery = new StringBuffer();
sbfQuery.append("SELECT * FROM EMP");
rs = stmt.executeQuery(sbfQuery.toString());

while(rs.next())
{
objEmployee = new Employee();
objEmployee.setEmpID(rs.getInt("emp_id"));
objEmployee.setName(rs.getString("name"));
objEmployee.setDesign(rs.getString("design"));
objEmployee.setSalary(rs.getInt("salary"));
aEmployeeList.add(objEmployee);
}

}catch(Exception ex){
throw ex;
}finally{
DBConnection.closeResources(con,rs,stmt,null);
}
return aEmployeeList;
}


//Method for deleting a selected employee
public boolean deleteEmployee(int iEmpID) throws Exception{
Connection con = null;
ResultSet rs = null;
Statement stmt = null;
StringBuffer sbfQuery = null;
try
{
con = DBConnection.getConnect();
stmt = con.createStatement();
sbfQuery = new StringBuffer();

con.setAutoCommit(false);

sbfQuery.append("DELETE FROM EMP WHERE emp_id = " + iEmpID);

int iRowCount = stmt.executeUpdate(sbfQuery.toString());

if(iRowCount != 1){
return false;
}
con.commit();
}catch(Exception ex){
throw ex;
}finally{
DBConnection.closeResources(con,rs,stmt,null);
}
return true;
}


}//end of class


-------------------------------------------------------------------------------------

Create ActionServlet


package com.ramsis;

import java.io.IOException;

import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ActionServlet extends HttpServlet
{
public void doGet(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{    
processsRequest(req,res);
}

public void doPost(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
processsRequest(req,res);
}

public void processsRequest(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{    
String action = null;
int iAction = 0;
ActionHelper objActionHelper= null;
Employee objEmployee = null;
ArrayList aEmployeeList = null;
String sForwardJSP = null;


try
{
action = req.getParameter("action");
objActionHelper =  new ActionHelper();
if(action != null && action.trim().length() > 0){
iAction = Integer.parseInt(action);
}

switch(iAction)
{

case 1 : // ADD_EMPLOYEE :
sForwardJSP = "/jsp/addEmployees.jsp";
break;

case 2 : // SAVE_EMPLOYEE :

objEmployee = new Employee();

objEmployee.setEmpID(Integer.parseInt(req.getParameter("emp_id").trim()));
objEmployee.setName(req.getParameter("name"));
objEmployee.setDesign(req.getParameter("design"));
objEmployee.setSalary(Integer.parseInt(req.getParameter("salary")));

if(!objActionHelper.addEmployee(objEmployee)){
req.setAttribute("Message","This Emp ID already exists.");
sForwardJSP = "/jsp/addEmployees.jsp";
}
else
{
req.setAttribute("Message","Employee is added successfully.");

aEmployeeList = objActionHelper.getAllEmployees();
req.setAttribute("EmployeeList",aEmployeeList);


sForwardJSP = "/jsp/displayEmployees.jsp";
}
break;

case 3 : // Delete an employee

String sEmpID = req.getParameter("empID");
int iEmpID = Integer.parseInt(sEmpID);

if(objActionHelper.deleteEmployee(iEmpID)){
req.setAttribute("Message","Employee is deleted successfully.");
}else{
req.setAttribute("Message","No employee matches search criteria");
}

case 4 : // get all employees

aEmployeeList = objActionHelper.getAllEmployees();
if(aEmployeeList != null && aEmployeeList.size() > 0){
req.setAttribute("EmployeeList",aEmployeeList);
}else{
req.setAttribute("Message","No employees in the system");
}
sForwardJSP = "/jsp/displayEmployees.jsp";
break;


}// end of switch

}
catch(Exception ex)
{
ex.printStackTrace();
req.setAttribute("Message",ex);
sForwardJSP = "/jsp/error.jsp";
}

// getServletConfig().getServletContext().getRequestDispatcher(sForwardJSP).forward(req,res);
RequestDispatcher dispatcher = req.getRequestDispatcher(sForwardJSP);
dispatcher.forward(req,res);


}// end of processRequest


}//end of class

-------------------------------------------------------------------------------------

Create DBConnection


package com.ramsis;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConnection
{
public static Connection getConnect() {

Connection con = null;

try {
   Class.forName("com.mysql.cj.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=admin123");

            InitialContext initialContext = new InitialContext();
            Context context = (Context) initialContext.lookup("java:comp/env");
            DataSource ds = (DataSource) context.lookup("test");
            con = ds.getConnection();
} catch(Exception e) {
e.printStackTrace();
}
return con;
}

public static void closeResources(Connection con,ResultSet rs, Statement stmt, PreparedStatement p_stmt) {
try {
if(rs != null){ rs.close(); }
if(p_stmt!=null){ p_stmt.close();}
if(stmt != null){ stmt.close(); }
if(con != null){ con.close(); }
} catch(Exception e) {
System.out.println("Exception in Finally block");
e.printStackTrace();
}
}
}

-------------------------------------------------------------------------------------

Create Employee

package com.ramsis;
public class Employee
{
private int empID = 0;
private String name = null;
private String design = null;
private int salary = 0;

public void setEmpID(int empID){
this.empID= empID;
}
public int getEmpId(){
return this.empID;
}


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

public void setDesign(String design){
this.design= design;
}
public String getDesign(){
return this.design;
}

public void setSalary(int salary){
this.salary= salary;
}
public int getSalary(){
return this.salary;
}

}//end of class

------------------------------------------------------------------------------------

Create addEmployees.jsp


<%
String sMessage = (String)request.getAttribute("Message");
%>


<html><head><title> Add Employee</title>
<link rel="stylesheet" href="style.css" media="screen" type="text/css">

<script>
function validate()
{
if (document.employeeform.emp_id.value=="")
{
alert("Please enter Emp ID"); return false;
}
return true;
}

function saveEmployee()
{
if(validate())
{
 document.employeeform.action.value = 2;
 document.employeeform.submit();
}
}

function viewAll(){
document.viewAllForm.action.value=4;
document.viewAllForm.submit();
}

</script>

</head>
<body>

<form name="viewAllForm" action="/CRUDOnJspServlet/ActionServlet" method="post">
<input type="hidden" name="action">
</form>


<form name="employeeform" action="/CRUDOnJspServlet/ActionServlet" method="post">
<input type="hidden" name="action">

<table border="0" align="center" cellspacing="2" cellpadding="2">

<tr><td colspan=2>
<table width="100%" border="0">
<tr><td align="left" height="40" width="30%">
<a href="javascript:viewAll()"><b>View All Emplyee</b></a>
</td></tr>
</table>
</td></tr>

<tr>
<td align="left" height="20">Emp Id</td>
<td><input type="text" name="emp_id"></td>
</tr>
<tr>
<td align="left" height="20">Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td align="left" height="20">Designation</td>
<td><input type="text" name="design"></td>
</tr>
<tr>
<td align="left" height="20">Salary</td>
<td><input type="text" name="salary"></td>
</tr>

<tr>
<td align="center" colspan="2">
<input type="button" value="Add Employee" onClick="javascript:saveEmployee();"> &nbsp;&nbsp;
<input type="reset" value="Reset">
</td>
</tr>

</table>

</form>

</body>
</html>

-------------------------------------------------------------------------------------

Create displayEmployees.jsp


<%@ page import="java.util.ArrayList,com.ramsis.Employee"%>
<html>
<head>
<title> Employee Listing</title>
<link rel="stylesheet" href="style.css" media="screen" type="text/css">
</head>
<%
String sMessage = (String)request.getAttribute("Message");
Employee objEmployee = null;
ArrayList aEmployeeList = (ArrayList)request.getAttribute("EmployeeList");
%>

<script>
function addEmployee(){
document.employeeform.action.value=1;
document.employeeform.submit();
}

function deleteEmployee(empID){
if(confirm("Do you want to delete this user ? ")){
document.employeeform.empID.value  = empID;
document.employeeform.action.value = 3;
document.employeeform.submit();
}
}
</script>


<body>

<form name="employeeform" action="/CRUDOnJspServlet/ActionServlet" method="post">
<input type="hidden" name="action">
<input type="hidden" name="empID">
</form>


<table border="0" align="center" cellspacing="2" cellpadding="2" width="50%">
<tr><td colspan="2" align="left" height="20">
<%if(sMessage != null){%>
<font color="red"><%=sMessage%></font>
<%}%>
</td></tr>

<tr><td align="left">
<table width="100%" border="0">
<tr><td align="left" height="40" width="30%">
<a href="javascript:addEmployee()"><b>Add New Emplyee</b></a>
</td></tr>
</table>
<td></tr>


<%
if(aEmployeeList != null && aEmployeeList.size() > 0){
%>

<tr><td>
<!-- Employee Listing -->
<table cellspacing="0" cellpadding="2" border="1" bgcolor="lightgrey" bordercolor="blue" width="100%">
<tr>
<td align="center"><b>Emp ID</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>Desigation</b></td>
<td align="center"><b>Salary</b></td>
<td align="center"><b>Delete</b></td>
</tr>
<%
for(int i = 0; i <aEmployeeList.size() ; i++){
objEmployee = (Employee)aEmployeeList.get(i);
%>

<tr>
<td align="center"><%=objEmployee.getEmpId()%></a></td>
<td><%=objEmployee.getName()%></td>
<td><%=objEmployee.getDesign()%></td>
<td><%=objEmployee.getSalary()%></td>
   <td align="center"><a href="javascript:deleteEmployee('<%=objEmployee.getEmpId()%>')"><p style="color:red;">Delete</p></a></td>
   
</tr>
<%
}//end of for loop
%>
</table><!- ends here -->
</td></tr>

<%
}// end of if condition
%>


</table>


</body>
</html>

-------------------------------------------------------------------------------------

create web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>CRUDOnJspServlet</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
 
  <servlet>
<servlet-name>ActionServlet</servlet-name>
<servlet-class>com.ramsis.ActionServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>ActionServlet</servlet-name>
<url-pattern>/ActionServlet</url-pattern>
</servlet-mapping>
 
</web-app>

-------------------------------------------------------------------------------------



No comments:

Post a Comment