java - 3层数据库。如何将 3 个实体相互关联？

Question

我有 3 个实体:类别、文件夹 和卡片。每个类别都可以包含文件夹，您可以在其中包含卡片。我可以通过使用外键成功关联类别和文件夹实体，它工作得很好。但是当我创建卡片实体并尝试将其与相同的逻辑关联时，工作室会抛出一个错误。还有其他方法可以链接这 3 个吗？实体代码如下:

@Entity(tableName = "categories")   
public class Categories {

@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "category_id")
private int categoryId;

@ColumnInfo(name = "category_name")
private String categoryName;

@Ignore
public Categories() {
}

文件夹

@Entity(tableName = "folders", foreignKeys = @ForeignKey(entity = Categories.class,
    parentColumns = "category_id", childColumns = "current_category_id", onDelete = ForeignKey.CASCADE))     
public class Folders {

@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "folder_id")
private int folderId = 0;

@ColumnInfo(name = "folder_name")
private String folderName;

@ColumnInfo(name = "current_category_id")
private int categoryId;

卡片

@Entity(tableName = "cards",foreignKeys = @ForeignKey(entity = Folders.class,
    parentColumns = "folder_id", childColumns = "current_folder_id", onDelete = ForeignKey.CASCADE))
public class Cards {

@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "card_id")
private int cardId = 0;

@ColumnInfo(name = "card_name")
private String cardName;

@ColumnInfo(name = "card_value")
private String cardValue;

@ColumnInfo(name = "current_folder_id")
private int folderId;

@Ignore
public Cards() {
}

Answer 1

要完成您正在尝试的操作，您可以使用:-

@Entity(tableName = "cards",foreignKeys = {
        @ForeignKey(entity = Folders.class, parentColumns = "folder_id", childColumns = "current_folder_id", onDelete = CASCADE),
        @ForeignKey(entity = Categories.class, parentColumns = "category_id", childColumns = "current_category_id", onDelete = CASCADE)})
public class Cards {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "card_id")
    private int cardId = 0;

    @ColumnInfo(name = "card_name")
    private String cardName;

    @ColumnInfo(name = "card_value")
    private String cardValue;

    @ColumnInfo(name = "current_folder_id")
    private int folderId;

    @ColumnInfo(name = "current_category_id") /*<<<<<<<<<< ADDED column */
    private int category_id;

    @Ignore
    public Cards() {
    }
}

但是，因为文件夹是类别的子级，那么从卡片到类别的链接是隐式的(即文件夹必须有类别行作为家长)以下内容应该足够了(并且没有不必要的列):-

@Entity(tableName = "cards",foreignKeys = {
        @ForeignKey(entity = Folders.class, parentColumns = "folder_id", childColumns = "current_folder_id", onDelete = CASCADE)})
public class Cards {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "card_id")
    private int cardId = 0;

    @ColumnInfo(name = "card_name")
    private String cardName;

    @ColumnInfo(name = "card_value")
    private String cardValue;

    @ColumnInfo(name = "current_folder_id")
    private int folderId;

    @Ignore
    public Cards() {
    }
}

<小时/>

示例

以下基本应用程序基于您的代码和第二个答案。它构建了一副扑克牌。

反射(reflect)花色红心、黑桃的类别......(请注意，一些白痴无意中(故意)引入了花色名称“哎呀”，所以包中有 65 张牌)

文件夹可以是宫廷牌或普通牌(2-10张)，每张牌都有面值。

然后将每张卡牌的信息(名称、值、ID、文件夹和类别(套装))输出到日志中。

代码

Categories.java

@Entity(tableName = "categories")
public class Categories {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "category_id")
    private int categoryId;

    @ColumnInfo(name = "category_name")
    private String categoryName;

    public Categories() {
    }

    @Ignore
    public Categories(String categoryName) {
        this.categoryName = categoryName;
    }

    public int getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }
}

Folders.java

@Entity(tableName = "folders", foreignKeys = @ForeignKey(entity = Categories.class,
        parentColumns = "category_id", childColumns = "current_category_id", onDelete = ForeignKey.CASCADE))
