With android Room, how to delete items more than 1000?

Question

got crash when the ids is > 999

android.database.sqlite.SQLiteException: too many SQL variables (code 1): , 
while compiling: delete from data where ids in (?,?,...)

saw this seems there is max limit to 999.

how to delete more than 1000 with Room?

Answer 1

Probably you have a list of ids to delete. Open a transaction, split the list in sublist and execute the SQL delete operation once for sublist.

For more information about Room the official documentation about Transactions with Room.

I didn't test the following code, but I think that it accomplishes your need.

@Dao
public interface DataDao {
    @Delete("delete from data where ids in :filterValues")
    long delete(List<String> filterValues)

    @Transaction
    public void deleteData(List<Data> dataToDelete) {
         // split the array in list of 100 elements (change as you prefer but < 999)
         List<List<Data>> subLists=DeleteHelper.chopped(dataToDelete, 100);
         List<String> ids=new ArrayList<>();

         for (List<Data> list: subList) {
           list.clear();
           for (Data item: list) {
              ids.add(item.getId());
           } 

           delete(ids);
         }

    }    
}

public abstract class DeleteHelper {
   // chops a list into non-view sublists of length L
   public static <T> List<List<T>> chopped(List<T> list, final int L) {
       List<List<T>> parts = new ArrayList<List<T>>();
       final int N = list.size();
       for (int i = 0; i < N; i += L) {
         parts.add(new ArrayList<T>(
            list.subList(i, Math.min(N, i + L)))
         );
    }
    return parts;
  }
}

I hope this help.

Answer 2

I think there are two ways to solve it.

First, chop chop your list and runs multiple times with delete method. (just like @xcesco answered)

Second, you can write very long query and run it with @RawQuery.

@RawQuery
abstract int simpleRawQuery(SupportSQLiteQuery sqliteQuery)

@Transaction
public int deleteData(List<Long> pkList) {
   SimpleSQLiteQuery query = new SimpleSQLiteQuery("DELETE FROM tb WHERE _id IN (" + StringUtils.join(pkList,",") + ")";
   return simpleRawQuery(query) 
}