Question 210704 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use resultMap with type of HashMap to select list of HashMap with mybatis?

My tables are like this:

    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");

Here is my Mapper function:

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

And I have a Mapper.xml file like this:


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

Basically, I am trying to write a universal method to query the statistics of data based on two table field variables. However, the table field pest_status is a bit(3) which means I need to configure typeHandler for this field. So I come up with the idea to use resultMap to configure typeHandler for the possibly queried field pest_status. Besides, as I want to reorganize the result to better suit json later, I choose HashMap to retrieve the value by its field name. Here is my reorganize function:

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

So I am expecting a list of map as the sql result, when performing the following sql in navicat, the result is what I expected, but after some experiment, mybatis actually returned me with a squeezed result:

Expected:

row_num county_id pest_status count area
1 1 100 132 1631432.957
2 1 000 144 1795440.317
3 1 011 137 1796610.9408
4 1 001 141 1824607.972
5 1 010 135 2280791.764
6 3 011 111 1852187.661
… … … … …

Actual result:

id county_id pest_status count area
1 county_id [48,48,48,48,48,48,48,48,48,48,48] 9999 2.8841…E8

So how can I get a list of map results without hindering the typeHandler I needed in the resultMap?

Environment:

  • 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
Tags:

2

Answers


  1. 0

    To use resultMap with a HashMap type to select a list of HashMaps with MyBatis, you can define a resultMap in your XML configuration file and specify the HashMap as the resultType. Then, use the resultMap in your SQL query to map the result to the desired HashMap structure.

    Login or Signup to reply.
  2. 0

    Based on the provided information, it seems that the issue is with the mapping of the pest_status column in the result. Since it is a bit(3) column, MyBatis is mapping it as a byte array (byte[]) by default.

    To resolve this issue and get the desired result, you can modify your Mapper.xml file to use a custom type handler for the pest_status column. Here’s how you can do it:

    1. Create a custom type handler for the bit(3) column. You can implement the TypeHandler interface and override the necessary methods. Here’s an example:
    import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BitTypeHandler extends BaseTypeHandler<String> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }
}
    1. Register the custom type handler in your MyBatis configuration. You can do this by adding the following line to your MyBatis configuration XML file:
    <typeHandlers>
    <typeHandler handler="com.example.BitTypeHandler"/>
</typeHandlers>
    1. Modify your Mapper.xml file to use the custom type handler for the pest_status column. Here’s an example:
    <resultMap id="statisticsResultMap" type="java.util.HashMap">
    <id property="row_num" column="row_num"/>
    <result property="county_id" column="county_id"/>
    <result property="pest_status" column="pest_status" typeHandler="com.example.BitTypeHandler"/>
    <result property="count" column="count"/>
    <result property="area" column="area"/>
</resultMap>

    By specifying the typeHandler attribute in the <result> element, you can instruct MyBatis to use the custom type handler for the pest_status column.

    With these changes, MyBatis should correctly map the pest_status column as a String in the result, instead of a byte array.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search