I have a table in my SQLite
database:
Species:
_id | species | description
---------------------------
1 | Aardvark | Some description for Aardvark
2 | Aardwolf | Some description for Aardwolf
3 | Caracal | Some description for Caracal
I receive a list of ids from the server and I want to display only the species corresponding to the ids I received.
Now I am aware of a few options:
1. The obvious and naive way of doing this is something along the lines of:
SQLiteDatabase db = this.openDatabase();
for (int id : idList) {
Cursor cursorSpecies = db.query(true, TABLE_SPECIES, new String[] {COL_SPECIES_SPECIES},
COL_ID + "=?", id, null, null, null, null);
cursorSpecies.moveToNext();
speciesList.add(cursorSpecies.getString(0));
cursorSpecies.close();
}
This will execute way too many operations and I assume multiple small "disk" reads, which would be very slow.
2. Another option would be to use an SQLiteStatement but this only returns a single value, which doesn't work for my example and shouldn't really be used for queries anyway.
3. Yet another option would be to manually concatenate the conditions into a raw SQL
query, something along the lines of:
SQLiteDatabase db = this.openDatabase();
String query = "SELECT * FROM " + TABLE_SPECIES + " WHERE ";
for (int id : idList) {
query += COL_ID + "=" + id + " OR ";
}
// I am aware this will end in an " OR" but this is not the point of this example so please ignore it.
Cursor cursorSpecies = db.rawQuery(query, null);
// Use the cursor and close it.
While this should work decently well, a very large query would probably break some query string length limit so this is not ideal either.
All of these examples would work to a certain extent but they all have pitfalls. Somehow I feel that I am missing a solution to this, hence the question:
What is the proper way of executing this type of query?
Thank you.