Android Room Persistence 라이브러리 : Upsert

Android Room Persistence 라이브러리 : Upsert

---

Android의 Room 지속성 라이브러리에는 개체 또는 컬렉션에 대해 작동하는 @Insert 및 @Update 주석이 정중하게 포함되어 있습니다. 그러나 데이터가 데이터베이스에있을 수도 있고 없을 수도 있으므로 UPSERT가 필요한 사용 사례 (모델이 포함 된 푸시 알림)가 있습니다.

Sqlite는 기본적으로 upsert가 없으며 해결 방법은이 SO 질문에 설명되어 있습니다. 거기에 솔루션이 주어지면 어떻게 Room에 적용할까요?

더 구체적으로 말하면 외래 키 제약 조건을 위반하지 않는 Room에서 삽입 또는 업데이트를 구현하려면 어떻게해야합니까? onConflict = REPLACE와 함께 insert를 사용하면 해당 행에 대한 모든 외래 키에 대한 onDelete가 호출됩니다. 제 경우에는 onDelete로 인해 계단식이 발생하고 행을 다시 삽입하면 외래 키가있는 다른 테이블의 행이 삭제됩니다. 이것은 의도 된 동작이 아닙니다.

---

아마도 BaseDao를 이렇게 만들 수 있습니다.

@Transaction으로 upsert 작업을 보호하고 삽입이 실패한 경우에만 업데이트를 시도하십시오.

@Dao
public abstract class BaseDao<T> {
    /**
    * Insert an object in the database.
    *
     * @param obj the object to be inserted.
     * @return The SQLite row id
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    public abstract long insert(T obj);

    /**
     * Insert an array of objects in the database.
     *
     * @param obj the objects to be inserted.
     * @return The SQLite row ids   
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    public abstract List<Long> insert(List<T> obj);

    /**
     * Update an object from the database.
     *
     * @param obj the object to be updated
     */
    @Update
    public abstract void update(T obj);

    /**
     * Update an array of objects from the database.
     *
     * @param obj the object to be updated
     */
    @Update
    public abstract void update(List<T> obj);

    /**
     * Delete an object from the database
     *
     * @param obj the object to be deleted
     */
    @Delete
    public abstract void delete(T obj);

    @Transaction
    public void upsert(T obj) {
        long id = insert(obj);
        if (id == -1) {
            update(obj);
        }
    }

    @Transaction
    public void upsert(List<T> objList) {
        List<Long> insertResult = insert(objList);
        List<T> updateList = new ArrayList<>();

        for (int i = 0; i < insertResult.size(); i++) {
            if (insertResult.get(i) == -1) {
                updateList.add(objList.get(i));
            }
        }

        if (!updateList.isEmpty()) {
            update(updateList);
        }
    }
}

---

더 우아한 방법으로 두 가지 옵션을 제안합니다.

로 insert작업 에서 반환 값을 확인 IGNORE합니다 OnConflictStrategy(-1과 같으면 행이 삽입되지 않았 음을 의미합니다).

@Insert(onConflict = OnConflictStrategy.IGNORE)
long insert(Entity entity);

@Update(onConflict = OnConflictStrategy.IGNORE)
void update(Entity entity);

public void upsert(Entity entity) {
    long id = insert(entity);
    if (id == -1) {
        update(entity);   
    }
}

에서 예외 처리 insert와 함께 작업을 FAILA와 OnConflictStrategy:

@Insert(onConflict = OnConflictStrategy.FAIL)
void insert(Entity entity);

@Update(onConflict = OnConflictStrategy.FAIL)
void update(Entity entity);

public void upsert(Entity entity) {
    try {
        insert(entity);
    } catch (SQLiteConstraintException exception) {
        update(entity);
    }
}

---

I could not find a SQLite query that would insert or update without causing unwanted changes to my foreign key, so instead I opted to insert first, ignoring conflicts if they occurred, and updating immediately afterwards, again ignoring conflicts.

The insert and update methods are protected so external classes see and use the upsert method only. Keep in mind that this isn't a true upsert as if any of the MyEntity POJOS have null fields, they will overwrite what may currently be in the database. This is not a caveat for me, but it may be for your application.

@Insert(onConflict = OnConflictStrategy.IGNORE)
protected abstract void insert(List<MyEntity> entities);

@Update(onConflict = OnConflictStrategy.IGNORE)
protected abstract void update(List<MyEntity> entities);

@Transaction
public void upsert(List<MyEntity> entities) {
    insert(models);
    update(models);
}

---

If the table has more than one column, you can use

@Insert(onConflict = OnConflictStrategy.REPLACE)

to replace a row.

Reference - Go to tips Android Room Codelab

---

Just an update for how to do this with Kotlin retaining data of the model (Maybe to use it in a counter as in example):

//Your Dao must be an abstract class instead of an interface (optional database constructor variable)
@Dao
abstract class ModelDao(val database: AppDatabase) {

@Insert(onConflict = OnConflictStrategy.FAIL)
abstract fun insertModel(model: Model)

//Do a custom update retaining previous data of the model 
//(I use constants for tables and column names)
 @Query("UPDATE $MODEL_TABLE SET $COUNT=$COUNT+1 WHERE $ID = :modelId")
 abstract fun updateModel(modelId: Long)

//Declare your upsert function open
open fun upsert(model: Model) {
    try {
       insertModel(model)
    }catch (exception: SQLiteConstraintException) {
        updateModel(model.id)
    }
}
}

You can also use @Transaction and database constructor variable for more complex transactions using database.openHelper.writableDatabase.execSQL("SQL STATEMENT")

---

This is the code in Kotlin:

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(entity: Entity): Long

@Update(onConflict = OnConflictStrategy.REPLACE)
fun update(entity: Entity)

@Transaction
fun upsert(entity: Entity) {
  long id = insert(entity)
   if (id == -1L) {
     update(entity)
  }

}

---

Another approach I can think of is to get the entity via DAO by query, and then perform any desired updates. This may be less efficient compared to the other solutions in this thread in terms of runtime because of having to retrieve the full entity, but allows much more flexibility in terms of operations allowed such as on what fields/variable to update.

For example :

private void upsert(EntityA entityA) {
   EntityA existingEntityA = getEntityA("query1","query2");
   if (existingEntityA == null) {
      insert(entityA);
   } else {
      entityA.setParam(existingEntityA.getParam());
      update(entityA);
   }
}

---

Should be possible with this sort of statement:

INSERT INTO table_name (a, b) VALUES (1, 2) ON CONFLICT UPDATE SET a = 1, b = 2

참고URL : https://stackoverflow.com/questions/45677230/android-room-persistence-library-upsert