suggestions using .map() with .join() in the query

[Alok Menghrajani]

Hi,

I want to check if anyone has a suggestion on using .map() with
.join() in the query.

If you look at the fetchXYZ methods in [1], you'll notice that:

fetchAuthors is fine. It's the simplest case and works as expected.


fetchOneAuthor fails to type check if you use MyTypedMapper. I
initially thought the root cause was the signature of the map method
(it's <E> List<E> map(RecordMapper<? super R, E> var1); in Result.java
but public final <E> E map(RecordMapper<Record, E> mapper) in
AbstractRecord). I however gave up trying to fix this issue as I
clearly don't know type systems.

The maven error message is fantastically useful:
error: method map in class AbstractRecord cannot be applied to given types;


fetchAuthorsOfTitle is the interesting case. It is a common pattern in
the codebase I'm working on. The .join() is not used to fetch more
data, but only to filter data / enforce privacy rules.

Is there a way to avoid having to use an untyped mapper? Can I tell
jOOQ that I want to select on a table and that join should not change
the type of the Record?


fetchOneAuthorWithTransaction is interesting because mvn does not
properly infer the type of Immutable.of() so I need to write
Immutable.<MyAuthor>of(). Intellij doesn't complain. It's not a jOOQ
specific thing, just a quirk which shows up when using closures (which
transactions encourage). I guess it's something that should be filed
as a bug with JetBrains.

Alok

[1] my test code:

package com.squareup.JooqTest;

import com.google.common.collect.ImmutableList;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.DSL;
import test.generated.tables.records.AuthorRecord;

import static test.generated.tables.Author.AUTHOR;
import static test.generated.tables.Book.BOOK;

/**
* create database library;
* use library;
* create table author (id int not null, first_name varchar(255),
last_name varchar(255), primary
* key (id));
* create table book (id int not null, author int not null, title
varchar(255), primary key (id));
* insert into author set id=1, first_name="Isaac", last_name="Asimov";
* ...
* insert into book set id=1, author=1, title="nightfall";
* ...
*/
public class App {
public static void main(String[] args) throws Exception {
App app = new App();
app.run();
}

public void run() throws Exception {
String userName = "root";
String password = "*****";
String url = "jdbc:mysql://localhost:3306/library";


try (Connection conn = DriverManager.getConnection(url, userName,
password)) {
DSLContext jooqContext = DSL.using(conn);
fetchAuthors(jooqContext);
fetchOneAuthor(jooqContext);
fetchAuthorsOfTitle(jooqContext, "nightfall");
fetchOneAuthorWithTransaction(jooqContext);
}
}

List<MyAuthor> fetchAuthors(DSLContext jooqContext) {
return jooqContext.selectFrom(AUTHOR).fetch().map(new MyTypedMapper());
}

MyAuthor fetchOneAuthor(DSLContext jooqContext) {
return jooqContext.selectFrom(AUTHOR).fetchOne().map(new MyUntypedMapper());
}

List<MyAuthor> fetchAuthorsOfTitle(DSLContext jooqContext, String title) {
return jooqContext
.select()
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR))
.where(BOOK.TITLE.eq(title))
.fetch()
.map(new MyUntypedMapper());
}

List<MyAuthor> fetchOneAuthorWithTransaction(DSLContext jooqContext) {
return jooqContext.transactionResult(configuration -> {
MyAuthor r = fetchOneAuthor(jooqContext);
if (r != null) {
return ImmutableList.of(r);
}
return ImmutableList.<MyAuthor>of();
});
}

class MyTypedMapper implements RecordMapper<AuthorRecord, MyAuthor> {
public MyAuthor map(AuthorRecord record) {
MyAuthor r = new MyAuthor();
r.id = record.getId();
r.firstName = record.getFirstName();
r.lastName = record.getLastName();

return r;
}
}

class MyUntypedMapper implements RecordMapper<Record, MyAuthor> {
public MyAuthor map(Record record) {
MyAuthor r = new MyAuthor();
r.id = record.getValue(AUTHOR.ID);
r.firstName = record.getValue(AUTHOR.FIRST_NAME);
r.lastName = record.getValue(AUTHOR.LAST_NAME);
return r;
}
}

class MyAuthor {
public int id;
public String firstName;
public String lastName;
}
}

[Lukas Eder]

Hello Alok,

