问题：

主键和外键实现Java和Android Room

易焱

2023-03-14

这是我第一次在StackOverflow中问问题，所以请原谅我，如果我问的问题不对或以某种方式。

我有两个来自家长班级评估的孩子班级（绩效评估和客观评估）。我能够成功地对我的学校项目的多态性部分要求进行向下预测，并且能够在向下预测后将父母和孩子的实例保存到相应的数据库中（我有一个评估表、绩效评估表和目标评估表）。但是，我现在遇到了Android Room数据库问题。我意识到我需要实现外键来正确执行CRUD操作。如何调整父类和子类代码，并添加正确的注释以正确实现外键和主键？我需要为每个类自动生成主键：父类（评估）和两个子类（性能评估和目标评估）。但我还需要将孩子的主键用作家长数据库的外键，以便在我删除绩效/目标评估实例时，也可以在我向下广播时删除评估实例。我发现很少有关于这方面的有用信息。提前谢谢。

我现在收到了这些错误：

构建错误

家长班：Assessment.java

@Entity(tableName = "assessment_table")
public class Assessment {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "assessment_id")
    private final int assessment_id;

    private String assessmentName;
    private String assessmentStart;
    private String assessmentEnd;
    private int courseID;

    public Assessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID) {
        this.assessment_id = assessment_id;
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

儿童课：表演ssessment.html" target="_blank">java

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "performance_id",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment{

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "performance_id")
    private int performanceID;
    private String type;

    public PerformanceAssessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int performanceID, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.performanceID = performanceID;
        this.type = type;
    }

子类：Objjectssessment.java

