Room database RawQuery() is not work on "IN" and "NOT IN" clause

Question

I have my one table like UserTable.

@Entity
public class UserTable{
  @PrimaryKey(autoGenerate = true)
    private int userId;
    private String userName;
    private String userEmailId;

  // Below code is getter and setter of this class.
}

@Dao
public interface UserDao {
    @Query("SELECT * FROM userTable")
    public List<UserTable> loadAllUsers();

    @Insert
    public long insertUserTable(UserTable userTable);

    @Insert
    public long[] insertUserTables(UserTable... userTables);

    @Update
    public int updateUserTable(UserTable userTable);

    @Delete
    public int deleteUserTable(UserTable userTable);

    @RawQuery
    public abstract List<UserTable> loadAllUserListByGivenIds
            (SupportSQLiteQuery query);

    public default List<UserTable> loadAllUserListByIds(long[] userIds) {
        List<UserTable> list;
        ArrayList<Object> argsList = new ArrayList<>();
        String selectQuery = "SELECT  * FROM UserTable WHERE userId IN (?);";
        argsList.add(userIds);
        
        SimpleSQLiteQuery simpleSQLiteQuery = new SimpleSQLiteQuery(selectQuery, argsList.toArray());
        list = loadAllUserListByGivenIds(simpleSQLiteQuery);
        return list;
    }
}

// Now in My MainActivity.class file, I have use following code:

 List<UserTable> userList= databaseClient
                .getAppDatabase()
                .userDao()
                .loadAllUserListByIds(new long[]{1L,2L});

My query is running in normal database, but when I was pass array of user ids then, in @RawQuery() method of dao class is not supported for "IN" clause used in where condition "WHERE userId IN (?)".

How, I will use "IN" clause in @RawQuery() of room database.

Answer 1

We can do it in kotlin in the more simpler way.

1. Let's create two helper methos

   object Helper {
      fun sqlIn(list: List<Any>, bindArgs: MutableList<Any>): String {
         bindArgs.apply { this.addAll(list) }
   
         return "IN (${list.joinToString(",") { "?" }})"
      }
   
      fun sqlNotIn(list: List<Any>, bindArgs: MutableList<Any>): String = "NOT ${sqlIn(list, bindArgs)}"
   }
   

2. Then you can use it in anywhere else

   val ids = listOf(1, 2, 3)
   val ownerId = 10
   
   val bindArgs = mutableListOf<Any>()
   val query = "SELECT * FROM posts WHERE id ${Helper.sqlIn(ids, bindArgs)} AND owner_id = ?"
   bindArgs.add(ownerId)
   
   dao.query(
       SimpleSQLiteQuery(query, bindArgs.toTypedArray())
   )
   

Answer 2

Much easier to use an @Query it's as simple as:-

@Query("SELECT * FROM UserTable WHERE userId IN (:idList)")
public List<UserTable> getWhatever(long[] idList);

You'd then use getWhatever(new long[]{1L,2L})

If you need it an @rawQuery though you could do it like (used previous answer code for my convenience) :-

private List<TableXEntity> loadAllUserListByIds(int order,long[] idList) {
    StringBuilder idListAsCSV = new StringBuilder(); //<<<<<<<<<<
    boolean afterFirst = false; //<<<<<<<<<<
    //<<<<<<<<<< all of the loop to create the CSV
    for (Long l: idList) {
        if (afterFirst) {
            idListAsCSV.append(",");
        }
        afterFirst = true;
        idListAsCSV.append(String.valueOf(l));
    }
    StringBuilder sb = new StringBuilder("SELECT * FROM ").append(DBHelper.TableX.NAME);
    sb.append(" WHERE " + DBHelper.TableX.COLUMN_ID + " IN(").append(idListAsCSV).append(") "); //<<<<<<<<<<
    switch (order) {
        case DBHelper.TableX.FIRSTNAME_DESCENDING:
            sb.append(DBHelper.TableX.ORDER_BY_FIRSTNAME_DESC);
            break;
        case DBHelper.TableX.FIRSTNAME_ASCENDING:
            sb.append(DBHelper.TableX.ORDER_BY_FIRSTNAME_ASC);
            break;
        case DBHelper.TableX.LASTNAME_DESCENDING:
            sb.append(DBHelper.TableX.ORDER_BY_LASTNAME_DESC);
            break;
        case DBHelper.TableX.LASTNAME_ASCENDING:
            sb.append(DBHelper.TableX.ORDER_BY_LASTNAME_ASC);
            break;
        default:
            break;
    }
    sb.append(";");
    return roomDao.rawq(new SimpleSQLiteQuery(sb.toString(),null));
}

i.e. provide a CSV (although I vaguely recall being able to pass an array)

---

To use bind arguments (the recommended way as binding arguments protects against SQL injection) then you need a ? for each value and a corresponding array of objects.

So for 3 id's you need IN(?,?,?) and the actual values, the bind arguments, in an Object[]. The following is an example that does this noting that it shows 2 ways of building the Object[] (the bind arguments/values):-

private List<TableXEntity> loadByidList(long[] idlist) {
    List<Object> bindargs = new ArrayList<>(); // way 1
    Object[] args4Bind = new Object[idlist.length]; // way 2
    StringBuilder placeholders = new StringBuilder(); // for the ? placeholders
    /* Build the sql before the place holders */
    StringBuilder sql = new StringBuilder("SELECT * FROM ")
            .append(DBHelper.TableX.NAME)
            .append(" WHERE ")
            .append(DBHelper.TableX.COLUMN_ID)
            .append(" IN (");
    boolean afterfirst = false;
    int i = 0; /* using for each so have index counter (as opposed to for(int i=0 ....) */
    for (long l: idlist) {
        bindargs.add(l); // for way 1
        args4Bind[i++] = String.valueOf(l); // for way 2
        if (afterfirst) {
            placeholders.append(",");
        }
        afterfirst = true;
        placeholders.append("?");
    }
    /* finalise the SQL */
    sql.append(placeholders.toString())
            .append(");");
    //return roomDao.rawq(new SimpleSQLiteQuery(sql.toString(),bindargs.toArray())); // way 1
    return roomDao.rawq(new SimpleSQLiteQuery(sql.toString(),args4Bind)); // way 2
}

Answer 3

Please try this, here it has working!

Try this simple trick to pass the arguments for IN operator-

List<Object> argList = new ArrayList<>();
 argList.add("3");
 argList.add("6");

    

Then prepare your raw query string:

Note- Match your argument list size with '?' size

   String selectQuery = "SELECT * FROM task WHERE id IN (?,?)";

After this pass the raw query string to SimpleSQLiteQuery-

   SimpleSQLiteQuery rawQuery = new SimpleSQLiteQuery(selectQuery, args.toArray());

Then fetch the List using DAO:

List<UserTable> taskList1=DatabaseClient
                        .getInstance(getApplicationContext())
                        .getAppDatabase()
                        .userTableDAO()
                        .getAllList(query);