Android:房间库中的多对多关系(sqllite)

在Room中实现多对多关系与在任何SQL数据库中实现多对多关系没有什么不同:拥有一个联接表。对于Room,该连接表是通过连接创建的 @Entity .

This sample project 从…起 this book 说明了该技术。

我有一个 Category 类,它碰巧实现了树结构(父类别和子类别之间的1:N关系):

@Entity(
  tableName="categories",
  foreignKeys=@ForeignKey(
    entity=Category.class,
    parentColumns="id",
    childColumns="parentId",
    onDelete=CASCADE),
  indices=@Index(value="parentId"))
public class Category {
  @PrimaryKey
  @NonNull
  public final String id;
  public final String title;
  public final String parentId;

  @Ignore
  public Category(String title) {
    this(title, null);
  }

  @Ignore
  public Category(String title, String parentId) {
    this(UUID.randomUUID().toString(), title, parentId);
  }

  public Category(String id, String title, String parentId) {
    this.id=id;
    this.title=title;
    this.parentId=parentId;
  }
}

还有,我有一个 Customer ,带有嵌套 CategoryJoin 表示客户和类别之间的M:N关系:

@Entity(indices={@Index(value="postalCode", unique=true)})
class Customer {
  @PrimaryKey
  @NonNull
  public final String id;

  public final String postalCode;
  public final String displayName;
  public final Date creationDate;

  @Embedded
  public final LocationColumns officeLocation;

  public final Set<String> tags;

  @Ignore
  Customer(String postalCode, String displayName, LocationColumns officeLocation,
           Set<String> tags) {
    this(UUID.randomUUID().toString(), postalCode, displayName, new Date(),
      officeLocation, tags);
  }

  Customer(String id, String postalCode, String displayName, Date creationDate,
           LocationColumns officeLocation, Set<String> tags) {
    this.id=id;
    this.postalCode=postalCode;
    this.displayName=displayName;
    this.creationDate=creationDate;
    this.officeLocation=officeLocation;
    this.tags=tags;
  }

  @Entity(
    tableName="customer_category_join",
    primaryKeys={"categoryId", "customerId"},
    foreignKeys={
      @ForeignKey(
        entity=Category.class,
        parentColumns="id",
        childColumns="categoryId",
        onDelete=CASCADE),
      @ForeignKey(
        entity=Customer.class,
        parentColumns="id",
        childColumns="customerId",
        onDelete=CASCADE)},
    indices={
      @Index(value="categoryId"),
      @Index(value="customerId")
    }
  )
  public static class CategoryJoin {
    @NonNull public final String categoryId;
    @NonNull public final String customerId;

    public CategoryJoin(String categoryId, String customerId) {
      this.categoryId=categoryId;
      this.customerId=customerId;
    }
  }
}

你的 @Dao 然后有基于关系检索对象的方法,例如:

  @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);

您需要创建3个表:

1. 歌曲(Song\u id、Song\u名称、艺术家)

2. 播放列表(Playlist\u id、Playlist\u name)

3. 连接表(playlist\u id、song\u id)

参考链接:

Many-to-Many relation exmples

您需要创建一个新的“junction”类,该类表示“beliens-to”关系。在房间里,应该是这样的:

@Entity(tableName = "playlist_entries",
    primaryKeys = { "songId", "playlistId" },
    foreignKeys = {
            @ForeignKey(entity = Song.class,
                        parentColumns = "id",
                        childColumns = "songId"),
            @ForeignKey(entity = Playlist.class,
                        parentColumns = "id",
                        childColumns = "playlistId")
            })
public class PlaylistEntry {
    public final int songId;
    public final int playlistId;

    public PlaylistEntry(final int songId, final int playlistId {
        this.songId = songId;
        this.playlistId = playlistId;
    }
}