Why does this Query return NULL?

Question

I have a derby users database which I query, when the user clicks login on the application.

However, when I query the users table with the parameter [user] derby returns a null Object instead of the record it ought to return.

Here is my code:

String ssql = "SELECT * FROM USERS WHERE UNAME LIKE ?";
   try{
       DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
       con = DriverManager.getConnection(url);
       sql = con.prepareStatement(ssql, Statement.RETURN_GENERATED_KEYS);
       sql.setString(1, cbox_chooseUser.getSelectedItem().toString());
       sql.executeQuery();
       ResultSet rs = sql.getGeneratedKeys();
           try{
              while (rs.next()) {
                 if(rs.getString("PW").toCharArray().equals(txt_password.getPassword())){
                     sql.close();
                     con.close();
                     return true;
                  }
             } catch (NPE ...) {...}
    }

I tried it multiple times wit a test user with both the pw and the username set to "test"; but I always get the same error. Why is the recordset always Null?

Thanks for your help :)

executeQuery already returns the resultset. And: You should not use "Select *". You want "PW" so "Select PW" ... — Fildor, Feb 23 '17 at 08:40
Which keys would be generated from a SELECT statement ? Did you mean `ResultSet rs = sql.executeQuery();` ? — Arnaud, Feb 23 '17 at 08:43
Outside that: You are not saving passwords in clear text, do you? http://stackoverflow.com/a/1054033/982149 — Fildor, Feb 23 '17 at 08:52
*FYI:* `toCharArray()` returns a `char[]` and `equals()` doesn't work on arrays. The `if` statement will always be false. — Andreas, Feb 23 '17 at 08:58

score 4 · Accepted Answer · answered Feb 23 '17 at 08:44

The documentation says

ResultSet getGeneratedKeys() throws SQLException

Retrieves any auto-generated keys created as a result of executing this Statement object.

If this Statement object did not generate any keys, an empty ResultSet object is returned.

Your select statement isn't generating any keys that's why it's returning an empty ResultSet. You aren't inserting anything hence no keys are being generated.

You can try ResultSet rs = sql.executeQuery();. It should work.

score 1 · Answer 2 · answered Feb 23 '17 at 09:38

You are using it in wrong way.

The generated keys concept should be used only in the case DML of insert type query but not in the case of select query.

select simply select the rows from the table. In this case there is no chance of any keys getting generated.

In the case of insert query if any column is configured as auto increment or kind of functionality then some keys will get generated. These keys can be caught using Statement.RETURN_GENERATED_KEYS in java.

As you are using select query there is no need of using Statement.RETURN_GENERATED_KEYS.

You just modify below lines and everything will be fine.

sql = con.prepareStatement(ssql, Statement.RETURN_GENERATED_KEYS);
sql.setString(1, cbox_chooseUser.getSelectedItem().toString());
sql.executeQuery();
ResultSet rs = sql.getGeneratedKeys();

with

sql = con.prepareStatement( ssql );
sql.setString( 1, cbox_chooseUser.getSelectedItem().toString() );
ResultSet rs = sql.executeQuery();

Why does this Query return NULL?

2 Answers2