How to perform CRUD operation using JSP, Servlet, and MYSQL. In this tutorial, we will see how to Read, Insert, Update, Delete Data in MySQL using JSP and Servlet.
What is CRUD Operation?
CRUD acronyms to Create, Read, Update, and Delete. When we are building an app we need our app to provide basic four functionality like create, read, update, and delete For any admin work, or for storing the information we use this operation. Let’s see standard CRUD operation.
- CREATE: SQL INSERT inserts to create a new record in the system.
- READ: SELECT statement of SQL to list all records from the database.
- UPDATE: UPDATE statement of SQL to EDIT the records.
- DELETE: In this operation, we use the DELETE statement of SQL to remove the record.
How to perform CRUD operation using JSP, SERVLET, and MYSQL
Below are the steps to create a simple Java application to perform the CRUD operations
- Create a Database and Table.
- Create a Dynamic or maven project in Java.
- Create a model(Dao classes)
- Design pages in Jsp or HTML.
- Servlet classes to handle the request and responses.
Let’s build one simple Java web application to manage Doctors.
We will be using the following things while building an application
- Java Servlet
- JSP
- MYSQL database
- JDBC
- Apache Tomcat Server, Eclipse IDE
Let’s create a project structure to follow the basic coding practice in Eclipse IDE
Create a table doctor_table in doctordb database
Create a database in MYSQL with the name “doctorDb” and table with the name as “doctor_table”
Create database doctordb; CREATE TABLE `doctordb`.`doctor_table` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `specialist` VARCHAR(45) NOT NULL, `email` VARCHAR(45) NOT NULL, `city` VARCHAR(45) NOT NULL, `country` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`));
Create a bean class Doctor.java
As we know bean classes are the classes that encapsulate many objects into a single object. so let’s create a class Doctor that contains all the properties of the doctor like ID, Name, Email Etc….
package org.doctor.bean;
public class Doctor {
private int id;
private String name;
private String specialist;
private String email;
private String city;
private String country;
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 getSpecialist() {
return specialist;
}
public void setSpecialist(String specialist) {
this.specialist = specialist;
}
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;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public Doctor(int id, String name, String specialist, String email, String city, String country) {
super();
this.id = id;
this.name = name;
this.specialist = specialist;
this.email = email;
this.city = city;
this.country = country;
}
public Doctor() {
super();
// TODO Auto-generated constructor stub
}
}
Create a database connection class to initialize the database
DBConnection.java is the database connection class of this application that contains the connection with MYSQL. Read more about Database connectivity in java using JDBC
package org.doctor.DBconnect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public static Connection getConn() {
String loadDriver="com.mysql.jdbc.Driver";
String dbURL="jdbc:mysql://localhost:3306/doctordb";
String dbUSERNAME="root";
String dbPASSWORD="root";
Connection con = null;
try {
Class.forName(loadDriver);
con = DriverManager.getConnection(dbURL,dbUSERNAME,dbPASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
}
Create a DAO(Data Access Object) class.
Dao classes are used to write business Logic for database operations such as insert, update, delete.
DoctorDao.java class contains methods to ADD doctors, Edit doctors, and Delete Doctors
package org.doctor.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.doctor.DBconnect.DBConnection;
import org.doctor.bean.Doctor;
public class DoctorDao {
public boolean addDoctor(Doctor doctor) {
// TODO Auto-generated method stub
Connection con = DBConnection.getConn();
String sql = "insert into doctor_table (id,name,specialist,email,city,country) values (NULL,?,?,?,?,?) ";
int i = 0;
try {
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setString(1, doctor.getName());
preparedStatement.setString(2, doctor.getSpecialist());
preparedStatement.setString(3, doctor.getEmail());
preparedStatement.setString(4, doctor.getCity());
preparedStatement.setString(5, doctor.getCountry());
i = preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (i == 0) {
return false;
} else {
return true;
}
}
public boolean editDoctor(Doctor doctor) {
Connection connection = DBConnection.getConn();
String sql = "update doctor_table set email=?, city=?, country=?";
sql += " where id=? ";
int i = 0;
try {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, doctor.getEmail());
preparedStatement.setString(2, doctor.getCity());
preparedStatement.setString(3, doctor.getCountry());
preparedStatement.setInt(4, doctor.getId());
i = preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (i == 0) {
return false;
} else {
return true;
}
}
public void deleteDoctor(int id) {
Connection connection = DBConnection.getConn();
String sql = "delete from doctor_table where id=?";
try {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Create an index.jsp page for the home page
index.jsp is a front end design of the application that will display add Doctor link and Table of List of Doctors with Edit and Delete option.
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="org.doctor.DBconnect.DBConnection"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<style>
<!--
a {
text-decoration: none;
}
-->
</style>
<body bgcolor="#00FFFF">
<form action="">
<h1 align="center">Doctor Management</h1>
<h2 align="center">
<a href="addDoctor.jsp">Add New Doctor</a><br>
</h2>
<!-- All DOctor shows-->
<h1 align="center">List of Doctors</h1>
<table border="1" align="center" cellpadding="5"
style="font-size: 200%; font-family: inherit; font-style: normal; background-color: window;">
<tr>
<th>ID</th>
<th>Name</th>
<th>Speciality</th>
<th>Email</th>
<th>City</th>
<th>Country</th>
<th>Actions</th>
</tr>
<%
Connection con = DBConnection.getConn();
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery("Select * from doctor_table");
while (resultSet.next()) {
%>
<tr>
<td>
<%
out.print(resultSet.getInt(1));
%>
</td>
<td>
<%
out.print(resultSet.getString(2));
%>
</td>
<td>
<%
out.print(resultSet.getString(3));
%>
</td>
<td>
<%
out.print(resultSet.getString(4));
%>
</td>
<td>
<%
out.print(resultSet.getString(5));
%>
</td>
<td>
<%
out.print(resultSet.getString(6));
%>
</td>
<form action="MyServlet" method="post">
<td><a value="Edit" name="Action"
href="edit.jsp?id=<%=resultSet.getInt(1)%> ">Edit</a> <input
type="hidden" name="j" value="<%= resultSet.getInt(1) %>">
<a href="delete.jsp">Delete</a> </td>
</tr>
</form>
<%
}
%>
</table>
</form>
</body>
</html>
addDoctor.jsp
This page will display the form for adding Doctors.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body bgcolor="#00FFFF">
<form action="MyServlet" method="post">
<h1 align="center">Add Doctor</h1>
<h1>
<a href="index.jsp">List Doctors</a>
</h1>
<table border="1" align="center" cellpadding="5"
style="font-size: 200%; font-family: inherit; font-style: normal; background-color: window;">
<tr>
<td>Name</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>Speciality</td>
<td><input type="text" name="speciality"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email"></td>
</tr>
<tr>
<td>City</td>
<td><input type="text" name="city"></td>
</tr>
<tr>
<td>Country</td>
<td><input type="text" name="country"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Action" value="Add Doctor"></td>
</tr>
</table>
</form>
</body>
</html>
edit.jsp
On clicking the edit link on the index.jsp page it will take us to this page to edit details of Doctors.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body bgcolor="#00FFFF">
<form action="MyServlet" method="post">
<h1 align="center">Edit Doctor</h1>
<h1>
<a href="index.jsp">List Doctors</a>
</h1>
<table border="1" align="center" cellpadding="5"
style="font-size: 200%; font-family: inherit; font-style: normal; background-color: window;">
<tr>
<td>Enter Doctor ID</td>
<td><input type="text" name="id" required></td>
</tr>
<tr>
<td>Enter Doctor Email</td>
<td><input type="text" name="email" required></td>
</tr>
<tr>
<td>Enter Doctor City</td>
<td><input type="text" name="city" required></td>
</tr>
<tr>
<td>Enter Doctor Country</td>
<td><input type="text" name="country" required></td>
</tr>
<tr>
<td></td>
<td align="center"><input type="submit" name="Action"
value="Edit"></td>
</tr>
</table>
</form>
</body>
</html>
delete.jsp
On clicking the delete link on the index.jsp page it will take us to this page to delete the entry.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body bgcolor="#00FFFF">
<h1>Delete Doctor</h1>
<h1>
<a href="index.jsp">List Doctors</a>
</h1>
<form action="MyServlet" method="post">
Enter ID<input type="text" name="id"> <input type="submit"
value="Delete" name="Action">
</form>
</body>
</html>
Create Servlet for Handling the request and response.
MyFilter.java
This class will be invoked at the preprocessing and postprocessing of a request.
package org.doctor.Controller;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;
/**
* Servlet Filter implementation class MyFilter
*/
public class MyFilter implements Filter {
/**
* Default constructor.
*/
public MyFilter() {
// TODO Auto-generated constructor stub
}
/**
* @see Filter#destroy()
*/
public void destroy() {
// TODO Auto-generated method stub
}
/**
* @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
*/
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException {
// TODO Auto-generated method stub
// place your code here
if (request.getParameter("Action").equals("Add Doctor"))
// pass the request along the filter chain
chain.doFilter(request, response);
if (request.getParameter("Action").equals("Edit"))
// pass the request along the filter chain
chain.doFilter(request, response);
if (request.getParameter("Action").equals("Delete"))
// pass the request along the filter chain
chain.doFilter(request, response);
}
/**
* @see Filter#init(FilterConfig)
*/
public void init(FilterConfig fConfig) throws ServletException {
// TODO Auto-generated method stub
}
}
MyServlet.java
This class will handle all the request and response object. We will take the input name parameter from the JSP page and compare it to the button click value and will perform the corresponding operation. To understand What is Servlet you can check it here Introduction with Servlet in Java.
package org.doctor.Controller;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.doctor.Dao.DoctorDao;
import org.doctor.bean.Doctor;
/**
* Servlet implementation class MyServlet
*/
public class MyServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public MyServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
if (request.getParameter("Action").equals("Add Doctor")) {
System.out.println("in");
PrintWriter printWriter = response.getWriter();
Doctor doctor = new Doctor();
DoctorDao dao = new DoctorDao();
doctor.setName((request.getParameter("name")));
doctor.setSpecialist((request.getParameter("speciality")));
doctor.setEmail((request.getParameter("email")));
doctor.setCity((request.getParameter("city")));
doctor.setCountry((request.getParameter("country")));
boolean result = dao.addDoctor(doctor);
RequestDispatcher dispatcher = request.getRequestDispatcher("addDoctor.jsp");
dispatcher.include(request, response);
printWriter.print("<br><h2>Doctor added Successfully!!</h2>");
}
if (request.getParameter("Action").equals("Edit")) {
PrintWriter printWriter = response.getWriter();
Doctor doctor = new Doctor();
DoctorDao dao = new DoctorDao();
doctor.setId(Integer.parseInt(request.getParameter("id")));
doctor.setEmail(request.getParameter("email"));
doctor.setCity(request.getParameter("city"));
doctor.setCountry(request.getParameter("country"));
boolean result = dao.editDoctor(doctor);
System.out.println(result);
RequestDispatcher dispatcher = request.getRequestDispatcher("edit.jsp");
dispatcher.include(request, response);
printWriter.print("<br><h2>Doctor Edited Successfully!!</h2>");
}
if (request.getParameter("Action").equals("Delete")) {
PrintWriter printWriter = response.getWriter();
Doctor doctor = new Doctor();
DoctorDao dao = new DoctorDao();
dao.deleteDoctor(Integer.parseInt(request.getParameter("id")));
RequestDispatcher dispatcher = request.getRequestDispatcher("delete.jsp");
dispatcher.include(request, response);
printWriter.print("<br><h2>Doctor Deleted Successfully!!</h2>");
}
}
}
web.xml
It is a deployment descriptor file to determine how servlet and filter are mapped.
<?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_3_0.xsd"
id="WebApp_ID" version="3.0">
<display-name>DoctorApplication</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>MyServlet</servlet-name>
<servlet-class>org.doctor.Controller.MyServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>MyServlet</servlet-name>
<url-pattern>/MyServlet</url-pattern>
</servlet-mapping>
<filter>
<filter-name>MyFilter</filter-name>
<filter-class>org.doctor.Controller.MyFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>MyFilter</filter-name>
<url-pattern>/MyFilter</url-pattern>
</filter-mapping>
</web-app>
Let’s run this project on the Server and see the following output
