How can I delete database records from a jsp page?

Question

The following code is a part of my project and the output of this code is that I get all the title of the posts in the database and a delete hyperlink in front of all enteries. When I click delete for a respective title it should be deleted from jsp page. How to write code for this?

<label><h3>Post published:</h3></label>
      <%
               rs = stmt.executeQuery("select title from Postdata");
       %>

                <table id="rounded-corner" summary="all posts">

                    <tbody>
                        <% while (rs.next()) {%>
                        <tr>
                            <td>
                                <%=rs.getString(1)%>
                            </td>
                            <td>

                                <a href><%=""%>Delete</a>
                            </td>
                        </tr>
                        <%}%>
                    </tbody>
                </table>

Solution 1

The ideal way to do this is to move all logic to a Servlet. This method I mention below is not recommended for many reasons you will learn as you gain more experience. However for now let us assume you are going to use only JSPs. There are many ways to do this, but this is one of the ways.

This method uses two JSPS, first JSP to display the records. The second JSP is a processing JSP which will do the deleting for you.

More in the detail below.

The first JSP will display the records. The second JSP is invoked when you click on "delete".

The second JSP would delete the record and then redirect back to the first JSP. This is a very inefficient way to do this and is not recommended. But it is quick and dirty and will save you time from creating Servlets, Java files etc.

JSP1.jsp
will have the same code as you posted. retrieve the primary key of the record as well. This will be used to identify the record that needs to be deleted. This id will be passed as a parameter to the second JSP.

<%
      rs = stmt.executeQuery("select primary_key_id, title from Postdata");
%>
  <table id="rounded-corner" summary="all posts">

     <tbody>
        <% while (rs.next()) {
             String primaryKey = rs.getString(1);
        %>
         <tr>
             <td>
                <%=rs.getString(2)%>
             </td>
             <td>

                 <a href ="JSP2.jsp?deleteid=<%=primaryKey%>">Delete</a>
              </td>
          </tr>
          <%}%>

        </tbody>
     </table>

JSP2.jsp
retrieve the parameter from the request. Then execute the Delete query for that id. Then redirect back to JSP1.

<%
    String recordToDelete = request.getParameter("deleteid");

    // Use PreparedStatements here instead of Statment
    rs = stmt.executeQuery("delete from Postdata where primary_key_id="+ recordToDelete );

     response.sendRedirect("JSP1.jsp"); // redirect to JSP one, which will again reload.
%>

This method is not recommended, but you get an idea what needs to be done.

Solution 2

Firstly you really want to use JSTL instead of 'raw' java in JSP files. And, like the previous commenter already mentioned, you would put this logic nowadays in a servlet/controller.

If you really like to do this with an jsp, the code should look something like this (using jstl):

<sql:setDataSource var="ds" ... />
<c:set var="title" value="${param['title']}"/>

<sql:update dataSource="${ds}">
   DELETE FROM Postdata where title = ?
   <sql:param value="${title}" />
</sql:update>

Where you create the link as following:

<td><a href="?title=[title to remove]">Delete</a></td>

Solution 3

Create a POST form with the record ID as hidden input value and a submit button. I assume that your table has an id column and that you've selected it as well.

<form action="delete" method="post">
    <input type="hidden" name="id" value="<%=rs.getLong("id")%>" />
    <input type="submit" value="Delete" />
</form>

In the servlet (or JSP if you really need it to be), just grab the ID as request parameter

String id = request.getParameter("id");

Then just do your JDBC thing.

preparedStatement = connection.prepareStatement("DELETE FROM PostData WHERE id = ?");
preparedStatement.setLong(1, Long.valueOf(id));
preparedStatement.executeUpdate();

---

Unrelated to the concrete problem, putting Java code in a JSP file is considered a poor practice, for sure if it is database interaction code. I suggest to invest some time in learning servlets.

See also:

• Our Servlets wiki page
• How to avoid Java code in JSP files?