Android Room数据库多表查询的使用实例

Android-Room数据库（介绍）

前言

在SQLite数据库中，我们可以指定对象之间的关系，因此我们可以将一个或多个对象与一个或多个其他对象绑定。这就是所谓的一对多和多对多的关系。

既然要多表查询，所以表之间就得有关联。这时候我们就得使用新的注解符@ForeignKey

接下来的内容，就需要上节的内容了

@Entity
public 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

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

@Dao
public 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());
  }

这样看来是插入成功了的。。。

来源：https://www.jianshu.com/p/f4923374885b