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

Question

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)
  }

Answer 1

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