How to detect empty ResultSet from MySQL?

Question

How can I detect an empty result set sent from MySQL if there is no possible result.

Answer 1

Just check if ResultSet#next() returns true. For instance.

 public boolean exist(String username, String password) throws SQLException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; boolean exist = false; try { connection = database.getConnection(); statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)"); statement.setString(1, username); statement.setString(2, password); resultSet = statement.executeQuery(); exist = resultSet.next(); } finally { close(resultSet, statement, connection); } return exist; } 

which you can use for example

 if (userDAO.exist(username, password)) { // Proceed with login? } else { // Show error? } 

In addition, you can also allow it to return fullworhy User or null if none exist. For instance.

 public User find(String username, String password) throws SQLException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; User user = null; try { connection = database.getConnection(); statement = connection.prepareStatement("SELECT id, username, email, dateOfBirth FROM user WHERE username = ? AND password = MD5(?)"); statement.setString(1, username); statement.setString(2, password); resultSet = statement.executeQuery(); if (resultSet.next()) { user = new User( resultSet.getLong("id"), resultSet.getString("username"), resultSet.getString("email"), resultSet.getDate("dateOfBirth")); } } finally { close(resultSet, statement, connection); } return user; } 

with

 User user = userDAO.find(username, password); if (user != null) { // Proceed with login? } else { // Show error? }