时间戳

首页 > 解决方案 > Android Room - 依赖表中的 CASCADE 用于 DELETE 并保持不可触摸以进行 INSERT 操作？

foreign-keys - Android Room - 依赖表中的 CASCADE 用于 DELETE 并保持不可触摸以进行 INSERT 操作？

问题描述

我有两张桌子 -users和conversations. 许多人users可能会参加一个conversation，一个人user可能是许多人的成员conversations 

@Entity(tableName = "users")
public class User {
    @NonNull
    @PrimaryKey
    @ColumnInfo(name = "userId")
    private String id;  
    ...
    //Getters and setters  
}  

@Entity(tableName = "conversations")
public class Conversation {
    @NonNull
    @PrimaryKey
    @ColumnInfo(name = "uuid")
    private String id;  
    ...
    //Getters and setters  
}

要跟踪我使用第三张表的user参与：conversation

@Entity(tableName = "user_conversation", indices = {@Index(value = {"userId", "conversationUuid"}, unique = true)},
    primaryKeys = {"userId", "conversationUuid"},
    foreignKeys = {
            @ForeignKey(onDelete = CASCADE, entity = User.class,         parentColumns = "userId", childColumns = "userId"),
            @ForeignKey(onDelete = CASCADE, entity = Conversation.class, parentColumns = "uuid",   childColumns = "conversationUuid")
    })
public class JoinUserConversation {
    @NonNull
    private String userId;

    @NonNull
    private String conversationUuid;
    ...
    //Getters and setters
}

Dao对象：

@Dao
public interface UsersDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insert(User user);
    ...
}

@Dao
public interface ConversationsDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    long insert(Conversation conversation);
    ....
}

当我从他们delete的表中user或从他们的表中删除它们时，我也conversation需要将它们用于此目的。但是当我或他们的表时，我不需要将它们从. 但据我了解，如果插入实体已存在于表中并且相应或将从表中删除，则将执行。如何保持不变？user_conversationonDelete = CASCADEinsertuserconversationuser_conversationinsert@Insert(onConflict = OnConflictStrategy.REPLACE)deleteuserconversationuser_conversationuser_conversationinsert

标签： foreign-keysandroid-sqliteandroid-roomdaocascade

解决方案

我相信您可以通过检查要插入的行的 id 是否存在来完成您想要的操作，如果不存在则更新，然后插入。

  • 注意我尝试使用deferred = truewith@Transaction但由于 CASCADE 做了它应该做的事情，所以没有冲突。

例如，对于用户：-

添加

@Query("SELECT count() FROM users WHERE userId = :userId")
int getUserCount(String userId);

和

@Update()
int updateUser(User user);

然后有一个方法，例如：-

private void upsertUser(User u) {
    if (mWTDao.getUserCount(u.getId()) > 0) {
        mWTDao.updateUser(u);
    } else {
        mWTDao.insert(u);
    }
}
  • mWTDao 是所有 Dao 存在的地方（示例相同）

例子

考虑以下摘录（来自测试代码）：-

    ......... other code

    upsertUser(new User("userid001","Fred"));
    upsertUser(new User("userid002","Mary"));
    upsertUser(new User("userid003","Jane"));
    upsertUser(new User("userid004","Tom"));

    mWTDao.insert( new Conversation("c001","SUBJECT A"));
    mWTDao.insert( new Conversation("c002","SUBJECT X"));

    mWTDao.insert(new JoinUserConversation("userid001","c001"));
    mWTDao.insert(new JoinUserConversation("userid003","c001"));

    mWTDao.insert(new JoinUserConversation("userid002","c002"));
    mWTDao.insert(new JoinUserConversation("userid004","c002"));

    logAllInfo("PRE");

    upsertUser(new User("userid001","Harry"));
    upsertUser(new User("userid002","Gertrude"));
    logAllInfo("POST");

}

private void upsertUser(User u) {
    if (mWTDao.getUserCount(u.getId()) > 0) {
        mWTDao.updateUser(u);
    } else {
        mWTDao.insert(u);
    }
}


