Servlet – CRUD
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 w3wiki -- Table name geekusers CREATE TABLE `w3wiki`.`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
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
// 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 "w3wiki" 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 w3wiki is the name of the database private static String jdbcURL = "jdbc:mysql://localhost:3306/w3wiki" ; 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
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
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
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.