Room persistance library. Delete all

Question

How can I delete all entries on specific table using Room Persistence Library? I need to drop table, but I cannot to find any information how to do this.

Only when database is migrating or to load all entries and delete them :)

Answer 1

You can create a DAO method to do this.

@Dao 
interface MyDao {
    @Query("DELETE FROM myTableName")
    public void nukeTable();
}

Answer 2

As of Room 1.1.0 you can use clearAllTables() which:

Deletes all rows from all the tables that are registered to this database as entities().

Answer 3

If want to delete an entry from the the table in Room simply call this function,

@Dao
public interface myDao{
    @Delete
    void delete(MyModel model);
}

Update: And if you want to delete complete table, call below function,

  @Query("DELETE FROM MyModel")
  void delete();

Note: Here MyModel is a Table Name.

Answer 4

Use clearAllTables() with RXJava like below inorder to avoid java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.

Completable.fromAction(new Action() {
        @Override
        public void run() throws Exception {
            getRoomDatabase().clearAllTables();
        }
    }).subscribeOn(getSchedulerProvider().io())
            .observeOn(getSchedulerProvider().ui())
            .subscribe(new Action() {
                @Override
                public void run() throws Exception {
                    Log.d(TAG, "--- clearAllTables(): run() ---");
                    getInteractor().setUserAsLoggedOut();
                    getMvpView().openLoginActivity();
                }
            }, new Consumer<Throwable>() {
                @Override
                public void accept(Throwable throwable) throws Exception {
                    Log.d(TAG, "--- clearAllTables(): accept(Throwable throwable) ----");
                    Log.d(TAG, "throwable.getMessage(): "+throwable.getMessage());


                }
            });

Answer 5

I had issues with delete all method when using RxJava to execute this task on background. This is how I finally solved it:

@Dao
interface UserDao {
    @Query("DELETE FROM User")
    fun deleteAll()
}

and

fun deleteAllUsers() {
    return Maybe.fromAction(userDao::deleteAll)
        .subscribeOn(Schedulers.io())
        .observeOn(AndroidSchedulers.mainThread())
        .subscribe ({
            d("database rows cleared: $it")
        }, {
            e(it)
        }).addTo(compositeDisposable)
}

Answer 6

This is how we do it from a Fragment.

fun Fragment.emptyDatabase() {
    viewLifecycleOwner.lifecycleScope.launchWhenCreated {
        withContext(Dispatchers.IO) {
            Database.getInstance(requireActivity()).clearAllTables()
        }
    }
}

If you are emptying the database from an activity use this:

fun Activity.emptyDatabase() {
    // create a scope to access the database from a thread other than the main thread
    val scope = CoroutineScope(Dispatchers.Default)
    scope.launch {
        SitukaDatabase.getInstance(this@emptyDatabase).clearAllTables()
    }
}

It could also be possible to call the clearAllTables method from the main thread. I haven't tried it out but I noticed that Android Studio does not recognize the call as a suspend function.

Answer 7

Combining what Dick Lucas says and adding a reset autoincremental from other StackOverFlow posts, i think this can work:

fun clearAndResetAllTables(): Boolean {
    val db = db ?: return false

    // reset all auto-incrementalValues
    val query = SimpleSQLiteQuery("DELETE FROM sqlite_sequence")

    db.beginTransaction()
    return try {
        db.clearAllTables()
        db.query(query)
        db.setTransactionSuccessful()
        true
    } catch (e: Exception){
        false
    } finally {
        db.endTransaction()
    }
}

Answer 8

Here is how I have done it in Kotlin.

1. Inject room db in the activity using DI (Koin).

    private val appDB: AppDB by inject()
   

2. Then you can simply call clearAllTables()

private fun clearRoomDB() {
    GlobalScope.launch {
        appDB.clearAllTables()
        preferences.put(PreferenceConstants.IS_UPLOADCATEGORIES_SAVED_TO_DB, false)
        preferences.put(PreferenceConstants.IS_MEMBERHANDBOOK_SAVED_TO_DB, false)
    }
}

Answer 9

To make use of the Room without abuse of the @Query annotation first use @Query to select all rows and put them in a list, for example:

@Query("SELECT * FROM your_class_table")

List`<`your_class`>` load_all_your_class();

Put his list into the delete annotation, for example:

@Delete

void deleteAllOfYourTable(List`<`your_class`>` your_class_list);