Jooq: Add support for H2, MySQL, Oracle, Postgres, and other GIS extensions

[lukaseder]

This was also requested in this thread:
https://groups.google.com/d/topic/jooq-user/sMYVnGEuLLI/discussion

Some more suggestions were given in this thread:
https://groups.google.com/d/topic/jooq-user/saaOD4_tz4c/discussion

[sdeleuze]

+1 for this feature request!

[lolobosse]

+1 for this feature request

[simonemurzilli]

+1 for this request

[maratynsky]

+1

[relgames]

Hi, I assume there are no plans to implement PostGIS support, as the issue remains open since 2012.
What is the current approach for GEOGRAPHY type? Custom converter?

[lukaseder]

@relgames Indeed, there's currently not a satisfactory solution to unify GIS support across major RDBMS. Unfortunately, this is all very vendor-specific, so it's quite hard for jOOQ to offer a solution.

In PostgreSQL, custom data type bindings are the way to go forward:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

[JonasTaulienSolutions]

Hi @lukaseder,
I almost finished implementing a Binder for a POINT in mysql.
My Binder can successfully insert POINTs into the table. The problem is, that I'm not able to get JOOQ to read out POINT columns in their WKT (Well Known Text) representations (currently the databaseObject is a byte array which is the 'internal representation' of mysql. I can not find any reference to parse that.).

I would like to modify JOOQ to read out columns of type POINT using ST_AsText(<column-name>) as <column-name>.

If that is currently not possible could you extend the Binder interface to allow customizing how the respective SelectFieldList entry gets created?

I already found your answer to this StackOverflow question. But I don't think

you might need to adapt your converter (the one inside the binding) to be able to read any type that comes back from the JDBC driver

is possible because I can not find any reference on how to parse the byte array which is the internal representation of the POINT in mysql.

My code so far:

public class MySqlPointBinding implements Binding<Object, MySqlPointBinding.MySqlPoint> {
    @Override
    public Converter<Object, MySqlPoint> converter() {
        return new Converter<>() {
            @Override
            public MySqlPoint from(Object databaseObject) {
                if (databaseObject == null) {  return null; }

                final var dbObjAsStr = databaseObject.toString();
                final var matcher = Pattern.compile("POINT\\((.*) (.*)\\)").matcher(dbObjAsStr);
                if (matcher.matches()) {
                    final var latStr = matcher.group(1);
                    final var longStr = matcher.group(2);

                    return new MySqlPoint(Double.parseDouble(latStr), Double.parseDouble(longStr));

                } else {
                    // My code always fails here because databaseObject is a byte array
                    throw new RuntimeException("Expected " + dbObjAsStr + " to match POINT(lat long)");
                }
            }

            @Override
            public Object to(MySqlPoint userObject) {
                return "POINT(" + userObject.latitude + " " + userObject.longitude + ")";
            }

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

            @Override
            public Class<MySqlPoint> toType() { return MySqlPoint.class;}
        };
    }

    @Override
    public void sql(BindingSQLContext<MySqlPoint> ctx) throws SQLException {
        if (ctx.render().paramType() == ParamType.INLINED) {
            ctx.render().visit(DSL.function(
                "ST_PointFromText",
                Object.class,
                DSL.inline(ctx.convert(this.converter()).value()),
                DSL.inline(4326),
                DSL.inline("axis-order=lat-long")
            ));

        } else {
            ctx.render().sql("ST_PointFromText(?, 4326, 'axis-order=lat-long')");
        }
    }

    @Override
    public void register(BindingRegisterContext<MySqlPoint> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }

    @Override
    public void set(BindingSetStatementContext<MySqlPoint> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }

    @Override
    public void get(BindingGetResultSetContext<MySqlPoint> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    @Override
    public void get(BindingGetStatementContext<MySqlPoint> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    @Override
    public void set(BindingSetSQLOutputContext<MySqlPoint> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(BindingGetSQLInputContext<MySqlPoint> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    public static class MySqlPoint {
        public final double latitude;
        public final double longitude;
        public MySqlPoint(double latitude, double longitude) {
            this.latitude = latitude;
            this.longitude = longitude;
        }
    }
}

Relevant section from my pom.xml:

<forcedType>
    <userType>com.example.MySqlPointBinding.MySqlPoint</userType>
    <binding>com.example.MySqlPointBinding</binding>
    <expression>.*.*</expression>
    <types>POINT</types>
</forcedType>

Kind regards

[lukaseder]

I'm probably way too late for your comment, @JonasTaulienSolutions. Sorry for the delay.

I would like to modify JOOQ to read out columns of type POINT using ST_AsText(<column-name>) as <column-name>.

You should use plain SQL templating for that, currently, and write an API for this functionality:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating

If that is currently not possible could you extend the Binder interface to allow customizing how the respective SelectFieldList entry gets created?

I don't think we should abuse the org.jooq.Binding SPI, which is strictly about bind variables. We have a feature request for rewriting entire column expressions (not just bindings) to produce new expressions: https://github.com/jOOQ/jOOQ/issues/8842, but there are still a lot of open questions about this feature.

[JonasTaulienSolutions]

Hey @lukaseder,
thanks for your reply!
You are not too late because until now, I never had to read out the POINT column directly.
And when I have to read it out I will use the DSL#function instead of plain sql templating:

dslContext.select(
    MY_TABLE.X,
    DSL
        .function(
            "ST_AsText",
            String.class,
            MY_TABLE.COORDINATES
        )
        .as("coordinates")
    MY_TABLE.Y
)

And then I will parse the POINT using the regex from my MySqlPointBinding-class.
But if I had multiple queries that read out the MY_TABLE.COORDINATES-column (or other columns of a POINT-type) I would have to write much boiler plate code.

I would like to configure JOOQ _once_ to use ST_AsText(<column-name>), so in my whole Application I could write:

dslContext.select(MY_TABLE.COORDINATES)

and get a MySqlPoint back as a result.

[lukaseder]

I've postponed this for a long time now. The 3.15 ship has sailed again, as we're going to support at least 4 new SQL dialects, some of which as sponsored projects.

But I'll elevate GIS support as a high priority for 3.16, recognising that this would add a lot of value to a lot of users.