Servlet – CRUD Operation with Example

CRUD means Create, Read, Update and Delete. These are the basic important operations carried out on the Database and in applications. We will build a simple User registration application using a Servlet, MYSQL, and JDBC for demonstration. In this example, we will be able to create users, read users, update users and delete users. 

Technology tools:

• MySQL(workbench) Database
• IDE(Intellij)
• Apache Tomcat(I used the Tomcat 9 version).

First, we download Apache Tomcat from download Apache Tomcat, Extract into your working directory.

Then we create a new project, am naming it app user. Usually, there are two approaches to this, the first is to create your project from the Database and the other one is to create the application first then set up the Database. In this article, we will be starting our project from the Database. So, let us set up our database, in this example am making use of MYSQL, check the image below for the demonstration on how to set up the database.

After that we set up our project, for this example am using Intellij IDE, 

• Create a new project by selecting File( it will show you various options)
• Select New, then project
• Click on Java Enterprise and choose web application as your project Template

Setting Up Application server

• Select the application and choose new
• Select Tomcat base directory(The one saved in your working directory)

This is what the project structure will look like:

The coding aspect. First, we create a model class called User, which is a simple POJO class;

User.java

Java

public class User {
    private int id;
    private String username;
    private String password;

    public int getId() { return id; }

    public void setId(int id) { this.id = id; }

    public String getUsername() { return username; }

    public void setUsername(String username)
    {
        this.username = username;
    }

    public String getPassword() { return password; }

    public void setPassword(String password)
    {
        this.password = password;
    }
}

Then we create a class that helps to perform CRUD operations on our database,

UserDaoHandler.java

Java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoHandler {
    public static Connection connectDB()
    {
        // connection object
        Connection connection = null;
        try {
            // returns the class object
            Class.forName("com.mysql.jdbc.Driver");
            // it creates a connection to the database using
            // the url
            connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/appuserdb",
                "adminuser", "user123");
        }

        catch (Exception message) {
            System.out.println(message);
        }
        return connection;
    }

    public static int addUser(User user) throws SQLException
    {
        int result = 0;
        Connection connect = UserDaoHandler.connectDB();

        // SQL statement is precompiled and stored in a
        // PreparedStatement object.
        PreparedStatement preparedStatement
            = connect.prepareStatement(
                "insert into user(username,password) values (?,?)");

        // set the parameter to the given Java String value
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getPassword());

        // execute SQl statement insert values to the
        // database
        result = preparedStatement.executeUpdate();

        // close the database connection
        connect.close();
        return result;
    }
    public static int updateUser(User user)
        throws SQLException
    {
        int result = 0;

        // create connection at the call of this method
        Connection connect = UserDaoHandler.connectDB();

        // SQL statement is precompiled and stored in a
        // PreparedStatement object
        PreparedStatement preparedStatement
            = connect.prepareStatement(
                "update user set username=?,password=? where id=?");

        // set the parameter to the given Java String value
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getPassword());

        // execute SQl statement, insert values to the
        // database
        result = preparedStatement.executeUpdate();

        // close the database connection
        connect.close();
        return result;
    }
    public static int deleteUser(int id) throws SQLException
    {
        int result = 0;

        // create connection at the call of this method
        Connection connect = UserDaoHandler.connectDB();

        // SQL statement is precompiled and stored in a
        // PreparedStatement object
        PreparedStatement preparedStatement
            = connect.prepareStatement(
                "delete from USER where id =?");
        // set the integer value to the  user id,
        preparedStatement.setInt(1, id);

        // execute SQl statement, insert values to the
        // database
        result = preparedStatement.executeUpdate();

        // close the database connection
        connect.close();

        return result;
    }
    public static User getUserById(int id)
        throws SQLException
    {
        // create a user object
        User user = new User();

        // create connection at the call of the method
        Connection connect = UserDaoHandler.connectDB();

        // SQL statement is precompiled and stored in a
        // PreparedStatement object
        PreparedStatement preparedStatement
            = connect.prepareStatement(
                "select * from USER where id=?");

        // set the integer value to the  user id,
        preparedStatement.setInt(1, id);

        // A table of data representing a database result
        // set,generated after the query
        ResultSet resultSet
            = preparedStatement.executeQuery();

        // checking for saved fields,if more than one
        if (resultSet.next()) {
            // value of the column is assigned to the set
            // method
            user.setId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
        }

        // close the database connection
        connect.close();
        return user;
    }
    public static List<User> getAllUsers(int start,
                                         int total)
        throws SQLException
    {
        // creating an empty arraylist of type User.
        List<User> list = new ArrayList<User>();

        // create connection at the call of the method
        Connection connect = UserDaoHandler.connectDB();

        // SQL statement and telling it to select from the
        // first index
        PreparedStatement preparedStatement
            = connect.prepareStatement(
                "select * from user limit " + (start - 1)
                + "," + total);
        ResultSet resultSet
            = preparedStatement.executeQuery();

        // this keep iterating the list of user
        // setting the values to the corresponding integer
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            // store the values into the list
            list.add(user);
        }

        // close the database connection
        connect.close();

        return list;
    }
}

After, we create our Servlet classes, each servlet class for the Create user, Update user, Read user, and Delete User from the database. Here are the classes;

Add User Servlet:

This method does a post method, it collects users’ information and adds it to the database, in this example, we are making use of “username”, “password” and “userId” as the field to be saved into the database.

