Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

jooq- fetching a single value

Tags:

java

sql

jooq

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?

like image 879
sethu Avatar asked Mar 12 '14 07:03

sethu


1 Answers

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.

like image 165
Lukas Eder Avatar answered Oct 10 '22 17:10

Lukas Eder