Java 定义类型处理MySQL point类型数据

1.三个类来处理

引入maven依赖

        <!-- 引入 jts 库解析 POINT -->
        <dependency>
            <groupId>com.vividsolutions</groupId>
            <artifactId>jts</artifactId>
            <version>1.13</version>
        </dependency>

import javax.validation.constraints.Max;
import javax.validation.constraints.Min;
import java.io.Serializable;

/**
 *  经纬度对象
 * <br>
 *  x 经度 lon 浮点数，范围为180 ~ -180 <br>
 *  y 纬度 lat 浮点数，范围为90 ~ -90
 */
public class GeoPoint implements Serializable {

    /**
     * 参数校验
     *  在lon、lat 字段添加@Min @Max 注解，并且在使用该类的地方加 @Valid 注解
     *  如：
     *   @Valid
     *   private GeoPoint lonlat;
     */

    // controller @RequestBody 参数内嵌套对象，必须有一个无参的构造函数
    public GeoPoint() {}
    /**
     * 经度
     */
    @Min(value = -180, message = "经度lon不能小于-180")
    @Max(value = 180, message = "经度lon不能大于180")
    private double lon;
    /**
     * 纬度
     */
    @Min(value = -90, message = "纬度lat不能小于-90")
    @Max(value = 90, message = "纬度lat不能大于90")
    private double lat;

    /**
     *
     * @param x 经度 lon 浮点数，范围为180 ~ -180
     * @param y 纬度 lat 浮点数，范围为90 ~ -90
     */
    public GeoPoint(double x, double y) {
        this.lon = x;
        this.lat = y;
    }

    public double getLon() {
        return lon;
    }

    public void setLon(double lon) {
        this.lon = lon;
    }

    public double getLat() {
        return lat;
    }

    public void setLat(double lat) {
        this.lat = lat;
    }


    @Override
    public String toString() {
        return "GeoPoint{" +
                "lon=" + lon +
                ", lat=" + lat +
                '}';
    }

}

import com.vividsolutions.jts.geom.*;
import com.vividsolutions.jts.geom.impl.CoordinateArraySequence;
import com.vividsolutions.jts.geom.impl.CoordinateArraySequenceFactory;
import com.vividsolutions.jts.io.*;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
public class GeoPointConverter {
    /**
     * Little endian or Big endian
     */
    private int byteOrder = ByteOrderValues.LITTLE_ENDIAN;
    /**
     * Precision model
     */
    private PrecisionModel precisionModel = new PrecisionModel();
    /**
     * Coordinate sequence factory
     */
    private CoordinateSequenceFactory coordinateSequenceFactory = CoordinateArraySequenceFactory.instance();
    /**
     * Output dimension
     */
    private int outputDimension = 2;
    /**
     * Convert byte array containing SRID + WKB Geometry into Geometry object
     */
    public GeoPoint from(byte[] bytes) {
        if (bytes == null) {
            return null;
        }
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes)) {
            // Read SRID
            byte[] sridBytes = new byte[4];
            inputStream.read(sridBytes);
            int srid = ByteOrderValues.getInt(sridBytes, byteOrder);
            // Prepare Geometry factory
            GeometryFactory geometryFactory = new GeometryFactory(precisionModel, srid, coordinateSequenceFactory);
            // Read Geometry
            WKBReader wkbReader = new WKBReader(geometryFactory);
            Geometry geometry = wkbReader.read(new InputStreamInStream(inputStream));
            Point point = (Point) geometry;
            // convert to GeoPoint
            GeoPoint geoPoint = new GeoPoint(point.getX(), point.getY());
            return geoPoint;
        } catch (IOException | ParseException e) {
            throw new IllegalArgumentException(e);
        }
    }
    /**
     * Convert Geometry object into byte array containing SRID + WKB Geometry
     */
    public byte[] to(GeoPoint geoPoint) {
        if (geoPoint == null) {
            return null;
        }
        //  @param x 经度 lon
        //  @param y 纬度 lat
        Coordinate coordinate = new Coordinate(geoPoint.getLon(), geoPoint.getLat());
        CoordinateArraySequence coordinateArraySequence = new CoordinateArraySequence(new Coordinate[]{coordinate}, 2);
        Point point = new Point(coordinateArraySequence, new GeometryFactory());
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            // Write SRID
            byte[] sridBytes = new byte[4];
            ByteOrderValues.putInt(point.getSRID(), sridBytes, byteOrder);
            outputStream.write(sridBytes);
            // Write Geometry
            WKBWriter wkbWriter = new WKBWriter(outputDimension, byteOrder);
            wkbWriter.write(point, new OutputStreamOutStream(outputStream));
            return outputStream.toByteArray();
        } catch (IOException ioe) {
            throw new IllegalArgumentException(ioe);
        }
    }
}

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.stereotype.Component;

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

@Component //注入Bean,全局配置使其自动转换，不需要在sql层手动指定
@MappedJdbcTypes(JdbcType.OTHER) // JdbcType只能用OTHER类型，没有point类型
@MappedTypes(GeoPoint.class) 
public class GeoPointTypeHandler extends BaseTypeHandler<GeoPoint> {
    GeoPointConverter converter = new GeoPointConverter();
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, GeoPoint parameter, JdbcType jdbcType) throws SQLException {
        ps.setBytes(i, converter.to(parameter));
    }
    @Override
    public GeoPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return converter.from(rs.getBytes(columnName));
    }
    @Override
    public GeoPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return converter.from(rs.getBytes(columnIndex));
    }
    @Override
    public GeoPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return converter.from(cs.getBytes(columnIndex));
    }
}

2.使用

model中使用 

public class PositionInfo implements Serializable
{
    private static final long serialVersionUID = 1L;

    /** 经纬度 */
    @Valid //接口中使用 @Valid 对参数 PositionInfo 进行校验，需要在这里也加@Valid 注解
    private GeoPoint lonlat;


//....
}

controller

@Validated
@RestController
@RequestMapping("/positioninfo")
public class PositionInfoController
{
    @Autowired
    private PositionInfoService positionInfoService;


    /**
     * 新增地理位置信息
     */
    @PostMapping
    public Result addPositionInfo( @RequestBody @Valid PositionInfo positionInfo)
    {
        System.err.println(positionInfo);
        return reult(positionInfoService.insertPositionInfo(positionInfo));
    }

DAO

    @Select("select * from position_info_table")
//    @Results(
//        @Result(column = "lonlat",property = "lonlat",
//                javaType = GeoPoint.class,typeHandler = GeoPointTypeHandler.class)
//    )
     List<PositionInfo> listPositionInfo(PositionInfo positionInfo);



    @Insert("insert into position_info_table (lonlat) values (#{p.lonlat})" )
    int insertPositionInfo(@Param("p") PositionInfo positionInfo);

测试

--------

# 无效，解决：在GeoPointTypeHandler 使用 @Component
#配置point解析工具GeoPointTypeHandler，使其自动转换，不需要在sql层手动指定
#mybatis:
#    type-handlers-package:com.jeaglo.auxpolice.tools