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

前⾔

在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\ indices = @Index(value={\"emp_id\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 getAllCompany();}

@Dao

public 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\ list.add(company);

company = new Company(\"Allen\ list.add(company);

company = new Company(\"Teddy\ list.add(company);

company = new Company(\"Mark\ list.add(company);

company = new Company(\"David\ list.add(company);

company = new Company(\"Kim\ list.add(company);

company = new Company(\"James\ list.add(company);

List departmentList = new ArrayList<>(); Department department = new Department(\"IT Billing\ departmentList.add(department);

department = new Department(\"Engineerin\ departmentList.add(department);

department = new Department(\"Finance\ 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()); }

查询结果如下，说明我们多表查询成功了：

到这⾥，多表查询就结束了。后续还会继续更新。

以上就是本⽂的全部内容，希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。