JDBC problem..please help, my project is due.i have no help

Question

This is a function that returns the minimum doctor id from doctor table after accepting a string s that is a consultation field in patient table. for eg , if in the form i wrote " cardiology", then it will return the minimum doctor id relating to the that field.also a doctor is free or not is decided by its current status. by default its no << that is its free>> and will be changed to yes after he has been alloted i want u all to look into this funtion coz there is a problem in the sql statements . thank you so much

public int getDocID(String s )
 {
  int did = 0;
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con = DriverManager.getConnection("jdbc:odbc:patientDSN");
   Statement stat = con.createStatement();
   ResultSet rs = stat.executeQuery("select min(Doc_ID) from Doctor where (Doc_CurrentStatus='No' and Doc_Speciality like '%"+s+"'%'");
   if(rs.next())
   {
    did = rs.getInt(1);
   }
            System.out.println(did);
   PreparedStatement ps1 = con.prepareStatement("UPDATE Doctor SET Doc_CurrentStatus='Yes' where Doc_ID = "+did+"");
            ps1.executeUpdate();
     }

  catch(Exception e)
  {e.printStackTrace();}
  return did;
 }

Answer 1

You have one

'

quote more than what you need. It's before the last

"%"

percentage symbol. You must close the last parenthesis too. You may want to take a look at PrepareStatement. Link to Java Tutorial

   ResultSet rs = stat.executeQuery(
   "select min(Doc_ID) from Doctor where (
        Doc_CurrentStatus='No' and Doc_Speciality like '%"+s+"%'
      )"
  );

You can perform this query:

select Doc_ID from Doctor where (Doc_CurrentStatus='No' and Doc_Speciality like '%"+s+"%') ORDER BY Doc_ID ASC LIMIT 1");

Can improve peformance.

Answer 2

sorry for the late reply,i didn't use order by clause.i used the first correction u suggested :-) Thanks again!

Answer 3

sorry but i cant find the check mark outline:(