Room Delete Query Does Not Remove the Row in Database

Android Room Database Delete Query Not Removing Any Rows

As per @Faisal's comment on the question. I was forgetting to run my delete method in a background thread.

I moved my delete code to an AsyncTask and it now works. Here is the repository function task:

private static class deleteAsyncTask extends AsyncTask<Void, Void, Void> {

private MessageDao mAsyncTaskDao;
private String mPhoneNumber = null;

public deleteAsyncTask(MessageDao dao) {
mAsyncTaskDao = dao;
}

public deleteAsyncTask(MessageDao dao, String phoneNumber) {
mAsyncTaskDao = dao;
mPhoneNumber = phoneNumber;
}


@Override
protected Void doInBackground(Void... voids) {
if (mPhoneNumber == null) {
mAsyncTaskDao.deleteAll();
}
else {
mAsyncTaskDao.delete(mPhoneNumber);
}
return null;
}
}

Android Room database - Delete rows after row limit?

in short you are comparing oranges to apples and as none match all are deleted

The subquery SELECT is_active FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000 returns a list of is_active values when is_active is 0. As such unless the is_active contains a value (date) that matches one or more values in the time_created column then the row(s) will deleted.

You need to compare like for like (oranges to oranges) as such you need to extract the time_created column not the is_active column from the subquery for the comparison.

So you should probably be using :-

@Query("DELETE FROM limit_orders where time_created NOT IN (SELECT time_created /*<<<<<<<<<<CHANGED COLUMN*/ FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")
  • you would probably not include the comment /*<<<<<<<<<<CHANGED COLUMN*/, this has only been included to show the change made.
  • if you have not got an index on the time_created column (if the time_created is the primary key column ) it would probably be best to add an index on the column.

However, I suspect the above will not do what you wish/expect.

The subquery will return 1000 rows (or up to 1000), thus the DELETE will delete all other rows (so if the table had 10000 rows you'd be left with 1000 rows (assuming unique time_created column, as discussed below)).

Rather I suspect that you want to DELETE the 1000 inactive (assuming 0 - inactive) rows thus really you want to delete the 1000 rows that have been extracted so instead of NOT IN you would want IN.

Note that if the time_created column is not UNIQUE (implied UNQIUE if it is the PRIMARY KEY @PrimaryKey in room) then other rows could be deleted. If rows can have the same time_created then you could use the rowid column instead.

  • The rowid column is a column that always exists (except for WITHOUT ROWID tables which room doesn't cater for via annotations) but is hidden. You can always refer to it though. It will always be unique.

  • So using the sure-fire only delete the 1000 rows you could have :-

  • @Query("DELETE FROM limit_orders where rowid IN (SELECT rowid FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")

Or do you want

re-reading your question is sounds as though you may want to preserve 1000 inactive and delete the other inactive rows. Thus preserving 100 inactive rows along with all active rows.

If this is the case then the initial answer is close except that you want to also(AND) preserve the active.

As such what you would want is an additional condition to preserve the active rows:-

@Query("DELETE FROM limit_orders WHERE time_created NOT IN (SELECT time_created /*<<<<<<<<<<CHANGED COLUMN*/ FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000) /*PRESERVE ACTIVEs >>>>>>>>>>*/ AND is_active <> 0")

Android Room @Delete with parameters

The beauty of room is, we play with the objects. As per requirement you can use
for kotlin:

@Delete
fun delete(model: LanguageModel)

for Java:

@Delete
void delete(LanguageModel model)

it will delete the exact object which is stored in the db with the same values. LanguageModel is my model class and it works perfectly.

Room database delete query

UPDATE or DELETE queries can return void or int. If it is an int, the value is the number of rows affected by this query

So you want delete you can use like this

@Query("DELETE FROM tableArticle WHERE titleOfAccess = :title")
abstract void deleteByTitle(String title);

or you can delete by model (if available)

@Delete()
void delete(Details details);

How do I remove a row from recyclerview using room?

Follow my simple steps to solve your issue,

Step 1:
Check once CustomApplication name mentioned or not in AndroidManifest.xml,

<application
android:name=".CustomApplication"

otherwise you get this issue

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.myapplication/com.example.myapplication.AddressActivity}: java.lang.ClassCastException: android.app.Application cannot be cast to com.example.myapplication.CustomApplication

Step 2:
Check your module level build.gradle file

apply this changes

apply plugin: 'kotlin-android'
apply plugin: 'kotlin-android-extensions'
apply plugin: 'kotlin-kapt'

check dependencies -- For Kotlin use kapt instead of annotationProcessor

implementation "androidx.room:room-runtime:2.2.5"
kapt "androidx.room:room-compiler:2.2.5"

otherwise you get this issue

java.lang.RuntimeException: cannot find implementation for com.example.myapplication.Database. Database_Impl does not exist

Step 3:
check your AddressDao interface, add this function

 @Delete
suspend fun deleteAddress(address: Address)

Step 4:

in AddressAdapter class, add this listener,

interface ItemListener {
fun onItemClicked(address: Address, position: Int)
}

add listener variable and setListener function

private lateinit var listener: ItemListener

interface ItemListener {
fun onItemClicked(address: Address, position: Int)
}

fun setListener(listener: ItemListener) {
this.listener = listener;
}

then update your code in tvDelete.setOnClickListener method

    viewHolder.tvDelete.setOnClickListener(View.OnClickListener { view ->
mItemManger.removeShownLayouts(viewHolder.swipelayout)
addresses.removeAt(position)

listener.onItemClicked(fl, position)

notifyDataSetChanged()
// notifyItemRemoved(position)
// notifyItemRangeChanged(position, addresses.size)
mItemManger.closeAllItems()
Toast.makeText(
view.context,
"Deleted " + viewHolder.tv.getText().toString(),
Toast.LENGTH_SHORT
).show()
})

Step 5:
In AddressActivity class, do this changes

Implement listener here,

class AddressActivity : AppCompatActivity(), AddressAdapter.ItemListener {

then override method

override fun onItemClicked(address: Address, position: Int) {

}

then set listener for adapter

        recyclerView.layoutManager = LinearLayoutManager(this, LinearLayoutManager.VERTICAL, false)
recyclerView.adapter = adapter
adapter.setListener(this)

then update code in override method

override fun onItemClicked(address: Address, position: Int) {
lifecycleScope.launch {
val application = application as CustomApplication
application.database.AddressDao().deleteAddress(address)
}
}

here I used coroutine otherwise you can use AsycTask also

for coroutine add this dependencies in your module build.gradle file

implementation "android.arch.lifecycle:extensions:1.1.1"
kapt "android.arch.lifecycle:compiler:1.1.1"
implementation "androidx.lifecycle:lifecycle-runtime-ktx:2.2.0"
implementation 'org.jetbrains.kotlinx:kotlinx-coroutines-android:1.3.0'

if you directly called deleteAddress method in UI class, you get this issue

java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.

so use such methods in background thread,

If you really want to execute in main UI thread, do this changes in your code

in AddressDao interface,

@Delete
fun deleteAddress(address: Address)

in CustomApplication class, add allowMainThreadQueries()

class CustomApplication : Application() {
lateinit var database: Database
private set
lateinit var addressDao: AddressDao
private set

override fun onCreate() {
super.onCreate()

this.database = Room.databaseBuilder<Database>(
applicationContext,
Database::class.java, "database"
).allowMainThreadQueries().build()
addressDao = database.AddressDao()
}
}


Related Topics



Leave a reply



Submit