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.

CodePudding user response：

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)

CodePudding user response：

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