mybatis与mysql,Mybatis和Mysql的Json类型

Mysql5.7新增加了Json类型字段，但是目前Mybatis中并不支持

1.新建MybatisJsonTypeHandler.java

import com.fasterxml.jackson.annotation.JsonInclude;

import com.fasterxml.jackson.core.JsonParser;

import com.fasterxml.jackson.databind.ObjectMapper;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;

import org.apache.ibatis.type.BaseTypeHandler;

import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.MappedJdbcTypes;

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

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

//import org.codehaus.jackson.map.ObjectMapper;

//import org.codehaus.jackson.map.SerializationConfig.Feature;

//import org.codehaus.jackson.map.annotate.JsonSerialize.Inclusion;

import java.sql.CallableStatement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* Author : Randy

* Date : 2018-06-28 12:55

* Comment :

*/

@MappedJdbcTypes(JdbcType.VARCHAR)

public class MybatisJsonTypeHandler extends BaseTypeHandler {

private static final ObjectMapper mapper = new ObjectMapper();

private Class clazz;

public MybatisJsonTypeHandler(Class clazz) {

if (clazz == null) throw new IllegalArgumentException("Type argument cannot be null");

this.clazz = clazz;

}

@Override

public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {

ps.setString(i, this.toJson(parameter));

}

@Override

public T getNullableResult(ResultSet rs, String columnName) throws SQLException {

return this.toObject(rs.getString(columnName), clazz);

}

@Override

public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {

return this.toObject(rs.getString(columnIndex), clazz);

}

@Override

public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {

return this.toObject(cs.getString(columnIndex), clazz);

}

private String toJson(T object) {

try {

return mapper.writeValueAsString(object);

} catch (Exception e) {

throw new RuntimeException(e);

}

}

private T toObject(String content, Class> clazz) {

if (content != null && !content.isEmpty()) {

try {

return (T) mapper.readValue(content, clazz);

} catch (Exception e) {

throw new RuntimeException(e);

}

} else {

return null;

}

}

static {

mapper.configure(JsonParser.Feature.ALLOW_MISSING_VALUES, false);

mapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);

}

}

2.在mybatis-config.xml中注册该Handler

javaType="com.demo.dto.ReportDataDto"/>

3.在mapper sql map中如下，其中content为mysql中的json类型字段

id,

create_time createTime,

command_id commandId ,

content,

partition_key partitionKey

insert into data(

id,

create_time,

command_id,

content,

partition_key

) values (

#{id},

#{createTime},

#{commandId},

#{content,typeHandler=com.demo.common.MybatisJsonTypeHandler},
　　　　　　　　 #{partitionKey}
)