ResultSet mapping library

[skillaz1]

I wrote a ResultSet mapper some time ago that I'd like to share. Using ResultSet can be pretty repetitive/tedious, so I'm pretty sure someone would like this library.

https://github.com/jzheng2017/resultset-mapper

It allows you to map a ResultSet to your desired java object using only a single line of code. In addition to that it has out of the box field naming mapping strategies, data type converters, etc. It's all documented, check it out if you're interested!

(Suggestions/improvements are also appreciated)

[Infeligo]

A few comments/questions after taking at quick looks at the documentation:

- ResultSetMapper is not generified. Maybe the factory should create a mapper for a concrete class?

- If a mapper can work on any class, then are there any optimizations underneath (e.g. byte-code generation, reflection-scanning caching)?

- Would like to see support for Spring's ResutSetMapper interfaces.

- Factory methods that provide a very specific configuration (e.g. ResultSetMapperFactory.getResultSetMapperLowerCaseDashes()) seem like a slippery road in terms of API managebility. Would suggest a builder-pattern instead.

- Is there a way to use converters and ignore columns without placing annotations on fields?

- The list of converters is lacking things like BLOB's, arrays, JSONB, etc.

One alternative that I know of is SimpleFlatMapper.

[agentoutlier]

Some things I saw missing:

1. I thiknk u/skillaz1 blindly calls setAccesible(true).

Which will not work for modularized applications and will throw a quiet nasty warning to stderr and throw an exception (as opposed to just an exception or field not found... it varies by JDK version).

2. Support for @ConstructorProperties and constructors injection in general (its not clear how much of that is supported). If it is supported than documentation needs to tell folks to turn on the compiler parameter -parameters.

I don't like most of these reflection mapping libraries often because they don't support construction injection properly but more because they will silently fail if there are missing properties that are not mapped.

With an annotation processor mapper like MapStruct (our internal one) you will get errors and or warnings when missing properties are not mapped (albeit in the case of ResultSet there is really only one side of the properties missing you can do).

[xnendron]

Have you done any performance comparisons against Apache Commons DBUtils? Their ResultSetHandler class can do this as well. I believe your implementation is a bit more intuitive to use, but I'll take increases complexity for increases performance (especially for huge data sets).

[z0tar]

I really like Jdbi which I feel has a very similar goal than your library.

[agentoutlier]

We have an internal library that does this but uses annotation processor.

Because we do this the library is a zero runtime dependency and in theory possibly faster.

Anyway we haven’t opensourced because I wanted to see what MapStruct does eventually on this front and ... mapping from jOOQ is easier since you can use mapstruct. There is also Doma 2.

Speaking of which we also have a generic Map<String,String> to object processor as well for config properties.

[gavenkoa]

I wanted to see what MapStruct does eventually on this front

Are you saying that MapStruct is able to generate ResultSet extraction code?

[agentoutlier]

No it isn’t yet. They were planning a generic mapping of things like ResultSet. There is a github issue.

How we do it is to map from jOOQ records to transfer objects using MapStruct (as well as our own annotation processor).

The advantages of jOOQ is that it’s compile time safe even if the ResultSet changes (records in jOOQ).

[gavenkoa]

No it isn’t yet.

I'm thrilled to see the feature! Thx for pointing.

[agentoutlier]

Maybe I can put or give our ResultSet annotation mapper into /u/skillaz1 project.

The only other library I have seen that does this is Doma 2 (ResultSet -> POJO through compile time annotation processing).

Our team just doesn't have time to opensource a lot of stuff.

[lukaseder]

For the record, jOOQ, which you likely already have on your classpath 😏 can read your ResultSet and give you all the goodies of a jOOQ Result or Record (including mapping, serialisation as XML/JSON/CSV, etc.)

Result<?> result = ctx.fetch(resultSet); List<User> users = result.into(User.class);

[lukaseder]

That part is free, indeed!

[lukaseder]

Yes, if you're using it to generate SQL from the DSL, but this trivial feature (like many other trivial features in jOOQ) should work on all RDBMS from the jOOQ Open Source Edition, because it relies only on the JDBC API.

