CODEWITHSUNDEEP
androidandroid-room
ip696
ip696

Reputation: 7104

room update (or insert if not exist) rows and return count changed rows

I need update and if not exist insert row to ROOM DB.

I make this: productRepository.updateProducts(productsResponse.getProductItems());

And:

@Override
public void updateProducts(final List<ProductItem> products) {
    new Thread(() -> {
        for (ProductItem item : products) {
            Product product = createProduct(item);
            productDao.insert(product);
        }
    }).start();
}

And in DAO:

@Insert
void insert(Product products);

But I have method 

@Update
void update(Product product);

And I have some questions:

  1. both methods is void. How can I return saved Product or boolean flag or inserted count after insert?

  2. if I try call update and I have not row will it be inserted?

  3. How can I update(if not - insert) row and return count updatet or inserted rows?

Upvotes: 81

Views: 124912

Answers (10)

Dan Alboteanu
Dan Alboteanu

Reputation: 10252

You can do with @Upsert

def room_version = "2.5.0-beta01" // or later
implementation "androidx.room:room-runtime:$room_version"
implementation "androidx.room:room-ktx:$room_version"


@Upsert(entity = Show::class)
fun updateOrInsert(shows: List<ShowUpdate>)

@Entity
data class Show(
@PrimaryKey var id: Int = 0,
val title: String? = null,
val imageUrl: String? = null,
val timestamp: Long = System.currentTimeMillis(),
val isFavorite: Boolean? = false
)

This updates only some fields of Show. Does NOT update the field isFavorite

@Entity
data class ShowUpdate(
@PrimaryKey var id: Int = 0,
val title: String? = null,
val imageUrl: String? = null,
val timestamp: Long = System.currentTimeMillis()
)

Upvotes: 9

AmirMohamamd
AmirMohamamd

Reputation: 412

you can use INSERT OR REPLACE in the Room ORM, i wrote a query for my self and it work, write like my query :

@Query(
    "INSERT OR REPLACE INTO VolumeManagement (id, max_volume, min_volume, stream_type, id_user) " +
            "VALUES ((SELECT id FROM VolumeManagement WHERE id_user = :userId AND stream_type = :streamType)," +
            " :newMaxVolume, :newMinVolume, :streamType, :userId)"
)
suspend fun updateOrInsertVolume(newMaxVolume: Int, newMinVolume: Int, streamType: Int, userId: String): Long

consider that my userId and streamType are unique

Upvotes: 0

Umy
Umy

Reputation: 573

UPDATE.

@Upsert added to Room 2.5.0-alpha03. No need for reinventing the wheel)

Thanks to @Ashwini for great idea.

@Dao
interface SendDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(model: DataModel): Long

    @Update
    suspend fun update(model: DataModel): Int

    @Transaction
    suspend fun insertOrUpdate(model: DataModel): Long {
        val id = insert(model)
        return if (id==-1L) {
            update(model)
            model.id
        } else {
            id
        }
    }
}

So we should call: database.sendDao().insertOrUpdate(DataModel(...))

Upvotes: 11

cinpeCan
cinpeCan

Reputation: 1

@Query("SELECT * FROM pojo WHERE pojo.id = :id")
Maybe<POJO> checkPOJO(String id);

you can insertOrUpdate like this:

checkPOJO(id).toSingle().concatMapCompletable(pojo->update(pojo))
   .onErrorResumeNext(t -> t instanceof NoSuchElementException ? 
   insert(...): Completable.error(t));

Upvotes: 0

Rainmaker
Rainmaker

Reputation: 11110

As @Danail Alexiev said @Insert can return a long. @Update can return an int.

But for what purpose are you using update? If you just want the whole object to be replaced by the new one then just specify the OnConflictStrategy

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insert(Product products);

The result will be: items that don't exist will be added, items that already exist will be replaced by the new ones.

In the case you need to update just one param (like quantity for example) you can do something like this

  @Insert(onConflict = OnConflictStrategy.REPLACE)
  void insert(Product products);

  @Query("SELECT * from item WHERE id= :id")
  List<Product> getItemById(int id);

  @Query("UPDATE item SET quantity = quantity + 1 WHERE id = :id")
  void updateQuantity(int id)

  void insertOrUpdate(Product item) {
                List<Product> itemsFromDB = getItemById(item.getId())
                if (itemsFromDB.isEmpty())
                    insert(item)
                else
                    updateQuantity(item.getId())   
            }
        }

