How to Read a Map from MySQL with MyBatis and Resolve ClassCastException

Background and Problem

The author encountered difficulties when working with MySQL and MyBatis, especially with complex SQL defined in XML configuration files. Two main issues arose: reading a map of service ‑to‑ host relationships into the JVM, and inserting a record that pulls values from other tables while using variable constants.

Reading a Map from MySQL

The goal is to cache the service ‑ host mapping in memory. The database stores the service_id column as a numeric type (11‑digit Long). Directly calling get("service_id").toString() caused a ClassCastException because the value was a Long, not a String.

Solution: retrieve the value as a generic Object first, then convert it to a string.

Object service_id = x.get("service_id");
return Integer.parseInt(service_id.toString());

XML Select Configuration

<select id="findAllHost" resultType="java.util.HashMap">
    SELECT service_id, domain
    FROM <include refid="ENV_TABLE"/>
</select>

Java Retrieval Method

List<HashMap<String, String>> findAllHost();

Data Processing

List<HashMap<String, String>> hosts = commonMapper.findAllHost();
Map<Integer, String> collect = hosts.stream()
    .collect(Collectors.toMap(
        x -> {
            Object service_id = x.get("service_id");
            return Integer.parseInt(service_id.toString());
        },
        x -> "https://" + x.get("domain")
    ));
ServerHost.init(collect);

Insert‑Select with Variable Constants

The author needed to create a new record where some columns are populated by queries to other tables. A pitfall appeared when trying to embed a variable value directly in the SELECT clause; MyBatis treated the variable as a column name, leading to a duplicate‑column error.

Fix: use a column alias to represent the constant value.

XML Insert Configuration

<insert id="addCase" useGeneratedKeys="true" keyProperty="id"
        parameterType="com.okay.family.common.bean.testcase.request.CaseAttributeBean">
    INSERT INTO
    <include refid="table"/>
    (uid,editor,envId,serviceId,moduleId,apiId,name,level,host,path,type,method,headermoco,paramsmoco)
    SELECT * FROM (
        #{uid} aa, #{uid} bb, #{envId} cc, #{serviceId} dd, #{moduleId} ee, #{apiId} ff,
        #{name} gg, 1, CONCAT("https://", domain), api_url, api_action, api_method,
        header_para, request_par
        FROM <include refid="api_info"/> a
        LEFT JOIN <include refid="server_env"/> s ON a.service = s.service_id
        WHERE a.id = #{apiId}
    ) t
</insert>

The author notes that storing the host in the database is optional, but kept the SQL record for completeness.