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);

Answer 2

The problem is caused by how the SQL is built. When deleteUsers is passed an empty list then the generated SQL will be:

 delete from user wher

which will result in all data being deleted (the table user is given the alias "wher"). I highly recommend checking at the start of the method if the collection is empty and either raising an exception or returning.

Add the following check

if (ids == null || ids.isEmpty()) {
  throw new IllegalArgumentException("ids must not be empty");
}

Answer 3

StringBuilder sql = new StringBuilder();
sql.append("delete from user where");
String orClause = "";
for (String id : ids) {
    sql.append(orClause);
    sql.append(" id = ?");
    orClause = " or";
}