Servlet that edit database record

Web application today is becoming popular like online registration, online reservation, and e-commerce. Web application usually has functions to store and retrieve data on database. Aside from those functions, another important database manipulation is to update the data in database. To edit or modify the data you need to connect to database programmatically, retrieve the data you want to edit, display it on web browser and let the user choose which record to be modify, display the chosen record to textboxt in web browser, change that old data to edit and then update that database.

Let assume that the name of database is “registration” and table name is “student” with the following fields: personID, fname,mi,lname,birthday,address,sex,civilstatus.

The code to display the records to be edit:

package registration;
import java.io.*;
import java.util.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class EditRegistration extends HttpServlet {
  public void service(HttpServletRequest request,
  HttpServletResponse response)
  throws IOException, ServletException{
  response.setContentType("text/html");
  PrintWriter out = response.getWriter();
  out.println("<html>");
  out.println("<head><title>Edit Records</title></head>");
  out.println("<body>");
  out.println("<a href='http://localhost:8081/registration/'>[Home]</a>");
  out.println("<h1>Edit Records</h1>");
  out.println("<form action='/servlet/registration.ShowEdit' method='POST'>");
  out.println("Enter ID: <input type='text' name='id'>");
  out.println("<input type='submit' name='btnDelete' value='Submit'>");
  out.println("</form>");
  // connecting to database
  Connection con = null;  
  Statement stmt = null;
  ResultSet rs = null;
  try {
  Class.forName("com.mysql.jdbc.Driver");
  con =DriverManager.getConnection 
  ("jdbc:mysql://localhost:3306/registration","root","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM student");
  // displaying records
 out.print("<table border='1'>");
 out.print("<tr'>");
 out.print("<td>");
 out.print("<strong><font size='2'>ID</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>First Name</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>M.I.</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>Last Name</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>Birth Day</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>Address</font></strong>");
 out.print("</td>");
 out.print("<td>");
 out.print("<strong><font size='2'>Sex</font></strong>");
 out.print("</td>");
out.print("<td>");
 out.print("<strong><font size='2'>Civil Status</font></strong>");
 out.print("</td>");
 out.print("</tr>");
while(rs.next()){
  out.print("<tr>");
  out.print("<td>");
  out.print(rs.getObject(1).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(2).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(3).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(4).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(5).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(6).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(7).toString());
  out.print("</td>");
  out.print("<td>");
  out.print(rs.getObject(8).toString());
  out.print("</td>");
 }
out.print("</table>");
out.println("</body></html>"); 
  } catch (SQLException e) {
 throw new ServletException("Servlet Could not display records.", e);
  } catch (ClassNotFoundException e) {
  throw new ServletException("JDBC Driver not found.", e);
  } finally {
  try {
  if(rs != null) {
  rs.close();
  rs = null;
  }
  if(stmt != null) {
  stmt.close();
  stmt = null;
  }
  if(con != null) {
  con.close();
  con = null;
  }
  } catch (SQLException e) {}
  }
  out.close();
  }
  }

The code that retrieve record to be edited:

