How to create custom binding from jOOQ Geometry to JTS Geometry

[Tomasz Burdka]

Hi, 

I'm using jOOQ to create new database based on existing one. Existing Postgis DB has a table with Geometry (Point,4326) type. When I'm using jOOQ to generate code, it switch this geometry type to SQLDataType.GEOMETRY. I am able to create new DB but dataType is incorrect, when I'm trying to insert anything I've receiving message:

The out of the box binding for geometry is available in the commercial jOOQ distribution only. Alternatively, you can implement your own custom binding.

How can I create custom binding to solve this issue? I've tried something like that:

import org.jooq.*;
import org.jooq.impl.DSL;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;

import javax.validation.constraints.NotNull;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;

public class JooqGeometryToJtsGeometryBinding implements Binding<Geometry, org.locationtech.jts.geom.Geometry> {
private final WKTReader reader = new WKTReader();

@Override
public @NotNull Converter<Geometry, org.locationtech.jts.geom.Geometry> converter() {
return new Converter<>() {
@Override
public org.locationtech.jts.geom.Geometry from(Geometry geometry) {
try {
return geometry == null ? null : reader.read(geometry.data());
} catch (ParseException e) {
throw new RuntimeException(e);
}
}

@Override
public Geometry to(org.locationtech.jts.geom.Geometry geometry) {
return Geometry.geometry(geometry.toString());
}

@Override
public @NotNull Class<Geometry> fromType() {
return Geometry.class;
}

@Override
public @NotNull Class<org.locationtech.jts.geom.Geometry> toType() {
return org.locationtech.jts.geom.Geometry.class;
}
};
}

@Override
public void sql(BindingSQLContext<org.locationtech.jts.geom.Geometry> bindingSQLContext) throws SQLException {
bindingSQLContext.render().visit(DSL.sql("?::geometry"));
}

@Override
public void register(BindingRegisterContext<org.locationtech.jts.geom.Geometry> bindingRegisterContext) throws SQLException {
throw new SQLFeatureNotSupportedException();
}

@Override
public void set(BindingSetStatementContext<org.locationtech.jts.geom.Geometry> bindingSetStatementContext) throws SQLException {
bindingSetStatementContext.statement()
.setObject(bindingSetStatementContext.index(), bindingSetStatementContext.convert(converter())
.value());
}

@Override
public void set(BindingSetSQLOutputContext<org.locationtech.jts.geom.Geometry> bindingSetSQLOutputContext) throws SQLException {
throw new SQLFeatureNotSupportedException();
}

@Override
public void get(BindingGetResultSetContext<org.locationtech.jts.geom.Geometry> bindingGetResultSetContext) throws SQLException {
bindingGetResultSetContext.convert(converter()).value((Geometry) bindingGetResultSetContext.resultSet()
.getObject(bindingGetResultSetContext.index()));
}

@Override
public void get(BindingGetStatementContext<org.locationtech.jts.geom.Geometry> bindingGetStatementContext) throws SQLException {
bindingGetStatementContext.convert(converter())
.value((Geometry) bindingGetStatementContext.statement()
.getObject(bindingGetStatementContext.index()));
}

@Override
public void get(BindingGetSQLInputContext<org.locationtech.jts.geom.Geometry> bindingGetSQLInputContext) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}

with this xml changes in pom.xml :

<forcedTypes>
<forcedType>
<!-- Specify the Java type of your custom type. This corresponds to the Binding's <U> type. -->
<userType>org.jooq.impl.Geometry</userType>

<!-- Associate that custom type with your binding. -->
<binding>my.custom.package.JooqGeometryToJtsGeometryBinding</binding>
</forcedType>
</forcedTypes>

But compiling doesn't work due to issues with:

import org.jooq.impl.Geometry; cannot resolve symbol 'Geometry' 

in generated class≥

[Lukas Eder]

The jOOQ type is org.jooq.Geometry, not org.jooq.impl.Geometry.

I hope this helps,
Lukas

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c14c5f93-a7e8-4dec-b8a0-e852dd742758n%40googlegroups.com.

[Tomasz Burdka]

it helps partially, I mean I have different errors right now:

• no suitable method found for createParameter(java.lang.String,org.jooq.DataType<java.lang.Boolean>,boolean,boolean,com.tomtom.orbis.orbisctsnapshotslingshot.slingshot.JooqGeometryToJtsGeometryBinding)
• no suitable method found for createParameter(java.lang.String,org.jooq.DataType<java.lang.Object>,boolean,boolean,com.tomtom.orbis.orbisctsnapshotslingshot.slingshot.JooqGeometryToJtsGeometryBinding)
  
• no suitable method found for createParameter(java.lang.String,org.jooq.DataType<java.lang.Object>,boolean,boolean,com.tomtom.orbis.orbisctsnapshotslingshot.slingshot.JooqGeometryToJtsGeometryBinding)
  
• no suitable method found for createParameter(java.lang.String,org.jooq.DataType<java.lang.Double>,boolean,boolean,com.tomtom.orbis.orbisctsnapshotslingshot.slingshot.JooqGeometryToJtsGeometryBinding)
  
• no suitable constructor found for AbstractRoutine(java.lang.String,org.jooq.codegen.maven.gss.Public,org.jooq.DataType<java.lang.Boolean>,com.tomtom.orbis.orbisctsnapshotslingshot.slingshot.JooqGeometryToJtsGeometryBinding)
  

[Lukas Eder]

Hi Tomasz,

Thanks for your message. Great! :)

Now. What's your hypothesis why this happens? What have you tried so far? What information can you share to make sure your particular errors can be reproduced? In our issue tracker, we usually ask people to provide an MCVE (Minimal, Complete, Verifiable Example), e.g. based on this template here. https://github.com/jOOQ/jOOQ-mcve. With an MCVE, it will be much simpler to help you, because it's immediately obvious what went wrong, or even, if you ran into a bug.

With a simple compilation error, it's quite harder. For example, how did you configure your code generator *exactly*. What does JooqGeometryToJtsGeometryBinding look like. What other information is needed to reproduce this exact case.

Looking forward to hearing from you again,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/47159046-2633-400b-a5a7-a5ff13af351en%40googlegroups.com.

[Sam S]

Hi Thomas, did you manage to get past this problem? I'm having similar issues.

Best,
S

Op maandag 5 september 2022 om 11:38:09 UTC+2 schreef Tomasz Burdka: