jooq- fetching a single value

Question

I had a question.. Why do I have repeat what I have selected in the fetch method.

Date minDate = getDSLContext()
    .select(CON_CAL_INSTANCE.DATE.min())
    .from(CON_CAL_INSTANCE)
    .join(CON_CAL_TEMPLATES)
    .on(CON_CAL_INSTANCE.TEMPLATE_ID.eq(CON_CAL_TEMPLATES.ID))
    .where(CON_CAL_TEMPLATES.ENTRY_TYPE.in("REPT", "HRPT"))
    .fetchOne(CON_CAL_INSTANCE.DATE.min());

So I have provided CON_CAL_INSTANCE.DATE.min() in my select clause, why do I have to repeat it in fetchOne(CON_CAL_INSTANCE.DATE.min())?

Or am I not doing this right?

Answer 1

You're doing it right. The way the jOOQ DSL is constructed with Java generics, your ResultQuery<Record1<Date>> doesn't "know" it is selecting only a single value, even if the ResultQuery uses Record1 as a row type.

Apart from repeating the column, you have some other options:

ResultQuery<Record1<Date>> query = // ...

// Use two method calls (this may result in a NullPointerException!
// as fetchOne() may return null):
Date date1 = query.fetchOne().value1();

// Use fetchValue():
Date date2 = getDSLContext().fetchValue(query);

See also the DSLContext.fetchValue() Javadoc.

Using a more LINQ-style syntax

On a side-note, there had been discussions in the past about using a more LINQ-style syntax in the jOOQ API:

from Table
where Predicates
select Projection

What may look like a good idea at first is raising new questions:

1. What about the ORDER BY, FOR UPDATE clauses, which should still be placed after SELECT. (See this post for details).
2. What about set operations, like UNION, INTERSECT and EXCEPT.

I've also written about the difference between lexical and logical order of operations in SQL, here

These open issues made us stick with the standard SQL syntax.