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
How to Make a Link Open an Instagram Page in an App Installed on Android
How to Transfer from One Fragment to Another in Android
Refresh/Reload/Re Instantiate a Fragment in an Android Tab System
Android Studio - Failed to Apply Plugin [Id 'Com.Android.Application']
How to Fix White Screen on App Start Up
Android No on Screen Keybord ( Only Voice )
Limitations on Opening Pdf File in Android
Full Screen Without Navigation & Status Bars
How to Dismiss Notification After Action Has Been Clicked
Could Not Resolve All Dependencies for Configuration ':Classpath'
Illegalstateexception: Link Does Not Have a Navcontroller Set
Send Post Request With Json Data Using Volley
Difference Between Add(), Replace(), and Addtobackstack()
How to Detect When the App Goes to Minimized or Exit
Could Not Connect to React Native Development Server on Android