postgresql Mybatis通过xml插入查询，以获取地理类型

我有一个postgres表Alpha，它有一个名为geom的列，类型为geography。我想通过mybatis xml查询将数据插入此列，但在运行插入查询时收到以下错误。

### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "'POINT(49.9469 41.7325)'"
  Position: 1077
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)

由于mybatis没有提供良好的错误日志，所以我不确定mybatis xml查询的错误是什么。
table-

CREATE TABLE ALPHA (
  geom georaphy(point, 4326)
);

我写的查询-

<insert id="save">
  INSERT INTO ALPHA (
    geom)
  VALUES (
    'POINT(${input.longitude} ${input.latitude})'
  );
</insert>

输入类型如下-

public class Input {
  private Double latitude;
  private Double longitude;
}

我也试过，但这次也失败了-

<insert id="save">
  INSERT INTO ALPHA (
    geom)
  VALUES (
    'POINT(#{input.longitude} #{input.latitude})'
  );
</insert>

如果您使用Postgis，这个TypeHandler可以帮助您。您pojo字段类型必须是JTS几何图形。

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.geotools.geometry.jts.WKBReader;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKBWriter;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Optional;
import java.util.concurrent.locks.ReentrantLock;

/**
 * @author Arjen10
 * @date 2022/8/30 11:30
 */
public class GeometryTypeHandler extends BaseTypeHandler<Geometry> {

    private static final WKBWriter WKB_WRITER = new WKBWriter();

    private static final WKBReader WKB_READER = new WKBReader();

    /**
     * 重入锁、锁写入
     */
    private static final ReentrantLock WRITE_LOCK = new ReentrantLock();

    /**
     * 重入锁、锁读取
     */
    private static final ReentrantLock READ_LOCK = new ReentrantLock();

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Geometry geometry, JdbcType jdbcType)
            throws SQLException {
        byte[] write;
        WRITE_LOCK.lock();
        try {
            //PostGIS 矢量数据存储方式为WKB
            write = WKB_WRITER.write(geometry);
        } catch (Exception e) {
            throw new SQLException(e);
        } finally {
            WRITE_LOCK.unlock();
        }
        try (InputStream is = new ByteArrayInputStream(Optional.of(write).get())) {
            ps.setBinaryStream(i, is);
        } catch (IOException | NullPointerException e) {
            throw new SQLException(e);
        }

    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String s) throws SQLException {
        return getGeometry(rs.getString(s));
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int i) throws SQLException {
        return getGeometry(rs.getString(i));
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int i) throws SQLException {
        return getGeometry(cs.getString(i));
    }

    private static Geometry getGeometry(String wkbString) throws SQLException {
        if (StringUtils.isBlank(wkbString)) {
            return null;
        }
        byte[] bytes = WKBReader.hexToBytes(wkbString);
        READ_LOCK.lock();
        try {
            return WKB_READER.read(bytes);
        } catch (ParseException e) {
            throw new SQLException(e);
        } finally {
            READ_LOCK.unlock();
        }
    }

}

postgresql Mybatis通过xml插入查询，以获取地理类型

1条答案

相关问题

热门标签

最新问答