要生成的sql: 作用是利用postgreGIS数据库函数计算两点(经纬度已知)的距离。
SELECT ST_Distance(
ST_Transform(ST_GeomFromText('POINT(-87.734087560562 43.770129071141)',4326),26986),
ST_Transform(ST_GeomFromText('POINT(-87.747382933006 43.759234252055)', 4326),26986)
);
最后成功的写法:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText( ,4326),26986),
ST_Transform(ST_GeomFromText(, 4326),26986))
${lot_lat}是字符串:-87.734087560562 43.770129071141 ${parkingPointStr}是字符串POINT(-87.747382933006 43.759234252055)
注意1:单引号的转义
方式a:如上使用
方式b:使用xml的转义字符:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(' POINT(${lot_lat}) ',4326),26986),
ST_Transform(ST_GeomFromText('${parkingPointStr} ', 4326),26986))
方式c:concat拼接的方式。 刚开始想拼接一对单引号出来,不行:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(concat(E'\'','POINT(${lot_lat})',E'\'','),4326),26986),
ST_Transform(ST_GeomFromText(concat(E'\'','${parkingPointStr}',E'\'','), 4326),26986))
后来坚持不懈,发现是这么写:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(concat('POINT(${lot_lat})'),4326),26986),
ST_Transform(ST_GeomFromText(concat('${parkingPointStr}'), 4326),26986))
这样也行:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(concat('POINT(',#{lot_lat},')'),4326),26986),
ST_Transform(ST_GeomFromText(concat('${parkingPointStr}'), 4326),26986))
这样也行:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText(concat('POINT(',#{lot_lat},')'),4326),26986),
ST_Transform(ST_GeomFromText(concat(#{parkingPointStr}), 4326),26986))
concat函数解析后会自带单引号?我之前为了加上单引号还这么写呢:SELECT concat('''','POINT(116.289573 39.892352)','''') SELECT concat('''','POINT(116.289573 39.892352)','''') SELECT concat('POINT(-87.747382933006 43.759234252055)') SELECT '''' || 'POINT(-87.747382933006 43.759234252055)' || ''''
这样不行:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText('\''POINT(${lot_lat}) '\'',4326),26986),
ST_Transform(ST_GeomFromText('\''${parkingPointStr} '\'', 4326),26986))
sqlSELECT concat('\'','ddd','\'') from t_parking也有语法错误,应该写为SELECT concat(E'\'','ddd',E'\'') from t_parking,postgresql转义放在E'\要转义的内容'里。详见
注意2:单引号为什么不能用双引号取代
sql里单引号不能被双引号取代着用。
注意3:${} 与 #{}的区别
前者是直接把字面值加在sql里,完全是字符串替换,当作占位符。常常用来传递表名,字段名,如order by ${param}
mybatis在处理#{}时,会将sql中的#{}替换为?号,调用PreparedStatement的set方法来赋值,有预编译的机制。另外,传入的数据都当成一个字符串,会对自动传入的数据加一个双引号。
后注入的参数将不会再进行SQL编译。我们知道,SQL注入是发生在编译的过程中,因为恶意注入了某些特殊字符,最后被编译成了恶意的执行操作。而预编译机制则可以很好的防止SQL注入。
注意4:mybatis里写的一条sql里可以${} 与 #{}混着用吗?
比如这条语句:
SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(117.343454 40.8978999)',4326),26986),ST_Transform(ST_GeomFromText('POINT(116.289573 39.892352)', 4326),26986))
FROM t_parking
WHERE id='0652b36f115c42578d13ffa45c4589c8'
写成:
SELECT ST_Distance(
ST_Transform(ST_GeomFromText( ,4326),26986),
ST_Transform(ST_GeomFromText(, 4326),26986))
FROM t_parking
and id = #{id,jdbcType=VARCHAR}
可以吗? 当然是可以的,但${}里不能带,jdbcType=VARCHAR 写这样and id = ${id,jdbcType=VARCHAR}报错:org.postgresql.util.PSQLException: 错误: 语法错误 在输入的末尾\n 位置。
写这样and id = '${id,jdbcType=VARCHAR}'报错:内部错误。
写这样and id = '${id}'当然是正确的。 写这样and id = ${id}当然是有语法错误的。
CREATE database testgis;--新建一个库 CREATE EXTENSION postgis;--给该库启用PostGIS(包括栅格),出现表spatial_ref_sys CREATE EXTENSION postgis_topology;--启用拓扑topology库 select ST_GeomFromText('POINT(116.289573 39.892352)', 4326); --试用
SELECT AddGeometryColumn ('t_parking', 'geom', 4326, 'POINT', 2)
update t_parking geom set geom = ST_GeomFromText('POINT(116.289573 39.892352)', 4326) where id='0652b36f115c42578d13ffa45c4589c8' SELECT ST_ASTEXT(geom) geom FROM t_parking where id='0652b36f115c42578d13ffa45c4589c8'
select a1.attname as column_name,t.typname as data_type,d.description as column_comment from (select a.attname,a.attrelid,a.atttypid,a.attnum from pg_attribute a,pg_class c where c.relname = 't_parking' and a.attnum>0 and a.attrelid=c.oid)a1 left join pg_type t on a1.atttypid=t.oid left join pg_description d on d.objoid=a1.attrelid and d.objsubid=a1.attnum;