Thank you for your detailed E-Mail. I'm commenting inline

2015-04-21 23:53 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

Hi,

I want to check if anyone has a suggestion on using .map() with
.join() in the query.

If you look at the fetchXYZ methods in [1], you'll notice that:

fetchAuthors is fine. It's the simplest case and works as expected.


fetchOneAuthor fails to type check if you use MyTypedMapper. I
initially thought the root cause was the signature of the map method
(it's <E> List<E> map(RecordMapper<? super R, E> var1); in Result.java
but public final <E> E map(RecordMapper<Record, E> mapper) in
AbstractRecord). I however gave up trying to fix this issue as I
clearly don't know type systems.

This is due to a design restriction we have been struggling with for a while. We cannot refer to the generic R type of a Record from within the record, as we don't declare the generic type recursively. If we wanted to get this right for the Record.map() method, we would have to declare:

interface Record<R extends Record> {

}

However, recursive generics are pandora's box that you generally don't want to open, especially in a type hierarchy like that of Record. I've blogged about this here:

http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism/

This will be addressed in jOOQ 4.0:

https://github.com/jOOQ/jOOQ/issues/2570

I suspect that by using a type similar to Optional<T>, we could properly solve this. In fact, you could actually use Optional:

Optional.of(record).map(myTypedMapper::map).get();

// or:

Optional.of(record).map(r -> myTypedMapper.map(r)).get();

// or just plain old ;-)

myTypedMapper.map(record);

A (silly) workaround would be to operate only with Result<R>, which doesn't have this limitation.

The maven error message is fantastically useful:
error: method map in class AbstractRecord cannot be applied to given types;


fetchAuthorsOfTitle is the interesting case. It is a common pattern in
the codebase I'm working on. The .join() is not used to fetch more
data, but only to filter data / enforce privacy rules.

Interesting. Is there a reason why you didn't semi-join that relation, e.g. via IN or EXISTS? Performance, perhaps?

Is there a way to avoid having to use an untyped mapper? Can I tell
jOOQ that I want to select on a table and that join should not change
the type of the Record?

You're looking for Result.into(Table), or ResultQuery.fetchInto(Table):

- http://www.jooq.org/javadoc/latest/org/jooq/Result.html#into-org.jooq.Table-

- http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#fetchInto-org.jooq.Table-

You can use these methods with any query. It will produce records of type R from Table<R>, containing only the columns of R, or null if the column name wasn't present in the result.

 

fetchOneAuthorWithTransaction is interesting because mvn does not
properly infer the type of Immutable.of() so I need to write
Immutable.<MyAuthor>of(). Intellij doesn't complain. It's not a jOOQ
specific thing, just a quirk which shows up when using closures (which
transactions encourage). I guess it's something that should be filed
as a bug with JetBrains.

There had been a lot of bugs in the javac compiler that have all been fixed in 1.8.0_40... I can't reproduce this particular one anymore, but I remember having run into this kind of type inference problem with 1.8.0_25 and earlier.

Cheers,
Lukas

[Alok Menghrajani]

Thanks for the quick response! I love that whenever I ask a question
on this list I have a response waiting for me when I wake up.

On Tue, Apr 21, 2015 at 11:21 PM, Lukas Eder <lukas...@gmail.com> wrote:
> [...]

>
> However, recursive generics are pandora's box that you generally don't want
> to open, especially in a type hierarchy like that of Record. I've blogged
> about this here:
> http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism/

Great blog post, going to share it with my friends!

> This will be addressed in jOOQ 4.0:
> https://github.com/jOOQ/jOOQ/issues/2570

:)

> I suspect that by using a type similar to Optional<T>, we could properly
> solve this. In fact, you could actually use Optional:
>
> Optional.of(record).map(myTypedMapper::map).get();
>
> // or:
> Optional.of(record).map(r -> myTypedMapper.map(r)).get();

This doesn't work, I might have gotten something wrong.

> // or just plain old ;-)
>
> myTypedMapper.map(record);

That's what I'm going to use for now.

> A (silly) workaround would be to operate only with Result<R>, which doesn't
> have this limitation.

I thought about this for a little. I think you end up having to
convert a list with zero or one elements (List<MyAuthor>) into a
MyAuthor. It's not super elegant.

> Interesting. Is there a reason why you didn't semi-join that relation, e.g.
> via IN or EXISTS? Performance, perhaps?

