Android Room One-to-Many Relationship

Question

I am trying to setup a one-to-many relationship, I have checked various articles and tutorials online but all the examples show one table having a one to many relationship with another table.

In my requirement I have two tables having one to many relationships with another table (check the diagram below)

Student table has FK from Class and School table. Most of the examples explain one to many relationship either by Class - Student or School - Student.

I want to do [Class, School] - Student.

Core Data takes cares of it on iOS, but I am not able to sort this out on Android. I even tried the nested relationship example shown in Android documentation but that is not working.

Answer 1

Your issue/problem hasn't been clearly stated. However, the following is a working example that demonstrates two ways of extracting the relationships based upon your schema.

• Note the use of Class as a class is fraught with potential issues and it is not recommended at all. However, the following does use the Class and to circumvent some issues may not fully reflect your schema.

Example code

The School entity :-

@Entity(tableName = "_school")
class School {
    @PrimaryKey
    @ColumnInfo(name = "school_id")
    Long Schoolid;
    @NonNull
    @ColumnInfo(name = "school_name")
    String SchoolName;

    School(){}

    @Ignore
    School(String schoolName) {
        this.SchoolName = schoolName;
    }
}

The Class (an unwise choice of name) :-

@Entity(tableName = "_class")
class Class {
    @PrimaryKey
    @ColumnInfo(name = "class_id")
    Long ClassId;
    @NonNull
    @ColumnInfo(name = "class_name")
    String ClassName;

    Class(){}

    @Ignore
    Class(String className) {
        this.ClassName = className;
    }
}

The Student entity ( Foreign Key Constraints included):-

@Entity(
        tableName = "_student", foreignKeys = {
        @ForeignKey(
                entity = School.class,
                parentColumns = {"school_id"},
                childColumns = {"school_id"},
                onDelete = ForeignKey.CASCADE,
                onUpdate = ForeignKey.CASCADE
        ),
        @ForeignKey(
                entity = Class.class,
                parentColumns = {"class_id"},
                childColumns = {"class_id"},
                onDelete = ForeignKey.CASCADE,
                onUpdate = ForeignKey.CASCADE
        )
        }
)
class Student {
    @PrimaryKey
    @ColumnInfo(name = "student_id")
    Long StudentId;
    @ColumnInfo(name = "Student_name")
    String StudentName;
    @ColumnInfo(name = "school_id", index = true)
    Long SchoolId;
    @ColumnInfo(name = "class_id", index = true)
    Long ClassId;

    Student(){}

    @Ignore
    Student(String studentName, long schoolId, long classId) {
        this.StudentName = studentName;
        this.SchoolId = schoolId;
        this.ClassId = classId;
    }
}

POJO Method 1 - Class StudentAndSchoolAndClass - (DOES NOT USE @Relation)

class StudentAndSchoolAndClass {

    @Embedded
    Student student;
    String school_name;
    String class_name;
}

