Hello guys, Bushan here, welcome to B2 Tech. In this article, we will be Creating fully fledged MVC database web application using JSP and Servlets, we are going to perform basic database operations Create, Read, Update and Delete.
So, we are creating an Employee Directory Web Application which keeps track of each employee details such as Name, Date of birth, Department and it should have the following features
- List Employee
- Add a new Employee
- Update an Employee
- Delete an Employee
The below big picture is the architecture of the Employee Directory Web Application
Step 1: Browser sends a request to EmployeeController, it will process that request, if any additional data is required, it will redirect it to EmployeeDAO
Step 2: EmployeeDAO, its responsibility is to interact with a database like querying to the database, adding a records etc.., It takes the database connection from a helper class DBConnectionUtil and talk to the database for additional data, and send the data back to EmployeeController
Step 3: EmployeeController will write that data to a respective JSP
Step 4: JSP will organize the data and send the JSP back to the Browser
So now let’s jump into developing an application, before that a quick look at the project structure
01-create-table.sql
create database employeedirectory; use employeedirectory; create table tbl_employee( name varchar(255) not null, dob varchar(255) not null, department varchar(255) not null );
02-employee-directory-dump.sql
create database employeedirectory; use employeedirectory; create table tbl_employee( name varchar(255) not null, dob varchar(255) not null, department varchar(255) not null ); insert into tbl_employee(name, dob, department)values("Jhon", "12-12-1991", "Marketing"); insert into tbl_employee(name, dob, department)values("Sara", "21-05-1992", "Testing"); insert into tbl_employee(name, dob, department)values("Paul", "23-04-1988", "Development"); insert into tbl_employee(name, dob, department)values("David", "18-03-1989", "Support"); select * from tbl_employee;
DBConnectionUtil.java
package in.bushansirgur.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBConnectionUtil { private static final String URL = "jdbc:mysql://localhost:3306/employeedirectory"; private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String USERNAME = "root"; private static final String PASSWORD = ""; private static Connection connection = null; public static Connection openConnection() { if (connection != null) return connection; else { try { Class.forName(DRIVER); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return connection; } } }
Employee.java
package in.bushansirgur.model; import java.sql.Date; public class Employee { private Integer id; private String name; private String department; private String dob; public Integer getId() { return id; } public void setId(Integer 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; } public String getDob() { return dob; } public void setDob(String dob) { this.dob = dob; } @Override public String toString() { return "Employee [id=" + id + ", name=" + name + ", department=" + department + ", dob=" + dob + ", getId()=" + getId() + ", getName()=" + getName() + ", getDepartment()=" + getDepartment() + ", getDob()=" + getDob() + ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + ", toString()=" + super.toString() + "]"; } }
EmployeeDAO.java
package in.bushansirgur.dao; import java.util.List; import in.bushansirgur.model.Employee; public interface EmployeeDAO { List<Employee> get(); Employee get(int id); boolean save(Employee employee); boolean delete(int id); boolean update(Employee employee); }
EmployeeDAOImpl.java
package in.bushansirgur.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import in.bushansirgur.model.Employee; import in.bushansirgur.util.DBConnectionUtil; public class EmployeeDAOImpl implements EmployeeDAO { Connection connection = null; ResultSet resultSet = null; Statement statement = null; PreparedStatement preparedStatement = null; @Override public List<Employee> get() { List<Employee> list = null; Employee employee = null; try { list = new ArrayList<Employee>(); String sql = "SELECT * FROM tbl_employee"; connection = DBConnectionUtil.openConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); while(resultSet.next()) { employee = new Employee(); employee.setId(resultSet.getInt("id")); employee.setName(resultSet.getString("name")); employee.setDepartment(resultSet.getString("department")); employee.setDob(resultSet.getString("dob")); list.add(employee); } }catch(SQLException e) { e.printStackTrace(); } return list; } @Override public Employee get(int id) { Employee employee = null; try { employee = new Employee(); String sql = "SELECT * FROM tbl_employee where id="+id; connection = DBConnectionUtil.openConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); if(resultSet.next()) { employee.setId(resultSet.getInt("id")); employee.setName(resultSet.getString("name")); employee.setDepartment(resultSet.getString("department")); employee.setDob(resultSet.getString("dob")); } }catch(SQLException e) { e.printStackTrace(); } return employee; } @Override public boolean save(Employee e) { boolean flag = false; try { String sql = "INSERT INTO tbl_employee(name, department, dob)VALUES" + "('"+e.getName()+"', '"+e.getDepartment()+"', '"+e.getDob()+"')"; connection = DBConnectionUtil.openConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); flag = true; }catch(SQLException ex) { ex.printStackTrace(); } return flag; } @Override public boolean delete(int id) { boolean flag = false; try { String sql = "DELETE FROM tbl_employee where id="+id; connection = DBConnectionUtil.openConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); flag = true; }catch(SQLException e) { e.printStackTrace(); } return flag; } @Override public boolean update(Employee employee) { boolean flag = false; try { String sql = "UPDATE tbl_employee SET name = '"+employee.getName()+"', " + "department = '"+employee.getDepartment()+"', dob = '"+employee.getDob()+"' where id="+employee.getId(); connection = DBConnectionUtil.openConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); flag = true; }catch(SQLException e) { e.printStackTrace(); } return flag; } }
EmployeeController.java
package in.bushansirgur.controller; import java.io.IOException; import java.util.List; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import in.bushansirgur.dao.EmployeeDAO; import in.bushansirgur.dao.EmployeeDAOImpl; import in.bushansirgur.model.Employee; public class EmployeeController extends HttpServlet { private static final long serialVersionUID = 1L; RequestDispatcher dispatcher = null; EmployeeDAO employeeDAO = null; public EmployeeController() { employeeDAO = new EmployeeDAOImpl(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String action = request.getParameter("action"); if(action == null) { action = "LIST"; } switch(action) { case "LIST": listEmployee(request, response); break; case "EDIT": getSingleEmployee(request, response); break; case "DELETE": deleteEmployee(request, response); break; default: listEmployee(request, response); break; } } private void deleteEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id = request.getParameter("id"); if(employeeDAO.delete(Integer.parseInt(id))) { request.setAttribute("NOTIFICATION", "Employee Deleted Successfully!"); } listEmployee(request, response); } private void getSingleEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{ String id = request.getParameter("id"); Employee theEmployee = employeeDAO.get(Integer.parseInt(id)); request.setAttribute("employee", theEmployee); dispatcher = request.getRequestDispatcher("/views/employee-form.jsp"); dispatcher.forward(request, response); } private void listEmployee(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List<Employee> theList = employeeDAO.get(); request.setAttribute("list", theList); dispatcher = request.getRequestDispatcher("/views/employee-list.jsp"); dispatcher.forward(request, response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String id = request.getParameter("id"); Employee e = new Employee(); e.setName(request.getParameter("name")); e.setDepartment(request.getParameter("department")); e.setDob(request.getParameter("dob")); if(id.isEmpty() || id == null) { if(employeeDAO.save(e)) { request.setAttribute("NOTIFICATION", "Employee Saved Successfully!"); } }else { e.setId(Integer.parseInt(id)); if(employeeDAO.update(e)) { request.setAttribute("NOTIFICATION", "Employee Updated Successfully!"); } } listEmployee(request, response); } }
employee-list.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Employee Directory</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> </head> <body> <div class = "container"> <h1>Employee Directory</h1> <hr/> <p>${NOTIFICATION}</p> <p> <button class = "btn btn-primary" onclick="window.location.href = 'views/employee-form.jsp'">Add Employee</button> </p> <table class = "table table-striped table-bordered"> <tr class = "thead-dark"> <th>Name</th> <th>Department</th> <th>Date of birth</th> <th>Actions</th> </tr> <c:forEach items="${list}" var="employee"> <tr> <td>${employee.name}</td> <td>${employee.department}</td> <td>${employee.dob}</td> <td> <a href = "${pageContext.request.contextPath}/EmployeeController?action=EDIT&id=${employee.id}">Edit</a> | <a href = "${pageContext.request.contextPath}/EmployeeController?action=DELETE&id=${employee.id}">Delete</a> </td> </tr> </c:forEach> </table> </div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> </body> </html>
employee-form.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> </head> <body> <div class = "container"> <h1>Employee Directory</h1> <hr/> <div class = "row"> <div class = "col-md-4"> <form action = "${pageContext.request.contextPath}/EmployeeController" method="POST"> <div class = "form-group"> <input type = "text" class = "form-control" name = "name" placeholder = "Enter Name" value = "${employee.name}"/> </div> <div class = "form-group"> <input type = "date" class = "form-control" name = "dob" value = "${employee.dob}"/> </div> <div class="form-group"> <input type = "text" class = "form-control" name = "department" placeholder = "Enter Department" value = "${employee.department}"/> </div> <input type = "hidden" name = "id" value = "${employee.id}"/> <button type = "submit" class = "btn btn-primary">Save</button> </form> </div> </div> <a href = "${pageContext.request.contextPath}/EmployeeController?action=LIST">Back to List</a> </div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> </body>
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>01-jsp-servlets-mvc-database-application</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> </welcome-file-list> <servlet> <description></description> <display-name>EmployeeController</display-name> <servlet-name>EmployeeController</servlet-name> <servlet-class>in.bushansirgur.controller.EmployeeController</servlet-class> </servlet> <servlet-mapping> <servlet-name>EmployeeController</servlet-name> <url-pattern>/EmployeeController</url-pattern> </servlet-mapping> </web-app>
Alright, that’s it for this post, i hope this post is helped you in someway or the other way, if it did then please share this with your friends, colleague and someone who is looking for this. You can also find the fully working project in my github repository @
Thanks and Regards,
Team B2 Tech
First of all thanks for the tutorial. It was exactly what I needed and very helpful. But I have a problem. in ’employee-list.jsp’, you used ‘ ‘ command line. I did not understood how that works because I can’t get anything from my list. Where did you connected EmployeeController and employee-list.jsp
“” this command line but It didn’t show for some reason sorry
c:forEach items=”${list}” var=”employee” this command line but It didn’t show up for some reason sorry.
Hello Gulhan,
Please check in the controller whether you have added the list to the request object or not. If you added then it should work. Please cross check and let me know. 🙂
Thanks and Regards,
Team B2 Tech
First of all thanks for the answer. I have and setted the attribute on Controller. But when I called from jsp, It returns a null list. So I tried to setattribute on jsp before I use ” c:forEach items=”${list}” var=”employee”” command and It worked. But I don’t want to set on jsp, I want to set it on controller. What do you think the problem could be ?.
Hey I talked with my boss and I found out the problem. He said you are expecting an action on Controller but in jsp you don’t send an action. So he created a form and passed the action to Controller. And problem solved 🙂
Cool.
Happy Coding! 🙂
I don’t know. Can you share the controller code ?
Hello Gulhan,
Thanks for reaching out. You will see the mapping in the web.xml 🙂
What is the use of serialVersionUID?
The docs for java.io.Serializable are probably about as good an explanation as you’ll get:
The serialization runtime associates with each serializable class a version number, called a serialVersionUID, which is used during deserialization to verify that the sender and receiver of a serialized object have loaded classes for that object that are compatible with respect to serialization. If the receiver has loaded a class for the object that has a different serialVersionUID than that of the corresponding sender’s class, then deserialization will result in an InvalidClassException. A serializable class can declare its own serialVersionUID explicitly by declaring a field named serialVersionUID that must be static, final, and of type long:
ANY-ACCESS-MODIFIER static final long serialVersionUID = 42L;
If a serializable class does not explicitly declare a serialVersionUID, then the serialization runtime will calculate a default serialVersionUID value for that class based on various aspects of the class, as described in the Java(TM) Object Serialization Specification. However, it is strongly recommended that all serializable classes explicitly declare serialVersionUID values, since the default serialVersionUID computation is highly sensitive to class details that may vary depending on compiler implementations, and can thus result in unexpected InvalidClassExceptions during deserialization. Therefore, to guarantee a consistent serialVersionUID value across different java compiler implementations, a serializable class must declare an explicit serialVersionUID value. It is also strongly advised that explicit serialVersionUID declarations use the private modifier where possible, since such declarations apply only to the immediately declaring class serialVersionUID fields are not useful as inherited members.
stupid tutorial no explaination
Hello Samuel,
Here is the youtube videos i explained everything in detail, please watch the videos,
JSP and Servlets for Beginners
Please explain me why use interface even without interface its work.please explain me
No one can execute this code without considering id value in database which is required as reference in each controller executio
Hi How Id is creating …..I did not get it ..Bcz we are not inserting id values
we made id as primary key in database.. it will automatically increment..
bro bro please help yaar what should i write in email and password in when index.jsp files run