Android—Room数据库多表查询(Relationships)

Android-Room数据库（介绍）

Android—Room自定义类型(TypeConverter)

Android—Room 数据库迁移(Migration)

Android — Room 数据库跳跃式升级(Migration)

前言

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

@Daopublic interface DepartmentDao {    @Query("SELECT * FROM department")    List getAllDepartment();    //使用内连接查询    @Query("SELECT emp_id,name,dept  from company INNER JOIN department ON Company.id=Department.emp_id")    List 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 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 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 = 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 = DepartmentDatabase.getInstance(this).getDepartmentDao().getAllDepartment();      LogUtil.debug("Department----->" + department.size());      for (Department result : department) {             LogUtil.debug("result--->" + result.getDept() + " " + result.getEmpId());      }

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

好了，下面我们要开始连表查询了。

当我们查询的时候，我们就会遇到一个问题，那就是我们关联两个表查询后的字段是company和departemnt组合之后的。所以这里我们又需要去再次创建一个Model类。

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

因为上面我们已经将查询方法写好了，直接调用即可。

 List 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数据库（介绍）

Android—Room自定义类型(TypeConverter)

Android—Room 数据库迁移(Migration)

Android — Room 数据库跳跃式升级(Migration)

前言

更多相关文章

随机推荐