How to avoid "SelectConditionStep<Record6<String, Integer, MeterProdCode, String, MeterType, Integer>>"?

[Thorsten Schöning]

Hi all,

I have a lot of SELECTs in which I only query individual columns of
various tables, sometimes JOINed, sometimes not. In many of those
cases the queries result in types with pretty long generic definitions
of "RecordX<...>", like in the following example:

> SelectConditionStep<Record6<String, Integer, MeterProdCode, String, MeterType, Integer>> select = this.getDbConn().getJooq()
> .select(REAL_ESTATE.NUMBER,
> METER.ID, METER.PROD_CODE, METER.READING_SERIAL, METER.TYPE,
> METER_BCD.ID)
> .from(FLAT)
> [...]
> .where(REAL_ESTATE.DELETED.isFalse())
> [...]

I would like to reduce that "Record6"-stuff to some better named
custom type but don't understand how to do that without implementing
all those interfaces defined by "Record", which already have been
implemented by jOOQ. Neither RecordImpl, AbstractRecord or their
counterparts for rows are publicly accessible or can be extended.

Looking at things like "fetchInto" and "Record.into", I additionally
have the feeling that I would be able to reduce "Record6"-stuff only
in combination with actually fetching data. But I sometimes build
queries spanning multiple methods and would like to get that
"Record6"-stuff out of the way in those cases as well.

The nearest thing I found was the following, which reads like fetching
all columns of the tables part of the query in the end:

https://www.jooq.org/doc/latest/manual/sql-execution/fetching/record-vs-tablerecord/

What I would really like to do is simply creating a custom class or
interface with very little boilerplate like a CTOR only or such,
actually extending/implementing "Record6" and somehow reusing all the
already available implementation of jOOQ. Than I would like to tell
"select" that the indidvidual columns are actually of my custom
records type and use that from than on.

> SelectConditionStep<RecMeterLidWithReMbcd> select = this.getDbConn().getJooq()
> .select(REAL_ESTATE.NUMBER,
> METER.ID, METER.PROD_CODE, METER.READING_SERIAL, METER.TYPE,
> METER_BCD.ID)
> .from(FLAT)
> [...]
> .where(REAL_ESTATE.DELETED.isFalse())
> [...]

Is something like that possible? Thanks!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hi Thorsten,

Thank you very much for your message.

The main reason for these types is to provide type safety *within* the DSL. This benefits syntax like union, row value expressions (e.g. row(1, 2).in(select(A, B).from(T))), etc. When consuming these types in client code, they can indeed get in the way. A lot of people will simply use wildcards, or the generic Record type as a workaround:

Result<?> result = ctx.select(...).fetch();

for (Record record : ctx.select(...)) { ... }

Since Java 9, var is even better, as it allows for omitting the declaration, locally, while not giving up on type safety:

var result = ctx.select(...).fetch();

for (var record : ctx.select(...)) { ... }

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

CREATE VIEW rec_meter_lid_with_re_mbcd AS

SELECT real_estate.number, meter.id, meter.prod_core, meter.reading_serial, meter.type, meter_bcd.id

FROM ...

Views are generally underappreciated in SQL, regardless if you're using jOOQ or not. They're not at all contradictory to the jOOQ vision. Au contraire. With the above view, you could now use the generated record type in your jOOQ SQL:

Select<RecMeterLidWithReMbcdRecord> select = ctx.selectFrom(REC_METER_LID_WITH_RE_MBCD);

Emitting types from ad-hoc queries would be great. In .NET, type providers would allow this, and in Scala, I've played around with macros in the past to achieve this. I don't think it is possible with vanilla Java annotation processing, although we're observing some promising libraries and approaches, including Manifold, which might allow for such tricks on a lower compiler level than APT.

We could, of course, also implement some auxiliary APIs for quick wins like yours, to help quickly define custom Record subtypes without implementing them. In order to do that, I'd love to understand your use-case a bit more. What is your expectation towards such a custom Record type? Why would you use it?

Cheers,

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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/13710196654.20190918195706%40am-soft.de.

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Donnerstag, 19. September 2019 um 09:52 schrieben Sie:

> Thank you very much for your message.

Thanks for your great answer, mentioned some very interesting
approaches I didn't had in mind.

> 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?

> 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? From my understanding it isn't, but
retrieving all columns of all tables of the query. 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.

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.

> 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.

> Select<RecMeterLidWithReMbcdRecord> select =
> ctx.selectFrom(REC_METER_LID_WITH_RE_MBCD);

Which would be exactly what I want in the end.

> 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.

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 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?

[Lukas Eder]

On Fri, Sep 20, 2019 at 9:12 AM Thorsten Schöning <tscho...@am-soft.de> 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