Hi Ben,
When I wrote the post, I was immediately aware of how ironic it is that
jOOQ also offers selectFrom(), and thus facilitates producing a similar
kind of overhead as indicated in the post.
There was a previous discussion on this list, which requested that the
fetch(BOOK.TITLE) feature should influence the actual query being
generated. At the time, I rejected the idea, because there are a lot of SQL
features that would break if we changed the behaviour as you suggest:
- FOR UPDATE (pessimistic locking, can only appear in the top level
statement)
- DISTINCT / UNION (we'd have to wrapt the query in a derived table, but
that can have even worse performance implications in some databases)
- ORDER BY (may reference SELECT columns that aren't really needed in the
projection. Yet, the presence of ORDER BY invalidates the idea of
generating a derived table)
- Hints in Oracle may be misplaced
- Many other weird SQL language quirks...
As you can see: It would be unwise to let fetch(Field) have any impact on
the query sent to the server, even if from a client perspective, it may
look reasonable, and even if for simple queries, it would seem
straightforward.
The real problem here is that the fetch() method cannot "reason" about the
type it is called upon. What you really want to do instead is this:
List<String> title = db.select(BOOK.TITLE)
.from(BOOK)
.where(BOOK.AUTHOR.eq("Robert Heinlein"))
.fetchXXX(); // Some method that would not wrap
String in Record1<String>
We could, however, have such a generic method on DSLContext, though:
public <T> List<T> fetchXXX(ResultQuery<? extends Record1<? extends T>>
query);
Or, perhaps, there's another solution that I currently don't see.
Lukas
2017-03-10 1:30 GMT+01:00 <[email protected]>:
> Hi Lukas,
>
> In your blog post regarding unnecessary work, I wonder jOOQ could do
> better when it known the fields the user is consuming.
>
> Take for example,
> List<String> title = db.selectFrom(BOOK).where(BOOK.AUTHOR.eq("Robert
> Heinlein")).fetch(BOOK.TITLE);
>
> This will generate a query like,
> SELECT book.id, book.author, book.title, book.isbn FROM book WHERE
> book.author = 'Robert Heinlein';
>
> This kind of shorthand is pretty common to see, instead of the more
> correct query.
> List<String> title = db.select(BOOK.TITLE).from(
> BOOK).where(BOOK.AUTHOR.eq("Robert Heinlein")).fetch(BOOK.TITLE);
>
> Ideally the renderer would optimize these queries to fetch only the
> necessary fields. Do you think jOOQ could do this in the simple cases?
>
> Cheers,
> Ben
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
