android - 补充房间中的记录列

问题描述

总的来说，我对 Android Room 和 SQLite 还很陌生，如果这是一个简单的问题，我很抱歉。

我从要插入数据库的 API 获取数据，以便在设备离线时可以访问它。

根据 API 的端点，我的 Data 对象的某些字段可能为空（考虑仅包含基本字段的摘要与包含所有字段的完全详细的对象）

为了保持数据库干净，我想更新条目，但只更新不为空的列（例如，我有新值）并保持其余列不变。

以下是一些需要说明的示例类：

人

@Entity(tableName = "person", indices = {
        @Index(value = "id", unique = true)
})
public class Person {
    @PrimaryKey
    public int id;

    public String name;

    public String description;
}

例子：

// create db
RoomDB db = RoomDB.create(ctx);

// create some sample objects
final Person p2 = new Person(2, "Peter", null);

// insert them into the db
db.personDao().insert(p2);

// create a updated peter that likes spiders
// but has no name (as a example)
final Person newPeter = new Person(2, null, "Peter likes spiders");

// and update him
db.personDao().updateNonNull(newPeter);

// now we read him back
final Person peter = db.personDao().getById(2);

在此示例中，“peter”的期望值为：

id = 2
name = "Peter"
description = "Peter likes spiders"

但是，使用 Room 的 @Update 或 @Insert 我只能得到这个：

id = 2
name = null
description = "Peter likes spiders"

我发现实现这一点的唯一方法是手动获取对象并补充如下值：

@Transaction
public void updateNonNull(Person newPerson) {
    final Person oldPerson = getById(newPerson.id);
    if (oldPerson == null) {
        insert(newPerson);
        return;
    }
    
    if (newPerson.name == null)
        newPerson.name = oldPerson.name;

    if (newPerson.description == null)
        newPerson.description = oldPerson.description;

    update(newPerson);
}

但是，这会导致相当多的代码带有更大的对象......所以我的问题是，有没有更好的方法来做到这一点？

编辑：

在@Priyansh Kedia 对 SQL 进行了一些测试后，我发现这些函数确实按预期工作，并且性能比 java 更高。

但是，由于 SQL 语句需要我编写大量查询，所以我决定使用基于反射的解决方案，如下所示。我这样做只是因为没有定期调用该函数，因此较低的性能不会太重要。

    /**
     * merge two objects fields using reflection.
     * replaces null value fields in newObj with the value of that field in oldObj
     * <p>
     * assuming the following values:
     * oldObj: {name: null, desc: "bar"}
     * newObj: {name: "foo", desc: null}
     * <p>
     * results in the "sum" of both objects: {name: "foo", desc: "bar"}
     *
     * @param type   the type of the two objects to merge
     * @param oldObj the old object
     * @param newObj the new object. after the function, this is the merged object
     * @param <T>    the type
     * @implNote This function uses reflection, and thus is quite slow.
     * The fastest way of doing this would be to use SQLs' ifnull or coalesce (about 35% faster), but that would involve manually writing a expression for EVERY field.
     * That is a lot of extra code which i'm not willing to write...
     * Besides, as long as this function isn't called too often, it doesn't really matter anyway
     */
    public static <T> void merge(@NonNull Class<T> type, @NonNull T oldObj, @NonNull T newObj) {
        // loop through each field that is accessible in the target type
        for (Field f : type.getFields()) {
            // get field modifiers
            final int mod = f.getModifiers();

            // check this field is not status and not final
            if (!Modifier.isStatic(mod)
                    && !Modifier.isFinal(mod)) {
                // try to merge
                // get values of both the old and new object
                // if the new object has a null value, set the value of the new object to that of the old object
                // otherwise, keep the new value
                try {
                    final Object oldVal = f.get(oldObj);
                    final Object newVal = f.get(newObj);

                    if (newVal == null)
                        f.set(newObj, oldVal);
                } catch (IllegalAccessException e) {
                    Log.e("Tenshi", "IllegalAccess in merge: " + e.toString());
                    e.printStackTrace();
                }
            }
        }
    }

标签： androidandroid-room

@Query("UPDATE person SET name = (CASE WHEN :name IS NOT NULL THEN :name ELSE name END), description = (CASE WHEN :description IS NOT NULL THEN :description ELSE description END) WHERE id = :id") Person update(id: Int, name: String, description: String)

android - 补充房间中的记录列

问题描述

解决方案

推荐阅读