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>

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

Download Code

How to use @Qualifier and @Primary Annotations in Spring

@Qualifier Annotation

Due to spring development there may be a situation when you create more than one bean of the same type and want to wire only one of them with a property. In such cases, you can use the @Qualifier annotation along with @Autowired to remove the confusion by specifying which exact bean will be wired.

@Primary Annotation

There may be a situation when you create more than one bean of the same type and want to wire only one of them with a property , and not want to use @Qualifier Annotation then you can Annotate any one bean as @Primary .
This annotation can be used on any class directly or indirectly annotated with the @Component annotation or on methods annotated with the @Bean annotation.

Project Code Structure

Create pom.xml file 

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.ramsis</groupId>

  <artifactId>springQualifierDemo</artifactId>

  <version>0.0.1-SNAPSHOT</version>

<properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <springframework.version>5.1.5.RELEASE</springframework.version>

    

    <maven.compiler.source>1.8</maven.compiler.source>

    <maven.compiler.target>1.8</maven.compiler.target>

  </properties>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>3.8.1</version>

      <scope>test</scope>

    </dependency>

        <dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-core</artifactId>

<version>${springframework.version}</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->

<dependency>

    <groupId>org.springframework</groupId>

    <artifactId>spring-context</artifactId>

    <version>${springframework.version}</version>

</dependency>

  </dependencies>

</project>

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

Create CompanyInfo Class

package com.ramsis.bean;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.stereotype.Component;

@Component

public class CompanyInfo {

@Autowired

@Qualifier("hyundai")

CompanyType companytype;

public void getInfo() {

companytype.getCompanyType();

}

}

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

Create CompanyType Class

package com.ramsis.bean;

public interface CompanyType {

public void getCompanyType();

}

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

Create Hyundai Class

package com.ramsis.bean;

import org.springframework.stereotype.Component;

@Component

public class Hyundai implements CompanyType {

@Override

public void getCompanyType() {

        System.out.println("Hyundai making Automobiles.");

}

}

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

Create Nokia Class

package com.ramsis.bean;

import org.springframework.stereotype.Component;

@Component

public class Nokia implements CompanyType {

@Override

public void getCompanyType() {

  System.out.println("Nokia making Mobile Phones.");

}

}

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

Create Reymond Class

package com.ramsis.bean;

import org.springframework.context.annotation.Primary;

import org.springframework.stereotype.Component;

@Component

@Primary

public class Reymond implements CompanyType {

@Override

public void getCompanyType() {

        System.out.println("Reymonds making fabrics and fashion products.");

}

}

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

Create Runner Class

package com.ramsis.Main;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.ramsis.bean.CompanyInfo;

import com.ramsis.config.Config;

public class Runner {

public static void main(String args[]) {

AnnotationConfigApplicationContext  context = new      AnnotationConfigApplicationContext(Config.class);

CompanyInfo info =context.getBean("companyInfo",CompanyInfo.class);

info.getInfo();

context.close();

}

}

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

Create Config Class

package com.ramsis.config;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

@Configuration
@ComponentScan(basePackages="com.ramsis.bean")
public class Config {

}

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

After run this project you will get the output as below mention image

Download Code

Spring Core Project Configuration without xml bean, using Eclipse Maven

Project Code Structure

Following screenshot shows the structure of the project we will create.

Create pom.xml file

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.ramsis</groupId>

  <artifactId>springConfig</artifactId>

  <version>0.0.1-SNAPSHOT</version>

<properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <springframework.version>5.1.5.RELEASE</springframework.version>

    

    <maven.compiler.source>1.8</maven.compiler.source>

    <maven.compiler.target>1.8</maven.compiler.target>

  </properties>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>3.8.1</version>

      <scope>test</scope>

    </dependency>

        <dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-core</artifactId>

<version>${springframework.version}</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->

<dependency>

    <groupId>org.springframework</groupId>

    <artifactId>spring-context</artifactId>

    <version>${springframework.version}</version>

</dependency>

  </dependencies>

</project>

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

Create Config Class

package com.ramsis.config;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

@Configuration
@ComponentScan(basePackages="com.ramsis.bean")
public class Config {
}

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

Create Employee Class

package com.ramsis.bean;

import org.springframework.stereotype.Component;

@Component

public class Employee {

int id;

String name;

String department;

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 getDepartment() {

return department;

}

public void setDepartment(String department) {

this.department = department;

}

}

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

Create Runner Class

package com.ramsis.Main;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.ramsis.bean.Employee;
import com.ramsis.config.Config;

public class Runner {
public static void main(String args[]) {

AnnotationConfigApplicationContext  context = new AnnotationConfigApplicationContext(Config.class);
Employee employee =context.getBean("employee",Employee.class);

employee.setName("Aman");
employee.setDepartment("IT Prod");

System.out.println("name: "+employee.getName());
System.out.println("Department: "+employee.getDepartment());

context.close();
}
}

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

After run this project you will get the output as below mention image

Dowlnload Code