Servlet – CRUD
Last Updated :
06 Jan, 2023
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 >
< 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 >
</ head >
< body >
< h1 >Add New GeekUser</ h1 >
< 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
Here let us give the inputs as follows
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
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
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 {
private static String jdbcUsername = "root" ;
private static String jdbcPassword = "*****" ;
public static Connection getConnection(){
Connection con= null ;
try {
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;
}
}
|
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();
String name = request.getParameter( "name" );
int numberOfPosts = Integer.parseInt(
request.getParameter( "numberOfPosts" ));
String technologiesPreferred
= request.getParameter( "technologiesPreferred" );
GeekUsers geekUser = new GeekUsers();
geekUser.setName(name);
geekUser.setNumberOfPosts(numberOfPosts);
geekUser.setTechnologiesPreferred(
technologiesPreferred);
int status = GeekUsersDao.save(geekUser);
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" );
}
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();
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){
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
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);
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();
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
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);
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();
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
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(id);
response.sendRedirect( "ViewServlet" );
}
}
|
GeekUsersDao.delete
Java
public static int delete( int id)
{
int status = 0 ;
try {
Connection con = GeekUsersDao.getConnection();
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.
Share your thoughts in the comments
Please Login to comment...