xml 查询一对多JsonArrayTypeHandler

JsonArrayTypeHandler 

package com.zq.system.utils.Mybatis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

public class JsonArrayTypeHandler extends BaseTypeHandler<Object> {
	protected final Logger logger = LoggerFactory.getLogger(JsonArrayTypeHandler.class);

	// 核心的转换处理
	private JSONArray parse(String json) {
		try {
			if (json == null || json.length() == 0) {
				return null;
			}
			return JSONArray.parseArray(json);
		} catch (JSONException e) {
			logger.error(json, e);
			return null;
		}
	}

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

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

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

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

JsonIntegerArrayTypeHandler

package com.zq.system.utils.Mybatis;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

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

/*
   <columnOverride column="ids" javaType="java.lang.Integer[]" typeHandler="JsonIntegerArrayTypeHandler"/>
 */
public class JsonIntegerArrayTypeHandler extends BaseTypeHandler<Integer[]> {
    private static final ObjectMapper mapper = new ObjectMapper();

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

    @Override
    public Integer[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.toObject(rs.getString(columnName));
    }

    @Override
    public Integer[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.toObject(rs.getString(columnIndex));
    }

    @Override
    public Integer[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex));
    }

    private String toJson(Integer[] params) {
        try {
            return mapper.writeValueAsString(params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "[]";
    }

    private Integer[] toObject(String content) {
        if (content != null && !content.isEmpty()) {
            try {
                return (Integer[]) mapper.readValue(content, Integer[].class);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }
}

JsonNodeTypeHandler

package com.zq.system.utils.Mybatis;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

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

/*
   <columnOverride column="json_string" javaType="com.fasterxml.jackson.databind.JsonNode" typeHandler="JsonNodeTypeHandler"/>
 */
public class JsonNodeTypeHandler extends BaseTypeHandler<JsonNode> {
    private static final ObjectMapper mapper = new ObjectMapper();


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JsonNode parameter, JdbcType jdbcType) throws SQLException {
        String str = null;
        try {
            str = mapper.writeValueAsString(parameter);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            str = "{}";
        }
        ps.setString(i, str);
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String jsonSource = rs.getString(columnName);
        if (jsonSource == null) {
            return null;
        }
        try {
            JsonNode jsonNode = mapper.readTree(jsonSource);
            return jsonNode;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String jsonSource = rs.getString(columnIndex);
        if (jsonSource == null) {
            return null;
        }
        try {
            JsonNode jsonNode = mapper.readTree(jsonSource);
            return jsonNode;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;

    }

    @Override
    public JsonNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String jsonSource = cs.getString(columnIndex);
        if (jsonSource == null) {
            return null;
        }
        try {
            JsonNode jsonNode = mapper.readTree(jsonSource);
            return jsonNode;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}

JsonStringArrayTypeHandler

package com.zq.system.utils.Mybatis;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

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

/*
   <columnOverride column="urls" javaType="java.lang.String[]" typeHandler="JsonStringArrayTypeHandler"/>
 */
public class JsonStringArrayTypeHandler extends BaseTypeHandler<String[]> {
    private static final ObjectMapper mapper = new ObjectMapper();

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

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

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

    @Override
    public String[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex));
    }

    private String toJson(String[] params) {
        try {
            return mapper.writeValueAsString(params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "[]";
    }

    private String[] toObject(String content) {
        if (content != null && !content.isEmpty()) {
            try {
                return (String[]) mapper.readValue(content, String[].class);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }
}

	<resultMap type="ZqSiteInternetOfThings"
		id="ZqSiteInternetOfThingsResult">
		<result property="id" column="id" />
		<result property="eId" column="e_id" />
		<result property="iccid" column="iccid" />
		<result property="imei" column="imei" />
		<result property="createTime" column="create_time" />
		<result property="createBy" column="create_by" />
		<result property="netInTime" column="net_in_time" />
		<result property="netExpireTime" column="net_expire_time" />
		<result property="setMealType" column="set_meal_type" />
		<result property="associatedBy" column="associated_by" />
        <result property="eName" column="e_name" />
		<result property="noticeNameList" column="JSON"
				typeHandler="com.zq.system.utils.Mybatis.JsonArrayTypeHandler" />
	</resultMap>



	<sql id="selectZqSiteInternetOfThingsVo">
		select iot.id, iot.e_id, iot.iccid, iot.imei, iot.create_time,
			   iot.create_by, iot.net_in_time, iot.net_expire_time,
			   iot.set_meal_type, iot.associated_by, ees.e_name,JSON.result as JSON
		from zq_site_internet_of_things
				 iot
				 left join zq_exchange_electric_station ees on iot.e_id=ees.e_id

				 left join (
			SELECT
				CONCAT(
						'[',
						IFNULL(
								GROUP_CONCAT(
										CONCAT(
												'{ "noticeName":"',concat(zcp.staff_name,'(',zcp.staff_phone,')'),'"}'
											) SEPARATOR
		','
									),
								''
							),
						']'
					) as result,ssp.things_id
			FROM zq_site_thing_notice ssp
					 LEFT JOIN zq_oa_staff zcp ON ssp.staff_id= zcp.staff_id
					 LEFT JOIN zq_site_internet_of_things zs ON zs.id= ssp.things_id
			group by ssp.things_id
		) JSON ON JSON.things_id = iot.id

	</sql>