How to implement ManyToMany in r2dbc

Question

R2DBC does not support composite keys currently. I wonder how we may implement a many-to-many relationship for now?

For example, given the two entities:

@Table
class Item(
  @Id var id: Long?,
  var title: String,
  var description: String,
)

@Table
class Tag(
  @Id
  var id: Long?,
  var title: String,
  var color: String,
)

and their schemas:

CREATE TABLE item (
    id                  SERIAL PRIMARY KEY  NOT NULL,
    title               varchar(100)        NOT NULL,
    description         varchar(500)        NOT NULL
);

CREATE TABLE tag (
    id                  SERIAL PRIMARY KEY  NOT NULL,
    title               varchar(100)        NOT NULL,
    color               varchar(6)          NOT NULL
);

I can create a table for the many-to-many mapping:

CREATE TABLE item_tag (
    item_id bigint  NOT NULL,
    tag_id  bigint  NOT NULL,
    PRIMARY KEY(item_id, tag_id)
);

But how should we define the mapping class ItemTag in kotlin/java?

@Table
class ItemTag(
  // ??????????????????????? @Id ?????????????????????
  var itemId: Long,
  var tagId: Long,
)

Or is it fine to omit the @Id? Then there cannot be any Repository for the class? I guess that would be fine. Is this the only implication?

There is no relationships mapping in R2DBC unlike JPA implementation. You will have to load relationships manually if you want to. Depending upon the database that you are using there are several ways to do so. — nicholasnet, Apr 13 '21 at 03:00
I know, but how to query the ItemTag table without adding an ID field? Without an ID field I cannot create a repository. This is what the question is about. — Stuck, Apr 13 '21 at 08:16
Oh ok I would use `@Transient` instead of `@ID` and load the entity since Composite key is not supported yet I think. — nicholasnet, Apr 14 '21 at 00:59
Just use plain Interface not CrudRepository and use `DatabaseClient` for implementation. Let me know if you need any example. — nicholasnet, Apr 14 '21 at 01:04
An example would be great! And would you still put the `@Table` annotation on the entity? — Stuck, Apr 14 '21 at 01:07

score 3 · Accepted Answer · answered Apr 14 '21 at 01:27

There might be other ways to do this. Since CompositeKey is not supported yet in R2DBC I think. Hence, this is just one way to resolve your problem.

Data class

data class ItemTag(val itemId: Long, val tagId: Long)

Then Repository

interface TagRepository {

    fun getItemTagByTagId(tagId: Long): Flow<ItemTag>
}

Repostory Impl

@Repository
class TagRepositoryImpl(private val databaseClient: DatabaseClient) : TagRepository {
    
    override fun getItemTagByTagId(tagId: Long): Flow<ItemTag> {

        return databaseClient.sql("SELECT * FROM item_tag WHERE tag_id = :tagId")
                             .bind("tagId", tagId)
                             .map(row, _ -> rowToItemTag(row))
                             .all()
                             .flow() 
    }

    private fun rowToItemTag(row: Row): ItemTag {

        return ItemTag(row.get("item_id", Long::class.java)!!, row.get("tag_id", Long::class.java)!!)
    }
    
}

Something like that.

How to implement ManyToMany in r2dbc

1 Answers1