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

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

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

1. Your application will be safe from SQL injection.

2. 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.