如何使用 MyBatis 从 PostGIS 查询列的子集？
我正在尝试使用 MyBatis 从 PostGIS 数据库查询数据，忽略地理空间数据。我在数据库中有下表：
CREATE TABLE salesgeometry
(
id bigint NOT NULL,
label character varying(255),
type character varying(255),
geom geometry,
CONSTRAINT salesgeometry_pkey PRIMARY KEY (id ),
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
)
我正在尝试使用此注释将其与 MyBatis 进行映射：
@Select("SELECT id, type, label FROM salesgeometry WHERE ST_Within(" +
"ST_GeomFromText('POINT(#{longitude} #{latitude})', 4326), geom) " +
"AND type = #{type}")
Geometry getGeometryAtLocation(
@NotNull @Param("type") String geometryType,
@NotNull @Param("longitude") BigDecimal longitude,
@NotNull @Param("latitude") BigDecimal latitude
);
并且目标类具有如下字段：
public class Geometry {
private long id;
private String type;
private String label;
...
}
不幸的是，这不起作用，而是我得到了
org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.
How do I query just the subset of columns从数据库中？
I'm trying to query data from a PostGIS database using MyBatis, ignoring the geospatial data. I have the following table in the database:
CREATE TABLE salesgeometry
(
id bigint NOT NULL,
label character varying(255),
type character varying(255),
geom geometry,
CONSTRAINT salesgeometry_pkey PRIMARY KEY (id ),
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
)
I'm trying to map this with MyBatis using this annotation:
@Select("SELECT id, type, label FROM salesgeometry WHERE ST_Within(" +
"ST_GeomFromText('POINT(#{longitude} #{latitude})', 4326), geom) " +
"AND type = #{type}")
Geometry getGeometryAtLocation(
@NotNull @Param("type") String geometryType,
@NotNull @Param("longitude") BigDecimal longitude,
@NotNull @Param("latitude") BigDecimal latitude
);
And the target class has fields like this:
public class Geometry {
private long id;
private String type;
private String label;
...
}
Unfortunately this does not work, instead I get a
org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.
How do I query just the subset of columns from the database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题是
ST_GeomFromText('POINT(#{longitude} #{latitude})', 4326)被 MyBatis 映射到一个准备好的语句，如下所示：ST_GeomFromText('POINT(? ?)', 4326)，它实际上不包含预期的参数，因为问号在引号内。解决方案是使用字符串连接（如
ST_GeomFromText('POINT(' || #{longitude} || ' ' || #{latitude} || ')', 4326）或使用字符串替换：ST_GeomFromText('POINT(${longitude} ${latitude})', 4326)，将值直接放入 SQL 语句中使用准备好的语句的参数的以下映射有效（注意经度和纬度的两个美元符号）：
The problem is that
ST_GeomFromText('POINT(#{longitude} #{latitude})', 4326)is mapped by MyBatis to a prepared statement looking like this:ST_GeomFromText('POINT(? ?)', 4326), which doesn't actually contain the expected parameters since the question marks are within quotes.The solution is to either use string concatenation (as in
ST_GeomFromText('POINT(' || #{longitude} || ' ' || #{latitude} || ')', 4326)or use string substitution instead:ST_GeomFromText('POINT(${longitude} ${latitude})', 4326), which puts the values into the SQL statement directly instead of using parameters of prepared statements.The following mapping works (note the two dollar symbols for longitude and latitude):