POJO Method 2 - Class StudentWithSchoolWithClass - (Uses @Relation's)

class StudentWithSchoolWithClass {

    @Embedded
    Student student;

    @Relation(entity = School.class,parentColumn = "school_id", entityColumn = "school_id")
    List<School> schoolList;
    @Relation(entity = Class.class,parentColumn = "class_id",entityColumn = "class_id")
    List<Class> classList;
}

The Dao Interface AllDao

@Dao
interface AllDao {

    @Insert
    Long insertSchool(School s);
    @Insert
    Long insertClass(Class c);
    @Insert
    Long insertStudent(Student s);
    @Query("SELECT * FROM _school")
    List<School> getAllSchools();
    @Query("SELECT * FROM _school WHERE school_id = :school_id ")
    School getSchoolById(Long school_id);
    @Query("SELECT * FROM _class")
    List<Class> getAllClasses();
    @Query("SELECT * FROM _class WHERE class_id = :class_id")
    Class getClassById(Long class_id);
    @Query("SELECT * FROM _student JOIN _school ON _school.school_id = _student.school_id JOIN _class ON _class.class_id = _student.class_id")
    List<StudentAndSchoolAndClass> getStudentAndSchoolAndClass();
    @Query("SELECT * FROM _student")
    List<StudentWithSchoolWithClass> getStudentWithSchoolWithClass();

}

• Note The two last queries use the respective POJO and especially that
• the PJO with @Relations has the relationships defined via the JOIN's

The @Database class MyDatabase

@Database(entities = {School.class,Class.class,Student.class},version = 1)
abstract class MyDatabase extends RoomDatabase {
    abstract AllDao allDao();
}

Lastly an Activity MainActivity that loads some data into the database and then extracts some of the data using the 2 @Queries and the respective POJO class.

public class MainActivity extends AppCompatActivity {

    MyDatabase db;
    AllDao allDao;

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

        // Build the MyDatabase instance
        db = Room.databaseBuilder(this,MyDatabase.class,"mydb")
                .allowMainThreadQueries()
                .build();
        // Build the allDao instance
        allDao = db.allDao();

        // Create some school objects
        School[] s_array = {new School("School1"),
                new School("School2"),
                new School("School3")
        };
        // Insert the Schools into the database
        for (School s: s_array) {
            allDao.insertSchool(s);
        }

        // Create some Class objects
        Class[] c_array = {
                new Class("Class1"),
                new Class("Class2"),
                new Class("Class3")
        };
        // Insert the classes
        for (Class c: c_array) {
            allDao.insertClass(c);
        }

        // Create some Student Objects
        Student[] st_array = {
                new Student("Fred",3,3), new Student("Mary",1,2)
        };
        //Insert the Students
        for(Student st: st_array) {
            allDao.insertStudent(st);
        }

        // Get the Students with the School and Class information using POJO 1 (realtionship via joins)
        List<StudentAndSchoolAndClass> sasac = allDao.getStudentAndSchoolAndClass();
        // Log the data
        for(StudentAndSchoolAndClass ssc: sasac) {
            Log.d("STUDENTINFO1","Student Name = " + ssc.student.StudentName +
                    "\n\t ID=" + ssc.student.StudentId + " SchoolID=" + ssc.student.SchoolId + " ClassID=" + ssc.student.ClassId +
                    "\n\t\t School Name = " + ssc.school_name +
                    "\n\t\t Class Name = " + ssc.class_name
                    );
        }
        // Get the Students with the School and Class information using POJO 2 (with @Relation's)
        List<StudentWithSchoolWithClass> swswc = allDao.getStudentWithSchoolWithClass();
        for(StudentWithSchoolWithClass ssc: swswc) {
            Log.d("STUDENTINFO2","Student Name = " + ssc.student.StudentName +
                    "\n\t ID=" + ssc.student.StudentId + " SchoolID=" + ssc.student.SchoolId + " ClassID=" + ssc.student.ClassId +
                    "\n\t\t School Name = " + ssc.schoolList.get(0).SchoolName +
                    "\n\t\t Class Name = " + ssc.classList.get(0).ClassName
            );
        }
    }
}

• Note in the first the School and Class name are members of the class, whilst for the second the School and Class are within a List. As the Student will only have a single school/class there is no need to traverse the list as the first element/item (0) will be the only element/item in the List.

Results

When the above is run (first time) the database is :-

The log contains :-

2021-04-01 22:09:51.977 D/STUDENTINFO1: Student Name = Fred
         ID=1 SchoolID=3 ClassID=3
             School Name = School3
             Class Name = Class3
2021-04-01 22:09:51.977 D/STUDENTINFO1: Student Name = Mary
         ID=2 SchoolID=1 ClassID=2
             School Name = School1
             Class Name = Class2


2021-04-01 22:09:51.982 D/STUDENTINFO2: Student Name = Fred
         ID=1 SchoolID=3 ClassID=3
             School Name = School3
             Class Name = Class3
2021-04-01 22:09:51.982 D/STUDENTINFO2: Student Name = Mary
         ID=2 SchoolID=1 ClassID=2
             School Name = School1
             Class Name = Class2