How should i make sure if the data already exists in the database

Question

My code is having some error, and when I'm writing a roll number which is not present in the database and pressing the delete button then also a message pops up saying "record deleted successfully".

Actually I wanted to create a project of students report and by connecting java and MySQL. So I wrote code for the delete button, in which if the roll no of a student is written and pressed delete it will delete the record of that particular student.

so hope u understood my problem and looking forward for an accurate answer.

Class.forName("java.sql.DriverManager");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/stud","root","");
Statement stmt=con.createStatement();
String query="delete from info where rollno="+rn.getText();
int  d = stmt.executeUpdate(query);
JOptionPane.showMessageDialog(null,"record deleted successfully!!!");
rn.setText("");

actually i don't get any errors...but i want to pop a message showing "this record does not exist" for the roll numbers which are not saved in my database. — myriad, Oct 01 '19 at 12:26
Try looking at the returned value in `d` I would hope that would tell you if the issued SQL command worked or failed. — RiggsFolly, Oct 01 '19 at 12:28
@myriad Nobody is going to write this for you. You are on your own, if you encounter any problems while writing the code then post it here, we will be happy to help :) — Aniket Sahrawat, Oct 01 '19 at 12:29
From the manual: _Return Value An int that indicates the number of rows affected, or 0 if using a DDL statement._ You are NOT using a DDL statement — RiggsFolly, Oct 01 '19 at 12:32
@myriad Pay attention to this answer https://stackoverflow.com/a/58185032/6099347 — Aniket Sahrawat, Oct 07 '19 at 10:30

Robert Kock · Answer 1 · 2019-10-01T12:43:24.100

First of all, use PreparedStatement in which you fill in the parameters instead of composing a SQL-string.
May avoid very nasty errors (How does the SQL injection from the "Bobby Tables" XKCD comic work?). So

PreparedStatement stmt = con.prepareStatement("DELETE FROM info WHERE rollno=?");
stmt.setLong(1, Long.parseLong(rn.getText()));
int d = stmt.executeUpdate();

As far as your question is concerned:
The method executeUpdate returns the number of rows affected.
If it equals 0, no rows were deleted.

if (d == 0)
{
  JOptionPane.showMessageDialog(null,"This record does not exist");
  // Return or thrown an exception or whatever to interrupt the operation
}
else
  JOptionPane.showMessageDialog(null,"record deleted successfully!!!");

if i try to delete a record which is actually saved in the database then this code works perfectly so i think that by adding a if condition we can avoid this error...........ie. the code will proceed further only if the if condition satisfies....do u have any idea about this?? — myriad, Oct 01 '19 at 12:34

score 2 · Accepted Answer · edited Oct 01 '19 at 12:37

2

showMessageDialog should get executed only if the value of d variable is positive i.e. some records got deleted from database. e.g.

Class.forName("java.sql.DriverManager");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/stud","root","");
Statement stmt=con.createStatement();
String query="delete from info where rollno="+rn.getText();
int  d = stmt.executeUpdate(query);
if(d>0){
    JOptionPane.showMessageDialog(null,"record deleted successfully!!!");
}
rn.setText("");

edited Oct 01 '19 at 12:37

RiggsFolly

93,638
21
103
149

answered Oct 01 '19 at 12:34

AbhiN

642
4
18

thank you very much...now my project runs perfectly – myriad Oct 01 '19 at 12:40
@myriad: Happy coding! :) – AbhiN Oct 01 '19 at 12:42
@myriad to bad this code is to prone to SQL injections as you should really use prepared statements.. – Raymond Nijland Oct 01 '19 at 12:46

score 1 · Answer 3 · answered Oct 01 '19 at 12:31

1

Check if result of executeUpdate is > 0. If yes, your entry was deleted.

answered Oct 01 '19 at 12:31

Ravi Ranjan

37
1
4

score 1 · Answer 4 · answered Oct 01 '19 at 12:42

Enter in rn: 1 or 1=1 and enjoy. Using PreparedStatements will prevent this evil SQL injection. Also it takes care of apostrophes around SQL strings and escaping apostrophe and other chars.

Connection con=DriverManager.getConnection("jdbc:mysql://localhost/stud","root","");
String query="delete from info where rollno=?";
try (PreparedStatement stmt = con.prepareStatement(query)) {
    stmt.setLong(1, Integer.parseLong(rn.getText()));
    int d = stmt.executeUpdate();
    if (d != 0) {
        JOptionPane.showMessageDialog(null, "Record deleted successfully.",
            JOptionPane.INFORMATION_MESSAGE);
    }
}

This try-with-resources will ensure that stmt is always closed

How should i make sure if the data already exists in the database

4 Answers4