mybatis 3 + spring : Can I put a collection result into two groups based on a column value from the query result

Question

I have a 2 java models like this

public class DeviceData {
    private String name;
    private List<Metric> up;
    private List<Metric> down;
}
public class Metric{
    private Long data;
    private LocalDateTime timeStamp;
}

actual query fetches results name-wise from db with column names like this..

-------------------------------------------------------------
data | timestamp            | name  | trafficType
-------------------------------------------------------------
10   | 2020-10-08 13:10:00  | test1 | down
20   | 2020-10-08 12:15:00  | test1 | up
30   | 2020-10-08 13:10:00  | test2 | down
40   | 2020-10-08 12:15:00  | test2 | up

No, I have been trying to make mybatis xml to divide the above query result into two groups based on the column trafficType up, down and copy the divided collection into the up list, down list of class DeviceData.java respectively. I tried using a discriminator but didn't seem to work as I need both cases.

So far resultMap is like this.. (basic)

<resultMap id="trafficData" type="DeviceData" autoMapping="true">
    <id property="name" column="name" jdbcType="VARCHAR"/>
    <collection property="upload" ofType="Metric" autoMapping="true">
    </collection>
</resultMap>

trying to achieve this JSON response:

[
    {
        "name": "test1",
        "down": [
            {
                "data": 10,
                "timeStamp": "2020-10-08 13:10:00"
            }
        ],
        "up": [
            {
                "data": 20,
                "timeStamp": "2020-10-08 12:15:00"
            }
        ]
    },
    {
        "name": "test2,
        "down": [

            {
                "data": 30,
                "timeStamp": "2020-10-08 13:10:00"
            }
        ],
        "up": [

            {
                "data": 40,
                "timeStamp": "2020-10-08 12:15:00"
            }
        ]
    }
]

Appreciate any ideas/syntax, thank you!

Answer 1

It may not be possible to divide the result of a single column into two lists.

To obtain the result you want, you need to separate the data in the result set.
It's a little bit verbose, but you can use CASE expression, for example.

select
  name,
  case traffic_type
    when 'up' then ts
    else null
  end as up_timeStamp,
  case traffic_type
    when 'up' then data
    else null
  end as up_data,
  case traffic_type
    when 'down' then ts
    else null
  end as down_timeStamp,
  case traffic_type
    when 'down' then data
    else null
  end as down_data
from device_data

With the sample data in your question, the result would look as follows.

+-------+-----------------------------+----------+-----------------------------+------------+  
| NAME  |         UP_TIMESTAMP        |  UP_DATA |        DOWN_TIMESTAMP       |  DOWN_DATA |  
+-------+-----------------------------+----------+-----------------------------+------------+  
| test1 |  null                       |  null    |  2020-10-08 13:10:00.000000 |  10        |  
| test1 |  2020-10-08 12:15:00.000000 |  20      |  null                       |  null      |  
| test2 |  null                       |  null    |  2020-10-08 13:10:00.000000 |  30        |  
| test2 |  2020-10-08 12:15:00.000000 |  40      |  null                       |  null      |  
+-------+-----------------------------+----------+-----------------------------+------------+  

The data you want to map to up list is in the columns with up_ prefix.
The same goes for down.

Regarding the result map, there should be two <collection />s as there are two list properties in DeviceType.
And it would be better to define another result map for Metric and re-use it with columnPrefix in this case.

<resultMap id="trafficData" type="test.DeviceData">
  <id property="name" column="name" />
  <collection property="up"
      resultMap="metric" columnPrefix="up_" />
  <collection property="down"
      resultMap="metric" columnPrefix="down_" />
</resultMap>

<resultMap id="metric" type="test.Metric">
  <id property="timeStamp" column="timeStamp" />
  <result property="data" column="data" />
</resultMap>

Here I assumed that timeStamp is the ID of Metric.
If there is no <id />, MyBatis will use the combination of timeStamp and data as the ID and it's less efficient even though the result may be the same.

If you want auto-mapping, just add autoMapping="true" to the result maps.

p.s.
Alternatively, it would also be possible to use ResultHandler.