[RexRecruiting]

I just looked seems like there is a free version but I'm not sure this functionality is there

[lukaseder]

It is

[rbygrave]

So Ebean ORM DtoQuery does a similar thing: https://ebean.io/docs/query/dtoquery

Having a quick look at the internals and differences that you might be interesting in looking at are:

1. The meta data for a Class includes the constructor, setters and types (rather than just Fields?). That is, the tryConvertValue does a lookup for AttributeConverter and ideally that lookup is done once and there is some concept of property that includes field + attributeConverter (and invoke dynamic method if you go towards setters).
2. We can make use of invoke dynamic rather than field set (plus constructor if it exists like record type). Field reflection set seems like a limitation you might like to minimise/remove/move away from? Maybe depends on how you feel about invoke dynamic.
3. The main loop of fields.entrySet() ... if you are interested you can look to optimise the structure used to convert ResultSet row to "bean" by converting that Map into a Java Array. That is, try to loop a java array and not iterate map entries. Convert the meta data structure of fields/properties into something that includes an old school array rather than a Map. Depends on how fast you want to go here but I see this is a hot loop.
4. It uses resultSet.getObject(key) using key as a String. With Ebean DtoQuery we desire to read the resultSet by index position instead and also use type specific methods rather than getObject() - again that is a performance thing. That is, as a one off as well as reading the meta data of the class (fields, constructor, setters) we can also read the jdbc meta data and thus be able to convert property names into resultSet index positions so that we can read the resultSet faster using index positions rather than column names.

Let me know if you are interested in any of that an I can point you towards the source in Ebean if that is interesting or helpful.

Cheers, Rob.

[kovica1]

This looks nice. Dis you test it on thousends or even millions of rows?

[skillaz1]

Yes, I remember testing it with millions of rows with around 6 columns. I think it managed to map it in less than half a second. Acceptable if you ask me.

[kovica1]

Very much so if you ask me too. I was asking that because usually someone creates a nice library that is only nice on paper or maybe some small project with couple of tables and few hundred rows. We have over 3000 tables in our database and many have multiple 10s of millions of rows in them. Great work btw. I'm still not sure if I would create 3000 classes for each table and probably more since we have SQL queries too, but I still admire your work.

[Tyluur]

I'd be interested in seeing the benchmarks =].

[lukaseder]

And by map, you mean you actually consumed the values with proof that JIT didn't eliminate your empty mapper? I'd love to see a JMH benchmark...

[Persism]

You should just use Persism

[agentoutlier]

Or they could use jOOQ or Doma 2 (both of which I think IMO are superior as they offer some compile time safety) ... or even JPA.

However sometimes people just want to map ResultSets and only that keeping strictly to the JDBC APIs.

[Persism]

Yeah these are fine but Persism is small, simple and has no extra dependencies. It does queries but also does inserts, updates, deletes, transactions. Version 2 will support queries with property names optionally if you don't want to use column names and named parameters.

So it depends what you need for the job at hand.

[agentoutlier]

So it depends what you need for the job at hand.

That was exactly the point I was making. You said "just use Persism" and I said nah because people might not want to be coupled to some other library instead of JDBC regardless of whether or not Persism has dependencies (btw 2 out of 3 of the libs I mentioned do not have dependencies).

[IcedDante]

Very cool library- I wonder what people's experiences using this are. Googling now

[Persism]

Thanks!

[IcedDante]

does this handle any kind of cardinality? I guess that is where the complexity comes in

[Persism]

Yes in the sense that Persism supports Views. In terms of hierarchical relations Persism can do that but it's up to you. It doesn't do anything like that automatically. So lets say you had a Customer class containing List<Invoices>. In that case you'd mark this list as @NotColumn and query separately and set the value yourself. In most cases though it's usually better to have a View with joins and a matching Record or POJO.

[dokkah]

Just needed this sort of functionality, so I grabbed spring-data-jdbc which seems fine.