Using custom data types in jooq's condition expression

user_6380415 0
Viewed 186

I am using jooq with a custom binding that converts all JTS geometry types to appropriate Postgis data types. This allows me to write and read JTS geometry types seamlessly, yet I fail to execute queries using those same custom types.

For example when I am trying to add this condition to a query:

Polygon polygon = geometryFactory.createPolygon(
  new Coordinate[]{topLeftCorner, bottomLeftCorner, bottomRightCorner, topRightCorner, topLeftCorner}
);
polygon.setSRID(4326);
DSL.condition("ST_WITHIN({0}, {1})", USER.COORDINATES, polygon)

it fails with the following message

org.jooq.exception.SQLDialectNotSupportedException: Type class org.locationtech.jts.geom.Polygon is not supported in dialect DEFAULT
jooq postgis java
1 Answers

Using jOOQ 3.16

Starting with jOOQ 3.16, #982 has been implemented and jOOQ supports ISO/IEC 13249-3:2016 spatial extensions out of the box. Your generated code will reference the SQLDataType.GEOMETRY type, for example, and you can use WKT or WKB encodeded spatial data in org.jooq.Geometry, the wrapper type, just like any other data type in jOOQ.

A lot of spatial functions are already supported out of the box. If something is still missing, you can always resort to using plain SQL templating.

Using older jOOQ versions

You need to create a custom data type binding for your various GIS types, and then either attach that to your generated code (e.g. to the ST_WITHIN stored function), or create auxiliary library methods that use the binding as follows:

DataType<Polygon> polygonType = SQLDataType.OTHER.asConvertedDataType(binding);
Field<Polygon> bindValue = DSL.val(polygon, polygonType);
DSL.condition("ST_WITHIN({0}, {1})", USER.COORDINATES, bindValue);
user_5217990
Related