JOIN queries in Room persistence library

5 min readFeb 19, 2018

This tutorial showing how JOIN queries can easily be done in Room persistence library. The basic Room database structure tutorial is here check now

Here we can learn basic join queries tutorial. it includes join multiple tables and get details using simple SQL queries. One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table, that is to use database joins. In Room, we can use normal SQL queries.

We are using the three Entities in This database and Corresponding @Dao’s

School
Student
ClassStudent

School

package com.cretlabs.roomdatabase.entities;import android.arch.persistence.room.Entity;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;/**
 * Created by Gokul on 2/18/2018.
 */
@Entity
public class School {    @NonNull
    @PrimaryKey
    private long schoolId;
    public School() {
    }
    private String schoolName;
    private String schoolAddress;
    private String schoolPhoneNumber;    public School(@NonNull long schoolId, String schoolName, String schoolAddress, String schoolPhoneNumber) {
        this.schoolId = schoolId;
        this.schoolName = schoolName;
        this.schoolAddress = schoolAddress;
        this.schoolPhoneNumber = schoolPhoneNumber;
    }    @NonNull
    public long getSchoolId() {
        return schoolId;
    }    public void setSchoolId(@NonNull long schoolId) {
        this.schoolId = schoolId;
    }    public String getSchoolName() {
        return schoolName;
    }    public void setSchoolName(String schoolName) {
        this.schoolName = schoolName;
    }    public String getSchoolAddress() {
        return schoolAddress;
    }    public void setSchoolAddress(String schoolAddress) {
        this.schoolAddress = schoolAddress;
    }    public String getSchoolPhoneNumber() {
        return schoolPhoneNumber;
    }    public void setSchoolPhoneNumber(String schoolPhoneNumber) {
        this.schoolPhoneNumber = schoolPhoneNumber;
    }
}

Student

package com.cretlabs.roomdatabase.entities;import android.arch.persistence.room.Entity;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;/**
 * Created by Gokul on 2/18/2018.
 */
@Entity( tableName = "Student")
public class Student {
    @NonNull
    @PrimaryKey(autoGenerate = true)
    private long studentId;
    public Student() {
    }
    private long classId;
    private String studentName;
    private String studentAddress;    public Student(@NonNull long studentId, long classId, String studentName, String studentAddress) {
        this.studentId = studentId;
        this.classId = classId;
        this.studentName = studentName;
        this.studentAddress = studentAddress;
    }    @NonNull
    public long getStudentId() {
        return studentId;
    }    public void setStudentId(@NonNull long studentId) {
        this.studentId = studentId;
    }    public long getClassId() {
        return classId;
    }    public void setClassId(long classId) {
        this.classId = classId;
    }    public String getStudentName() {
        return studentName;
    }    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }    public String getStudentAddress() {
        return studentAddress;
    }    public void setStudentAddress(String studentAddress) {
        this.studentAddress = studentAddress;
    }
}

ClassStudent

package com.cretlabs.roomdatabase.entities;import android.arch.persistence.room.Entity;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;/**
 * Created by Gokul on 2/18/2018.
 */@Entity
public class ClassStudent {    @NonNull
    @PrimaryKey
    private long classId;
    private long schoold;
    private String className;
    private String classNumber;
    private String classDivision;    public ClassStudent(@NonNull long classId, long schoold, String className, String classNumber, String classDivision) {
        this.classId = classId;
        this.schoold = schoold;
        this.className = className;
        this.classNumber = classNumber;
        this.classDivision = classDivision;
    }    @NonNull
    public long getClassId() {
        return classId;
    }    public void setClassId(@NonNull long classId) {
        this.classId = classId;
    }    public long getSchoold() {
        return schoold;
    }    public void setSchoold(long schoold) {
        this.schoold = schoold;
    }    public String getClassName() {
        return className;
    }    public void setClassName(String className) {
        this.className = className;
    }    public String getClassNumber() {
        return classNumber;
    }    public void setClassNumber(String classNumber) {
        this.classNumber = classNumber;
    }    public String getClassDivision() {
        return classDivision;
    }    public void setClassDivision(String classDivision) {
        this.classDivision = classDivision;
    }
}

We have inserted sample data to db like this

