0

I've been trying to solve this issue for the past couple of days. I have a SerachUser function where I input all the data like age, gender, city and interests into each string and check them into a select query command.

If data is present, I print them out.

Unfortunately the search isn't working completely. For eg: my table user doesn't have 'F' gender. But if I type 'F' I still get data instead of displaying "ResultSet in empty in Java".

Below is a brief code I have done.

try{
    conn = DriverManager.getConnection(DB_URL,"shankarv5815","1807985");
    st = conn.createStatement();  
    rs = st.executeQuery("Select loginID, f_name, l_name from users where gender = '" + 
    searchUser.getGender() + "' and age between '" + min + "' and '" + max + "' and city = '" + 
    searchUser.getCity() + "' and interest1 = '" + searchUser.getInterest1() + 
    "' or interest2 = '" + searchUser.getInterest1() + "' or interest3 = '" + 
    searchUser.getInterest1() + "' and loginID != '" + curUser + "'");  



    if (rs.next() == false) {
       System.out.println("ResultSet in empty in Java");
    } 
    else {

          do {
              String id = rs.getString(1);
              String fName = rs.getString(2);
              String lName = rs.getString(3);
              System.out.print(id +" ," + fName + ", " + lName);
              System.out.println();
             } while (rs.next());
         }                
  }
  catch(SQLException e){
       e.printStackTrace();
  }
  finally
  {
    try
      {
       conn.close();
       st.close();
       rs.close();    
      }
    catch(SQLException e)
      {
       e.printStackTrace();
      }           
 }
Vaibhav27
  • 19
  • 8

1 Answers1

2

A reduced version of your query is :

Select * from users
Where gender = 'F'
And interest1 = 'FISHING'
Or interest2 = 'FISHING'

However, AND has higher priority than OR, so this query is equivalent to :

Select * from users
Where ( gender = 'F' And interest1 = 'FISHING')
Or interest2 = 'FISHING'

What you need to do is add brackets, so : 

Select * from users
Where gender = 'F'
And ( interest1 = 'FISHING' Or interest2 = 'FISHING')

By the way, you are also leaving yourself wide open to a SQL injection attack, by including the search terms directly in the SELECT statement ( see What is SQL injection? ).

Much better would be to get in the habit of always using a PreparedStatement.

GMB
  • 216,147
  • 25
  • 84
  • 135
racraman
  • 4,988
  • 1
  • 16
  • 16