Building a Unified Test Management Platform with Spring Boot and Custom MyBatis TypeHandlers

Project Overview

The platform integrates several test‑management capabilities (test account, public data, test case, test case collection, and test result reporting). The backend is built with Spring Boot and the frontend uses mockJS generated by the moco tool.

Public Data Management Design

Public data required by test cases (common parameters and header values) is stored as an Array of objects, each containing text, value, and type. The type defaults to string, so it is omitted in the stored representation. This structure matches the mockJS format used for API requests.

Domain Model

public class PubDataBean extends AbstractBean {
    private static final long serialVersionUID = 8754205931721071606L;

    @Range(min = 1L, max = 5L, message = "环境设置错误")
    private int envId;

    @Min(value = 1L)
    private int uid;

    @NotNull(message = "公共数据不能为空")
    @Size(min = 1, message = "数据长度错误")
    private List<PubDataDetailBean> list;

    @Length(min = 1, max = 16, message = "名字长度错误")
    private String name;

    private int id;
}

Custom MyBatis TypeHandlers

ListPubDetailHandler

Handles conversion between List<PubDataDetailBean> and a VARCHAR column.

package com.okay.family.common.typehandler;

import com.alibaba.fastjson.JSON;
import com.okay.family.common.bean.pubdata.PubDataDetailBean;
import com.okay.family.fun.config.Constant;
import com.okay.family.fun.utils.Join;
import org.apache.ibatis.type.*;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@MappedTypes(PubDataDetailBean.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class ListPubDetailHandler extends BaseTypeHandler<List<PubDataDetailBean>> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<PubDataDetailBean> parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, Join.join(parameter, Constant.MYSQL_SEPARATE));
    }

    @Override
    public List<PubDataDetailBean> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String str = rs.getString(columnName);
        if (str != null) {
            List<String> parts = Arrays.asList(str.split(Constant.MYSQL_SEPARATE));
            return parts.stream()
                        .map(p -> JSON.parseObject(p, PubDataDetailBean.class))
                        .collect(Collectors.toList());
        }
        return null;
    }

    @Override
    public List<PubDataDetailBean> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String str = rs.getString(columnIndex);
        if (str != null) {
            List<String> parts = Arrays.asList(str.split(Constant.PART));
            return parts.stream()
                        .map(p -> JSON.parseObject(p, PubDataDetailBean.class))
                        .collect(Collectors.toList());
        }
        return null;
    }

    @Override
    public List<PubDataDetailBean> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String str = cs.getString(columnIndex);
        if (str != null) {
            List<String> parts = Arrays.asList(str.split(Constant.PART));
            return parts.stream()
                        .map(p -> JSON.parseObject(p, PubDataDetailBean.class))
                        .collect(Collectors.toList());
        }
        return null;
    }
}

JsonHandler

Handles conversion between com.alibaba.fastjson.JSONObject and a VARCHAR column.

package com.okay.family.common.typehandler;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.*;
import java.sql.*;

@MappedTypes(JSONObject.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JsonHandler extends BaseTypeHandler<JSONObject> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter.toJSONString());
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        return json != null ? JSONObject.parseObject(json) : null;
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        return json != null ? JSONObject.parseObject(json) : null;
    }

    @Override
    public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        return json != null ? JSONObject.parseObject(json) : null;
    }
}

MyBatis Mapper Configuration (mapper.xml)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.okay.family.mapper.PubDataMapper">
    <sql id="table">qa_pub_data</sql>

    <resultMap id="entityMap" type="com.alibaba.fastjson.JSONObject">
        <!-- JSON field with custom handler -->
        <result column="list" property="pubdata" typeHandler="com.okay.family.common.typehandler.JsonHandler"/>
    </resultMap>

    <resultMap id="PubDataBean" type="com.okay.family.common.bean.pubdata.PubDataBean">
        <result property="id" column="id"/>
        <result property="uid" column="uid"/>
        <result property="envId" column="envId"/>
        <result property="name" column="name"/>
        <result property="list" column="list" typeHandler="com.okay.family.common.typehandler.ListPubDetailHandler"/>
    </resultMap>

    <select id="getAllDatas" parameterType="java.lang.Integer" resultMap="PubDataBean">
        SELECT * FROM <include refid="table"/> WHERE uid = #{0}
    </select>

    <select id="getDatasByEnv" resultMap="PubDataBean">
        SELECT * FROM <include refid="table"/> WHERE uid = #{arg0} AND envId = #{arg1}
    </select>

    <update id="updateDataAttribute" parameterType="com.okay.family.common.bean.pubdata.EditPubBean">
        UPDATE <include refid="table"/> SET name = #{name} WHERE id = #{id} AND uid = #{uid}
    </update>

    <update id="saveData" parameterType="com.okay.family.common.bean.pubdata.SavePubDataBean">
        UPDATE <include refid="table"/> SET list = #{list,jdbcType=OTHER,typeHandler=com.okay.family.common.typehandler.ListPubDetailHandler} WHERE id = #{id} AND uid = #{uid}
    </update>

    <insert id="add" useGeneratedKeys="true" keyProperty="id" parameterType="com.okay.family.common.bean.pubdata.EditPubBean">
        INSERT INTO <include refid="table"/> (uid, name, envId) VALUES (#{uid}, #{name}, #{envId})
    </insert>

    <delete id="delData" parameterType="com.okay.family.common.bean.pubdata.EditPubBean">
        DELETE FROM <include refid="table"/> WHERE id = #{id} AND uid = #{uid}
    </delete>
</mapper>

Key Points & Future Work

The custom BaseTypeHandler implementations enable seamless persistence of non‑primitive collections and JSON objects in MySQL VARCHAR columns.

Storing public data as an array of {text, value, type} objects satisfies the mockJS contract without extra transformation.

Current implementation works, but further optimisation (e.g., using MySQL JSON column type, batch updates, or stricter validation) is planned.