On Fri, Sep 20, 2019 at 9:12 AM Thorsten Schöning <[email protected]>
wrote:
> > Result<?> result = ctx.select(...).fetch();
> > for (Record record : ctx.select(...)) { ... }
>
> That doesn't really make "SelectConditionStep" itself shorter, but
> only hides it in combination with the "fetch". Some of my statements
> need to be build using different methods and variables for individual
> parts of the query, so sadly this is not always an option for me. But
> 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.
> > BookRecord book = record.into(BOOK);
> > AuthorRecord author = record.into(AUTHOR);
>
> What I don't understand from your example in the docs is the following
> sentence:
>
> > Sometimes, you may want to explicitly select only a subset of your
> > columns, but still use strongly typed records.
>
> While your select is the following:
>
> > Record record = create.select()
> > .from(BOOK)
> > [...]
>
>
> 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.
> That's a problem
> 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.
> Or is there some magic in the background regarding "Record.into" and
> which of the actual record getters are called in the end? Or is
> "subset" speaking of cases like your first one above:
>
> > Result<?> result = ctx.select(...).fetch();
> > 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.
> > The quickest way to get named records in jOOQ would be to write views and
> > generate record types for them. In your case, you could write
>
> Great idea I didn't had in mind yet. Creating a type in Java needed at
> more than one place is of course a sign of redundancy I have in the
> 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).
> > What is your expectation towards such a custom Record type? Why would you
> > use it?
>
> I would use it whenever I really need to only query some columns of
> 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 :)
> Some custom type allows me proper documentation of that type, easy
> renaming and especially reducing the length of variables holding some
> SelectConditionStep like in my example. I have a lot of those
> currently.
>
> Using more views is definitely the correct thing for some statements,
> but looking at my source it seems I have some very custom queries only
> once or twice as well, for which changing the database model might not
> 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.
> I most likely would not even need to add any additional API, really
> 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 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].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO7n_eh%2BHS11wz8KGva20pGmfvXbEkeYZtbK0qYiT%3Dx7%3Dg%40mail.gmail.com.
