mybatis将二维数组使用json格式保存到mysql数据库中，TypeHandler -Java

事例：将一组数据（per_survey_design是二维数组样式的数组，如[[1, 2, 3], [3, 4, 5]]）插入到Exemple表中；

注：使用mysql5.7新特性，字段类型才可以设为json。

先写一个类型转换器：TypeHandler
在mybatis中用于实现java类型和JDBC类型的相互转换

package com.pdm.core.typeHandler;

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

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.pdm.common.util.JsonUtil;

/**
 * <p>
 * Class: ArrayIntegerTypeHandler.java
 * </p>
 * <p>
 * Description: jsonarray 格式的字符串转换为相应的数组
 * </p>
 * 
 * @param <T>
 * 
 */
public class JsonArrayTypeHandler<T> extends BaseTypeHandler<Object> {

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

	@Override
	public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
		String value = rs.getString(columnName);
		if (!value.isEmpty() && !value.equals("null")) {
			return JsonUtil.fromArrayList(value, Object.class);
		}
		return new ArrayList<T>();
	}

	@Override
	public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		String value = rs.getString(columnIndex);
		if (!value.isEmpty() && !value.equals("null")) {
			return JsonUtil.fromArrayList(rs.getString(columnIndex), Object.class);
		}
		return new ArrayList<T>();
	}

	@Override
	public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		String value = cs.getString(columnIndex);
		if (!value.isEmpty() && !value.equals("null")) {
			return JsonUtil.fromArrayList(value, Object.class);
		}
		return new ArrayList<T>();
	}

}

`//将JSON字符串反序列化为Java对象列表。

 public static final <T> List<T> fromArrayList(String json, Class<T> clazz) 
    {
        JsonParser parser = new JsonParser();
        JsonArray Jarray = parser.parse(json).getAsJsonArray();
        List<T> lcs = new ArrayList<T>();
        Gson gson = new GsonBuilder().setPrettyPrinting() .disableHtmlEscaping() .create();
        for(JsonElement obj : Jarray ){
            T cse = gson.fromJson( obj.toString() ,clazz);
            lcs.add(cse);  
        }
        return lcs;

切记：自定义完成之后就需要在mybatis-config.xml文件中配置以注册到mybatis中

<typeHandlers>
          <!-- 注册自定义handler -->
             <typeHandler jdbcType="VARCHAR" javaType="List" handler="com.pdm.core.typeHandler.JsonArrayTypeHandler" />
          </typeHandlers>

<insert id="create" parameterType="com.pdm.predamo.survey.entity.Exemple">
		insert into `Exemple`(`answer_id`,`type`,`att_index`,`level_index`,`per_survey_design`,`value`)
		values(#{answerId},#{type},#{attIndex},#{levelIndex},#{perSurveyDesign,jdbcType=VARCHAR,typeHandler=com.pdm.core.typeHandler.JsonArrayTypeHandler},#{value})
	</insert>

mysql数据库，exemple表中per_survey_design是json类型；
这里将属性设为List

public class Exemple implements Serializable {

	/**
	 * Comment for <code>serialVersionUID</code>
	 */
	private static final long serialVersionUID = 1L;

	private java.lang.Long id;
	private java.lang.Long answerId;
	private java.lang.Integer type;
	private java.lang.Integer attIndex;
	private java.lang.Integer levelIndex;
	private List<Object> perSurveyDesign;
	private java.lang.Integer value;

	public java.lang.Long getId() {
		return this.id;
	}

	public void setId(java.lang.Long id) {
		this.id = id;
	}

	public java.lang.Long getAnswerId() {
		return this.answerId;
	}

	public void setAnswerId(java.lang.Long answerId) {
		this.answerId = answerId;
	}

	public java.lang.Integer getType() {
		return this.type;
	}

	public void setType(java.lang.Integer type) {
		this.type = type;
	}

	public java.lang.Integer getAttIndex() {
		return attIndex;
	}

	public void setAttIndex(java.lang.Integer attIndex) {
		this.attIndex = attIndex;
	}

	public java.lang.Integer getLevelIndex() {
		return levelIndex;
	}

	public void setLevelIndex(java.lang.Integer levelIndex) {
		this.levelIndex = levelIndex;
	}

	public List<Object> getPerSurveyDesign() {
		return this.perSurveyDesign;
	}


	public void setPerSurveyDesign(List<Object> perSurveyDesign) {
		this.perSurveyDesign = perSurveyDesign;
	}


	public java.lang.Integer getValue() {
		return this.value;
	}


	public void setValue(java.lang.Integer value) {
		this.value = value;
	}

}

测试类：

@Test
	@Rollback(value = false)
	public void create() {
		Exemple cbc = new Exemple();
		cbc.setAnswerId(11);
		cbc.setAttLevelIndex(111);
		List<Object> list1 = new ArrayList<Object>();
		List<Integer> list2 = new ArrayList<Integer>();
		List<Integer> list3 = new ArrayList<Integer>();
		list2.add(1);
		list2.add(2);
		list2.add(3);
		list3.add(6);
		list3.add(7);
		list3.add(8);
		list1.add(list2);
		list1.add(list3);
		cbc.setPerSurveyDesign(list1);
		ServiceResult<Integer> result = exempleService.create(cbc);
		GsonBuilder gb = new GsonBuilder();
		gb.setDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println(gb.create().toJson(result));
	}

最终插入成功；

不太好的地方还请大家多多包涵，希望能帮到大家！