Mybatis3+spring:我可以根据查询结果中的列值将集合结果分成两组吗

Question

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

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

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

尝试实现此JSON响应：

[
    {
        "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"
            }
        ]
    }
]

欣赏任何想法/语法，谢谢！

Answer 1

可能无法将单个列的结果分成两个列表。

要获得您想要的结果，您需要分离结果集中的数据。
这有点冗长，但您可以使用case表达式，例如。

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

使用问题中的示例数据，结果如下所示。

+-------+-----------------------------+----------+-----------------------------+------------+  
| 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      |  
+-------+-----------------------------+----------+-----------------------------+------------+  

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