Mysql 5.7新增json类型对应的Mybatis TypeHandler欢迎使用Markdown编辑器

112 阅读 0 评论 74 点赞

我是靠谱客的博主秀丽鲜花，这篇文章主要介绍Mysql 5.7新增json类型对应的Mybatis TypeHandler欢迎使用Markdown编辑器，现在分享给大家，希望可以做个参考。

欢迎使用Markdown编辑器

Mysql 5.7中新增了Json类型，但是Mybatis中并没有很好地支持，必须自己编写TypeHandler进行处理。

TypeHandler

复制代码

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(GeoPoint.class)
public class GeoPointTypeHandler extends BaseTypeHandler<GeoPoint> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, GeoPoint parameter, JdbcType jdbcType) throws SQLException {

}

@Override
    public GeoPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return bytes2GeoPoint(rs.getBytes(columnName));
    }

@Override
    public GeoPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return bytes2GeoPoint(rs.getBytes(columnIndex));
    }

@Override
    public GeoPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return bytes2GeoPoint(cs.getBytes(columnIndex));
    }

private GeoPoint bytes2GeoPoint(byte[] bytes) {
        if (bytes != null && bytes.length == 25) {
            double lon = bytes2Double(bytes, 9);
            double lat = bytes2Double(bytes, 17);

return new GeoPoint(lat, lon);
        } else {
            return null;
        }
    }

private double bytes2Double(byte[] bytes, int start) {
        long value = 0;
        for (int i = 0; i < 8; i++) {
            value |= ((long) (bytes[start + i] & 0xff)) << (8 * i);
        }
        return Double.longBitsToDouble(value);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(GeoPoint.class)
public class GeoPointTypeHandler extends BaseTypeHandler<GeoPoint> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, GeoPoint parameter, JdbcType jdbcType) throws SQLException {

    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return bytes2GeoPoint(rs.getBytes(columnName));
    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return bytes2GeoPoint(rs.getBytes(columnIndex));
    }

    @Override
    public GeoPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return bytes2GeoPoint(cs.getBytes(columnIndex));
    }

    private GeoPoint bytes2GeoPoint(byte[] bytes) {
        if (bytes != null && bytes.length == 25) {
            double lon = bytes2Double(bytes, 9);
            double lat = bytes2Double(bytes, 17);

            return new GeoPoint(lat, lon);
        } else {
            return null;
        }
    }

    private double bytes2Double(byte[] bytes, int start) {
        long value = 0;
        for (int i = 0; i < 8; i++) {
            value |= ((long) (bytes[start + i] & 0xff)) << (8 * i);
        }
        return Double.longBitsToDouble(value);
    }
}

在getNullableResult中，我们对Mysql中取出的byte[]进行解析，取出经纬度数据，转换为GeoPoint类型。但是插入操作仍然需要在mapper.xml文件中手动修改，见下文。

mybstis-config.xml

复制代码

1
2
3
4
5
<typeHandlers>
        <typeHandler handler="com.test.GeoPointTypeHandler"
                     javaType="org.elasticsearch.common.geo.GeoPoint" jdbcType="OTHER"/>
    </typeHandlers>

在Mybatis配置文件中配置该TypeHandler。

Mapper.xml

复制代码

1
2
3
4
5
6
7
8
9
10
11
<mapper namespace="">
  <resultMap id="BaseResultMap" type="">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="location" jdbcType="OTHER" property="location" javaType="org.elasticsearch.common.geo.GeoPoint" typeHandler="com.test.GeoPointTypeHandler" />
  </resultMap>
  <insert id="insert" parameterType="com.test.DistrictDO">
    insert into district (id, location)
    values (#{id,jdbcType=INTEGER}, POINT(#{location.lon}, #{location.lat}))
  </insert>
</mapper>

mapper.xml中，该字段jdbcType=“OTHER”。注意在插入时，需要使用POINT(#{location.lon}, #{location.lat})。

##DO

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
public class DistrictDO {
    /**
     */
    private Integer id;

    /**
     *   经纬度
     */
    private Object location;
    // getter and setter
}