Canonical Fetching Approach

[robert....@cloudability.com]

With all the different ways to do the same thing in JOOQ it's often difficult to find the best approach. I'm trying to write up our basic jOOQ idioms. In doing so I was wondering if their is any difference between these two variants?

    List<MyObject> results = select.fetch(this::toMyObject);

And:

    List<MyObject> results = select.fetch().stream().map(this::toMyObject).collect(Collectors.toList());

The latter is more verbose but is a standard idiom that allows me to build my own collection types, Maps, or groupings. Which actually leads me to wonder why there is a bunch of fetchSet methods but no:

   Set<MyObject> results = select.fetchSet(this::toMyObject);

Why have so many fetchSet methods but none that takes a record mapper? From my perspective there are just too many options in jOOQ. Ever seen that movie The Wonder Boys? It's basically about a brilliant author who lost the ability to edit himself resulting in a novel that was too long for anyone to follow. :) Might it be time to start deprecating methods. For example, fetch().stream() can do almost everything. Or you could even make a more general purpose collecting fetch and get rid of the rest of all of them. 

   fetch(RecordMapper<...>, Collector<...>);

So two requests:

   1. What are the primary query patterns (canonical approaches)

   2. Please start deprecating methods. :)

[Lukas Eder]

Hi Robert,

Indeed, there's a delicate balance between providing convenience methods and basic functional building blocks, which can be composed to do equivalent things.

Pro convenience methods: They're more easy to discover. Yet, they lead to a combinatorial explosion of overloaded API.

Pro functional building blocks: They're more composable and complete. Yet, they're much harder to learn.

Take Java 8's Collectors, for instance. Sure, they're "standard idiom" but it certainly does take a while to understand what's available and what isn't. And thus must be built manually through Collector.of(). Have you built your own collectors? They're completely unreadable, even if the atomic operation is really straightforward (and in fact works the exact same way when implementing custom aggregate functions in most SQL databases).

Collectors does have quite a few basic convenience methods, including the Collectors.toList() method you've referenced. But what if I don't want a List? OK, I have Collectors.toCollection() but I may not even want a Collection. I'm on my own again.

I do think that the various convenience methods are more of a help than distraction, even if in your particular case, you're probably right. I'll further discuss inline.

2017-06-25 19:39 GMT+02:00 <robert....@cloudability.com>:

With all the different ways to do the same thing in JOOQ it's often difficult to find the best approach. I'm trying to write up our basic jOOQ idioms. In doing so I was wondering if their is any difference between these two variants?

    List<MyObject> results = select.fetch(this::toMyObject);

And:

    List<MyObject> results = select.fetch().stream().map(this::toMyObject).collect(Collectors.toList());

You could consider them roughly the same, although I'd still think that the former has high chances of being faster, in general.

The latter is more verbose but is a standard idiom that allows me to build my own collection types, Maps, or groupings.

Sure, that's great!

 

Which actually leads me to wonder why there is a bunch of fetchSet methods but no:

   Set<MyObject> results = select.fetchSet(this::toMyObject);

You're right, that's missing functionality. I've created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/6358

 

Why have so many fetchSet methods but none that takes a record mapper? From my perspective there are just too many options in jOOQ. Ever seen that movie The Wonder Boys? It's basically about a brilliant author who lost the ability to edit himself resulting in a novel that was too long for anyone to follow. :) Might it be time to start deprecating methods.

I think you're overestimating the problem here...

 

For example, fetch().stream() can do almost everything. Or you could even make a more general purpose collecting fetch and get rid of the rest of all of them. 

   fetch(RecordMapper<...>, Collector<...>);

That, on the other hand, is a really interesting idea. Then again, why even do it, if we can use a Stream, which has map(RecordMapper) and collect(Collector)?

 

So two requests:

   1. What are the primary query patterns (canonical approaches)

Maybe, now that you have my position available, you could tell me what you are really looking for? I mean, the primary query pattern is ResultQuery.fetchLazy(). To get a Cursor. With that Cursor, you can then do anything you want. Even the basic fetch() method, which returns a Result is convenience on top of that. Or fetchOne() which fetches only one Record: Convenience!

You could reduce it further and claim that the JDBC ResultSet type is really the primary query pattern, and the Cursor is just sugar on top of it.