private static void populateSampleData(RoomDatabase db) {
    List<School> schoolList = new ArrayList<>();
    schoolList.add(new School(1, "School 1", "School Address 1", "1111111111"));
    schoolList.add(new School(2, "School 2", "School Address 2", "2222222222"));
    schoolList.add(new School(3, "School 3", "School Address 3", "3333333333"));
    schoolList.add(new School(4, "School 4", "School Address 4", "4444444444"));
    schoolList.add(new School(5, "School 5", "School Address 5", "5555555555"));
    db.schoolDao().insertMultipleRecord(schoolList);
    List<ClassStudent> classStudentList = new ArrayList<>();
    classStudentList.add(new ClassStudent(10, 1, "ClassStudent 1", "ClassStudent Address 1", "6666666666"));
    classStudentList.add(new ClassStudent(11, 2, "ClassStudent 2", "ClassStudent Address 2", "7777777777"));
    classStudentList.add(new ClassStudent(12, 3, "ClassStudent 3", "ClassStudent Address 3", "8888888888"));
    classStudentList.add(new ClassStudent(13, 4, "ClassStudent 4", "ClassStudent Address 4", "9999999999"));
    classStudentList.add(new ClassStudent(14, 5, "ClassStudent 5", "ClassStudent Address 5", "10101010110"));
    db.classDao().insertMultipleRecord(classStudentList);    List<Student> studentList = new ArrayList<>();
    studentList.add(new Student(21, 10, "Student 1", "Student Address 1"));
    studentList.add(new Student(22, 10, "Student 2", "Student Address 2"));
    studentList.add(new Student(23, 10, "Student 3", "Student Address 3"));    studentList.add(new Student(24, 11, "Student 4", "Student Address 4"));
    studentList.add(new Student(25, 11, "Student 5", "Student Address 5"));
    studentList.add(new Student(26, 11, "Student 6", "Student Address 6"));    studentList.add(new Student(27, 12, "Student 7", "Student Address 7"));
    studentList.add(new Student(28, 12, "Student 8", "Student Address 8"));
    studentList.add(new Student(29, 12, "Student 9", "Student Address 9"));    studentList.add(new Student(30, 13, "Student 10", "Student Address 10"));
    studentList.add(new Student(31, 13, "Student 11", "Student Address 11"));
    studentList.add(new Student(32, 13, "Student 12", "Student Address 12"));    studentList.add(new Student(30, 14, "Student 13", "Student Address 13"));
    studentList.add(new Student(31, 14, "Student 14", "Student Address 14"));
    studentList.add(new Student(32, 14, "Student 15", "Student Address 15"));    studentList.add(new Student(30, 15, "Student 16", "Student Address 16"));
    studentList.add(new Student(31, 15, "Student 17", "Student Address 17"));
    studentList.add(new Student(32, 15, "Student 18", "Student Address 18"));
    db.studentDao().insertMultipleRecord(studentList);
}

Suppose we have to Display all School name, all class names and display all students in that class. How we can query on this database.

We need

All School name
All Class name
All Students in each class in each school

Thus we can use JOIN queries

First, we need to create a class to hold this much of data from the database as follows.

package com.cretlabs.roomdatabase.entities;/**
 * Created by Gokul on 2/18/2018.
 */public class JoinSchoolClassStudentData {
    private String schoolName;
    private String className;
    private String studentDetails;    public String getSchoolName() {
        return schoolName;
    }    public void setSchoolName(String schoolName) {
        this.schoolName = schoolName;
    }    public String getClassName() {
        return className;
    }    public void setClassName(String className) {
        this.className = className;
    }    public String getStudentDetails() {
        return studentDetails;
    }    public void setStudentDetails(String studentDetails) {
        this.studentDetails = studentDetails;
    }
}

Need to add one more interface in SchoolDao and its returns A list of type JoinSchoolClassStudentData

package com.cretlabs.roomdatabase.dao;import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Delete;
import android.arch.persistence.room.Insert;
import android.arch.persistence.room.Query;import com.cretlabs.roomdatabase.entities.JoinSchoolClassStudentData;
import com.cretlabs.roomdatabase.entities.School;import java.util.List;import static android.arch.persistence.room.OnConflictStrategy.IGNORE;/**
 * Created by Gokul on 2/18/2018.
 */@Dao
public interface SchoolDao {    @Query("SELECT * FROM School")
    List<School> getAllSchools();    @Query("SELECT * FROM School WHERE  schoolId= :schoolId")
    School loadSchoolById(int schoolId);    @Query("SELECT * FROM School where schoolName = :schoolName ")
    List<School> findSchoolByName(String schoolName);    @Insert(onConflict = IGNORE)
    void insertSchool(School school);    @Insert(onConflict = IGNORE)
    void insertMultipleRecord(List<School> schools);    @Delete
    void deleteSchool(School school);    @Query("SELECT School.schoolName,ClassStudent.className," +
            "COUNT(Student.studentId) as studentcount," +
            "'[' || GROUP_CONCAT(DISTINCT('{\"id\":'||'\"'||Student.studentId || '\"'|| ', \" name \":'|| '\"'||Student.studentName|| '\"'||'}'))||']' studentDetails " +
            "FROM School " +
            "LEFT JOIN ClassStudent ON ClassStudent.schoold=School.schoolId\n" +
            "LEFT JOIN Student ON ClassStudent.classId=Student.classId\n" +
            "GROUP BY School.schoolId")
    List<JoinSchoolClassStudentData> getSchoolCLassDataWithStudents();
}

JOIN query looks like, Student list will return as JSON array and can be converted to object Using Gson.

@Query("SELECT School.schoolName,ClassStudent.className," +
        "COUNT(Student.studentId) as studentcount," +
        "'[' || GROUP_CONCAT(DISTINCT('{\"id\":'||'\"'||Student.studentId || '\"'|| ', \" name \":'|| '\"'||Student.studentName|| '\"'||'}'))||']' studentDetails " +
        "FROM School " +
        "LEFT JOIN ClassStudent ON ClassStudent.schoold=School.schoolId\n" +
        "LEFT JOIN Student ON ClassStudent.classId=Student.classId\n" +
        "GROUP BY School.schoolId")
List<JoinSchoolClassStudentData> getSchoolCLassDataWithStudents();

Download Full Source code From Github

Thank you Happy Coding :D

Article is originally published on http://thoughtnerds.com/join-queries-room-persistence-library/

JOIN queries in Room persistence library

Written by Gokul Balakrishnan