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.