Open In App

Servlet – CRUD

Improve
Improve
Like Article
Like
Save
Share
Report

CRUD means Create, Read, Update and Delete. These are the basic important operations carried out on the Database and in applications. We can able to manipulate the tables of any database using CRUD operations. Here in this article, let us take MYSQL for it. Table creation in MySQL

-- Let us keep the db name as geeksforgeeks
-- Table name geekusers
CREATE TABLE `geeksforgeeks`.`geekusers` (
  `geekUserId` INT NOT NULL AUTO_INCREMENT,
  `geekUserName` VARCHAR(25) NULL,
  `numberOfPosts` INT NULL,
  `technologiesPreferred` VARCHAR(45) NULL,
  PRIMARY KEY (`geekUserId`));

Let us have an index.html file where the flow starts. An HTML file can come up with CSS for beautification and javascript for validation. 

HTML




<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Addition of GeekUsers</title>
   
<!-- Section used as css styling for table --> 
<style>
    .css-styled-table {
        border-collapse: collapse;
        margin: 25px 0;
        font-size: 0.9em;
        font-family: sans-serif;
        min-width: 400px;
        box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
    }
    .css-styled-table thead tr {
        background-color: #009879;
        color: #ffffff;
        text-align: left;
    }
    .css-styled-table th,
    .css-styled-table td {
        padding: 12px 15px;
    }
    .css-styled-table tbody tr {
        border-bottom: 1px solid #dddddd;
    }
 
    .css-styled-table tbody tr:nth-of-type(even) {
        background-color: #f3f3f3;
    }
 
    .css-styled-table tbody tr:last-of-type {
        border-bottom: 2px solid #009879;
    }
    .css-styled-table tbody tr.active-row {
        font-weight: bold;
        color: #009879;
    }
</style>
   
<!-- Section used as css styling for table -->
</head>
<body>
 
<h1>Add New GeekUser</h1>
   
<!-- SaveServlet is the servlet name that is looked up
     and POST is the method that got called --> 
<form action="SaveServlet" method="post">
<table class="css-styled-table">
  <tr><td>Name:</td><td><input type="text" name="name"/></td></tr>
  <tr><td>Number of Posts:</td><td><input type="text" name="numberOfPosts"/></td></tr>
  <tr><td>Technologies Preferred:</td><td><input type="text" name="technologiesPreferred"/></td></tr>
  <tr><td colspan="2"><input type="submit" value="Save GeekUser"/></td></tr>
</table>
</form>
 
<br/>
<a href="ViewServlet">view GeekUsers</a>
 
</body>
</html>


On running the HTML file, we can see the output as

On invoking index.html

Here let us give the inputs as follows

Input data

With on click of “Save GeekUsers“, we can see the below output. It indicates the record is saved successfully and also there is a provision to view the records also by means of “view GeekUsers”

We can able to see a record got inserted in MySQL also

MySQL data

Let us see the backend code for it. First, let us see about the “Model” Java file. Here it is GeekUsers.java. It will contain some attributes similar to the columns of the MySQL table named “geekusers”. Its corresponding setter and getter methods are defined which are required throughout the program.

Java




// Model file equivalent
// to MySQL table GeekUsers
public class GeekUsers {
    private int geekUserId;
    private int numberOfPosts;
    private String geekUserName, technologiesPreferred;
 
    public int getId() { return geekUserId; }
 
    public void setId(int id) { this.geekUserId = id; }
 
    public String getName() { return geekUserName; }
 
    public void setName(String name)
    {
        this.geekUserName = name;
    }
 
    public int getNumberOfPosts() { return numberOfPosts; }
 
    public void setNumberOfPosts(int numberOfPosts)
    {
        this.numberOfPosts = numberOfPosts;
    }
 
    public String gettechnologiesPreferred()
    {
        return technologiesPreferred;
    }
 
    public void
    setTechnologiesPreferred(String technologiesPreferred)
    {
        this.technologiesPreferred = technologiesPreferred;
    }
}


