How to find the selected id in my List<String> ids arraylist?

Question

Here is my code. I am trying to use JUnit to test the deleteUsers() method, but everytime I write my test, it deletes all the users that I have in the database. How can i delete a single user? Below is the code for the method and for the test.

@Override
public boolean deleteUsers(List<String> ids) throws Exception {
    StringBuilder sql = new StringBuilder();
    sql.append("delete from user where ");
    for (String id : ids) {
        sql.append(" id = ? or");
    }
    String strSql = sql.toString().substring(0, sql.length() - 2) + ";";

    PreparedStatement preparedStatement = this.connection.prepareStatement(strSql);

    for (int i = 0; i < ids.size(); i++) {
        preparedStatement.setInt(1 + i, Integer.parseInt(ids.get(i)));
    }

    int lines = preparedStatement.executeUpdate();

    preparedStatement.close();
    return lines > 0;
}

Answer 1

You're missing a check for empty input. In your test you pass an empty list to deleteUsers which results in this SQL statement:

delete from user wher;

I'd expect that the DBMS would reject this as invalid SQL but perhaps there are some where this is interpreted as delete from user which simply deletes all users. (As @SteveBosman pointed out the wher is interpreted as table alias as it is - due to the missing last e - no reserved word anymoere)

Basically you have 2 options. Either deleting all users by passing an empty list is a valid use case - in which case you should handle it properly by producing proper SQL. Or this is not expected and you should adapt your code to throw an Exception if ids is empty.

@Override
public boolean deleteUsers(List<String> ids) throws Exception {
  if (ids == null || ids.size() == 0) {
    throw new IllegalArgumentException("List of IDs must not be empty");
  }
  
  ...
}

You could of course return false in case of an empty input as well to indicate no users were deleted.

To pass values to the deleteUsers method in your test you need to add values to the used list:

userDAOImpl.addUser("admin3", "111222");
final List<String> idsToDelete = new ArrayList<>();
idsToDelete.add("111222");
userDAOImpl.deleteUsers(idsToDelete);