This week, find out how to connect your JSPs to a database and build dynamic, data-driven Web pages. This primer covers different techniques to select, insert and delete records, and uses a simple Web-based address book to illustrate the Connection, Statement, and ResultSet objects.
Finally, it's time to do a little routine maintenance. This example demonstrates how to use the DELETE statement to delete a particular entry. Again, the basic principles remain the same, with only the query string changing.
First, the initial list page has to be altered to include a link to delete a specific entry - this is similar to the manner in which the "edit this entry" link was added. Assuming that's taken care of, the script "delete.jsp" should be called with the number of the record to be deleted. So, just as you have the link
"<a href=edit.jsp?id=" + ID + ">edit this entry</a>"
you will now have the additional link
"<a href=delete.jsp?id=" + ID + ">delete this entry</a>"
Let's take a look at "delete.jsp".
<%@ page language="java" import="java.sql.*" %>
// form data
String fid = request.getParameter("id");
int id = Integer.parseInt(fid);
// database parameters
// create connection string
conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" +
// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(conn);
// query statement
Statement SQLStatement = Conn.createStatement();
// generate query
String Query = "DELETE FROM abook WHERE id=" + id;
// get result code
int SQLStatus = SQLStatement.executeUpdate(Query);
if(SQLStatus != 0)
out.println("Entry successfully deleted.");
out.println("Error! Please try again.");
// close connection
And that's about all we have for this issue of The JSP
Files. Next time, we'll be taking a look at the HTTP session management capabilities available in JSP - so make sure you come back for that one!