A DAO class (Data Access Object) is required which can help to get the JDBC connection of MySQL and also all the methods that are required to perform CRUD operations are defined here. Below is the code snippet required for getting the connection. As we are using JDBC, we should have a URL of type

jdbcURL = "jdbc:mysql://localhost:3306/<name of the database>";
// Let us use "geeksforgeeks" for it
jdbcUsername = "root"; // username generally "root"
jdbcPassword = "xxxxx";  // password that is used to connect to mysql

Java code snippet for getting the connection

Java




import java.util.*;
import java.sql.*; 
public class GeekUsersDao {
   
    // Here geeksforgeeks is the name of the database
    private static String jdbcURL = "jdbc:mysql://localhost:3306/geeksforgeeks";
    private static String jdbcUsername = "root";
   
    // provide your appropriate password  here
    private static String jdbcPassword = "*****";
 
    public static Connection getConnection(){
        Connection con=null;       
        try {
             // This is the way of connecting MySQL
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
        } catch (SQLException e) {
            System.out.println("Message.. " + e.getMessage());
            e.printStackTrace();           
        } catch (ClassNotFoundException e) {
            System.out.println("Message.. " + e.getMessage());
            e.printStackTrace();
        }       
        return con;
    }
    // Rest of the methods like Save,
    // Update, Delete etc., should come here
}


Let us see the servlet code now. From index.html, the input values are sent to “/SaveServlet”.  ‘SaveServlet’ is the servlet name that is looked up and ‘POST’ is the method that got called 

SaveGeekUserServlet.java

Java




import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/SaveServlet")
public class SaveGeekUserServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
 
        // Getting all the request parameters from
        // index.html
        String name = request.getParameter("name");
        int numberOfPosts = Integer.parseInt(
            request.getParameter("numberOfPosts"));
        String technologiesPreferred
            = request.getParameter("technologiesPreferred");
 
        // GeekUsers object is created
        GeekUsers geekUser = new GeekUsers();
 
        // Collected parameters like name, numberOfPosts and
        // technologiesPreferred are set for the object so
        // that it can be retrieved in other places
        geekUser.setName(name);
        geekUser.setNumberOfPosts(numberOfPosts);
        geekUser.setTechnologiesPreferred(
            technologiesPreferred);
 
        // Calling save method in GeekUsersDao by passing
        // geekUser
        int status = GeekUsersDao.save(geekUser);
 
        // This is possible when the record is saved
        // successfully
        if (status > 0) {
            out.print("
 
 
 
<p>Record saved successfully!</p>
 
 
 
");
            request.getRequestDispatcher("index.html")
                .include(request, response);
        }
        else {
            // If there is an issue in saving the record, we
            // need to show this message
            out.println("Sorry! unable to save record");
        }
 
        out.close();
    }
}


Java method to save the record to the table is given below

Using PreparedStatement, we can execute any SQL statement. In order to save the record, we need to insert the details in ‘geekusers’ table

Java




public static int save(GeekUsers e)
{
    int status = 0;
    try {
        Connection con = GeekUsersDao.getConnection();
        // Using PreparedStatement, we can execute any SQL
        // statement In order to save the record, we need to
        // insert the details in 'geekusers' table "e" is an
        // object of type "GeekUsers" which exactly resembles
        // geekusers table From html page, the values has
        // been passed to a servlet and from there, this
        // method is called
        PreparedStatement ps = con.prepareStatement(
            "insert into geekusers(geekUserName,numberOfPosts,technologiesPreferred) values (?,?,?)");
        ps.setString(1, e.getName());
        ps.setInt(2, e.getNumberOfPosts());
        ps.setString(3, e.gettechnologiesPreferred());
 
        status = ps.executeUpdate();
 
        con.close();
    }
    catch (Exception ex) {
        System.out.println("Message.." + ex.getMessage());
        ex.printStackTrace();
    }
 
    return status;
}


In order to view the saved record, we can use the “View GeekUsers” link

