SugarORM write WHERE IN (?) and WHERE NOT IN (?) statements

On my current freelance Android project, I chose SugarORM as the ORM of choice, mostly for the simplicity and ease of getting started with it. Unfortunately, like a lot of open source Android libraries, the documentation for it is pretty modest at best.

Recently, I had to try to write your standard WHERE IN ? and WHERE NOT IN ? statements where the conditions themselves would vary depending on the data that I received from the API. The methods of doing this was not anywhere in the SugarORM documentation, so I thought the best way of doing this would be writing a simple conditional. Unfortunately, this seemingly simple task wasn’t easy and it took me about an hour to make the query work.

Apparently, other developers have ran into similar problems, but since searching through the internets didn’t yield me with a working solution, I’m going to write the solution here in the hopes of saving other developers some valuable time.

So let’s say that you have some sort of Collection like an ArrayList, List, Array, and etc where you would like to use the conditions in that as a conditional in the WHERE IN (?) statement. You would do something like this.

Let’s say that you have a User model that looks like this

public class User extends SugarRecord {
    public String name;
}

You would write a WHERE IN SQL statement with SugarORM like this

ArrayList<String> conditionsList = new ArrayList<>();
conditionsList.add("joe");
conditionsList.add("karen");
conditionsList.add("chris");
StringBuilder queryBuilder = new StringBuilder();
for (int i = 0; i < conditionsList.size(); i++) {
    if (i == 0) {
        queryBuilder.append("(");
    }
    queryBuilder.append("'" + conditionsList.get(i) + "'");
    if (i != conditionsList.size() - 1) {
        queryBuilder.append(",");
    }
    if (i == conditionsList.size() - 1) {
        queryBuilder.append(")");
    }
}
List<User> userList = User.findWithQuery(User.class, "Select * from USER Where name IN " + queryBuilder.toString());

The above snippet should get you the data that you are looking for.

About the Author Chris Jeon

Software developer currently focusing on Android development.