But what are you really looking for? Or put differently: What is it that really bothers you?

 

   2. Please start deprecating methods. :)

We might, but again, these tools are often more useful than they are annoying, so I simply don't agree with this (yet) :)

[robert....@cloudability.com]

Well, it's hard to say anything bothers me, this is such a great API. I'm just not fond of having many ways to do the same thing. It particularly makes it hard for my new developers, and so I'm looking to give them direction. For example to collect identities from a joined query I can collect them like fetch("id"), fetch(0), fetch(DSL.name("id")), fetch(Record1::value1), etc. Alternatively, they could do fetch().map(...). It's overwhelming for them and makes it hard for them to make decisions about what to do. So I want to choose one or two idioms that they should stick to. Maybe it is always Cursor, but what I see instead is fetch in some places, fetchInto, in others, fetch.map others, and event fetch.stream and then just .stream (without a finally call to close). Its hard for example to visually understand the difference between stream and fetch.stream. I see fetchOptional in places where the query can return more than one record whereas fethLazy().fetchOptional does something different than fetchOptional alone. So it's a lot of choice which (just to me maybe) makes the API harder to understand an use. For some people fewer choices make things easier to understand. But of course fewer choices means some things are harder to do. So I understand it is a balance.

[Lukas Eder]

Hi Robert,

Thank you very much for elaborating. I think I see what you mean. Let me summarise this topic from a different angle, so we'll get closer to what makes it particularly hard for your peers to grasp the jOOQ API at first:

Like many APIs, jOOQ tries to establish a vocabulary, and a common set of types from your examples, we can see:

2017-06-26 16:48 GMT+02:00 <robert....@cloudability.com>:

For example to collect identities from a joined query I can collect them like fetch("id"), fetch(0), fetch(DSL.name("id")), fetch(Record1::value1), etc.

This belongs to the term "Record" of the vocabulary, which is a type that blends Map, List, and Tuple semantics:

- fetch("id") sees the Record as a Map<String, ?>

- fetch(DSL.name("id")) sees the Record as a Map<Name, ?>

- fetch(Field<?>) sees the Record as a Map<Field<?>, ?>

- fetch(0) sees the Record as a List<Object>

- fetch(Record1::value1) sees the Record as a tuple of degree 1 with attribute types <T1>

All of these methods make perfect sense, once the vocabulary is understood. Once it is understood, users will become much more productive, because the same ideas (String = name, Name = qualified name, Field = typed, qualified name, int = index) are repeated throughout the jOOQ API. In fact, users will expect them to be there and they're surprised when they're not - as you were because of the missing fetchSet(RecordMapper).

If this were Ceylon or TypeScript, or some other sophisticated language, then there would be a type alias "FieldAccess" for the union type "String | Name | Field<?> | int", which is commonly used everywhere.

This union type can be compared with JDK's own implicit union types, such as "String | File | InputStream | Reader | URI | URL | Source", which are all different types for the same thing, and they all need to be repeated by API, wherever one of them makes sense. Consider the JAXB.unmarshal() methods...

 

Alternatively, they could do fetch().map(...). It's overwhelming for them and makes it hard for them to make decisions about what to do.

Indeed, so here we move on from the Record vocabulary to the Result vocabulary, where the type of the individual field is less interesting compared to the type of the set of records returned from a query. In this case, we want a Map<K, V> in situations where Maps (Key => Value) are more interesting result formats than Result (Record arrays).

 

So I want to choose one or two idioms that they should stick to.

Or perhaps better: How can all the idioms be best explained? Because there aren't too many, really, even if it might look overwhelming at first.

 

Maybe it is always Cursor,

Probably not - that would be quite low level in most cases ;)

 

but what I see instead is fetch in some places, fetchInto, in others, fetch.map others, and event fetch.stream and then just .stream (without a finally call to close).

That's again a matter of the Result vocabulary:

- fetch(): Out of the box Result

- fetch().into(): Custom POJO types and RecordMappers

- fetch().map(): The aforementioned Map

- fetch().stream(): A Stream for custom processing

 

Its hard for example to visually understand the difference between stream and fetch.stream.

Yes indeed. The ResultQuery.stream() method might be something that users may struggle with. Perhaps the current implementation is a mistake and lazy streams should be made available only through fetchLazy().stream(). I've thought about this a couple of times... Will review again soon:

