Digest for jooq-user@googlegroups.com - 1 update in 1 topic

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 1 update in 1 topic

http://groups.google.com/group/jooq-user/topics mailing list
Lukas Eder <[hidden email]>: Sep 23 10:43AM +0200

On Fri, Sep 20, 2019 at 9:12 AM Thorsten Schöning <[hidden email]>
wrote:
 
> will keeping using "Record" in the loop in mind.
 
> Within that loop I could simply use "rec.get(Field)" as well as
> "Record.into(...)" as mentioned in your docs?
 
Yes of course. You are already using it, probably.
 
 
> > [...]
 
> https://www.jooq.org/doc/latest/manual/sql-execution/fetching/record-vs-tablerecord/
 
> How is that selecting a subset?
 
 
That is an example for "Alternatively, you may want to join a one-to-one
relationship and receive the two individual strongly typed records after
the join." There's no example in the manual for this "subset of columns"
projection in that particular paragraph.
 
 
> for me because I need to JOIN a lot of tables in many statements for
> access permission reasons while only actually reading very few columns
> of very few tables.
 
You probably need semi join, not inner join. You can either use jOOQ's
synthetic LEFT SEMI JOIN syntax, or spell out the SQL version explicitly
using EXISTS or IN:
https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/join-clause/
 
 
I've often seen that people underuse semi joins. Very often, it's
semantically more correct, as it does not produce any unwanted cartesian
products when joining to-many relationships. And with some RDBMS, it can be
much faster, when the optimiser cannot prove that the inner join is
effectively a semi join.
 
If you have good reasons to stick with inner join (e.g. performance on your
specific RDBMS, which might perform poorly for semi joins), you can still
project columns explicitly from few tables only, of course.
 
 
> > for (Record record : ctx.select(...)) { ... }
 
> After using "Record.into(...)" in the loop I would be able to query
> only the columns mentioned in "select(...)" I guess.
 
Yes, that's what "subset" means here.
 
I've written a ton of SQL statements in many applications prior to creating
jOOQ. I don't think I've ever used SELECT * except for quick and dirty
ad-hoc queries (to check something in production), or in derived tables.
But even then, I usually prefer listing all the columns I need explicitly.
Sure, it's more work up front, but there are significant performance
benefits to projecting only explicit column lists all the time. These
include:
 
- (Much) less data transfer between server and client, including the
reduced buffer sizes everywhere leading to less memory consumption
- Possibility of join elimination
- Possibility of using covering indexes
 
I'll soon blog about this. The latter 2 bullets are very often overlooked.
The 3rd one can have significant benefits.
 
 
> SQL-queries already. OTOH, that means changing two places where some
> simple custom queries in Java only with some additional type would be
> sufficient sometimes.
 
There are different aspects to designing systems of course. In my
experience, I've always treated SQL queries as one-shot structural type
providers where the row type of a query is almost never reusable. If Java
had type providers like F# and some other languages, we would be generating
tuple types for each jOOQ query. The workaround is reduced type safety, or,
of course, the tedious manual writing of a DTO per query.
 
In some cases, generic DTOs that occasionally (or even often) have blank
values in some fields can do the trick.
 
There's no silver bullet here, I'm afraid (unless we had type providers,
then that would be the silver bullet).
 
 
> some table instead of all colums of all tables and beyond 2 columns
> most likely. Because for 1 or 2 columns using generics is OK mostly,
> but afterwards I feel it's getting to verbose.
 
Agreed about the generics :)
 
 
> be worth it. For those cases some way to simply give a custom name to
> the fopllowing would be sufficient already:
 
> > Record6<String, Integer, MeterProdCode, String, MeterType, Integer>
 
I see - so type aliases would be a killer in Java. Other languages, like
TypeScript for example, have them. Or C++ as you've mentioned.
 
 
> only some custom name, pretty much like "typedef" in C++.
 
> I've read about RecordMappers, but have the feeling those wouldn't
> make "SelectConditionStep" itself shorter as well?
 
No, they wouldn't. They don't affect the record type, they only affect the
types you're getting after calling one of the many into(Class<?>) methods.
 
I currently don't see a robust, viable, and not too hacky solution to
achieve the nominal typing for such record types that you're looking for -
at least not in Java. You could probably pull it off in Scala or Ceylon,
maybe even Kotlin, using type aliases.
 
Perhaps using annotation processing and following some restrictions on how
jOOQ queries are created, it might be possible to generate the record type
in the result. But the one that is used in the DSL, that seems to be quite
difficult to achieve.
 
Lukas
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].