This Servlet web developement tutorial will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE . using Mysql Database.Servlet crud tutorial The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.
We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the records table in the database named lba.lba table consist of following columns eid,fname,lname
Index.html
In this page system shall be able to record the employee details then system shall be able to retrieve employee details.
<html>
<head>
<title>Employee Details</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<form method="POST" action="employee">
<table width="400px" border="1">
<tr>
<td colspan="2"><h1>Employee Registation</h1> </td>
</tr>
<tr>
<td>Employee ID</td>
<td><input type="text" name="empid" id="empid"></td>
</tr>
<tr>
<td>Firstname</td>
<td><input type="text" name="fname" id="fname"></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type="text" name="lname" id="lname"></td>
</tr>
<tr>
<td colspan="2"> <input type="submit" value="submit"></td>
</tr>
</table>
</form>
<p><a href="viewemployee">View Employee</a></p>
</body>
</html>
After fill the form click submit button form will be goes to the action=”employee” page
you have create the page .
employee.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
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("/employee")
public class employee extends HttpServlet {
Connection con;
PreparedStatement pst;
int row;
public void doPost(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
{
rsp.setContentType("text/html");
PrintWriter out = rsp.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
String empid = req.getParameter("empid");
String empfname = req.getParameter("fname");
String emplname= req.getParameter("lname");
pst = con.prepareStatement("insert into employee(id,fname,lname)values(?,?,?) ");
pst.setString(1, empid);
pst.setString(2, empfname);
pst.setString(3, emplname);
row = pst.executeUpdate();
out.println("<font color='green'> Record Addedddd </font>");
} catch (ClassNotFoundException ex) {
Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
out.println("<font color='red'> Record Failed </font>");
}
}
}
Display the record in to the table
viewemployee.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
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("/viewemployee")
public class viewemployee extends HttpServlet {
Connection con;
PreparedStatement pst;
ResultSet rs;
int row;
public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
{
rsp.setContentType("text/html");
PrintWriter out = rsp.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
String sql;
sql = "select * from employee";
Statement stmt = con.createStatement();
rs = stmt.executeQuery(sql);
out.println("<table cellspacing='0' width='350px' border='1'>");
out.println("<tr>");
out.println("<td> EmpID </td>");
out.println("<td> Firstname </td>");
out.println("<td> Lastname </td>");
out.println("<td> Edit </td>");
out.println("<td> Delete </td>");
out.println("</tr>");
while(rs.next())
{
out.println("<tr>");
out.println("<td>" + rs.getString("id") + "</td>");
out.println("<td>" + rs.getString("fname") + "</td>");
out.println("<td>" + rs.getString("lname") + "</td>");
out.println("<td>" + "<a href='Editreturn?id=" + rs.getString("id") + "'> Edit </a>" + "</td>");
out.println("<td>" + "<a href='Delete?id=" + rs.getString("id") + "'> Delete </a>" + "</td>");
out.println("</tr>");
}
out.println("</table>");
} catch (ClassNotFoundException ex) {
Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
out.println("<font color='red'> Record Failed </font>");
}
}
}
Select the row of the table which row need to be edit click the edit link of the row . After click the link will be goes to the action=”Editreturn” page
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
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("/Editreturn")
public class Editreturn extends HttpServlet {
Connection con;
PreparedStatement pst;
ResultSet rs;
int row;
public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
{
rsp.setContentType("text/html");
PrintWriter out = rsp.getWriter();
String eid = req.getParameter("id");
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
pst = con.prepareStatement("select * from employee where id = ?");
pst.setString(1, eid);
rs = pst.executeQuery();
while(rs.next())
{
out.print("<form action='EditServlet' method='POST'");
out.print("<table");
out.print("<tr> <td>EmpID</td> <td> <input type='text' name ='empid' id='empid' value= '" + rs.getString("id") + "'/> </td> </tr>");
out.print("<tr> <td>Firstname</td> <td> <input type='text' name ='fname' id='fname' value= '" + rs.getString("fname") + "'/> </td> </tr>");
out.print("<tr> <td>Firstname</td> <td> <input type='text' name ='lname' id='lname' value= '" + rs.getString("lname") + "'/> </td> </tr>");
out.print("<tr> <td colspan ='2'> <input type='submit' value= 'Edit'/> </td> </tr>");
out.print("</table");
out.print("</form");
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
out.println("<font color='red'> Record Failed </font>");
}
}
}
Matching record has been passing to the edit return for edit the records. after make the changes you will be able to click Edit button
EditServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
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 EditServlet extends HttpServlet {
Connection con;
PreparedStatement pst;
ResultSet rs;
int row;
public void doPost(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
{
rsp.setContentType("text/html");
PrintWriter out = rsp.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
String empid = req.getParameter("empid");
String fname = req.getParameter("fname");
String lname = req.getParameter("lname");
pst = con.prepareStatement("update employee set fname = ?, lname = ? where id = ?");
pst.setString(1, fname);
pst.setString(2, lname);
pst.setString(3, empid);
row = pst.executeUpdate();
out.println("<font color='green'> Record Updateeeedd </font>");
} catch (ClassNotFoundException ex) {
Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
out.println("<font color='red'> Record Failed </font>");
}
}
}
Delete.java
select the row of the table which record need to delete click the delete link of the row.
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
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("/Delete")
public class Delete extends HttpServlet {
Connection con;
PreparedStatement pst;
ResultSet rs;
int row;
public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
{
rsp.setContentType("text/html");
PrintWriter out = rsp.getWriter();
String empid = req.getParameter("id");
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
pst = con.prepareStatement("delete from employee where id = ?");
pst.setString(1, empid);
row = pst.executeUpdate();
out.println("<font color='green'> Record Deletedddddd </font>");
} catch (ClassNotFoundException ex) {
Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
out.println("<font color='red'> Record Failed </font>");
}
}
}
i have attached the video link below. which will do this tutorials step by step.