Relevant code for View Servlet

Java




import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/ViewServlet")
public class ViewGeekUserServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter printWriter=response.getWriter();
        printWriter.println("<a href='index.html'>Add New GeekUser</a>");
        printWriter.println("<h1>GeekUsers List</h1>");
         
        List<GeekUsers> list=GeekUsersDao.getAllGeekUses();
         
        printWriter.print("<table border='1' bordercolor='#009879' width='50%'");
        printWriter.print("<tr><th>Id</th><th>Name</th><th>Number Of Post</th><th>Preferred Technology</th><th>Edit</th><th>Delete</th></tr>");
        for(GeekUsers e:list){
            // each row is identified by means of its id
            // hence on click of 'edit', it is sent as
            // <a href='EditServlet?id="+e.getId()+"'>edit</a>
            // on click of 'delete', it is sent as
            // <a href='DeleteServlet?id="+e.getId()+"'>delete</a>
            printWriter.print("<tr><td >"+e.getId()+"</td><td>"+e.getName()+"</td><td>"+e.getNumberOfPosts()+"</td><td>"+e.gettechnologiesPreferred()+"</td><td><a href='EditServlet?id="+e.getId()+"'>edit</a></td><td><a href='DeleteServlet?id="+e.getId()+"'>delete</a></td></tr>");
        }
        printWriter.print("</table>");
         
        printWriter.close();
    }
}


For getting all the geekusers, “getAllGeekUses” method is used from “GeekUsersDao”

PreparedStatement ps=con.prepareStatement(“select * from geekusers”);

ResultSet rs=ps.executeQuery();

// and then the resultset is 

// iterated as shown in the below code

Java




public static List<GeekUsers> getAllGeekUses()
{
    List<GeekUsers> list = new ArrayList<GeekUsers>();
 
    try {
        Connection con = GeekUsersDao.getConnection();
        PreparedStatement ps = con.prepareStatement(
            "select * from geekusers");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            GeekUsers e = new GeekUsers();
            e.setId(rs.getInt(1));
            e.setName(rs.getString(2));
            e.setNumberOfPosts(rs.getInt(3));
            e.setTechnologiesPreferred(rs.getString(4));
            list.add(e);
        }
        con.close();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
 
    return list;
}


We have the option to edit the data as well as delete the data. Let  us see them 

edit data

Corresponding Servlet code on “edit” is “EditGeekUserServlet.java”

Java




import java.io.IOException;
import java.io.PrintWriter;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/EditServlet")
public class EditGeekUserServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
        out.println("<h1>Update GeekUser</h1>");
        String sid=request.getParameter("id");
        int id=Integer.parseInt(sid);
       
        // We need to get the id from view servlet
        // request and it has to be parsed with
        // Integer.parseInt in order to get as number
        // Then specific user is retrieved by means
        // of GeekUsersDao.getGeekUsersById(id)
        GeekUsers e=GeekUsersDao.getGeekUsersById(id);
         
        out.print("<form action='EditServlet2' method='post'>");
        out.print("<table>");
        out.print("<tr><td></td><td><input type='hidden' name='id' value='"+e.getId()+"'/></td></tr>");
        out.print("<tr><td>Name:</td><td><input type='text' name='name' value='"+e.getName()+"'/></td></tr>");
        out.print("<tr><td>Number Of Posts:</td><td><input type='text' name='numberOfPosts' value='"+e.getNumberOfPosts()+"'/></td></tr>");
        out.print("<tr><td>Technologies Preferred:</td><td><input type='text' name='technologiesPreferred' value='"+e.gettechnologiesPreferred()+"'/></td></tr>");
         
        out.print("<tr><td colspan='2'><input type='submit' value='Edit & Save '/></td></tr>");
        out.print("</table>");
        out.print("</form>");
         
        out.close();
    }
}


Let us see GeekUsersDao.getGeekUsersById()

Java