Thanks for pointing this out, I'll keep it in mind for the future.
Right now, I'm converting jdbi code to jOOQ so I want to keep the same
queries.

> You're looking for Result.into(Table), or ResultQuery.fetchInto(Table):
>
> -
> http://www.jooq.org/javadoc/latest/org/jooq/Result.html#into-org.jooq.Table-
> -
> http://www.jooq.org/javadoc/latest/org/jooq/ResultQuery.html#fetchInto-org.jooq.Table-
>
> You can use these methods with any query. It will produce records of type R
> from Table<R>, containing only the columns of R, or null if the column name
> wasn't present in the result.

Thanks. fetchInto solves most of the issues I had and lets me use type
safe mappers!

> There had been a lot of bugs in the javac compiler that have all been fixed
> in 1.8.0_40... I can't reproduce this particular one anymore, but I remember
> having run into this kind of type inference problem with 1.8.0_25 and
> earlier.

Yes, some of my coworkers pointed out that it might be a compiler bug.
I'm using 1.8.0_25, so things might be better once I upgrade.

Alok

[Alok Menghrajani]

Hi,

I just wanted to point out that when using fetchInto(FOO), you'll
usually want to also do select(FOO.fields()) and avoid fetching all
the columns.

For example:
jooqContext.select()
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR))
.where(BOOK.TITLE.eq("nightfall"))
.fetchInto(AUTHOR);

Will result in:
select author.id, author.first_name, author.last_name, book.id,
book.author, book.title from author join book on author.id =
book.author where book.title = ?

However:
jooqContext.select(AUTHOR.fields())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR))
.where(BOOK.TITLE.eq("nightfall"))
.fetchInto(AUTHOR);

Results in the more efficient:
select author.id, author.first_name, author.last_name from author join
book on author.id = book.author where book.title = ?

I'm guessing jooq could be improved to detect when too much data is
being fetched and thrown away from the database (in development, the
framework could keep track of which fields were eventually used and
log some kind of warning?).

Alok

[Lukas Eder]

Hi Alok,

I just found that this old mail still lacks an answer to your question at the bottom...

I think that such an approach is a bit too "clever". Think about it this way:

1. fetchInto(AUTHOR) is just short for fetch().into(AUTHOR), so the fetch() action is decoupled from the mapping action into(AUTHOR). You could even map the same Result<?> several times, e.g. once into(AUTHOR) and once into(BOOK)

2. You should be the one completely defining your SQL statement, not some internal rule inside of jOOQ. For instance, the semantics of SELECT DISTINCT T1.A, T1.B, T2.X, T2.Y FROM ... is very different from the similar statement SELECT DISTINCT T1.A, T1.B FROM ...

Hope this helps,

Lukas

[Alok Menghrajani]

Hey Lukas,

Thanks for getting back on this. The way I was imagining it would be something that would warn the developer in development mode. In such a setup, something "too clever" which might have false positives isn't a big deal (as long as there's a way to disable for specific queries).

Alok

--
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.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

2015-07-17 0:30 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

Hey Lukas,

Thanks for getting back on this. The way I was imagining it would be something that would warn the developer in development mode.

Aha, I see. Something like a set of programming style rules, such as are available in FindBugs and PMD and other code quality tools - except that with jOOQ, this can be done only once a query is executed at runtime.

But it's still rather difficult to assess whether the selected columns will really be needed or not. I mean, I suspect that all the involved objects need to be tagged and tracked throughout your app until they're garbage collected...

It's a very interesting topic, though. Not only given your specific use-case of calling fetchInto(Table), but in general. I mean, people keep adding columns to complex SELECT statements as they need more data from the database, but hardly anyone ever dares removing a column, because the consuming side may well be in the frontend...

I think that real value would be generated from being able to actually measure end-to-end performance, collecting statistics from the database and tracking problematic queries back through jOOQ to client code. 1-2 excess columns aren't a problem, but they can be if they enforce suboptimal execution plans...

 

In such a setup, something "too clever" which might have false positives isn't a big deal (as long as there's a way to disable for specific queries).

Personally, I don't really agree. I've given PMD several chances but the huge amount of trivial stuff that is reported makes the tool completely useless to me, at least on an existing code base. But code quality this is a very emotional topic, so I'm sure there are plenty of other opinions. :)