package registration;
import java.io.*;
import java.lang.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class ShowEdit extends
 HttpServlet{
  public void doPost(HttpServletRequest request, 
HttpServletResponse response)
throws ServletException, IOException{
  response.setContentType("text/html");
  PrintWriter pw = response.getWriter();
  String connectionURL = "jdbc:mysql://localhost/registration";
  Connection connection;
  try{
  String strID = request.getParameter("id");
  // connecting to database
  Connection con = null;  
  Statement stmt = null;
  ResultSet rs = null;
  
  Class.forName("com.mysql.jdbc.Driver");
  con =DriverManager.getConnection 
  ("jdbc:mysql://localhost:3306/registration","root","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM student where id=" +strID);
  // displaying records
String id="";
String fname="";
String mi="";
String lname="";
String bday="";
String address="";
while(rs.next()){
  id=rs.getObject(1).toString();
  fname =rs.getObject(2).toString();
  mi=rs.getObject(3).toString();
  lname=rs.getObject(4).toString();
  bday=rs.getObject(5).toString();
  address=rs.getObject(6).toString();
}
pw.println("<html>");
pw.println("<head>");
pw.println("<title>Edit</title>");
pw.println("</head>");
pw.println("<body>");
pw.println("<a href='http://localhost:8081/registration/'>[Home]</a>");
pw.println("<h1>Edit Records</h1>");
pw.println("<form action='/servlet/registration.DoEdit' method='POST'>");
pw.println("<table>");
pw.println("<tr>");
pw.println("<td>");
pw.println("ID:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='id' value =" + id + " size='20'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("First Name:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='fname' value =" + fname + " size='20'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("MI:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='mi' value =" + mi + " size='20'>");
pw.println("</td>");
pw.println("<tr>");
pw.println("<td>");
pw.println("Last Name:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='lname' value =" + lname + " size='20'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("Birth Date:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='dbirth' value =" + bday + " size='20'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("Address:");
pw.println("</td>");
pw.println("<td>");
pw.println("<input type='text' name='address' value =" + address + " size='20'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("<strong>Sex:</strong>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("<input type='radio' name='sex' value='Male'>Male<br>");
pw.println("<input type='radio' name='sex' value='Female'>Female<br>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("<strong>Civil Status:</strong>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr>");
pw.println("<td>");
pw.println("<input type='radio' name='civilstatus' value='Single'>Single<br>");
pw.println("<input type='radio' name='civilstatus' value='Married'>Married<br>");
pw.println("<input type='radio' name='civilstatus' value='Widow'>Widow<br>");
pw.println("</td>");
pw.println("</tr>");
pw.println("<tr colspan='2'>");
pw.println("<td>");
pw.println("<input type='submit' name='btnSubmit' value='Update'>");
pw.println("</td>");
pw.println("</tr>");
pw.println("</table>");
pw.println("</form>");
pw.println("</body>");
pw.println("</html>");
}
  catch (Exception e){
  pw.println(e);
}
}
}

The code that update record on database:

package registration;
import java.io.*;
import java.lang.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DoEdit extends
 HttpServlet{
  public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
  response.setContentType("text/html");
  PrintWriter pw = response.getWriter();
  String connectionURL = "jdbc:mysql://localhost/registration";
  Connection connection;
  try{
  
  String strID = request.getParameter("id");
  String strfname = request.getParameter("fname");
  String strmi = request.getParameter("mi");
  String strlname = request.getParameter("lname");
  String strbday = request.getParameter("dbirth");
  String straddress = request.getParameter("address");
  String strsex = request.getParameter("sex");
  String strcivilstatus = request.getParameter("civilstatus"); 
  int intID=Integer.parseInt(strID);
  Class.forName("org.gjt.mm.mysql.Driver");
  connection = DriverManager.getConnection
  (connectionURL, "root", "");
    PreparedStatement pst = connection.prepareStatement("update student set fname =?, mi=?, lname=?, birthday=?, address=?, sex=?, civilstatus=? where id ="+intID + "");
      pst.setString(1, strfname);
    pst.setString(2, strmi);
    pst.setString(3, strlname);
    pst.setString(4, strbday);
    pst.setString(5, straddress);
    pst.setString(6, strsex);
    pst.setString(7, strcivilstatus);
int i = pst.executeUpdate();
  if(i!=0){
  pw.println("<br>Record has been updated");
  pw.println("<br>");
  pw.println("<a href='http://localhost:8081/servlet/registration.EditRegistration'>Home</a>");
  }
  else{
  pw.println("failed to update the record");
  }
  }
  catch (Exception e){
  pw.println(e);
  }
  }
}

The code above is a Java servlet that retrieve and edit the record stored in MySQL database. It will serve as example to beginner programmer who wants to learn Java servlet database programming.


Related article: Java servlet view example