CRUD stands for Create, Read, Update, and Delete, which are the basic operations used to manage data in a database. In this example, we will build a simple User Registration application using Servlet, MySQL, and JDBC to perform all CRUD operations.
- Used to manage user data such as adding, viewing, updating, and deleting records.
- Demonstrates how Servlet interacts with MySQL database using JDBC.
- Commonly used in real-world web applications for data management.
Prerequisites
- java 8 or + version
- MySQL(workbench) Database
- IDE(Intellij)
- Apache Tomcat (I used the Tomcat 9 version).
Steps to Impement Servlet - CRUD Operation
Step 1: Create Database (MySQL)
First, we create the database and table in MySQL and appuserdb is the database user table stores user detailsi and id is auto-incremented.
Step 2: Create Project (IntelliJ + Tomcat)
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:
Step 3: Create Model Class (User.java)
Represents database table structure it is Used to transfer user data between layers
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;
}
}
Step 4: Create Database Connection (UserDaoHandler.java)
Then we create a class that helps to perform CRUD operations on our database,
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;
}
}
Step 5: Create servlet class (AddUser)
AddUser.java for adding users to the database.
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();
}
}
Step 6: Create servlet class (UpdateUser)
This method gets the user id and matches it to the one in the database, then does an update on it.
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
{
}
}
Step 7: Create servlet class (PatchUser)
This method simply exchanges information on the database to the input parameters gotten from the web page and saves it into the database.
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();
}
}
Step 8: Create servlet class (ViewUser)
This method gets all users from the database and displays it in a simple table format.
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
{
}
}
Step 9: Create servlet class (Delete)
The method below deletes the user from the database and sends the user view response, displaying the current users in the database.
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
{
}
}
Step 10: Run Your application
- Start the Apache Tomcat server from IntelliJ IDEA.
- Right-click the project and select Run to deploy the application on the server.
- Open the browser and access the application using the Tomcat URL:
http://localhost:8080/appuser_war_exploded/
Output:
The Java servlet renders the Java server page anytime a user calls.
when user succesfully inserted the data and Record saved succesfully message shown on the browser screen.
When we want to add a user The browser show the page
Explanation: The application uses Servlets to handle user requests and JDBC to interact with the MySQL database. Each servlet performs a specific CRUD operation such as adding, viewing, updating, or deleting users. The DAO class manages all database queries, while the User model stores user data throughout the application flow.