要生成的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 id="selectDistance" resultType="java.lang.String" parameterType="java.util.Map">
SELECT ST_Distance(
<if test="lot_lat != null" >
ST_Transform(ST_GeomFromText(<![CDATA['POINT(${lot_lat})']]> ,4326),26986),
</if>
<if test="parkingPointStr != null">
ST_Transform(ST_GeomFromText(<![CDATA['${parkingPointStr}']]>, 4326),26986))
</if>
</select>
${lot_lat}是字符串:-87.734087560562 43.770129071141
${parkingPointStr}是字符串POINT(-87.747382933006 43.759234252055)
注意1:单引号的转义
方式a:如上使用<![CDATA[……]]>
方式b:使用xml的转义字符:
<select id="selectDistance" resultType="java.lang.String" parameterType="java.util.Map">
SELECT ST_Distance(
<if test="lot_lat != null" >
ST_Transform(ST_GeomFromText(' POINT(${lot_lat}) ',4326),26986),
</if>
<if test="parkingPointStr != null">
ST_Transform(ST_GeomFromText('${parkingPointStr} ', 4326),26986))
</if>
</select>
方式c: