MyBatis将查询的两个字段分别作为Map的key和value

问题背景

首先查出 危险源id 和 危险源报警的个数 alarm

遍历危险源，将报警数填充进去

所以，我需要根据id得到alarm

最方便的就是Map

经过sql查询

        -- 危险源下的对象的报警个数
        select id, ifnull(alarm_count,0) alarm from spang_monitor_danger_source
        left join(
            -- 危险源被绑定的对象的报警个数
            select hazard_source,count(alarm_count) alarm_count from spang_monitor_target
            left join (
                -- 统计对象绑定的点位报警个数
                select target_num,count(point_num) alarm_count from (
                    -- 被对象关联的报警点位
                    select t.target_num, p.point_num from spang_monitor_target_point t
                    left join (
                        -- 报警点位
                        select point_num from spang_monitor_point where is_deleted = 0 and alarm_status = 1
                    )p on p.point_num=t.point_num
                )t1 GROUP BY target_num
            )t2 on target_number = t2.target_num
            where is_deleted = 0
            GROUP BY hazard_source
        )t3 on t3.hazard_source = id
        where is_deleted = 0

数据库查询出来的数据结果如下：

解决方案

把id作为key，alarm作为value。

1. MapResultHandler

package com.spang.monitor.common.handler;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

import java.util.HashMap;
import java.util.Map;

@SuppressWarnings("all")
public class MapResultHandler<K,V> implements ResultHandler<Map<K,V>> {

    private final Map<K,V> mappedResults = new HashMap<>();

    @Override
    public void handleResult(ResultContext context) {
        Map map = (Map) context.getResultObject();
        mappedResults.put((K)map.get("key"), (V)map.get("value"));
    }

    public Map<K,V> getMappedResults() {
        return mappedResults;
    }
}

2. Dao

void selectAlarmCount(MapResultHandler<Long, Double> resultHandler);

3. xml

    <resultMap id="mapResult" type="java.util.HashMap">
        <result property="key" column="id"/>
        <result property="value" column="alarm"/>
    </resultMap>
    <select id="selectAlarmCount" resultMap="mapResult">
        -- 危险源下的对象的报警个数
        select id, ifnull(alarm_count,0) alarm from spang_monitor_danger_source
        left join(
            -- 危险源被绑定的对象的报警个数
            select hazard_source,count(alarm_count) alarm_count from spang_monitor_target
            left join (
                -- 统计对象绑定的点位报警个数
                select target_num,count(point_num) alarm_count from (
                    -- 被对象关联的报警点位
                    select t.target_num, p.point_num from spang_monitor_target_point t
                    left join (
                        -- 报警点位
                        select point_num from spang_monitor_point where is_deleted = 0 and alarm_status = 1
                    )p on p.point_num=t.point_num
                )t1 GROUP BY target_num
            )t2 on target_number = t2.target_num
            where is_deleted = 0
            GROUP BY hazard_source
        )t3 on t3.hazard_source = id
        where is_deleted = 0
    </select>

4. 使用方式

        //危险源对应的对象的报警个数
        MapResultHandler<Long, Double> resultHandler = new MapResultHandler<>();
        dangerSourceMapper.selectAlarmCount(resultHandler);
        Map<Long, Double> alarmCountMap = resultHandler.getMappedResults();

注意：

1、dao中定义的接口返回值类型必须是void，如上面void selectAlarmCount(MapResultHandler<Long, Double> resultHandler);

2、xml中定义的resultMap返回值类型是HashMap，其里面定义的两个result中的key要与MapResultHandler.java中代码mappedResults.put(m.get(“key”), m.get(“value”))保持一致；其value属性必须和Mybatis.xml中sql的返回值名称一致

3、Map resultMap = handler.getMappedResults();中的resultMap就是我们一开始讲到的需要的数据类型