Access on the fly to resulset values

[Erwan Bocher]

Hi everyone,

I'm testing JDBI 3 with H2GIS spatial extension (http://www.h2gis.org/). It works well !

I'm looking for a way to create on the fly a Feature object  as defined by the Open Geospatial Standard Feature API (https://docs.opengeospatial.org/is/17-069r3/17-069r3.html).

A feature object contains a set of proprieties (any columns) and a set of geometries (geometry column datatype).  I have successfully linked the H2GIS geometry datatype with the JTS library, thanks to Baremaps example

https://github.com/baremaps/baremaps/blob/main/baremaps-postgres/src/test/java/com/baremaps/postgres/PostgisPluginTest.java

I'd like  something like that

Jdbi jdbi = Jdbi.create(...) .installPlugin(new H2GISPlugin());

List<Feature> features = jdbi.withHandle(handle ->

handle.createQuery("SELECT * FROM mygeodata limit 2") .map(new FeatureMapper()).list());

Because the sql query can change every time, I would like to be able to feed the Feature on the fly  and access the resulset values like this

feature.getString("name") // where name is the name of the column

feature.getGeometry("the_geom") // where the_geom is a column geometry mapped to JTS geometry type

I have looked in RowMapper and Reflection Mappers but I don't see a way to do it.

Note :  I'd like also to stream and iterate the features to manage large geometry table. So the method getString, getGeometry on the feature object must interact with the resultset.

Thanks a lot in advance for your help!

Erwan

[Steven Schlansker]

Hi Erwan, that's exciting to hear that H2GIS is working well for you!

Generally, the mapper is expected to extract the relevant data and store it locally, in the format you like.

Since the ResultSet is stateful (it remembers the current row), as the Features are collected, your reference to the ResultSet becomes outdated.

So you would write a FeatureMapper that looks at the current row's geometry, and decides on the appropriate return object for the row with a full copy of the geometry.

For streaming, once you `.map(new FeatureMapper())`, you get back a `ResultIterable` type. You can advance this manually to stream data, or for your convenience there are `withStream`, `useStream`, and `stream` methods which let you integrate your database streaming records into a Java Streams pipeline.

If you want lower level control, take a look at `reduceRows` or `collectRows` (or the lower level `reduceResultSet`) methods on Query which lets you apply your logic in a streaming manner without forcing you to look at only the current row.

Hope that helps

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/5a12de90-fe9c-4e30-a5b6-a7afcf1e7dd8n%40googlegroups.com.

[Erwan Bocher]

Dear Steven,

Thanks for the explanations.

I will do some tests

Erwan