public static GeekUsers getGeekUsersById(int id)
{
    GeekUsers e = new GeekUsers();
 
    try {
        Connection con = GeekUsersDao.getConnection();
        // We are getting the details for a specific user
        // and hence the query has to be sent in the below
        // way
        PreparedStatement ps = con.prepareStatement(
            "select * from geekusers where geekUserId=?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            e.setId(rs.getInt(1));
            e.setName(rs.getString(2));
            e.setNumberOfPosts(rs.getInt(3));
            e.setTechnologiesPreferred(rs.getString(4));
        }
        con.close();
    }
    catch (Exception ex) {
        ex.printStackTrace();
    }
 
    return e;
}


Now, if we update (change) the data, corresponding details will be updated and reflected in the screen as well as in MySQL

Data has been updated for geekUserId = 1

Data has been updated for geekUserId = 1

Let us query the MySQL part also

Relevant Servlet code to do the above operation is “EditGeekUserServlet2.java

Java




import java.io.IOException;
import java.io.PrintWriter;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/EditServlet2")
public class EditGeekUserServlet2 extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
         
        String sid=request.getParameter("id");
        int id=Integer.parseInt(sid);
        String name=request.getParameter("name");
        int numberOfPosts=Integer.parseInt(request.getParameter("numberOfPosts"));
        String technologiesPreferred=request.getParameter("technologiesPreferred");   
         
        GeekUsers geekUser=new GeekUsers();
        geekUser.setId(id);
        geekUser.setName(name);
        geekUser.setNumberOfPosts(numberOfPosts);
        geekUser.setTechnologiesPreferred(technologiesPreferred);
         
        // GeekUsersDao.update method is called
        // along with the modified values for geekUser
        int status=GeekUsersDao.update(geekUser);
        if(status>0){
            response.sendRedirect("ViewServlet");
        }else{
            out.println("Sorry! unable to update record");
        }
         
        out.close();
    }
 
}


GeekUsersDao.update()

Java




public static int update(GeekUsers e)
{
    int status = 0;
    try {
        Connection con = GeekUsersDao.getConnection();
        // AS we are not sure about what fields need to be
        // updated, we are setting for all the fields by
        // means of update query This will update the record
        // for the corresponding geekUserId
        PreparedStatement ps = con.prepareStatement(
            "update geekusers set geekUserName=?,numberOfPosts=?,technologiesPreferred=? where geekUserId=?");
        ps.setString(1, e.getName());
        ps.setInt(2, e.getNumberOfPosts());
        ps.setString(3, e.gettechnologiesPreferred());
        ps.setInt(4, e.getId());
 
        status = ps.executeUpdate();
 
        con.close();
    }
    catch (Exception ex) {
        ex.printStackTrace();
    }
 
    return status;
}


Let us see the delete part now. Assume that we have 2 records, on click of delete of 2nd record, row 2 is deleted

deletepart

Let us see the relevant code for it.

“DeleteGeekUserServlet”.java

Java




import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
@WebServlet("/DeleteServlet")
public class DeleteGeekUserServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
        String sid = request.getParameter("id");
        int id = Integer.parseInt(sid);
        // GeekUsersDao.delete method is called with the
        // corresponding id and then it is redirected to
        // viewservlet
        GeekUsersDao.delete(id);
        response.sendRedirect("ViewServlet");
    }
}


GeekUsersDao.delete

Java




public static int delete(int id)
{
    int status = 0;
    try {
        Connection con = GeekUsersDao.getConnection();
        // delete query is given to delete the record for
        // the given geekUserId
        PreparedStatement ps = con.prepareStatement(
            "delete from geekusers where geekUserId=?");
        ps.setInt(1, id);
        status = ps.executeUpdate();
        con.close();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
 
    return status;
}


Visual representation of CRUD flow

Conclusion 

In the above said ways, we can perform “Creation/Read/Update/Delete” operations of any database using Servlet technology. A model class, DAO class, and corresponding servlet class will do wonders to perform simple CRUD operations.



Last Updated : 06 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads