Kotlin Android Room中基于复合主键的多对多关系实现方法

Alright, let's break down how to set up a proper many-to-many relationship between your EquipmentModel (which uses a composite primary key) and LocationModel in Room. Since many-to-many relationships require a junction table to map the two entities, here's exactly what you need to do:

Step 1: Create the Junction Table #

First, we need an intermediate entity to store the mappings between Equipment and Location. Since Equipment uses a composite key (component_id + id), our junction table will include both of those fields plus the Location's primary key:

@Entity(
    tableName = "equipment_location_junction",
    primaryKeys = ["component_id", "equipment_id", "location_id"],
    foreignKeys = [
        ForeignKey(
            entity = EquipmentModel::class,
            parentColumns = ["component_id", "id"],
            childColumns = ["component_id", "equipment_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = LocationModel::class,
            parentColumns = ["id"],
            childColumns = ["location_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class EquipmentLocationJunction(
    @ColumnInfo(name = "component_id") val componentId: Int,
    @ColumnInfo(name = "equipment_id") val equipmentId: Int,
    @ColumnInfo(name = "location_id") val locationId: Int
)

A few key notes here:

• The composite primary key ensures each mapping is unique (no duplicate associations between the same Equipment and Location)
• We use ForeignKey.CASCADE so that if an Equipment or Location is deleted, all related mappings are automatically removed—adjust this rule if your business logic requires something else (like RESTRICT to block deletes with existing associations)
• Critical: Remove the locationId field from your original EquipmentModel—since we're moving to many-to-many, a single Equipment can't only be tied to one Location anymore.

Step 2: Create Relationship Data Classes #

Next, we'll define data classes that let Room return a Location with all its associated Equipments, and vice versa. These use Room's @Embedded and @Relation annotations to handle the join behind the scenes.

Location with Associated Equipments

data class LocationWithEquipments(
    @Embedded val location: LocationModel,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = EquipmentLocationJunction::class,
            parentColumn = "location_id",
            entityColumns = ["component_id", "id"]
        )
    )
    val equipments: List<EquipmentModel>
)

The associateBy parameter tells Room to use our junction table, and explicitly maps the Location's ID to the Equipment's composite key fields.

Equipment with Associated Locations

data class EquipmentWithLocations(
    @Embedded val equipment: EquipmentModel,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = EquipmentLocationJunction::class,
            parentColumns = ["component_id", "id"],
            entityColumn = "location_id"
        )
    )
    val locations: List<LocationModel>
)

Here, we specify the Equipment's composite primary key in parentColumns so Room can correctly match the junction table entries to the right Equipment.

Step 3: Add DAO Methods for Relationship Queries #

Finally, update your DAO to include methods that fetch the associated entities, plus methods to add/remove mappings:

@Dao
interface EquipmentLocationDao {
    // Get a single Location and all its linked Equipments
    @Transaction
    @Query("SELECT * FROM location WHERE id = :locationId")
    suspend fun getLocationWithEquipments(locationId: Int): LocationWithEquipments

    // Get a single Equipment and all its linked Locations
    @Transaction
    @Query("SELECT * FROM equipment WHERE component_id = :componentId AND id = :equipmentId")
    suspend fun getEquipmentWithLocations(componentId: Int, equipmentId: Int): EquipmentWithLocations

    // Add a new Equipment-Location mapping
    @Insert
    suspend fun insertJunction(junction: EquipmentLocationJunction)

    // Remove an existing Equipment-Location mapping
    @Query("DELETE FROM equipment_location_junction WHERE component_id = :componentId AND equipment_id = :equipmentId AND location_id = :locationId")
    suspend fun deleteJunction(componentId: Int, equipmentId: Int, locationId: Int)
}

The @Transaction annotation is crucial here—it ensures that Room fetches both the main entity and its associated records in a single atomic operation, preventing inconsistent data.

Quick Bonus Tips #

• If you need bulk operations (like inserting multiple mappings at once), add @Insert suspend fun insertJunctions(junctions: List<EquipmentLocationJunction>) to your DAO
• For full list queries (e.g., all Locations with their Equipments), add a @Transaction @Query("SELECT * FROM location") suspend fun getAllLocationsWithEquipments(): List<LocationWithEquipments> method

内容的提问来源于stack exchange，提问作者Jubiler