The result will be: Try looking up the item in the DB, if found update a property, if not just insert a new item. So you only need to call one method insertOrUpdate from your DAO.

Upvotes: 100

Rahul Rathore
Rahul Rathore

Reputation: 807

You also can check if the entry exists in the database or not you can make your logic accordingly

Kotlin

@Query("SELECT * FROM Product WHERE productId == :id")
fun isProductExist(id: Int): Boolean

Java

@Query("SELECT * FROM Product WHERE productId == :id")
public boolean isExist(int id);

Upvotes: -4

Keyu Zala
Keyu Zala

Reputation: 11

You can check below method insertModel() where you can get onComplete() and onError() method:

    val db: AppDatabase = Room.databaseBuilder(mCtx, AppDatabase::class.java, "db_nam.sqlite").build()

    companion object {
        @SuppressLint("StaticFieldLeak")
        private var mInstance: DatabaseClient? = null

        @Synchronized
        fun getInstance(mCtx: Context): DatabaseClient {
            if (mInstance == null) {
                mInstance = DatabaseClient(mCtx)
            }
            return mInstance as DatabaseClient
        }
    }

    // SEE HERE FOR INSERTING DATA SUCCESSFULLY AND ERROR CODE  
    private fun insertModel(rss_Model: RSS_Model) {
        Completable.fromAction {

            db.database_dao().insertRSS(rss_Model)

        }.observeOn(AndroidSchedulers.mainThread())
                .subscribeOn(Schedulers.io()).subscribe(object : CompletableObserver {
                    override fun onSubscribe(d: Disposable) {}

                    override fun onComplete() {
                        // Log.e("onComplete","GET SUCCESS HERE")
                    }

                    override fun onError(e: Throwable) {
                        // Log.e("onError","onError")
                    }
                })
    }

Upvotes: 1

BaDo
BaDo

Reputation: 578

You should use Rx Android Single to solve this problem. Example:

@Query("SELECT * FROM subjectFormTable WHERE study_subject_id ==:subjectFormId")
fun getSubjectForm(subjectFormId: Int): Single<SubjectFormEntity>

We use 

val compositeDisposable = CompositeDisposable()

And

compositeDisposable.add(
            subjectFormDao.getSubjectForm(studySubjectId)
            .subscribeOn(Schedulers.io())
            .observeOn(AndroidSchedulers.mainThread())
            .subscribe({
                Log.e(TAG, "successful ")
            }, {
                Log.e(TAG, "error "+it.message)
                //Insert or Update data here
            })
    )

Upvotes: -11

DawidJ
DawidJ

Reputation: 1265

You can check your database if there is already item with specific field, for exmaple:

@Query("SELECT id FROM items WHERE id = :id LIMIT 1")
fun getItemId(id: String): String?

@Insert
fun insert(item: Item): Long

@Update(onConflict = OnConflictStrategy.REPLACE)
fun update(item: Item): Int

Item is Your object, and in your code:

 fun insertOrUpdate(item: Item) {
            database.runInTransaction {
                val id = getItemDao().getItemId(item.id)

                if(id == null)
                    getItemDao().insert(item)
                else
                    getItemDao().update(item)
            }
        }

Upvotes: 9

Danail Alexiev
Danail Alexiev

Reputation: 7782

  1. A method, annotated with @Insert can return a long. This is the newly generated ID for the inserted row. A method, annotated with @Update can return an int. This is the number of updated rows.

  2. update will try to update all your fields using the value of the primary key in a where clause. If your entity is not persisted in the database yet, the update query will not be able to find a row and will not update anything.

  3. You can use @Insert(onConflict = OnConflictStrategy.REPLACE). This will try to insert the entity and, if there is an existing row that has the same ID value, it will delete it and replace it with the entity you are trying to insert. Be aware that, if you are using auto generated IDs, this means that the the resulting row will have a different ID than the original that was replaced. If you want to preserve the ID, then you have to come up with a custom way to do it.

Upvotes: 85

Related Questions