@Entity(tableName = "objective_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "objective_id",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class ObjectiveAssessment extends Assessment{

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "objective_id")
    private int objective_ID;
    private String type;

    public ObjectiveAssessment(int assessmentID, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int objective_ID, String type) {
        super(assessmentID, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.objective_ID = objective_ID;
        this.type = type;
    }

以下是我在应用程序中添加新评估的部分：

public void saveAssessment(View view) {

        assessmentTitle = editName.getText().toString();
        assessmentStart = editStart.getText().toString();
        assessmentEnd = editEnd.getText().toString();

        //Check if fields are empty:
        if (assessmentTitle.isEmpty() || assessmentStart.isEmpty() || assessmentEnd.isEmpty()) {

            Toast.makeText(AddAssessmentScreen.this, "Fill out required fields.", Toast.LENGTH_LONG).show();
            return;

        }
        else {

            //Check assessment type selected (used Downcasting for polymorphism):

            if (assessment_type == true) {
                Assessment performanceAssessment = new PerformanceAssessment(0, assessmentTitle, assessmentStart, assessmentEnd, currentCourseID, 0, selectedString);
                PerformanceAssessment castedPerformance = (PerformanceAssessment) performanceAssessment;
                //Insert assessment to database performance_assessment table (Performance child type):
                repository.insert(castedPerformance);
                Repository addToAssessment = new Repository(getApplication());
                //Insert assessment to database assessment_table (Assessment parent type):
                addToAssessment.insert(performanceAssessment);

            }
            else {
                Assessment objectiveAssessment = new ObjectiveAssessment(0,assessmentTitle, assessmentStart, assessmentEnd, currentCourseID, 0, selectedString);
                ObjectiveAssessment castedObjective = (ObjectiveAssessment) objectiveAssessment;
                //Insert assessment to database objective_assessment table (Objective child type):
                repository.insert(castedObjective);
                Repository addToAssessment = new Repository(getApplication());
                //Insert assessment to database assessment_table (Assessment parent type):
                addToAssessment.insert(objectiveAssessment);
            }

            Toast.makeText(AddAssessmentScreen.this, "New assessment added. Refresh previous screen.", Toast.LENGTH_LONG).show();

        }

毕瀚

2023-03-14

如何调整父类和子类代码，并为正确实现外键和主键添加适当的注释？

让assessment_id始终是主键，即不要在子类中编码它，然后在子类中有一个引用/映射/与父类关联的字段。

所以性能评估可以是：-

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment {

    private int assessment_reference; //<<<<<<<<<
    private String type;

    public PerformanceAssessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }
    ....

所有编译和基础表将使用以下方式构建：-

    _db.execSQL("CREATE TABLE IF NOT EXISTS `assessment_table` (`assessment_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `assessmentName` TEXT, `assessmentStart` TEXT, `assessmentEnd` TEXT, `courseID` INTEGER NOT NULL)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `performance_assessment` (`assessment_reference` INTEGER NOT NULL, `type` TEXT, `assessment_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `assessmentName` TEXT, `assessmentStart` TEXT, `assessmentEnd` TEXT, `courseID` INTEGER NOT NULL, FOREIGN KEY(`assessment_reference`) REFERENCES `assessment_table`(`assessment_id`) ON UPDATE CASCADE ON DELETE CASCADE )");

显然，客观评估与此类似

但我还需要将孩子的主键用作家长数据库的外键，以便在我删除绩效/目标评估实例时，也可以在我向下广播时删除评估实例。

如果一个评估有多个绩效评估，这在1（评估）-多（绩效评估）中是可能的，那么该怎么办？删除1个绩效评估将删除父评估，由于使用了onDelete CASCADE将删除的内容级联到所有其他绩效评估和客观评估。删除不会传播到父级（这就是为什么使用术语级联，因为它意味着向下，而不是无论如何）。

例如，您有一个包含4个绩效评估的评估，假设3个客观评估其中一个绩效评估被错误添加。应删除所有内容并重新输入以纠正错误的绩效评估。

您可以引入一个触发器来自动向上传播，删除任何内容并删除所有内容，如果这是您想要的。

补充评论

我对评估和绩效/目标评估有一对一的关系。一个评估只能有绩效或目标类型。

这可能是您所希望的，但没有什么可以阻止具有多个绩效和/或目标的评估。这可能是问题，也可能不是问题。

此外，我没有向下投射，而是向上投射我在应用程序中保存评估的位置。我遇到外键约束失败（代码787），并意识到这是因为我在向下投射。我现在唯一的困境是，每当我保存评估时，它只会保存在评估表上，而不会保存在绩效评估或目标评估tbl上。

你要做的是插入评估（父），然后使用父的id插入相关的绩效/目标，assessment_reference设置评估的值。

787的意思是，如果评估中的评估id不是评估参考，则不能插入子项（绩效/目标）。

你能做的，很可能适合你的情况是a）有一个@Insert长插入（评估）和@Insert long（性能评估性能评估）（同样适用于目标）

然后使用（假设dao是@dao的实例）

和使用

dao.insert(new PerformanceAssessment(0,"the title", etc,dao.insert(Assessment(....),"the type");

因此，评估作为插入绩效/目标的一部分添加，评估id用作评估参考值。

注意@Insert只返回long，因此您必须将其转换为int。我建议将id和引用更改为long。开销（如果有的话）的影响最小。数据库中的存储不会受到影响，并且可以省去将long转换为int的麻烦

集会示威

下面是一个基于代码的演示，该代码紧密地反映了您的需求，但有一些细微的变化。

id已更改为long而不是int。

 自动生成已被删除，但会像以前一样生成id。2.从SQLite的角度来看，自动生成所做的一切就是包含AUTOINCREment关键字。这实际上是低效的，不推荐使用。https://sqlite.org/autoinc.html

所以代码是：-

看法

@Entity(tableName = "assessment_table")
public class Assessment {

    @PrimaryKey /* no need for autogenerate */
    @ColumnInfo(name = "assessment_id")
    /* As not autogenerate then use Long and default to null */
    /* same as autogenerate but without the overheads/inefficiencies */
    private Long assessment_id = null; //Long rather than long so can be null and have id generated.
    private String assessmentName;
    private String assessmentStart;
    private String assessmentEnd;
    private long courseID;

    public Assessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID) {
        this.assessment_id = assessment_id;
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

    @Ignore
    /* Alternative constructor no need to provide id for inserting */
    public Assessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID) {
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

    public Long getAssessment_id() {
        return assessment_id;
    }

    public void setAssessment_id(Long assessment_id) {
        this.assessment_id = assessment_id;
    }

    public String getAssessmentName() {
        return assessmentName;
    }

    public void setAssessmentName(String assessmentName) {
        this.assessmentName = assessmentName;
    }

    public String getAssessmentStart() {
        return assessmentStart;
    }

    public void setAssessmentStart(String assessmentStart) {
        this.assessmentStart = assessmentStart;
    }

    public String getAssessmentEnd() {
        return assessmentEnd;
    }

    public void setAssessmentEnd(String assessmentEnd) {
        this.assessmentEnd = assessmentEnd;
    }

    public long getCourseID() {
        return courseID;
    }

    public void setCourseID(long courseID) {
        this.courseID = courseID;
    }
}

性能评估

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment {

    private long assessment_reference;
    private String type;

    public PerformanceAssessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Ignore
    public PerformanceAssessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Override
    public Long getAssessment_id() {
        return super.getAssessment_id();
    }

    @Override
    public void setAssessment_id(Long assessment_id) {
        super.setAssessment_id(assessment_id);
    }

    @Override
    public String getAssessmentName() {
        return super.getAssessmentName();
    }

    @Override
    public void setAssessmentName(String assessmentName) {
        super.setAssessmentName(assessmentName);
    }

    @Override
    public String getAssessmentStart() {
        return super.getAssessmentStart();
    }

    @Override
    public void setAssessmentStart(String assessmentStart) {
        super.setAssessmentStart(assessmentStart);
    }

    @Override
    public String getAssessmentEnd() {
        return super.getAssessmentEnd();
    }

    @Override
    public void setAssessmentEnd(String assessmentEnd) {
        super.setAssessmentEnd(assessmentEnd);
    }

    @Override
    public long getCourseID() {
        return super.getCourseID();
    }

    @Override
    public void setCourseID(long courseID) {
        super.setCourseID(courseID);
    }

    public long getAssessment_reference() {
        return assessment_reference;
    }

    public void setAssessment_reference(long assessment_reference) {
        this.assessment_reference = assessment_reference;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

客观评价

@Entity(tableName = "objective_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class ObjectiveAssessment extends Assessment {

    private long assessment_reference;
    private String type;

    public ObjectiveAssessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Ignore
    public ObjectiveAssessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Override
    public Long getAssessment_id() {
        return super.getAssessment_id();
    }

    @Override
    public void setAssessment_id(Long assessment_id) {
        super.setAssessment_id(assessment_id);
    }

    @Override
    public String getAssessmentName() {
        return super.getAssessmentName();
    }

    @Override
    public void setAssessmentName(String assessmentName) {
        super.setAssessmentName(assessmentName);
    }

    @Override
    public String getAssessmentStart() {
        return super.getAssessmentStart();
    }

    @Override
    public void setAssessmentStart(String assessmentStart) {
        super.setAssessmentStart(assessmentStart);
    }

    @Override
    public String getAssessmentEnd() {
        return super.getAssessmentEnd();
    }

    @Override
    public void setAssessmentEnd(String assessmentEnd) {
        super.setAssessmentEnd(assessmentEnd);
    }

    @Override
    public long getCourseID() {
        return super.getCourseID();
    }

    @Override
    public void setCourseID(long courseID) {
        super.setCourseID(courseID);
    }

    public long getAssessment_reference() {
        return assessment_reference;
    }

    public void setAssessment_reference(long assessment_reference) {
        this.assessment_reference = assessment_reference;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

POJO嵌入了评估，并通过@Relation包含Perf和Obj（这不是理想的，因为只有1或1以外的规则将为null）。因此：-

绩效评估和目标评估

class AssessmentWithPerformanceAssessmentAndObjectiveAssessment {
    @Embedded
    Assessment assessment;
    @Relation(
            entity = PerformanceAssessment.class,
            parentColumn = "assessment_id",
            entityColumn = "assessment_reference"
    )
    PerformanceAssessment performanceAssessment;
    @Relation(
            entity = ObjectiveAssessment.class,
            parentColumn = "assessment_id",
            entityColumn = "assessment_reference"
    )
    ObjectiveAssessment objectiveAssessment;
}

单个@Dao注释类：-

评估道

@Dao
abstract class AssessmentDao {

    @Insert
    abstract long insert(Assessment assessment);
    @Insert
    abstract long insert(PerformanceAssessment performanceAssessment);
    @Insert
    abstract long insert(ObjectiveAssessment objectiveAssessment);
    /*
        use to check if an Assessment has any children
     */
    @Query("WITH cte_counts(counter) AS (" +
            "SELECT count(*) > 0 FROM performance_assessment WHERE assessment_reference=:assessment_id " +
            "UNION ALL SELECT count(*) FROM objective_assessment WHERE assessment_reference=:assessment_id" +
            ")" +
            "SELECT sum(counter) > 0 FROM cte_counts")
    abstract boolean hasChildAlready(long assessment_id);

    @Transaction
    @Query("SELECT * FROM assessment_table WHERE assessment_id=:assessment_id")
    abstract AssessmentWithPerformanceAssessmentAndObjectiveAssessment getAssessmentWithPerformanceAssessmentOrObjectiveAssessmentById(long assessment_id);

    @Transaction
    @Query("SELECT * FROM assessment_table")
    abstract List<AssessmentWithPerformanceAssessmentAndObjectiveAssessment> getAllAssessmentsWithPerformanceAssessmentOrObjectiveAssessmentById();
}

请注意，抽象类而不是接口（可以是接口）

一个@Database注释类

评估数据库

@Database(entities = {Assessment.class,PerformanceAssessment.class,ObjectiveAssessment.class},version =1)
abstract class AssessmentDatabase extends RoomDatabase {
    abstract AssessmentDao getAssessmentDao();

    private static volatile AssessmentDatabase instance = null;
    static AssessmentDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,AssessmentDatabase.class,"assessment.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

注意为了方便和简洁允许在主线程上运行。

最后，在活动中实际使用上述内容：-

public class MainActivity extends AppCompatActivity {

    AssessmentDatabase db;
    AssessmentDao dao;

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

        db = AssessmentDatabase.getInstance(this);
        dao = db.getAssessmentDao();

        /* Various Inserts */
        long loneAssessment = dao.insert(
                new Assessment(
                "Lone Assessment with no children",
                "2021-01-15",
                "2021-03-15",
                0L
                )
        );
        /* id will be 100, subsequent id's if generated will be 101,102......*/
        long a1 = dao.insert(new Assessment(100,"A1","2022-01-01","2022,03-01",10));
        long p1 = dao.insert(new PerformanceAssessment("P1","2022-01-31","2022-01-31",10,a1,"X"));

        /* back to using generated Assessment id */
        long a2 = dao.insert(new Assessment("A1","2022-01-01","2022-03-01",11));
        long p2 = dao.insert(new ObjectiveAssessment("O1","2022-01-31","2022-01-31",11,a2,"Y"));

        /* Build the Assessment and ObjectiveAssessment ready for  insert SEE WARNING */
        Assessment a10 = new Assessment("A10","2022-01-01","2022-03-01",20);
        ObjectiveAssessment o10 = new ObjectiveAssessment("O10","2022-01-31","2022-01-31",a10.getCourseID(),0,"Z");
        /* WARNING assessment_reference WILL NOT BE ANY GOOD (will be 0) */
        long a10id = dao.insert(a10);
        o10.setAssessment_reference(a10id); /*<<<<<<<<<< NOW assessment_reference should be good */
        dao.insert(o10);

        /* Both together */
        dao.insert(
                new PerformanceAssessment("P20","2022-05-07","2022-05-07",11,
                        /* get the Assessment ID from the insert of the Assessment */
                        dao.insert(
                                new Assessment("A20","2022-04-01","2022-06-17",21)
                        ),
                        "ZZ"
                )
        );

        /* Extract all the Assessments with their children (if any)*/
        for(AssessmentWithPerformanceAssessmentAndObjectiveAssessment awpoo: dao.getAllAssessmentsWithPerformanceAssessmentOrObjectiveAssessmentById()) {
            Log.d("DBINFO","Assessment is " + awpoo.assessment.getAssessmentName() + " id is " + awpoo.assessment.getAssessment_id() + " etc....");
            /* need to check if the the performanceassessment is null - it will be if there isn't one */
            if (awpoo.performanceAssessment != null) {
                Log.d("DBINFO","\t\tPerformanceAssessment = " + awpoo.performanceAssessment.getAssessmentName() +
                        " id is "+ awpoo.performanceAssessment.getAssessment_id() +
                        " references Asessment with id " + awpoo.performanceAssessment.getAssessment_reference());
            } else {
                Log.d("DBINFO","\t\tNo PerformanceAssessment.");
            }
            /* null check see performance assessment check above */
            if (awpoo.objectiveAssessment != null) {
                Log.d("DBINFO","\t\tObjectiveAssessment = " + awpoo.objectiveAssessment.getAssessmentName() +
                        " id is "+ awpoo.objectiveAssessment.getAssessment_id() +
                        " references Asessment with id " + awpoo.objectiveAssessment.getAssessment_reference());
            } else {
                Log.d("DBINFO","\t\tNo ObjectiveAssessment.");
            }
        }
    }
}

运行时（第一次运行时，由于id使用100，如果运行两次将失败），日志显示：-

D/DBINFO: Assessment is Lone Assessment with no children id is 1 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       No ObjectiveAssessment.
D/DBINFO: Assessment is A1 id is 100 etc....
D/DBINFO:       PerformanceAssessment = P1 id is 1 references Asessment with id 100
D/DBINFO:       No ObjectiveAssessment.
D/DBINFO: Assessment is A1 id is 101 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       ObjectiveAssessment = O1 id is 1 references Asessment with id 101
D/DBINFO: Assessment is A10 id is 102 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       ObjectiveAssessment = O10 id is 2 references Asessment with id 102
D/DBINFO: Assessment is A20 id is 103 etc....
D/DBINFO:       PerformanceAssessment = P20 id is 2 references Asessment with id 103
D/DBINFO:       No ObjectiveAssessment.

使用App Inspection，则数据库如下所示：-

请参见评估id为2（其非QIUE行标识符）的PerformanceAssessment如何将评估参考值设置为103。这表示评估id为103的评估是父评估（或该绩效评估与评估id为103的评估相关（属于）

主键和外键实现Java和Android Room

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档