Skip to content

gokul42252/RoomDatabaseJoin

BranchesTags

Repository files navigation

RoomDatabaseJoin

JOIN queries Room persistence library

This tutorial showing how JOIN queries Room persistence library. The basic Room database structure tutorial is here check now 

Here we can learn advanced 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.

joins

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

  1. School
  2. Student
  3. 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 {

<span>@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;
    }


<span>@NonNull



    public long getSchoolId() {
return schoolId;
    }


<span>public void </span><span>setSchoolId</span>(<span>@NonNull </span><span>long </span>schoolId) {
    <span>this</span>.<span>schoolId </span>= schoolId<span>;



    }


<span>public </span>String <span>getSchoolName</span>() {
    <span>return </span><span>schoolName</span><span>;



    }


<span>public void </span><span>setSchoolName</span>(String schoolName) {
    <span>this</span>.<span>schoolName </span>= schoolName<span>;



    }


<span>public </span>String <span>getSchoolAddress</span>() {
    <span>return </span><span>schoolAddress</span><span>;



    }


<span>public void </span><span>setSchoolAddress</span>(String schoolAddress) {
    <span>this</span>.<span>schoolAddress </span>= schoolAddress<span>;



    }


<span>public </span>String <span>getSchoolPhoneNumber</span>() {
    <span>return </span><span>schoolPhoneNumber</span><span>;



    }


<span>public void </span><span>setSchoolPhoneNumber</span>(String schoolPhoneNumber) {
    <span>this</span>.<span>schoolPhoneNumber </span>= schoolPhoneNumber<span>;



    }
}
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;
    }

<span>@NonNull



    public long getStudentId() {
return studentId;
    }


<span>public void </span><span>setStudentId</span>(<span>@NonNull </span><span>long </span>studentId) {
    <span>this</span>.<span>studentId </span>= studentId<span>;



    }


<span>public long </span><span>getClassId</span>() {
    <span>return </span><span>classId</span><span>;



    }


<span>public void </span><span>setClassId</span>(<span>long </span>classId) {
    <span>this</span>.<span>classId </span>= classId<span>;



    }


<span>public </span>String <span>getStudentName</span>() {
    <span>return </span><span>studentName</span><span>;



    }


<span>public void </span><span>setStudentName</span>(String studentName) {
    <span>this</span>.<span>studentName </span>= studentName<span>;



    }


<span>public </span>String <span>getStudentAddress</span>() {
    <span>return </span><span>studentAddress</span><span>;



    }


<span>public void </span><span>setStudentAddress</span>(String studentAddress) {
    <span>this</span>.<span>studentAddress </span>= studentAddress<span>;



    }
}

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 {

<span>@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;
    }


<span>@NonNull



    public long getClassId() {
return classId;
    }


<span>public void </span><span>setClassId</span>(<span>@NonNull </span><span>long </span>classId) {
    <span>this</span>.<span>classId </span>= classId<span>;



    }


<span>public long </span><span>getSchoold</span>() {
    <span>return </span><span>schoold</span><span>;



    }


<span>public void </span><span>setSchoold</span>(<span>long </span>schoold) {
    <span>this</span>.<span>schoold </span>= schoold<span>;



    }


<span>public </span>String <span>getClassName</span>() {
    <span>return </span><span>className</span><span>;



    }


<span>public void </span><span>setClassName</span>(String className) {
    <span>this</span>.<span>className </span>= className<span>;



    }


<span>public </span>String <span>getClassNumber</span>() {
    <span>return </span><span>classNumber</span><span>;



    }


<span>public void </span><span>setClassNumber</span>(String classNumber) {
    <span>this</span>.<span>classNumber </span>= classNumber<span>;



    }


<span>public </span>String <span>getClassDivision</span>() {
    <span>return </span><span>classDivision</span><span>;



    }


<span>public void </span><span>setClassDivision</span>(String classDivision) {
    <span>this</span>.<span>classDivision </span>= classDivision<span>;



    }
}
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 if 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();

JOIN queries Room persistence library

Thank you Happy Coding :D

Full tutorial on http://thoughtnerds.com/join-queries-room-persistence-library/

 

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

7 stars

Watchers

2 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages