如何在MyBatis中用HashMap类型ResultMap获取正确列表并保留TypeHandler？

我需要编写一个通用统计方法，基于两个表字段变量查询统计数据。由于表中pest_status字段是PostgreSQL的bit(3)类型，必须配置TypeHandler来处理。因此我选择java.util.HashMap作为resultMap的类型，既可以为该字段配置TypeHandler，又便于后续重组结果适配JSON格式。

表结构 #

DROP TABLE IF EXISTS "public"."agri_pest_control";
CREATE TABLE "public"."agri_pest_control" (
  "pest_type" varchar(10) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
  "pest_status" bit(3) DEFAULT '000'::"bit",
  "crop_type" varchar(20) COLLATE "pg_catalog"."default",
  "id" int8 NOT NULL,
  "comments" varchar(255) COLLATE "pg_catalog"."default",
  "land_id" int8,
  "test_time" timestamp(6)
)

ALTER TABLE "public"."agri_pest_control" ADD CONSTRAINT "agri_pest_control_pkey" PRIMARY KEY ("id");

DROP TABLE IF EXISTS "public"."agri_land";
CREATE TABLE "public"."agri_land" (
  "id" int4 NOT NULL DEFAULT nextval('agri_land_id_seq'::regclass),
  "geom" "public"."geometry",
  "shape_area" float8,
  "county_id" int2
);

ALTER TABLE "public"."agri_land" ADD CONSTRAINT "agri_land_pkey" PRIMARY KEY ("id");

Mapper方法 #

List<Map<String, Object>> selectStatistics(
        @Param("landId") Long landId,
        @Param("pestStatus") byte[] pestStatus,
        @Param("pestType") String pestType,
        @Param("cropType") String cropType,
        @Param("testStartTime") LocalDateTime localDateTime,
        @Param("testEndTime") LocalDateTime localDateTime1,
        @Param("statisticsFirstObject") String statisticsFirstObject,
        @Param("statisticsSecondObject") String statisticsSecondObject
);

Mapper.xml配置 #

<resultMap id="statisticsMap" type="java.util.HashMap">
        <id property="id" column="row_num"></id>
        <result column = "pest_status" property = "pest_status" typeHandler="cn.iocoder.yudao.module.agri.utils.BitByteaTypeHandler"/>
</resultMap>
<select id="selectStatistics" resultMap="statisticsMap">
    SELECT ROW_NUMBER() over
    ( ORDER BY sl.area ) as row_num, * from
    (SELECT
        ${statisticsFirstObject} as ${statisticsFirstObject},
        ${statisticsSecondObject} as ${statisticsSecondObject},
        count(whl.id) count,
        sum(whl.poly_area) area
    FROM
        agri_pest_control AS pc
    JOIN agri_land whl ON whl.ID = pc.land_id
    <where>
        1=1
        <if test="pestStatus != null and pestStatus.length!=0">
            AND pest_status = #{pestStatus,typeHandler=cn.iocoder.yudao.module.agri.utils.BitByteaTypeHandler}
        </if>
        <if test="cropType != null and cropType !=''">
            AND crop_type = #{cropType}
        </if>
        <if test="testStartTime != null and testEndTime != null">
            AND test_time between #{testStartTime} and #{testEndTime}
        </if>
        <if test="pestType != null and pestType!= ''">
            AND pest_type = #{pestType}
        </if>
        <if test="landId != null and landId != ''">
            AND land_id = #{landId}
        </if>
        AND pc.deleted = 0
        AND whl.deleted = 0
    </where>
        GROUP BY
        ${statisticsFirstObject} ,
        ${statisticsSecondObject}) sl
</select>

结果重组函数 #

public static Map<String, Map<String, Object>> reorganize(String statisticsType, String statisticsFirstObject, String statisticsSecondObject, List<Map<String, Object>> StatisticsResultMapList){
    Map<String, Map<String, Object>> dict = new HashMap<>();
    List<Object> distinctFirstObjectList = new ArrayList<>();
    List<Object> distinctSecondObjectList = new ArrayList<>();
    if (statisticsType!=null && !statisticsType.equals("")){
        for(Map<String, Object> statisticsResultMap:StatisticsResultMapList) {
            if(statisticsFirstObject != null && statisticsSecondObject != null){
                if (!distinctFirstObjectList.contains(statisticsResultMap.get(statisticsFirstObject))){
                    distinctFirstObjectList.add(statisticsResultMap.get(statisticsFirstObject));
                }
                if (!distinctSecondObjectList.contains(statisticsResultMap.get(statisticsSecondObject))){
                    distinctSecondObjectList.add(statisticsResultMap.get(statisticsSecondObject));
                }
            }
        }
        for(Object firstObject:distinctFirstObjectList){
            Map<String, Object> temp = new HashMap<>();
            for(Object secondObject:distinctSecondObjectList){
                for(Map<String, Object> statisticsResultMap:StatisticsResultMapList){
                    if(statisticsResultMap.get(statisticsSecondObject).equals(secondObject) && statisticsResultMap.get(statisticsFirstObject).equals(firstObject)){
                        temp.put(secondObject.toString(), statisticsResultMap.get(statisticsType));
                    }
                }
                dict.put(firstObject.toString(), temp);
            }
        }
    }
    else
        throw exception(STATISTICS_TYPE_NOT_EXISTS);
    return dict;
}

预期与实际结果 #

预期结果 #

实际结果 #

环境信息 #

• PostgreSQL: 12.0
• Driver version: 42.3.8
• Java: 1.8.0_351
• MyBatis: 3.5.10
• MyBatis spring: 2.0.7
• MyBatis-plus: 3.5.3.1

求助 #

如何获取正确的HashMap列表结果，同时不影响resultMap中配置的TypeHandler功能？

内容的提问来源于stack exchange，提问作者Paul