How to delete a specific row from database?

[shihab2555]

I have created a table named student including column stu_id, stu_name & Blood_group.I created a delete button to delete rows and I used the following code when delete button is pressed.I take input using jtextfield.But this can only delete by taking input in stu_id.But now i also want to delete by Blood_group or stu_name or both.here is my code.

private void cmd_deleteActionPerformed(java.awt.event.ActionEvent evt) {                                           

int p= JOptionPane.showConfirmDialog(null,"Do you really want to      delete?","Delete",JOptionPane.YES_NO_OPTION);
if(p==0){
try{


String sql="Delete from student where stu_id=?";



pst=conn.prepareStatement(sql);
pst.setString(1, jTextField1.getText());

pst.execute();

}       
catch(Exception e){
 JOptionPane.showMessageDialog(null, e);
 }
   Update_table();
}                                          

}

I figured i have to change my query.So i changed my code to this.

 private void cmd_deleteActionPerformed(java.awt.event.ActionEvent evt) {                                           

int p= JOptionPane.showConfirmDialog(null,"Do you really want to delete?","Delete",JOptionPane.YES_NO_OPTION);
if(p==0){
    try{

  String s[]=new String[20];
s[1]=jTextField1.getText();
s[2]=jTextField2.getText();
s[3]=jTextField3.getText();

String sql="Delete from student where ";

if(s[1] != null)
    sql=sql+"stu_id='"+s[1]+"' AND ";

if(s[2] != null)
    sql=sql+"stu_name='"+s[2]+"' AND ";

if(s[3]!= null)
    sql=sql+"Blood_group='"+s[3]+"'";



pst=conn.prepareStatement(sql);

pst.execute();

}       
catch(Exception e){
 JOptionPane.showMessageDialog(null, e);
 }
   Update_table();
}                                          

}

But this didn't work.Unfortunately now i have to give input all 3 column to delete a row.How can i solve that?Did my ques was clear.Thanx in advance.

[JamesCherrill]

Maybe OR those conditions rather than ANDing them?

[shihab2555]

It won't work.For example i want to delete the row which contain stu_name=mark AND blood_group=B+.If i use OR than it will delete all row containing B+ and also all row containing mark.any other solution please.

[JamesCherrill]

Then you will have to construct different SQL commands for each of the 6 possible combinations of criteria.

[shihab2555]

But if i have 10 column instead of 3 column than i think i won't be the best idea to combine the possible criteria.Please give me a better solution.

[JamesCherrill]

This isn't a Java question, it's an SQL question, and I'm not the best person to answer it. Try asking how to phrase the SQL query in our Databases forum - someone will know the answer.

[shihab2555]

Thankyou.Your help mean a lot to me

[shihab2555]

Can you tell me what is the default value of a jtextfield.I added those jtextefield by drag and drop process in netbeans.Than i right click on it,click edit text and than i deleted the text(it was 'jtextfield').Now what is the value of jtextfield? Is it "" or null or anything else?

[JamesCherrill]

By "value" do you mean the text in the JTextField? If so, the default is a zero-length String, ""

[shihab2555]

Than i think it should work the way i did in the second code by typing s[1/2/3]="" instead of s[1/2/3]=null. But it didn't work.Can you tell me why?

[JamesCherrill]

I have no idea what you are trying to achieve without any context. And "didn't weork" tells me nothing.
s is an array of Strings which was populated from the text of some JTextFields. Did you want to change the text fields? If so you need to use JTextField's setText method

[shihab2555]

I have done that already.I tried both null and "".But it didn't work.I changed the code to this.I used set text method before the new code.

if (s[1] != null || s[2] != null || s[3] != null)
{
String sql="Delete from student where ";
String andsql="";
if(s[1] != null)
{
sql=sql+"stu_id='"+s[1]+"' ";
andsql=" and ";
}
if(s[2] != null )
{
sql=sql+andsql+"stu_name='"+s[2]+"' ";
andsql=" and ";
}
if(s[3]!= null)
{
sql=sql+andsql+"Blood_group='"+s[3]+"'";
}
pst=conn.prepareStatement(sql);
pst.execute();
}

But I found the mistake.I printed the String "sql" inside the if condition just before "pst=conn.prepareStatement(sql);" of this code.I have given no input in the jtextfield and pressed the delete button.Than the output appears in output screen is
Delete from student where stu_id='' and stu_name='' and Blood_group=''
So the initial value is not null.I tried "" instead of null.But it reamain the same.What is the initial value?

[JamesCherrill]

The text from an empty field is "" - a string of zero length.
You can't test the contents of a string with = or ==, you need equals(...).
The safest test here is to trim off any blank chars and see if anything is left, ie
s[1].trim().length() == 0

[shihab2555]

Thanks a lot.At last my problem is solved.Jamescherill, you again became my life saver.