SQLite - How to efficiently load data which contains in id list

Question

Let's consider below function,

data class ModelData(var id: String?, var name: String?) {

}

fun loadData(ids: List<String>): List<ModelData> {

    // Here, I have to return list of model data which matches with ids

}

I can do it with a loop which seems very inefficient.

fun loadData(ids: List<String>): List<ModelData> {
    val list = List<ModelData>
    val selection = DBKeys.MODEL_ID + " LIKE ?"
    val selectionArgs = arrayOf<String>("")
    for (id in ids) {
        val selectionArg = arrayOf<String>(id)
        val cursor = DBManager.query(TABLE_RECORD, selection, selectionArgs, null, null, null)
        // prepare model 'data' from cursor
        list.add(data)
    }
    return list
}

I wonder is there any other efficient way? It would be better if there were SQLite selection query with contains operation.

Update

From the suggested post by ADM, I found example,

String[] args = new String[]{A,B} // if A, B are variables
String[] args = new String[]{"A","B"}    
Cursor cur = db.query("tab1", null, "name in (?,?)", args, null, null, null);

I wonder if the variables are from list, is there any way to prepare sanitized input it without using loop?

You can use [`SQLite IN`](http://www.sqlitetutorial.net/sqlite-in/). — ADM, Jun 26 '19 at 04:38
@ADM could you explain in the answer a little bit more with syntax? Thanks — Sazzad Hissain Khan, Jun 26 '19 at 04:40
Are you loading into a list view? Why not use a cursor adapter? — Haider Malik, Jun 26 '19 at 04:43
[Here](https://stackoverflow.com/questions/16295432/sqlite-in-operator-in-query) is an example . — ADM, Jun 26 '19 at 04:43
@HaiderMalik No I don't need show in UI. Only to load and process. — Sazzad Hissain Khan, Jun 26 '19 at 04:44
@ADM Thanks a lot. I still need a loop to prepare the sanitized arguments from ids right? — Sazzad Hissain Khan, Jun 26 '19 at 04:44
@ADM your clue solves my problem. You can post it in answer so that i can select as correct answer. — Sazzad Hissain Khan, Jun 27 '19 at 14:08

score 0 · Accepted Answer · answered Jul 01 '19 at 02:38

As per @ADM s clue in the comment, it is possible to use IN operation in SQLite database. Here is an example,

val args = arrayOf("1", "2") // static
val cursor = db.query("records", null, "_ID in (?, ?)", args, null, null)

Most of the times you need to prepare args from a variable length of list. In that case you can apply below trick. Let's say ids is a list of string IDs with whom database IDs need to be matched.

val selection = "_ID IN (${ids.joinToString { "\"$it\"" }})"  // dynamic
val cursor = db.query("records", null, selection, null, null, null)

SQLite - How to efficiently load data which contains in id list

Update

1 Answers1