https://github.com/jOOQ/jOOQ/issues/6364

The aspect of the Result vocabulary stays the same, though. Result is a List, which can be streamed, which makes perfect sense. It can also be iterated with iterator() explicitly, or with a foreach loop implicitly, or you don't even have to call fetch():

https://blog.jooq.org/2016/09/27/a-hidden-jooq-gem-foreach-loop-over-resultquery

 

I see fetchOptional in places where the query can return more than one record

Well, to be fair, that's not a jOOQ problem :)

You can write a SELECT subquery returning more than one record, where this isn't allowed, e.g. this will fail in all databases:

SELECT (SELECT 1 UNION SELECT 2)

 

And hey, you could take this argument one step further. Obviously, developers should use SQL's LIMIT 1, when they want only one result from a query that might return more than one result, not do this in the client. How could the jOOQ API help developers learn this kind of basic SQL? I don't know - that's why I blog about these things so much and offer SQL trainings to customers.

Likewise, they could do something silly as:

hugecollection

    .stream()

    .map(e -> expensiveMapping(e))

    .collect(Collectors.toList())

    .subList(0, 2);

Instead of the "obviously" much better

hugecollection

    .stream()

    .limit(2)

    .map(e -> expensiveMapping(e))

    .collect(Collectors.toList());

Is it really obvious? Not so much to the untrained eye. The first solution maps all the collection elements before limiting its size (O(N)), whereas the second solution limits the size first and applies the expensive mapping only to two values (O(1)).

At some point, I think it's OK to expect a developer to learn their craft beyond entry level, and this won't be limited to the jOOQ API... There's a lot that can go "wrong" with any API, including JDK ones.

But again, I most certainly agree with helping those who are willing to learn find an easier way through the API jungle.

whereas fethLazy().fetchOptional does something different than fetchOptional alone.

Indeed, I think there is an existing issue that cleans up the Cursor vocabulary. I cannot seem to find it, so I've created a new one:

https://github.com/jOOQ/jOOQ/issues/6363

 

So it's a lot of choice which (just to me maybe) makes the API harder to understand an use. For some people fewer choices make things easier to understand. But of course fewer choices means some things are harder to do. So I understand it is a balance.

I think that the carefully chosen amount of choices we're currently offering, along with the mostly consistent vocabulary, is some of jOOQ's greatest assets. Yes, it does take some time to discover all of the vocabulary, and I'm working on a new "getting started" manual section that should explain this vocabulary to new users:

https://github.com/jOOQ/jOOQ/issues/5669

I definitely don't think we should remove or deprecate much API. See, no one enjoys doing XML work in Java, because you have to get a hold of a DocumentBuilderFactory and then of a DocumentBuilder to parse a Document and then you have to go through hoops to add stuff to the document, let alone to serialise it with a TransformerFactory -> Transformer and wrap the Document in a Source and unwrap the result from the Result.

All of these XML operations are low level, basic building blocks. They're extremely low level and verbose, lacking any sort of convenience for the most common use-cases. So what do people do? They write their own utilities. JDBC is the same. jOOQ doesn't want to be this way. It wants to offer all of this convenience through a well established and simple vocabulary, where it makes sense.

What I've taken from this discussion, however, are two things:

1) This vocabulary is not easy to discover for new users, and I don't think we can change that through the API / Javadoc. Which is why I'll definitely write this new manual section very soon.

2) You've discovered some inconsistencies, which are very regrettable. Luckily, we're not the JDK, so indeed, we can fix things where they violate the vocabulary.

I'm very happy to address both of these things, without the assumption that all has gone wrong or that there is a systematic problem in the jOOQ API ;) I hope this makes sense. If you find additional inconsistencies, please do let me know and we'll see what can be fixed.

Lukas

[Lukas Eder]

By the way, I've just re-discovered this issue, which is due for jOOQ 3.10:

https://github.com/jOOQ/jOOQ/issues/6326

Am Mittwoch, 28. Juni 2017 16:08:24 UTC+2 schrieb Lukas Eder:

Hi Robert,

Thank you very much for elaborating. I think I see what you mean. Let me summarise this topic from a different angle, so we'll get closer to what makes it particularly hard for your peers to grasp the jOOQ API at first:

Like many APIs, jOOQ tries to establish a vocabulary, and a common set of types from your examples, we can see: