Jooq: Add support for ResultQuery.fetchSingle(), which returns exactly one record

[lukaseder]

A new method should be added to throw an exception both when there are 0 or > 1 resulting records (as opposed to fetchOne(), which throws only on > 1 records)

The exception that would be thrown on error is a NoDataFoundException

---

As suggested by Mark Derricutt in this discussion here:
https://groups.google.com/d/msg/jooq-user/7hfDlmEwfQI/TXhyJDHpDAAJ

Or on Stack Overflow here:
https://stackoverflow.com/q/45342644/521799

[nipafx]

I often use _single_ for that, so fetchSingle() maybe?

[lukaseder]

Yes, it has been decided. It'll be fetchSingle(). The most convincing argument was made on Twitter, as maths calls sets of degree 1 Singleton. Sets resonate well with SQL, so the name single will not be surprising.

[hilton]

For what it’s worth (since I’m late to the party), fetchSingle is a terrible name, because there’s nothing in the usual (dictionary) meanings of ‘one’ and ‘single’ that tells you which is which. That’s just as unfriendly as a command line tool with commands called fetch and pull: you might as well call your methods fetchOne_1 and fetchOne_2.

If it’s reasonable in your API that fetchOne might also return zero results, then the answer to this question is in the question:

Add support for ResultQuery.fetchSingle(), which returns exactly one record

The name you’re looking for is fetchExactlyOne.

[lukaseder]

Thanks for joining, @hilton.

I don't agree that fetchSingle is a terrible name. It is, in fact, a very precise name. I prefer fetchSingle over fetchExactlyOne because the term single is more concise and it doesn't resort to using an adverb to render another concept more precise, adverbs/adjectives used in APIs being a smell on their own in my opinion. Again, I like how it can be derived from the mathematical concept of a Singleton, which is exactly what we're talking about here.

To be fair, in the presence of a fetchExactlyOne method, we would have to rename fetchOne to fetchAtMostOne in terms of consistency (and also put some cardinality precision in fetch, which fetches 0..N rows)

So, perhaps, we might discuss in favour of what other name we could deprecate fetchOne() for (in the long run). But I'm not convinced yet, because the term "one" as opposed to "many" has a strong establishment in relational ecosystems, given the fact that "one" in "many-to-one", "one-to-many", and "one-to-one" really means a cardinality of 0..1. That in its historic defense.

So, I think your conclusion of the name being "terrible" might be a bit exaggerated, unless you meant to hint at a quote of Winston Churchill:

single is the worst name for a 1:1 cardinality, except for all the other names.

[hilton]

Yes, I was exaggerating, which was uncalled for. Sorry about that.

Essentially, the trade-off is between two choices:

• fetchAtMostOne and fetchExactlyOne - ugly, but the names tell you the difference between the two
• fetchOne and fetchSingle (or fetch with some other synonym of ‘one’) - the names don’t tell you which is which but at least suggest that they’re similar with some difference between them that will presumably be explained in the documentation.

I guess my point is that although it’s easy to rationalise meanings of ‘single’ and ‘one’ that are the precise meanings you want in this case, you shouldn’t expect anyone else to arrive with the same interpretation. Instead, this particular difference between ‘one’ and ‘single’ would be jOOQ-specific jargon.

[hilton]

Alternative approach: name them fetchOne and fetchOneOrThrow, for which there’s some precedent in the JDK these days.

[lukaseder]

Yes, I was exaggerating, which was uncalled for. Sorry about that.

No worries at all, and no offense taken! :)

Instead, this particular difference between ‘one’ and ‘single’ would be jOOQ-specific jargon.

Oh yes, I absolutely agree. Unfortunately, there's some 9 year history here, which cannot be gotten rid of easily, given that fetchOne and fetch are probably among the most used methods in the API.

Btw, there's fetchMany() whose name might have been picked a bit prematurely. It doesn't mean what you might think right now in the context of this ticket. It actually means fetch several times (several 0..N results). Luckily it's used so rarely, so it won't confuse many people. But the problem is, it's occupying another term that could be used in a better, more consistent way.

Another example that was recently fixed was the misleading re-use of fetchOne on a Cursor, where it really meant fetchNext. Also not used very often, thus easy to deprecate/rename.

I still think that single is a very precise term with no ambiguity, and I'm happy to re-think one as a term in the future. Or drop it, in favour of the already existing fetchOptional once we drop Java 6 / 7 support, but the new alternative needs to be compelling.

Alternative approach: name them fetchOne and fetchOneOrThrow, for which there’s some precedent in the JDK these days.

That's an interesting idea. However, you know, fetchOne already throws TooManyRowsException when there would have been more than 1 row, so maybe that would be confusing on an entirely new level :)

In any case, what definitely needs to be done soon is an introduction page somewhere at the beginning of the manual, which explains all the jargon, because across the API, it's mostly very consistent and terms are reused all the time, even if they're jOOQ-specific.