Result set from sqlite query does not have a rs.next()

Question

In my code, I am trying to get a user with the username = "Jason" and has a password of "1234". However, the query that I am using is not returning any values in Java. On the other hand, when I write the same query on my SQL bench it returns a row.

    @FXML
    private TextField tf_username;

    @FXML
    private TextField pf_password;

    @FXML
    void logIn(MouseEvent event) throws SQLException, IOException {
       String username = tf_username.getText();
       String password = pf_password.getText();
       String checkPassSql = "select * from users where username" + " = '"+username+"'and password = '"+password+"' ";
       
       Connection connection = DbConnect.getInstance().getConnection();
       
       Statement statement = connection.createStatement();
       
       ResultSet rs = statement.executeQuery(checkPassSql);
              
       
       if(rs.next()){
        //change the next line from signUp scene to home scene  
        Parent root = FXMLLoader.load(getClass().getResource("home.fxml"));
        Node node = (Node) event.getSource();
        Stage stage = (Stage) node.getScene().getWindow();
        stage.setScene(new Scene(root)); 
       }
       
       
       
    }

This is the expected result set from the SQL workbench

Try changing your SQL query to: `select count(*) from users` and see what value it returns. If it returns a number greater than one, then try this query: `select username from users` — Abra, Jul 25 '20 at 16:02
How do I print what is returned by the SQL query in Java? select count(*) from users and it returns true. — Jason Sutanto, Jul 25 '20 at 16:08
_How do I print what is returned by the SQL query in Java?_ `System.out.println(rs.next().getInt(1))` — Abra, Jul 25 '20 at 16:13
If you have not already done so, take a look at this [introduction to jdbc](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html). Also, use a [prepared statement](https://stackoverflow.com/questions/2099425/when-should-we-use-a-preparedstatement-instead-of-a-statement). — andrewJames, Jul 25 '20 at 16:24

score 0 · Answer 1 · answered Jul 25 '20 at 19:21

First of all, you should use PreparedStatement instead of Statement for a parameterized query. Two important advantages of doing so will be:

Your application will be safe from SQL injection.

You do not need to put ' around the text values explicitly.

String sql = "SELECT * FROM users WHERE username=? AND password=?";

PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, username);
preparedStatement.setString(2, password);

ResultSet rs = preparedStatement.executeQuery();

Where does your code seem to be failing?

If you execute the following code,

public class Main {
    public static void main(String[] args) {
        String username = "Jason";
        String password = "1234";
        String sql = "select * from users where username" + " = '" + username + "'and password = '" + password + "' ";
        System.out.println(sql);
    }
}

you will get the following output:

select * from users where username = 'Jason'and password = '1234'

which is different from what you have executed on SQL workbench. In order to match it, your code should have been as follows:

String sql = "select * from users where username" + " = \"" + username + "\" and password = \"" + password
                + "\"";

That would have been converted into the following:

select * from users where username = "Jason" and password = "1234"

which is same as what you've tested on SQL workbench.

However, as I've already mentioned earlier, use PreparedStatement instead of Statement.

Result set from sqlite query does not have a rs.next()

1 Answers1

Where does your code seem to be failing?