JSP Delete Record From Table Using MySQL
This tutorial explains you that how to write a JSP for deleting a record from database table. In this section you will see all the steps required for deleting a record of a table.
In this tutorial you will learn that how to delete a record of a database table in JSP. In this example we will discuss about how to delete a record of database table using Eclipse IDE and Tomcat 7 server.
Example
Here we will give an example for deleting a record of a database table in jsp. To accomplish this task we will have to first create a database table and required to insert some record. And then we will be required to create a JSP page for deleting the record. In the JSP page we have written the code for loading the jdbc driver and for making a connection with the corresponding database driver. In the progress of code we have written a sql query for deleting the record. Further we have written the code for executing this query and transfer the control from one page to another on the basis of the query result. The control where it is transferred are also a JSP page which displays the messages corresponding to result of query.
Database Table
CREATE TABLE `person` (
`personID` bigint(10) NOT NULL,
`personName` varchar(15) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`address` tinytext,
`mobile_no` bigint(15) DEFAULT NULL,
PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Then inserted some records into the table by the following query:
insert into person(personID,personName,date_of_birth,address,mobile_no) values ( '1','A','2000-01-19','Delhi','3333333333333') insert into person(personID,personName,date_of_birth,address,mobile_no) values ( '2','B','2000-02-19','mumbai','4444444444444444') insert into person(personID,personName,date_of_birth,address,mobile_no) values ( '3','C','2000-03-19','chennai','66666666666') insert into person(personID,personName,date_of_birth,address,mobile_no) values ( '4','D','2000-04-19','kolkata','7777777777') insert into person(personID,personName,date_of_birth,address,mobile_no) values ( '5','E','2000-05-19','patna','8888888888')
Source Code
jspDelete.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*;" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Delete Data</title>
</head>
<body>
<form action="jspDelete.jsp">
<table>
<tr>
<td>Enter ID To Delete</td>
<td><input type="text" name="id"/></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Delete"/></td>
</tr>
</table>
</form>
<%! String driverName = "com.mysql.jdbc.Driver";%>
<%!String url = "jdbc:mysql://localhost:3306/record";%>
<%!String user = "root";%>
<%!String psw = "root";%>
<%
String id = request.getParameter("id");
if(id != null)
{
Connection con = null;
PreparedStatement ps = null;
int personID = Integer.parseInt(id);
try
{
Class.forName(driverName);
con = DriverManager.getConnection(url,user,psw);
String sql = "DELETE FROM person WHERE personID="+personID;
ps = con.prepareStatement(sql);
int i = ps.executeUpdate();
if(i > 0)
{%>
<jsp:forward page="/success.jsp"/>
<%
}
else
%>
<jsp:forward page="/failure.jsp"/>
<%
}
catch(SQLException sqe)
{
request.setAttribute("error", sqe);
out.println(sqe);
}
}
%>
</body>
</html>
success.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
out.print("Record Deleted Successfully");
%>
</body>
</html>
failure.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
out.println("There is a problem in Deleting Record.");
out.print("Check Manually in your Database");
%>
</body>
</html>
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>jspDeleteData</display-name> <welcome-file-list> <welcome-file>jspDelete.jsp</welcome-file> </welcome-file-list> </web-app>
Output
Initially the table is as follows :
When you will run this example you will get the output as follows :
1. When the above example will be executed successfully then a form will be displayed on your browser where an input box is given for providing the ID of which you want to delete from the table as follows
2. Suppose I have provided the id = 1 and click on delete button then the output will be as follows :
3. If once the record is deleted and again if I give the id = 1 and click on the delete button then the output will be as follows :
4. And the table after deleting the record will be look like as follows :