Java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet(name = "addUser", value = "/addUser")
public class AddUser extends HttpServlet {
    protected void
    processRequest(HttpServletRequest request,
                   HttpServletResponse response)
        throws ServletException, IOException
    {
        response.setContentType("text/html;charset=UTF-8");
    }
    @Override
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
    }

    // override the supertype method post
    @Override
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
        processRequest(request, response);

        // print object for string formatting
        PrintWriter out = response.getWriter();

        // Httpservletrequest get parameters from user
        String username = request.getParameter("username");
        String password = request.getParameter("password");

        // Instances of User class
        User user = new User();

        // set the parameters gotten to the 'Username' field
        // of User class
        user.setUsername(username);
        user.setPassword(password);

        int status = 0;
        try {
            // static method add the values stored in the
            // user object to the database
            status = UserDaoHandler.addUser(user);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        // check if the values correspond to the one
        // specified
        if (status > 0) {
            out.print("
<p>Record saved successfully!</p>
");
            request.getRequestDispatcher("index.html")
                .include(request, response);
        }
        else {
            out.println("Sorry! unable to save record");
        }

        // close database connection
        out.close();
    }
}

AddUser.java for adding users to the database.

Update User Servlet:

This method gets the user id and matches it to the one in the database, then does an update on it.

UpdateUser.java

Java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet(name = "updateUser", value = "/updateUser")
public class UpdateUser extends HttpServlet {
    // override the supertype method post
    @Override
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
        PrintWriter out = response.getWriter();

        // collect user id parameter
        String userId = request.getParameter("id");

        // the string value is parse as integer to id
        int id = Integer.parseInt(userId);
        try {

            // this statement get user by id
            User user = UserDaoHandler.getUserById(id);

            // this print the jsp and render web page
            out.println("<h2>Edit User Account</h2>");
            out.print(
                "<form action='patchUser' method='post'>");
            out.print("<table>");
            out.print(
                "<tr><td></td><td><input type='hidden' name='id' value='"
                + user.getId() + "'/></td></tr>");
            out.print(
                "<tr><td>Name:</td><td><input type='text' name='name' value='"
                + user.getUsername() + "'/></td></tr>");
            out.print(
                "<tr><td colspan='2'><input type='submit' value='Update'/></td></tr>");
            out.print("</table>");
            out.print("</form>");

            // close database connection
            out.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
    }
}

The Patch Method:

This method simply exchanges information on the database to the input parameters gotten from the web page and saves it into the database.

PatchUser.java

Java

import static java.lang.System.out;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet(name = "patchUser", value = "/patchUser")
public class PatchUser extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
    }

    @Override
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
        PrintWriter out = response.getWriter();
        String userId = request.getParameter("id");
        int id = Integer.parseInt(userId);
        String username = request.getParameter("name");
        String password = request.getParameter("password");
        User user = new User();
        user.setId(id);
        user.setUsername(username);
        user.setPassword(password);
        try {
            int result = UserDaoHandler.updateUser(user);
            if (result > 0) {
                response.sendRedirect("viewUser? page =1");
            }
            else {
                out.print("unable to connect");
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        out.close();
    }
}

View user Servlet:

This method gets all users from the database and displays it in a simple table format.

ViewUser.java

Java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet(name = "viewUser", value = "/viewUser")
public class ViewUser extends HttpServlet {
    protected void
    processRequest(HttpServletRequest request,
                   HttpServletResponse response)
        throws ServletException, IOException
    {
        response.setContentType("text/html;charset=UTF-8");
    }

    // override the supertype method get
    @Override
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
        processRequest(request, response);
        PrintWriter out = response.getWriter();

        // assigning integer values to web pages
        String pageId = request.getParameter("page");
        int total = 3;
        int pagesId = Integer.parseInt(pageId);
        if (pagesId == 1) {
        }
        else {
            pagesId = pagesId - 1;
            pagesId = pagesId * total + 1;
        }

        // initializing list of users
        List<User> list = null;
        out.println(
            "<a href='/appuser_war_exploded/'>Add user</a>");

        out.print("<h1> User Table: </h1>");
        out.print(
            "<table border='1' cellpadding='4' width='80%'>");
        out.print("<tr><th>Id</th><th>username</th></tr>");
        try {
            // getting all users and assigning to the page
            // numbers
            list = UserDaoHandler.getAllUsers(pagesId,
                                              total);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        // ensuring list is not null
        if (list != null) {
            // iterating through the list of Users
            // And getting username and id of users.
            for (User user : list) {
                out.print("<tr><td>" + user.getId()
                          + "</td><td>" + user.getUsername()
                          + "</td></tr>");
            }

            // printing out in a jsp web format.
            out.print("</table>");
            out.print("<a href='viewUser?page=1'>1</a> ");
            out.print("<a href='viewUser?page=2'>2</a> ");
            out.print("<a href='viewUser?page=3'>3</a> ");
        }
    }

    @Override
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
    }
}

Delete Servlet:

The method below deletes the user from the database and sends the user view response, displaying the current users in the database.

DeleteUser.java

Java

import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.*;
import javax.servlet.annotation.*;
import javax.servlet.http.*;

@WebServlet(name = "deleteUser", value = "/deleteUser")
public class DeleteUser extends HttpServlet {
    protected void
    processRequest(HttpServletRequest request,
                   HttpServletResponse response)
        throws ServletException, IOException
    {
        response.setContentType("text/html;charset=UTF-8");
    }

    // overriding the supertype method get
    @Override
    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
        throws ServletException, IOException
    {
        processRequest(request, response);

        // get user by id
        String userId = request.getParameter("id");

        // the int value of the parameter
          // is parse to the id
        int id = Integer.parseInt(userId);
        try {
            // the delete method is
              // invoked on user with the
            // specified id
            UserDaoHandler.deleteUser(id);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        // it sent the current
         // user view as response
        response.sendRedirect("viewUser?page=1");
    }

    @Override
    protected void doPost(HttpServletRequest request,
                          HttpServletResponse response)
        throws ServletException, IOException
    {
    }
}

Output:

The Java servlet renders the Java server page anytime a user calls.