private void logAllInfo(String tag) {

    Log.d("INFO" + tag,"Logging Conversations via Original Mapping Table ");

    for (JoinUserConversation juc: mWTDao.getAllJUC()) {
        Conversation c = mWTDao.getConversation(juc.getConversationUuid());
        User u = mWTDao.getUser(juc.getUserId());
        Log.d("INFO" + tag,"\tFor Conversation " + c.getTitle() + "ID is (" + c.getId() + ") User is " + u.getName() + " ID is (" + u.getId() + ")");
    }

    Log.d("MAPINFO" + tag,"Logging Users ");

    for (User u: mWTDao.getAllUsers()) {
        Log.d("USERINFO" +  tag,"\tID is " + u.getId() + " Name is " + u.getName());
    }

    Log.d("CONVINFO","Logging Conversations");
    for (Conversation c: mWTDao.getAllConversations()) {
        Log.d("CONVINFO" + tag,"\tID is " + c.getId() + " Ttitle is " + c.getTitle());
    }
}
  • 这增加了 4 个用户（使用upsertUser方法）和 2 个对话和一些 user_conversations
  • 它显示信息
  • 然后它尝试使用upsertUser方法添加已经存在的用户
  • 它再次显示信息，表明现有用户已更新。

结果

2019-09-18 08:14:19.214  D/INFOPRE: Logging Conversations via Original Mapping Table 
2019-09-18 08:14:19.220  D/INFOPRE:     For Conversation SUBJECT AID is (c001) User is Fred ID is (userid001)
2019-09-18 08:14:19.222  D/INFOPRE:     For Conversation SUBJECT AID is (c001) User is Jane ID is (userid003)
2019-09-18 08:14:19.224  D/INFOPRE:     For Conversation SUBJECT XID is (c002) User is Mary ID is (userid002)
2019-09-18 08:14:19.226  D/INFOPRE:     For Conversation SUBJECT XID is (c002) User is Tom ID is (userid004)
2019-09-18 08:14:19.226  D/MAPINFOPRE: Logging Users 
2019-09-18 08:14:19.227  D/USERINFOPRE:     ID is userid001 Name is Fred
2019-09-18 08:14:19.227  D/USERINFOPRE:     ID is userid002 Name is Mary
2019-09-18 08:14:19.227  D/USERINFOPRE:     ID is userid003 Name is Jane
2019-09-18 08:14:19.227  D/USERINFOPRE:     ID is userid004 Name is Tom
2019-09-18 08:14:19.227  D/CONVINFO: Logging Conversations
2019-09-18 08:14:19.228  D/CONVINFOPRE:     ID is c001 Ttitle is SUBJECT A
2019-09-18 08:14:19.228  D/CONVINFOPRE:     ID is c002 Ttitle is SUBJECT X



2019-09-18 08:14:19.233  D/INFOPOST: Logging Conversations via Original Mapping Table 
2019-09-18 08:14:19.236  D/INFOPOST:    For Conversation SUBJECT AID is (c001) User is Harry ID is (userid001)
2019-09-18 08:14:19.239  D/INFOPOST:    For Conversation SUBJECT AID is (c001) User is Jane ID is (userid003)
2019-09-18 08:14:19.241  D/INFOPOST:    For Conversation SUBJECT XID is (c002) User is Gertrude ID is (userid002)
2019-09-18 08:14:19.243  D/INFOPOST:    For Conversation SUBJECT XID is (c002) User is Tom ID is (userid004)
2019-09-18 08:14:19.243  D/MAPINFOPOST: Logging Users 
2019-09-18 08:14:19.244  D/USERINFOPOST:    ID is userid001 Name is Harry
2019-09-18 08:14:19.244  D/USERINFOPOST:    ID is userid002 Name is Gertrude
2019-09-18 08:14:19.244  D/USERINFOPOST:    ID is userid003 Name is Jane
2019-09-18 08:14:19.244  D/USERINFOPOST:    ID is userid004 Name is Tom
2019-09-18 08:14:19.244  D/CONVINFO: Logging Conversations
2019-09-18 08:14:19.245  D/CONVINFOPOST:    ID is c001 Ttitle is SUBJECT A
2019-09-18 08:14:19.245  D/CONVINFOPOST:    ID is c002 Ttitle is SUBJECT X
  • 可以看出，外键子级仍然存在，但详细信息已更新，并且没有新的非预期用户。

推荐阅读