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.

[urtrivedi]

delete must be done on primary key only, if you dont have primary key, then u must add one, i guess stud_id is good choice for pk,

then use only stud_id in where condtion to delte record

[shihab2555]

yeah stu_id is the primary key.But what if i want to delete all the row contain blood_group=B+ than what should i do?

[urtrivedi]

then dont use stud id, just use only condtion
blood_group='B+'

more condtion u add, less record will be deleted, if u add stud_id in condtion only 1 record will delete

[shihab2555]

I think you don't understand my ques.Did you read may java code? I know the query.(For example "Delete from student where Blood_group='B+'" or "Delete from student where Blood_group='B+' and stu_name='mark'").But every time i can't change my query.Sometime i want to delete the row containing Blood_group='B+'.again sometime i want to delete the row containing Blood_group='B+' AND stu_name='mark'.What will be the query.I tried to change the query the way i did in my second code.But it didn't work

[urtrivedi]

I hope this helps

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();  
}

[mlesniak]

I think urtrivedi's solution is good.
I would suggest changing the user inputs by adding a drop-down list of delete options containing 1. by stu_id, 2. by blood group, 3. by Student name, 4. both Student name and blood group. Then build the sql statement according to the user's selection, as you will know whether to prompt for one, two or three criteria. (these may not be the exact options that you require, but I hope you get the idea.) You can use a switch statement to choose which code to execute.

[shihab2555]

To mlesniak
Sorry to say your idea will not work.Because i will convert this table to 10 column.So this won't work.

[shihab2555]

To urtrivedi
Your method in fantastic.But sorry to say it also didn't work.But I found the mistake.I printed the String "sql" inside the if condition in 27th line of your 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.

[shihab2555]

Problem solved.I used s[1].trim().length() != 0 instead of s[1]!=null . Thanks for your help