public class Folders {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "folder_id")
    private int folderId = 0;

    @ColumnInfo(name = "folder_name")
    private String folderName;

    @ColumnInfo(name = "current_category_id")
    private int categoryId;

    public Folders() {}

    @Ignore
    public Folders(int categoryId, String folderName) {
        this.categoryId = categoryId;
        this.folderName = folderName;
    }

    public int getFolderId() {
        return folderId;
    }

    public void setFolderId(int folderId) {
        this.folderId = folderId;
    }

    public String getFolderName() {
        return folderName;
    }

    public void setFolderName(String folderName) {
        this.folderName = folderName;
    }

    public int getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }

    public Categories getOwningCategories(GamesDatabase gdb) {
        return gdb.getGamesDBDao().getACategoriesRowById(this.categoryId);
    }
}

• 注意 getOwningCategories 方法(GamesDatabase 是 @Database 类，getGaMesDao 是访问 @Dao 的方法(全部都在一个中)文件 GamesDao.java))

Cards.java

@Entity(tableName = "cards",foreignKeys = {
        @ForeignKey(entity = Folders.class, parentColumns = "folder_id", childColumns = "current_folder_id", onDelete = CASCADE)})
public class Cards {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "card_id")
    private int cardId = 0;

    @ColumnInfo(name = "card_name")
    private String cardName;

    @ColumnInfo(name = "card_value")
    private String cardValue;

    @ColumnInfo(name = "current_folder_id")
    private int folderId;

    public Cards() {}

    @Ignore
    public Cards(String cardName, String cardValue, int folderId) {
        this.cardName = cardName;
        this.cardValue = cardValue;
        this.folderId = folderId;
    }

    public Folders getOwningFolders(GamesDatabase gdb) {
        return gdb.getGamesDBDao().getAFoldersRowById(folderId);
    }

    public Categories getOwningCategories(GamesDatabase gdb) {
        return gdb.getGamesDBDao().getACategoriesRowById(getOwningFolders(gdb).getCategoryId());
    }

    public int getCardId() {
        return cardId;
    }

    public void setCardId(int cardId) {
        this.cardId = cardId;
    }

    public String getCardName() {
        return cardName;
    }

    public void setCardName(String cardName) {
        this.cardName = cardName;
    }

    public String getCardValue() {
        return cardValue;
    }

    public void setCardValue(String cardValue) {
        this.cardValue = cardValue;
    }

    public int getFolderId() {
        return folderId;
    }

    public void setFolderId(int folderId) {
        this.folderId = folderId;
    }
}

• 再次注意 getOwning??? 方法。

GamesDao.java

@Dao
public interface GamesDao {

    @Insert()
    long[] insertCategoriesRows(Categories... categories);

    @Insert()
    long insertCategoriesRow(Categories categories);

    @Insert()
    long[] insertFoldersRows(Folders... folders);

    @Insert()
    long insertFoldersRow(Folders folders);

    @Insert
    long[] insertCardsRows(Cards... cards);

    @Insert()
    long insertCardsRow(Cards cards);

    @Delete()
    int deleteManyCategoriesRows(Categories... categories);

    @Delete()
    int deleteACategoriesRow(Categories categories);

    @Delete()
    int deleteManyFoldersRows(Folders... folders);

    @Delete()
    int deleteAFolders(Folders folders);

    @Delete()
    int deleteManyCardsRows(Cards... cards);

    @Delete()
    int deleteACardRow(Cards cards);

    @Query("SELECT * FROM categories")
    Categories[] getAllCategoriesRows();

    @Query("SELECT * FROM folders")
    Folders[] getAllFoldersRows();

    @Query("SELECT * FROM cards")
    Cards[] getAllCards();

    @Query("SELECT * FROM categories WHERE category_id = :id")
    Categories getACategoriesRowById(int id);

    @Query("SELECT * FROM folders WHERE folder_id = :id")
    Folders getAFoldersRowById(int id);

    @Query("SELECT * FROM cards WHERE card_id = :id")
    Cards getACardsRowById(int id);

    @Query("SELECT * FROM folders WHERE current_category_id = :id")
    Folders[] getFoldersPerCategoriesId(int id);
}

