Android Room數據庫多表查詢的使用實例

2019-10-22 18:14:19

字體：大中小

來源：轉載

供稿：網友

前言

在SQLite數據庫中，我們可以指定對象之間的關系，因此我們可以將一個或多個對象與一個或多個其他對象綁定。這就是所謂的一對多和多對多的關系。

既然要多表查詢，所以表之間就得有關聯。這時候我們就得使用新的注解符@ForeignKey

接下來的內容，就需要上節的內容了

@Entitypublic class Company {  @PrimaryKey(autoGenerate = true)  private int id;  private String name;  private int age;  private String address;  private double salary;  public Company(String name, int age, String address, double salary) {    this.name = name;    this.age = age;    this.address = address;    this.salary = salary;  }  //省略了getter/setter方法}

下面我們再新建一個與之關聯的表

@Entity(foreignKeys = @ForeignKey(entity = Company.class,parentColumns = "id",childColumns = "emp_id",onDelete = CASCADE),    indices = @Index(value={"emp_id"},unique = true))public class Department {  @PrimaryKey(autoGenerate = true)  private int id;  private String dept;  @ColumnInfo(name = "emp_id")  private int empId;  public Department(String dept, int empId) {    this.dept = dept;    this.empId = empId;  }  //省略了getter/setter方法}

這里我使用了@ForeignKey關聯了company表，主鍵id，外鍵emp_id，緊接著使用了indices創建了唯一索引。

下面就是創建Dao

@Daopublic interface CompanyDao {  @Query("SELECT * FROM company")  List<Company> getAllCompany();}

@Daopublic interface DepartmentDao {  @Query("SELECT * FROM department")  List<Department> getAllDepartment();  //使用內連接查詢  @Query("SELECT emp_id,name,dept from company INNER JOIN department ON Company.id=Department.emp_id")  List<InnerJoinResult> getDepartmentFromCompany();}

最后就是創建Database

@Database(entities = {Department.class, Company.class}, version = 1, exportSchema = false)public abstract class DepartmentDatabase extends RoomDatabase {  public static final String DB_NAME = "CompanyDatabase.db";  private static volatile DepartmentDatabase instance;  public static synchronized DepartmentDatabase getInstance(Context context) {    if (instance == null) {      instance = create(context);    }    return instance;  }  private static DepartmentDatabase create(final Context context) {    return Room.databaseBuilder(        context,        DepartmentDatabase.class,        DB_NAME).allowMainThreadQueries().build();  }  public abstract DepartmentDao getDepartmentDao();  public abstract CompanyDao getCompanyDao();}

這里我想大家經過之前的文章介紹都很熟悉了吧。這里就不多解釋了，不記得的，請看之前的文章。

具體使用

    List<Company> list = new ArrayList<>();    Company company = new Company("Paul",32,"California",20000.0);    list.add(company);    company = new Company("Allen",25,"Texas",15000.0);    list.add(company);    company = new Company("Teddy",23,"Norway",20000.0);    list.add(company);    company = new Company("Mark",25,"Rich-Mond",65000.0);    list.add(company);    company = new Company("David",27,"Texas",85000.0);    list.add(company);    company = new Company("Kim",22,"South-Hall",45000.0);    list.add(company);    company = new Company("James",24,"Houston",10000.0);    list.add(company);    List<Department> departmentList = new ArrayList<>();    Department department = new Department("IT Billing",1);    departmentList.add(department);    department = new Department("Engineerin",2);    departmentList.add(department);    department = new Department("Finance",7);    departmentList.add(department);    DepartmentDatabase.getInstance(this)    .getCompanyDao().insert(list);    DepartmentDatabase.getInstance(this)        .getDepartmentDao().insert(departmentList);

這樣我們就把需要的數據插入到數據庫了，下面我們查詢一下，看看有沒有插入成功。

查詢代碼如下：

   List<Company> company = DepartmentDatabase.getInstance(this).getCompanyDao().getAllCompany();   LogUtil.debug("Company----->" + company.size());   for (Company result : company) {      LogUtil.debug("result--->" + result.getName() + " " + result.getAge()+" "+result.getAddress()+" "+result.getSalary());   }   List<Department> department = DepartmentDatabase.getInstance(this).getDepartmentDao().getAllDepartment();   LogUtil.debug("Department----->" + department.size());   for (Department result : department) {       LogUtil.debug("result--->" + result.getDept() + " " + result.getEmpId());   }

Android,Room多表查詢,Room數據庫

這樣看來是插入成功了的。。。

好了，下面我們要開始連表查詢了。

當我們查詢的時候，我們就會遇到一個問題，那就是我們關聯兩個表查詢后的字段是company和departemnt組合之后的。所以這里我們又需要去再次創建一個Model類。

public class InnerJoinResult {  @ColumnInfo(name="emp_id")  private int empId;  private String name;  private String dept;  //這里同樣省略了getter/setter方法}

因為上面我們已經將查詢方法寫好了，直接調用即可。

 List<InnerJoinResult> company1 = DepartmentDatabase.getInstance(this).getDepartmentDao().getDepartmentFromCompany(); LogUtil.debug("InnerJoinResult----->" + company1.size()); for (InnerJoinResult result : company1) {    LogUtil.debug("result--->" + result.getEmpId() + " " + result.getName() + " " + result.getDept()); }

查詢結果如下，說明我們多表查詢成功了：

Android,Room多表查詢,Room數據庫