java- mysql空间数据转为String 类型

在使用SQL语言查询空间数据时，如果不把存入mysql的空间数据转化为可读的字符串，会在返回时出现"乱码"现象，以点数据为例，具体现象如下：

所以如果要展现这些数据，就得把空间数据转为可读的String 类型。

使用mybatis-plus，引入vividsolutions依赖：

    <dependency>
      <groupId>com.vividsolutions</groupId>
      <artifactId>jts</artifactId>
      <version>1.13</version>
    </dependency>

具体方法类如下：

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.*;
import lombok.SneakyThrows;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;

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

/**
 * Auth:ZERO_LIGHT
 * GeometryHandler
 * 将字符串坐标转换为数据库中Geometry型的handler
 *
 * @author ZERO_LIGHT
 */
@MappedTypes({String.class})
public class GeometryHandler extends BaseTypeHandler<String> {

	@Override
	@SneakyThrows
	public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) {
		if ("".equals(s)) {
			preparedStatement.setBytes(i, null);
		} else {
			WKTReader wktReader = new WKTReader();

			Geometry pointGeo = wktReader.read(s);
			WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN);
			byte[] writer = wkbWriter.write(pointGeo);
			byte[] wkb = new byte[writer.length + 4];
			ByteOrderValues.putInt(0, wkb, ByteOrderValues.LITTLE_ENDIAN);//定义SRS,这里是0
			System.arraycopy(writer, 0, wkb, 4, writer.length);
			preparedStatement.setBytes(i, wkb);
		}
	}

	@Override
	@SneakyThrows
	public String getNullableResult(ResultSet resultSet, String s) {
		return getGeometryStr(resultSet.getBytes(s));
	}

	@Override
	@SneakyThrows
	public String getNullableResult(ResultSet resultSet, int i) {
		return getGeometryStr(resultSet.getBytes(i));
	}

	@Override
	@SneakyThrows
	public String getNullableResult(CallableStatement callableStatement, int i) {
		return getGeometryStr(callableStatement.getBytes(i));
	}

	/**
	 * byte数组转字符串
	 *
	 * @param bytes byte数组
	 * @return bute数组对应的字符串
	 * @throws ParseException 转换异常
	 */
	private String getGeometryStr(byte[] bytes) throws ParseException {
		if (bytes != null) {
			byte[] bytesN = new byte[bytes.length - 4];
			System.arraycopy(bytes, 4, bytesN, 0, bytes.length - 4);
			Geometry geometry = new WKBReader().read(bytesN);
			Coordinate[] coordinates = geometry.getCoordinates();
			StringBuilder sb = new StringBuilder();
			sb.append(geometry.getGeometryType()).append("(");
			for (Coordinate coordinate : coordinates) {
				sb.append(coordinate.y).append(" ").append(coordinate.x).append(",");//指定经度在前，纬度在后，防止出现倒置
			}
			sb.deleteCharAt(sb.length() - 1);  // 删除最后一个逗号
			sb.append(")");
			return sb.toString();
		}
		return null;
	}
}

用法--在实体类映射空间数据的字段中加上@TableField(typeHandler = GeometryHandler.class)

例：

以及sql的撰写：

@Mapper
public interface RenshouReduction3Repository extends BaseRepository<RenshouReduction3> {

	@Select("SELECT st_astext(the_geom) as the_geom FROM rsxshzxcjznlpgxzdw WHERE id = #{id}  AND del_flag = 0")//注意要在空间字段前加上st_astext 
	String getGeom(@Param("id") String id);//这里是的业务是根据id查询

}

效果：

点数据

面数据：