CODEWITHSUNDEEP
htmlservletsjdbc
Chandra Kishore
Chandra Kishore

Reputation: 47

Delete row from database through Servlet

I had a problem in my servlet regarding deleting record from my database. Please look over my servlet code and please do correct me. Thank you in advance DeleteRow servlet is working perfectly right when a single ID is given manually. Scenario is:

ManageSinger.java servlet displays the records in database along with a "Delete" hyperLink in every row. But problem is whenever i try to press delete button.. it receives a null value for ID in deleterow.java servlet .. Please guide me from here how can only that corresponding ID can be pass to another servlet.

ManageSinger.java

package com.ea.servlet;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class ManageSinger extends HttpServlet {

/**
 * 
 */
private static final long serialVersionUID = 1L;

public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException {
    PrintWriter out = res.getWriter();
    res.setContentType("text/html");
    out.println("<html><body>");
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/EATWO","root","");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from singerdetails");
        out.println("<form method = \"post\">");
        out.println("<table border=1 width=50% height=50%>");
        out.println("<tr><th align=\"center\">Singer Name</th><th align=\"center\">StageName</th><th align=\"center\">Language</th><th></th><tr>");
        while (rs.next()) {
            String singername = rs.getString("singername");
            String stagename = rs.getString("stagename");
            String language = rs.getString("language"); 
            String id = rs.getString("userID");
            HttpSession session = req.getSession(true); 
            session.setAttribute("userID",id);
            out.println("<tr><td align=\"center\">" + singername + "</td><td align=\"center\">" + stagename + "</td><td align=\"center\">" + language + "</td><td align=\"center\"><a href = \"./deleterow\">Delete</a></td></tr>");
        }
        out.println("</table>");
        out.println("</form");
        out.println("</body></html>");
        con.close();
       }
        catch (Exception e) {
        e.printStackTrace();
    }finally{

    }
}
}

DeleteRow.java

package com.ea.servlet;

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 javax.jws.Oneway;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


public class DeleteRow extends HttpServlet
{
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
Connection con;
PreparedStatement st;
ResultSet rs;
try
{ 
HttpSession session = req.getSession(true);
Class.forName("com.mysql.jdbc.Driver");
String id = (String) session.getAttribute("id");
System.out.println(id);
con =        DriverManager.getConnection("jdbc:mysql://localhost:3306/EATWO","root","");
st= con.prepareStatement("delete from singerdetails where userID = ?");
st.setString(1, id);
st.executeUpdate();
int i = st.executeUpdate();
if(i!=0)
pw.println("Deleting row...");
else if (i==0)
{
pw.println("<br>Row has been deleted successfully.");
}
}
catch(SQLException sx)
{
pw.println(sx);
}
catch(ClassNotFoundException cx)
{
pw.println(cx);
}   
}
}

Upvotes: 1

Views: 14036

Answers (2)

bprasanna
bprasanna

Reputation: 2453

As per your current ManageSinger.java only the last userID gets set in session, which is why the last userID gets deleted. In ManageSinger.java instead of setting the userID as session attribute you can set it as url parameter for each record.

Following lists the changes:

//HttpSession session = req.getSession(true); 
//session.setAttribute("userID",id);
out.println("<tr><td align=\"center\">" + singername + "</td><td align=\"center\">" + stagename + "</td><td align=\"center\">" + language + "</td><td align=\"center\"><a href = \"./deleterow?userID="+id+"\">Delete</a></td></tr>");

In DeleteRow.java instead of getting the userID from session, you can get it from the url as following:

//HttpSession session = req.getSession(true);
//String id = (String) session.getAttribute("id");
String id = req.getParameter("userID").toString();

Upvotes: 1

Avinash Reddy
Avinash Reddy

Reputation: 2280

In DeleteRow.java say you should do String id = (String) session.getAttribute("userID"); instead of (String) session.getAttribute("id") .In ManageSinger.java you are setting the attribute as follows session.setAttribute("userID",id);

Upvotes: 2

Related Questions