Populating an array in the results object with MyBatis

Question

What is the recommended approach for populating an array with values from a column in the SQL result when using MyBatis?

I have the following code which is working but I need the response to be collapsed into a single object that contains all the appointment IDs.

Record

@Serializable
data class SiteSchedule(
    val id: String,
    @Serializable(with = LocalDateSerializer::class) val date: LocalDate,
    val appointmentId: String
)

Mapper

@Mapper
interface SiteScheduleMapper {

  @Select(
      """
      select schedule.id, schedule.date, appointment.id
      from schedule, appointment
      where schedule.date = #{date}
      and appointment.schedule_id = schedule.id
      and appointment.status = 'S'
      """)
  fun findSchedule(
      @Param("date") date: LocalDate
  ): Array<SiteSchedule>
}

SQL Query Result

id	date	appointmentId
1	2021-07-01	100
1	2021-07-01	200
1	2021-07-01	300
1	2021-07-01	400

Current API Response

[
  {
    "id": "1",
    "dateTime": "2021-07-01",
    "appointmentId": "100"
  },
  {
    "id": "1",
    "dateTime": "2021-07-01",
    "appointmentId": "200"
  },
  {
    "id": "1",
    "dateTime": "2021-07-01",
    "appointmentId": "300"
  },
  {
    "id": "1",
    "date": "2021-07-01",
    "appointmentId": "400"
  }
]

Desired API Response

[
  {
    "id": "1",
    "date": "2021-07-01",
    "appointmentIds": ["100","200","300","400"]
  }
]

Answer 1

I solved my issue by creating an XML mapper that defined a collection property in the result map:

SiteScheduleMapper.xml

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.domain.mappers.SiteScheduleMapper">
    <resultMap id="siteScheduleResultMap" type="com.domain.SiteSchedule">
        <id column="sch_id" property="scheduleId" />
        <result column="appt_dt" property="date" />
        <collection property="appointmentIds" ofType="String">
            <result column="appt_id" />
        </collection>
    </resultMap>
</mapper>

Record

@Serializable
data class SiteSchedule
constructor(
    val id: String? = "",
    @Serializable(with = LocalDateSerializer::class) val date: LocalDate? = null,
    val appointmentIds: MutableList<String>? = mutableListOf()
)

Mapper interface

@Mapper
interface SiteScheduleMapper {

  @Select(
      """
      select schedule.id, schedule.date, appointment.id
      from schedule, appointment
      where schedule.date = #{date}
      and appointment.schedule_id = schedule.id
      and appointment.status = 'S'
      """)
  @ResultMap("siteScheduleResultMap")
  fun findSchedule(
      @Param("date") date: LocalDate
  ): Array<SiteSchedule>
}