How to map the result of jooq multiset into Hashmap(Java Map)?

user_7624673 0
Viewed 232

I have the following class and query. I want to use multiset to map the result of the images into Map<String, String>(Key: OrderNumber / Value: FileKey), but I don't know how to do it. Could you help me how to map the multiset result into hashmap?

data class User(
  val id: UUID,
  val name: String,
  val images: Map<String, String>?
)

@Repository
@Transactional(readOnly = true)
class FetchUserRepository(private val ctx: DSLContext) {
  private val user = JUser.USER
  private val userImage = JUserImage.USER_IMAGE 

  override fun fetch(): List<User> {
    return ctx.select(
      user.ID,
      user.NAME,
      multiset(
        select(userImage.ORDER_NUMBER.cast(String::class.java), userImage.FILE_KEY)
          .from(userImage)
          .where(userImage.USER_ID.eq(user.ID))
      ).convertFrom { r -> r.map(mapping(???)) } // I'm not sure how to map the result to hashmap
    )
      .from(user)
      .fetchInto(User::class.java)
  }
jooq kotlin
2 Answers

jOOQ 3.16 solution

The type of your multiset() expression is Result<Record2<String, String>>, so you can use the Result.intoMap(Field, Field) method, or even Result.collect(Collector) using the Records.intoMap() collector, which allows for avoiding the repetition of field names:

{ r -> r.collect(Records.intoMap()) }

I've explained this more in detail in a blog post, here.

jOOQ 3.17 solution

In fact, this seems so useful and powerful, let's add some convenience on top of the existing API using some extensions (located in the jOOQ-kotlin extensions module):

// New extension functions, e.g.
fun <R : Record, E> Field<Result<R>>.collecting(collector: Collector<R, *, E>)
  = convertFrom { it.collect(collector) }

fun <K, V> Field<Result<Record2<K, V>>>.intoMap(): Field<Map<K, V>> 
  = collecting(Records.intoMap())

// And then, you can write:
multiset(...).intoMap()

The feature request is here: https://github.com/jOOQ/jOOQ/issues/13538

user_5217990

In addition to Lukas's answer, I would like to provide an alternative option with jsonObject & jsonObjectAgg.

The result of this query would be returned as JSON format, and it can be easily projected to the target class via Jackson or whatever. (It is really powerful feature when it comes to nested collection within the target class)

I believe it is the one of the coolest features of jOOQ as MULTISET :)

data class User(
  val id: UUID,
  val name: String,
  val images: Map<String, String>?
)

@Repository
@Transactional(readOnly = true)
class FetchUserRepository(private val ctx: DSLContext) {
  private val user = JUser.USER
  private val userImage = JUserImage.USER_IMAGE

  override fun fetch(): List<User> {
    return ctx.select(
      jsonObject(
        key("id").value(user.ID),
        key("name").value(user.NAME),
        key("images").value(
          field(
            select(
              jsonObjectAgg(
                userImage.ORDER_NUMBER.cast(String::class.java),
                userImage.FILE_KEY
              )
            )
              .from(userImage)
              .where(userImage.USER_ID.eq(user.ID))
          )
        )
      )
    )
      .from(user)
      .fetchInto(User::class.java)
  }
}
user_76246730
Related