Mybatis之TypeHandler（自定义接收返回数组或对象的数据类型）

1.自定义typeHandler类型

mybatis默认是没有实现jsonb类型字段对应的TypeHandler，所以一般我们需要自定义mybatis的TypeHandler的一个简单实现：

1、jsonarray数组json:

public class JSONArrayHandler extends BaseTypeHandler<JSONArray> {

    public JSONArray delResult(String jsonSource) throws SQLException {
        if(jsonSource != null){
            JSONArray jsonArray;
            try{
                jsonArray = JSONArray.parseArray(jsonSource);
            }catch (JSONException ex){
                throw new SQLException("There is an error converting JSONArray to json format for the content:" + jsonSource);
            }
            return jsonArray;
        }
        return null;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps,
                                    int i,
                                    JSONArray parameter, //需要转换的类型,JSON类型
                                    JdbcType jdbcType) throws SQLException {
        ps.setString(i,parameter.toJSONString());
    }

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

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

    @Override
    public JSONArray getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return delResult(cs.getString(columnIndex));
    }
}

2、jsonObjectd对象:

**
 * JSON格式
 * 
 */
public class JSONTypeHandler extends BaseTypeHandler<JSONObject> {

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType)
			throws SQLException {
		ps.setString(i, (parameter == null ? null : parameter.toJSONString()));
	}

	@Override
	public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {

		String value = rs.getString(columnName);
		if(rs.wasNull()) {
			return null;
		}
		return StringUtils.isEmpty(value) ? null : JSONObject.parseObject(value);
	}

	@Override
	public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		String value = rs.getString(columnIndex);
		if(rs.wasNull()) {
			return null;
		}
		return StringUtils.isEmpty(value) ? null : JSONObject.parseObject(value);
	}

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

在使用mybatis的xml文件形式select时：使用typeHandler选择返回数据类型

    @ColumnType(typeHandler = JSONArrayHandler.class)
    private JSONArray gender;


    @ColumnType(typeHandler = JSONTypeHandler.class)
    private  JSONObject excludedConvertedAudience;

       
<resultMap id="selectMap" type="com.ads.model.entity.audience">
 <result property="excludedConvertedAudience" column="excluded_converted_audience" typeHandler="com.ads.mybatis.handlers.JSONTypeHandler"/>
        <result property="gender" column="gender" typeHandler="com.ads.mybatis.handlers.JSONArrayHandler"/>
   </resultMap>


<select id="selectList" resultMap="selectMap">

        SELECT
        *
        FROM
        list
    </select>

在使用mybatis的xml文件形式insert时：需在values时选择其他类型返回数据，并在typeHandler选择返回数据类型

    <insert id="insertAndGetId" parameterType="com.ads.model.entity.audience.AdsGdtAudienceConfig"  useGeneratedKeys="true" keyProperty="id">
        INSERT INTO ads_gdt_audience_config (genderexcluded_converted_audience)
        VALUES(#{gender,jdbcType=OTHER,typeHandler=com.ads.mybatis.handlers.JSONArrayHandler},#{excludedCustomAudience,jdbcType=OTHER,typeHandler=com.ads.mybatis.handlers.JSONArrayHandler})
    </insert>

数据类型举例：

{
"excludedConvertedAudience": {"conversion_behavior_list":["OPTIMIZATIONGOAL_APP_ACTIVATE"],"excluded_dimension":"EXCLUDED_DIMENSION_APP"},
"gender": ["MALE"]
}