Implementing DAO Methods

In addition to setting up the join entity, we need to leverage it in our DAO. Otherwise, the join entity is pointless.

Adding and Removing Relations

In many ORMs, where relations are directly implemented on model objects, you connect objects by direct manipulation. In our case, a Customer might have addCategory() and removeCategory() methods, and Category might have addCustomer() and removeCustomer().

Since Room models foreign keys, not relations, that’s not how we connect a Customer and a Category. Instead, we do it much the same way as you would with plain SQL: @Insert and @Delete Customer.CategoryJoin instances representing a particular customer-category connection.

And, to that end, we have suitable DAO methods for this:

  @Insert
  void insert(Customer.CategoryJoin... joins);

  @Delete
  void delete(Customer.CategoryJoin... joins);

And, to connect a specific Customer instance to a specific Category instance, we set up the Customer.CategoryJoin instance and insert() it:

    tags.add("sculpture");
    tags.add("bronze");
    tags.add("slow-pay");

    final LocationColumns loc=new LocationColumns(40.7047282, -74.0148544);

    final Customer firstCustomer=new Customer("10001", "Fearless Girl", loc, tags);

    tags.remove("slow-pay");
    tags.add("large");

    final Customer secondCustomer=new Customer("10002", "Charging Bull", loc, tags);

    store.insert(firstCustomer, secondCustomer);

    final Category root=new Category("Root!");
    final Category child=new Category("Child!", root.id);

    store.insert(root, child);

    final Customer.CategoryJoin join=
      new Customer.CategoryJoin(root.id, secondCustomer.id);

    store.insert(join);

Fetching Via the Join

If an ORM offers addCategory() and removeCustomer() methods, presumably that ORM also offers getCategories() on Customer and getCustomers() on Category, to identify the members of a relation with a specific entity.

Again, Room does not work that way.

Instead, we crack open our SQL syntax reference and craft an INNER JOIN ourselves, to use in a @Query method:

  @Query("SELECT categories.* FROM categories\n"+
    "INNER JOIN customer_category_join ON categories.id=customer_category_join.categoryId\n"+
    "WHERE customer_category_join.customerId=:customerId")
  List<Category> categoriesForCustomer(String customerId);

  @Query("SELECT Customer.* FROM Customer\n"+
    "INNER JOIN customer_category_join ON Customer.id=customer_category_join.customerId\n"+
    "WHERE customer_category_join.categoryId=:categoryId")
  List<Customer> customersForCategory(String categoryId);

Here we have methods that return the members of a specific relation, so we can find the categories for a Customer or the customers for a Category. And the DAO methods return sensible data types. But, it is our job to set up the SQL.

So, in the case of categoriesForCustomer(), our SQL:

• Retrieves all columns from the categories table…
• …where we JOIN with customer_category_join based on the IDs…
• …and find all those where the join entity points to a specific customer ID