Hi Andreas, Some late feedback on this issue (which I haven't forgotten). Thanks for your patience!
I've wrestled with the PostgreSQL JDBC driver's and PostGIS's various PG* data types. They're not really very user friendly, I'm afraid, but I guess that's as good as it gets when working with PostGIS. Here's an updated version of the Binding and the Converter implementations: package org.jooq.test.all.converters; import java.math.BigDecimal; import java.sql.SQLException; import org.jooq.Converter; import org.jooq.test.all.types.Position; import org.postgis.Geometry; import org.postgis.PGgeometry; import org.postgis.Point; /** * @author Lukas Eder * @autho Andreas Ahlenstorf * @see <a href= * "https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ "> * https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ </a> */ public class PositionConverter implements Converter<Object, Position> { @Override public Position from(Object object) { if (object == null) { return null; } Geometry geometry = null; try { geometry = PGgeometry.geomFromString(object.toString()); } catch (SQLException e) { throw new IllegalArgumentException(e); } if (!(geometry instanceof Point)) { throw new IllegalArgumentException("Geometry is not a org.postgis.Point."); } Point point = (Point) geometry; return new Position(BigDecimal.valueOf(point.getX()), BigDecimal.valueOf(point.getY())); } @Override public Object to(Position position) { if (position == null) { return null; } Point p = new Point(position.latitude.doubleValue(), position.longitude.doubleValue()); p.setSrid(Position.SPATIAL_REF_SYS); return new PGgeometry(p); } @Override public Class<Object> fromType() { return Object.class; } @Override public Class<Position> toType() { return Position.class; } } package org.jooq.test.all.bindings; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import org.jooq.Binding; import org.jooq.BindingGetResultSetContext; import org.jooq.BindingGetSQLInputContext; import org.jooq.BindingGetStatementContext; import org.jooq.BindingRegisterContext; import org.jooq.BindingSQLContext; import org.jooq.BindingSetSQLOutputContext; import org.jooq.BindingSetStatementContext; import org.jooq.Converter; import org.jooq.impl.DSL; import org.jooq.test.all.converters.PositionConverter; import org.jooq.test.all.types.Position; /** * @author Lukas Eder * @autho Andreas Ahlenstorf * @see <a href= * "https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ "> * https://groups.google.com/d/msg/jooq-user/TBQZCPTCvnk/HJYND9AQs6EJ </a> */ public class PostgresPositionBinding implements Binding<Object, Position> { private final Converter<Object, Position> converter = new PositionConverter(); @Override public Converter<Object, Position> converter() { return converter; } @Override public void sql(BindingSQLContext<Position> ctx) throws SQLException { // this change is key here. I suspect the manual was misleading... ctx.render().visit(DSL.sql("?::geography")); } @Override public void set(BindingSetStatementContext<Position> ctx) throws SQLException { ctx.statement().setObject(ctx.index(), ctx.convert(converter).value()); } @Override public void get(BindingGetResultSetContext<Position> ctx) throws SQLException { ctx.convert(converter).value(ctx.resultSet().getObject(ctx.index())); } @Override public void get(BindingGetStatementContext<Position> ctx) throws SQLException { ctx.convert(converter).value(ctx.statement().getObject(ctx.index())); } @Override public void get(BindingGetSQLInputContext<Position> bindingGetSQLInputContext) throws SQLException { throw new SQLFeatureNotSupportedException(); } @Override public void set(BindingSetSQLOutputContext<Position> bindingSetSQLOutputContext) throws SQLException { throw new SQLFeatureNotSupportedException(); } @Override public void register(BindingRegisterContext<Position> ctx) throws SQLException { throw new SQLFeatureNotSupportedException(); } } With the above, I could successfully run the following test: @Test public void testPostgreSQLGISGeographyTypeBinding() { clean(T_PG_EXTENSIONS); assertEquals(1, create().insertInto(T_PG_EXTENSIONS) .columns(T_PG_EXTENSIONS.ID, T_PG_EXTENSIONS.PG_POSITION) .values(1, new Position(BigDecimal.ZERO, BigDecimal.ONE)) .execute()); Position position = create().fetchOne(T_PG_EXTENSIONS).getPgPosition(); assertEquals(0, BigDecimal.ZERO.compareTo(position.latitude)); assertEquals(0, BigDecimal.ONE.compareTo(position.longitude)); } Hope this helps, Lukas 2015-06-19 17:58 GMT+02:00 Andreas Ahlenstorf <[email protected]> : > Hi Lukas, > > On 19 June 2015 at 17:40, Lukas Eder <[email protected]> wrote: > > > I'm sorry, I don't have any news on this issue yet - and I'll be away > from > > E-Mail for the next three weeks. Sorry that I can't be of any help with > this > > issue right now. > > No problem. I'm currently working around the issue by using a geometry > constructor (ST_GeomFromEWKT(?)) and returning an EWKT string from the > converter for all write operations. Not pretty, but it does its job so > far. > > Regards, > Andreas > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
