change password from database according to different id beginnings

Question

i have 3 tables (doctor , nurse , patient) all of them have different id beginnings , doctors id start with 101 , nurse start with 102 , and patient start with 200 . and i want to change the password according to the beginnings of their id . in my JFrame i have 5 JComponents , 4 Jtextfields , 1 Jbutton 1 Jtextfields for the id (name : idField ) 1 Jtextfields for the current password (name : currentPass) 2 Jtextfields for the new password ( name : newPass1 , newPass2) 1 Jbutton for the action ( name : changeButton )

i did 2 different ways in my code , but both did't work with me . can you help me with this problem ?

first way :

private void changeButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             
       id=idField.getText();
       newpass1=newPass1.getText();
       newpass2=newPass2.getText();

        try {
        con = DriverManager.getConnection("jdbc:derby://localhost:1527/hj", "xxx", "xxx");
        st = con.createStatement();

        if (newpass1.equals(newpass2)){


          ResultSet rs = st.executeQuery("update patient set patient_Password="+ newpass1 +" where patient_Id="+id+" and patient_Id like '200%'");  
          JOptionPane.showMessageDialog(this , "Successfully changed", "Patient password successfuly changed !",JOptionPane.PLAIN_MESSAGE);

          ResultSet rs1 = st.executeQuery("update Nurse set nurse_password="+ newpass1 +" where nurse_id="+id+" and nurse_id like '102%'");
            JOptionPane.showMessageDialog(this , "Successfully changed", "Nurse password successfuly changed !",JOptionPane.PLAIN_MESSAGE);

            ResultSet rs2 = st.executeQuery("update doctor set doctor_password="+ newpass1 +" where doctor_id="+id+" and doctor_id like '101%'");
            JOptionPane.showMessageDialog(this , "Successfully changed", "Doctor password successfuly changed !",JOptionPane.PLAIN_MESSAGE);

        } else 
            JOptionPane.showMessageDialog(this , "Not equal", "Your new passwords are not equal!! , try again",JOptionPane.ERROR_MESSAGE );
       }catch (Exception x){
           JOptionPane.showMessageDialog(this, x.getStackTrace());
       }
    }

second way :

 private void changeButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             
       id=idField.getText();
       newpass1=newPass1.getText();
       newpass2=newPass2.getText();

        try {
        con = DriverManager.getConnection("jdbc:derby://localhost:1527/hj", "xxx", "xxx");
        st = con.createStatement();

        if (newpass1.equals(newpass2)){

        if (id.startsWith("200")){
          ResultSet rs = st.executeQuery("update patient set patient_Password="+ newpass1 +" where patient_Id="+id+"");  
          JOptionPane.showMessageDialog(this , "Successfully changed", "Patient password successfuly changed !",JOptionPane.PLAIN_MESSAGE);
        } 
        else if (id.startsWith("102")){
          ResultSet rs = st.executeQuery("update Nurse set nurse_password="+ newpass1 +" where nurse_id="+id+"");
            JOptionPane.showMessageDialog(this , "Successfully changed", "Nurse password successfuly changed !",JOptionPane.PLAIN_MESSAGE);
                }
        else if (id.startsWith("101")){
            ResultSet rs = st.executeQuery("update doctor set doctor_password="+ newpass1 +" where doctor_id="+id+"");
            JOptionPane.showMessageDialog(this , "Successfully changed", "Doctor password successfuly changed !",JOptionPane.PLAIN_MESSAGE);
        }

        } else 
            JOptionPane.showMessageDialog(this , "Not equal", "Your new passwords are not equal!! , try again",JOptionPane.ERROR_MESSAGE );
       }catch (Exception x){
           JOptionPane.showMessageDialog(this, x.getStackTrace());
       }
    }

Answer 1

Do use PreparedStatement!

    if (id.startsWith("200")){
      try (PreparedStatement pstmt = conn.prepareStatement("UPDATE patient SET patient_passwort=? WHERE patient_id=?");) {
          pstmt.setString(1, newpass1);
          pstmt.setString(2, id);
          int rows = pstmt.executeUpdate();

          JOptionPane.showMessageDialog(this , "Successfully changed", 
             "Patient password successfuly changed! (updated rows: "+rows+")", JOptionPane.PLAIN_MESSAGE);
       }
    } 

By concatenating the query, you'll get update patient set patient_Password=abcdefghi where patient_Id=200340 and patient_Id like '200%'. The new password (here abcdefghi) is not quoted, which is mandatory for strings in queries. The patient_id is also not quoted, but it's possibly a number field, which has not to be quoted.

BTW:

• the query part patient_id like '200%' is not needed.

• You should close any PreparedStatement/Statement instances, which can be done by using try-with-resources (try (PreparedStatement xxx = ...) { ... your code } // closes automatically). Same goes for Connection and for ResultSet.

• since the id is an integer, you might want to use it as such: int updId = Integer.parseInt(id); ... pstmt.setInt(2, updId); ...

Tipp: You'll ease your life even more, if you use Apache commons-dbutils. e.g. org.apache.commons.dbutils.QueryRunner

  QueryRunner r = new QueryRunner();
  int rows = r.update(conn, 
       "UPDATE patient SET patient_passwort=? WHERE patient_id=?",
       newpass1, id);