I've been trying to use postgis with jooq and have run into a problem when
attempting to use postgis functions. I've defined a converter and binder
for the "geometry" data type and it appears to work fine in queries that
don't use postgis functions.
For example, for the following table:
CREATE TABLE example (
id BIGSERIAL PRIMARY KEY,
location GEOGRAPHY(POINT, 4326) NOT NULL
);
queries like these work fine: (dbSessionManager is just a thin wrapper
around DSLContext)
fun createExample(location: Location): ExampleRecord {
return dbSessionManager.session()
.insertInto(EXAMPLE)
.set(EXAMPLE.LOCATION, location.convertToPoint())
.returning()
.fetchOne()
}
or
fun getExampleByLocation(location: Location): ExampleRecord? {
return dbSessionManager.session()
.selectFrom(EXAMPLE)
.where(EXAMPLE.LOCATION.eq(location.convertToPoint()))
.limit(1)
.fetchOne()
}
But this query
fun getExampleInRadius(location: Location, radius: Double):ExampleRecord? {
val routine = StDwithin3()
routine.set__1(EXAMPLE.LOCATION)
routine.set__2(location.convertToPoint())
routine.set__3(radius)
return dbSessionManager.session()
.selectFrom(EXAMPLE)
.where(routine.asField())
.limit(1)
.fetchOne()
}
fails with the following error:
org.jooq.exception.DataAccessException: SQL [select "public"."example"."id",
"public"."example"."location" from "public"."example" where "postgis".
"st_dwithin"(cast("public"."example"."location" as USER-DEFINED),
cast(?::geography
as USER-DEFINED), cast(? as double precision)) limit ?]; ERROR: syntax
error at or near "USER"
because for some reason jooq is trying to cast the location to
"USER-DEFINED" instead of geography. It appears that
`AbstractRoutine.pgArgNeedsCasting` forces all parameters of overloaded
functions to be cast, but I'm not sure why its being cast to "USER-DEFINED"
instead of "geography". Maybe I've set up the converter/binder wrong, but
its very close to what was recommended here:
https://groups.google.com/forum/#!topic/jooq-user/TBQZCPTCvnk
In the interest of making this easier to debug, I've put together a very
stripped down version of my codebase that still exhibits this behavior. If
you feel like taking a look, it is here:
https://github.com/amc6/jooq_postgis_bug_example
The key files are:
- db/migration/V2__add_postgis.sql
<https://github.com/amc6/jooq_postgis_bug_example/blob/master/database/migrations/src/main/resources/db/migration/V2__add_postgis.sql>
- Contains the example table definitions and postgis extension
installation.
- The one non-standard thing I am doing here is installing postgis
into a separate schema, but that does not appear to affect the presence
of
this bug.
- com/sharespace/database/PostGisGeographyBinding.java
<https://github.com/amc6/jooq_postgis_bug_example/blob/master/database/jooq/src/main/java/com/sharespace/database/PostGisGeographyBinding.java>
- Contains the Binding and Converter. Very similar to what was suggested
in https://groups.google.com/forum/#!topic/jooq-user/TBQZCPTCvnk.
- com/sharespace/persistence/Persister.kt
<https://github.com/amc6/jooq_postgis_bug_example/blob/master/server/src/main/kotlin/com/sharespace/persistence/Persister.kt>
- com/sharespace/persistence/PersisterTest.kt
<https://github.com/amc6/jooq_postgis_bug_example/blob/master/server/src/test/kotlin/com/sharespace/persistence/PersisterTest.kt>
- Contains a few tests to show exactly what works and what does not. In
particular, use the function ST_DWithin using jooq does not work, but
using
ST_DWithin as raw sql does work.
- There are a few other miscellaneous files that are probably
unnecessary for such a small project, but are leftover from me paring down
a full project/repo. I'm fairly confident that this bug isn't a result of
them.
Any insight would be greatly appreciated!
Best,
Adam
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
