Deleting a record created dynamically in a database using Servlets and JDBC

[sudhasundaram]

Hi,

I have got the codings for inserting and displaying the records in the database. But i couldn't able to delete a specific row, which is created dynamically.

i.e, if i give any one of the field value in a text box, then using the request.getParameter() method i have to delete the entire record with the value given in the textbox.

I need the jdbc codings....... for that........

i have tried the following codings but it does not work.

Statement st = con.createStatement("DELETE FROM email WHERE email="+'"'+request.getParameter("email1")+'"');
ResultSet rs=st.executeQuery();


The solution can also be in MVC pattern.


It would be so helpful, if u provide me the solution.

[ess]

You should not use the Execute Query statement...as that is suppose to be used with select statements...or other statements that are ought to return record(s) from a given database.

When updating or deleting records, you should use the Execute Update method. for example

int affectedRecords;

affectedRecords= st.executeUpdate("DELETE FROM email WHERE email="+'"'+request.getParameter("email1")+'"');

Once you've executed the above statement, you will get an integer value indicating the number of affected row(s)

Cheers,
Ess

[sudhasundaram]

Thanks a lot for your reply. But i still have problem with the coding.
here is the code for the bean

dbemailBean.java

import java.sql.*;

public class dbemailBean
{

Connection con=null;
String email;


public void connect()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:emailsource");
}
catch(Exception e)
{
System.out.println(e);
}
}

public void setEmail(String email)
{

this.email=email;
}



public void delete()
{
try
{
Statement st = con.createStatement();

String email= request.getParameter("email");
String sql = "DELETE FROM email WHERE email=\'"+email+"\'";
System.out.println(sql);
int result =st.executeUpdate(sql);

}
catch(Exception e)
{
System.out.println(e);
}
}

public String getEmail()
{
return email;
}


public ResultSet display()
{
ResultSet rs=null;
try
{
Statement stmt = con.createStatement();
rs=stmt.executeQuery("select email from email");
}
catch(Exception e)
{
System.out.println(e);
}
return rs;
}

public void close() {
try {
if(con != null) {
con.close();
}
}catch(Exception e) {
System.out.println(e);
}
}


}


The servlet part is as follows

dbemailServlet.java

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

public class dbemailServlet extends HttpServlet
{
public void doGet(HttpServletRequest req,HttpServletResponse res)throws ServletException,IOException
{
res.setContentType("text/html");
PrintWriter pw=res.getWriter();
try
{
dbemailBean bean=new dbemailBean();
bean.connect();
RequestDispatcher rd=null;

if((req.getParameter("action")).equals("display"))
{
bean.setEmail(req.getParameter("email1"));

bean.delete();

bean.getEmail();

ResultSet rs=bean.display();
req.setAttribute("RSObject",rs);
rd=req.getRequestDispatcher("/displayemail.jsp");
rd.forward(req,res);

}
}
catch(Exception e)
{
System.out.println(e);
}
}
}

When i use this code errors are not generated but the record is not getting deleted.

[ess]

try and change the following from
"DELETE FROM email WHERE email=\'"+email+"\'";

to

"DELETE FROM email WHERE email='"+email+"'";

Personally, I usually create SQL statements outside my server side script, test it...check what could possible go wrong i.e. SQL Injection...and once satisfied with the SQL statement...I then add it to the server side script...if the database does not support stored procedures that is.

Also...I strongly recommend that you make use of regular expressions to validate input before appending parameters to the sql statement. Most SQL Injections attacks occur due to poor server side validation of input parameters.

If you are not familiar with SQL Injection or regular expressions...do a google search and I am sure you find loads of references.

By the way...I know it is a daft question...but hopefully...there is no harm in asking. Before adding users to the table...do you check that the supplied email address does not already exist and that emails are unique for every account? if not...than your statement could easily delete a bunch of records if the where clause evaluates to true.

In any case, let's know if the above suggestion works or not.

Cheers,
Ess

[sudhasundaram]

Thanks a lot.............
It worked on fine after trying with the coding format u have given.

[ess]

Glad that it worked in the end

Cheers,
Ess