• 注意getFoldresPerCategories方法

GamesDatabase.java

@Database(version = 1,exportSchema = true, entities = {Categories.class,Folders.class,Cards.class})
public abstract class GamesDatabase extends RoomDatabase {

    public static final String DBNAME = "games";

    public abstract GamesDao getGamesDBDao();
}

MainActivity.java

public class MainActivity extends AppCompatActivity {

    GamesDatabase mGamesDB;
    GamesDao mGamesDao;
    private static final String[] card_suits = new String[]{"Spades","Hearts","Clubs","Diamonds","ooops"};
    private static final String[] courtcard_values = new String[]{
            "Jack","Queen","King","Ace"
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mGamesDB = Room.databaseBuilder(this,GamesDatabase.class,GamesDatabase.DBNAME)
                .allowMainThreadQueries()
                .build();

        // Suits
        mGamesDao = mGamesDB.getGamesDBDao();
        for (String s: card_suits) {
            mGamesDao.insertCategoriesRow(new Categories(s));
        }

        for (Categories c: mGamesDao.getAllCategoriesRows()) {
            mGamesDao.insertFoldersRow(new Folders(c.getCategoryId(),"CourtCards"));
            mGamesDao.insertFoldersRow(new Folders(c.getCategoryId(),"NormalCards"));
            for (Folders f: mGamesDao.getFoldersPerCategoriesId(c.getCategoryId())) {
                if (f.getFolderName().equals("CourtCards")) {
                    for (String s: courtcard_values) {
                        mGamesDao.insertCardsRow(new Cards(s,getCourtCardValueFromName(s),f.getFolderId()));
                    }
                } else {
                    for (int i=2; i < 11; i++) {
                        mGamesDao.insertCardsRow(new Cards(String.valueOf(i),String.valueOf(i),f.getFolderId()));
                    }
                }
            }
        }

        for (Cards card: mGamesDao.getAllCards()) {
            logCardsInfo(card);
        }

        mGamesDB.close();
    }

    private String getCourtCardValueFromName(String courtCardName) {
        int basevalue = 11;
        for (String s: courtcard_values) {
            if (s.equals(courtCardName)) {
                return String.valueOf(basevalue);
            }
            basevalue++;
            if (basevalue > 13) basevalue = 1;
        }
        return "UNKNOWN";
    }

    private void logCardsInfo(Cards card) {
        Log.d("CARDINFO",
                "Card Name is " + card.getCardName() +
                        "\n\tValue is " + card.getCardValue() +
                        "\n\tCardID is " + card.getCardId() +
                        "\n\tIn Folder " + card.getOwningFolders(mGamesDB).getFolderName() +
                        "\n\t\tIn Category " + card.getOwningCategories(mGamesDB).getCategoryName()
        );
    }
}

- 注意为了方便起见allowMainThreadQueries()已被使用。

结果

2019-09-16 08:58:23.530 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is Jack
        Value is 11
        CardID is 1
        In Folder CourtCards
            In Category Spades
2019-09-16 08:58:23.534 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is Queen
        Value is 12
        CardID is 2
        In Folder CourtCards
            In Category Spades
2019-09-16 08:58:23.538 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is King
        Value is 13
        CardID is 3
        In Folder CourtCards
            In Category Spades
2019-09-16 08:58:23.546 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is Ace
        Value is 1
        CardID is 4
        In Folder CourtCards
            In Category Spades
2019-09-16 08:58:23.553 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is 2
        Value is 2
        CardID is 5
        In Folder NormalCards
            In Category Spades
2019-09-16 08:58:23.559 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is 3
        Value is 3
        CardID is 6
        In Folder NormalCards
            In Category Spades

......

2019-09-16 08:58:23.822 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is 8
        Value is 8
        CardID is 63
        In Folder NormalCards
            In Category ooops
2019-09-16 08:58:23.826 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is 9
        Value is 9
        CardID is 64
        In Folder NormalCards
            In Category ooops
2019-09-16 08:58:23.832 17468-17468/aso.so57943963foreignkeys D/CARDINFO: Card Name is 10
        Value is 10
        CardID is 65
        In Folder NormalCards
            In Category ooops