Developing jsp application with jdbcand session management

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.

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 "; 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 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.

         Insert title here    Doctor Management
 Add New Doctor List of Doctors
  %> ID  Name  Speciality  Email  City  Country  Actions  
          ">Edit ">    Delete

addDoctor.jsp

This page will display the form for adding Doctors.

     Insert title here   Add Doctor
 List Doctors 
  Name Speciality Email City Country

edit.jsp

On clicking the edit link on the index.jsp page it will take us to this page to edit details of Doctors.

     Insert title here   Edit Doctor
 List Doctors 
  Enter Doctor ID Enter Doctor Email Enter Doctor City Enter Doctor Country

delete.jsp

On clicking the delete link on the index.jsp page it will take us to this page to delete the entry.

     Insert title here  Delete Doctor
 List Doctors 
 Enter ID

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("
Doctor added Successfully!!
"); > 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("
Doctor Edited Successfully!!
"); > 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("
Doctor Deleted Successfully!!
"); > > >

web.xml

It is a deployment descriptor file to determine how servlet and filter are mapped.

  DoctorApplication index.html  MyServlet org.doctor.Controller.MyServlet  MyServlet /MyServlet  MyFilter org.doctor.Controller.MyFilter  MyFilter /MyFilter

Let’s run this project on the Server and see the following output