regarding fetching results from multiple tables in jOOQ and store it to POJO

[ankur bhargava]

this might be silly but just to seek confirmation, 

I have a situation when I am querying one db table and I want fetched records map to XyzRecord.java this we can achieve easily but what should be done when  query is resulting multiple columns   from multiple tables and we want to map it to a JAVA POJO kind of class. And we want  it to store fetched records in List<POJO> collection Or in any collection like HashMap. 

DSLContext jOOQDslCtx = getJooqDslCtx(getDBConnection("mytestdb")); 

List<AuthorRecord> authList =  jOOQDslCtx.select().from(Author.AUTHOR).fetchInto(AuthorRecord.class); 

above code is working fine and I want to achieve the same in case of when query fetches columns from multiple tables. 

Please advice!

Ankur

[Garret Wilson]

This is a huge coincidence, but I was just logging in to ask what I believe is the same question.

Let's say was have a table of books and a table of authors. I want to return a list of instances of Book, but with attached Author instances as well (which I can access using Book.getAuthors()). For better efficiency and for atomicity, I suppose I can do a join, which won't return me a BookRecord anymore. Moreover, as there could be many, many books returned, I use fetchLazy() to give me a cursor. As far as I can tell, I have to do this:

List<Book> bookList=new ArrayList<Book>();
Book currentBook=null;
Author currentAuthor=null;
while(cursor.hasNext()) {
  Record record=cursor.fetchOne();
  String bookID=record.getValue(BOOK.ID);
  //if we are changing books
  if(currentBook==null || !currentBook.getID.equals(bookID) {
    //if we were populating a book
    if(currentBook!=null) {
      bookList.add(currentBook);
     }
    //start a new book
    currentBook=new Book(bookID);
    currentAuthor=null;
    currentBook.setTitle(record.getValue(BOOK.TITLE));
    ...
  }
  String authorID=record.getValue(AUTHOR.ID); 
  if(currentAuthor==null || !currentAuthor.getID().equals(authorID)) {
    if(currentAuthor!=null) {
      currentBook.addAuthor(author);
    }
    currentAuthor=new Author();
    currentAuthor.setLastName(record.getValue(AUTHOR.LAST_NAME));
    ...

And so it goes. Because a join will produce records with duplicated information for the objects in one-to-many relationships, I'll have to have this bulky logic for detecting when an instance changes, correct? But that is so tedious!! I could instead do multiple queries to create the instances as I walk the graph---but isn't that less efficient and not atomic?

What does jOOQ expect us to do to retrieve instance graphs in an efficient way?

[Lukas Eder]

Hello Ankur,

I'm not quite sure what exactly you're asking here. The "into(Class)" operation is completely independent of what you queried before in your jOOQ SQL statement. By default, it follows the algorithm specification defined in DefaultRecordMapper:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultRecordMapper.html

That algorithm can be overridden by implementing a custom RecordMapperProvider:

http://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos-with-recordmapper-provider/

In order to fetch into HashMaps, there are a couple of relevant fetch methods as well (the list in the manual is non-exhaustive):

http://www.jooq.org/doc/latest/manual/sql-execution/fetching/arrays-maps-and-lists/

Let me know if this answers your question(s).

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

[Lukas Eder]

Hi Garret,

Nice to hear from you again. I think your question may indeed be similar to Ankur's, although yours is a bit more general.

There are two things to say about your approach:

1) Much of it is covered already by DefaultRecordMapper:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultRecordMapper.html

It performs the mapping of a Record into a (custom) POJO. I see that you have a non-default constructor taking only the bookID, but that's fine. You can override DefaultRecordMapper behaviour if needed:

http://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos-with-recordmapper-provider/

In order to group books by author, you might also find jOOQ's grouping methods useful. For instance:

http://www.jooq.org/javadoc/latest/org/jooq/Result.html#intoGroups-org.jooq.Field:A-java.lang.Class-

Some more functionality is provided by ModelMapper, for instance, which integrates with jOOQ:

http://modelmapper.org

2) This standard approach works quite well until you have ambiguities between columns of the two tables. E.g. the RecordMapper does not distinguish between BOOK.ID and AUTHOR.ID, both being ID columns.

Some more responses to your questions inline:

And so it goes. Because a join will produce records with duplicated information for the objects in one-to-many relationships, I'll have to have this bulky logic for detecting when an instance changes, correct? But that is so tedious!!

I don't think you'll have to repeat all this code every time you change fetched relationships. It's true that jOOQ doesn't have all use-cases nicely covered yet, but with all the meta-information available, I'm sure you can factor out some algorithms. We're also happy to add new methods to org.jooq.Result if it helps grouping parent / child relationships, and if we an see a clear and reusable use-case.

 

I could instead do multiple queries to create the instances as I walk the graph---but isn't that less efficient and not atomic?

You should certainly avoid the N+1 problem, i.e. fetching BOOK records on a per-AUTHOR basis.

However, you may indeed be quite efficient with a "1+1 querying pattern", when selecting first:

SELECT * FROM AUTHOR WHERE [some criteria]

And then collect all AUTHOR.ID values (if you only have few of them!) to run:

SELECT * FROM BOOK WHERE AUTHOR_ID IN (...)

 

Or, alternatively (if you potentially have many AUTHOR_ID values):

SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT ID FROM AUTHOR WHERE [some criteria])

I personally prefer the latter, to avoid too many hard-parses for different IN-lists, and too many new cursors in your cursor cache (if your database has that).

Depending on the number of columns in AUTHOR, you can avoid transferring a lot of redundant data by running two queries instead of one.

What does jOOQ expect us to do to retrieve instance graphs in an efficient way?

jOOQ indeed has very limited support for such instance graphs, because the whole idea of SQL denormalisation (through joins) is the fact that you don't have graphs in your SQL output, but flat tables. Hibernate and other ORMs work around this limitation by having re-defined an entirely new query language that does not "suffer" from these denormalisation problems. In the future, we may be implementing similar extensions to SQL, i.e. JOIN operations that produce nested tables where the relationships are set out clearly.

Unfortunately, there is currently no magic bullet for querying instance graphs with jOOQ. For simple use-cases, there are tools like Result.intoGroups(). For more complex use-cases, Java 8 and Streams may help transforming Results a bit more nicely (examples at the bottom):

http://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/

But fully-functional object-graphs are a thing that are best supported in ORMs. The general difference between domain-model-centric approaches vs. relational-model-centric approaches from our perspective is explained here:

http://www.hibernate-alternative.com

We'll hopefully also be doing a piece about how jOOQ (and Hibernate) fit into a CQRS model, soon.

Hope this helps.

I'm very open to further discuss more issues around here. This topic pops up frequently, and there's certainly room for improvement in that area.

Lukas

--

[ankur bhargava]

Hi Lukas, 

My questions was, what should be the approach when we are creating query which would have JOIN operations and in result it is selecting different columns from different tables then how would store result in JAVA POJO class? Do we need to create new java pojo class which would contain all properties's for resultant columns? Or can we store results into in any Collection ? 

I am going through the links and will update you if these links would answer my question. 

Thanks,

Ankur

[Lukas Eder]

Hi Ankur,

Thanks for the clarification.

There are lots of models of how people like to model their data transfer objects. jOOQ only provides some basic mapping algorithms that we have found to be very common over the years. But we refrain from saying anything like: You should use POJOs. Or you should use HashMaps. Or you should use jOOQ's UpdatableRecords...

In my personal opinion (not really a jOOQ recommendation), I really like to keep all things "relational" (or at least SQL-esque) throughout the application. I like jOOQ's Record types, and if possible UpdatableRecords, because they can be stored back to the database with optimistic locking and all the other features. A Record is a very convenient value-type representation of the data produced by your query. A Record can be easily transformed:

- Using jOOQ's various Result / Record methods

- Using Java 8 Streams if you're working with that (examples in here: http://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/)

- Using XSLT if you're using XML, and jOOQ's Result.formatXML()

- Using JavaScript if you're exporting records to your browser using Result.formatJSON()

But that's me. I like functional and declarative languages much more than OO domain models.

As you can see, this will easily evolve into an endless discussion :) There's no right or wrong in these design decisions. jOOQ should support all of them through the most generic API possible. Which also means that jOOQ might not help you make the best decision for *your* application.

Cheers,

Lukas

[Garret Wilson]

Hi, Lukas. I'm not sure you understood what I meant by "duplicate information", and what I meant by "multiple queries... less efficient". I'm no SQL expert, so if I'm off base here let me know. But let me break it down.

Let's say I have a BookManager interface with method getBooks(). (Assume also for the moment that authors are also objects, and that no book has the same author---that more accurately describes my particular domain model.) Conceptually that's straightforward:

List<Book> books=new ArrayList<Book>();
Cursor<BookRecord> bookCursor=dslContext.select().from(BOOK).fetchLazy;
while(bookCursor.hasNext()) {
    BookRecord bookRecord=bookCursor.fetchOne()
    Book book=new Book(bookRecord.getIsbn());
    book.setTitle(bookRecord.getTitle());
    ...
    Cursor<AuthorRecord> authorCursor=dslContext.select().from(AUTHOR).where(AUTHOR.BOOK).EQUALS(bookRecord.getKey()).fetchLazy();
    while(authorCursor.hasNext()) {
        AuthorRecord authorRecord=authorCursor.fetchOne()
        Author author=new Author(authorRecord.getURI());
        author.setFirstName(authorRecord.getFirstName());
        ...
        book.add(author);
    }
    books.add(book);
}

But look at all those queries. Wouldn't it be more efficient to do a join?

Cursor<Record> joinCursor=dslContext.select().from(AUTHOR).join(AUTHOR).onKey().fetchLazy();

But doesn't that mean that in the resulting "table" I'll have multiple author rows for each book, with the book information repeated in each one? I'll have to do something like this:

Book book=null;
while(joinCursor.hasNext()) {
  Record record=joinCursor.fetchOne();
  if(book==null || !book.getISBN().equals(record.getValue(AUTHOR.ISBN))) {
    book=new Book(record.getValue(AUTHOR.ISBN)
    book.setTitle(record.getValue(BOOK.TITLE)
    ...
    books.add(book);
  }
    Author author=new Author(record.getValue(AUTHOR.URI));
    author.setFirstName(authorRecord.getValue(AUTHOR.FIRST_NAME);
    ...
    book.add(author);
}

And imagine if the instance hierarchy is three layers down---all that ugly logic to see if we've moved to a new row for which we've changed the current parent entity we're on for each level. Is there an easier way?

[Lukas Eder]

2014-07-01 16:57 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

Hi, Lukas. I'm not sure you understood what I meant by "duplicate information",

I'm pretty sure I did: You're repeating "parent" (e.g. author) record data for each "child" (e.g. book)

 

and what I meant by "multiple queries... less efficient".

Hmm, what could you mean other than N+1 querying? :)

Some explanations:

http://stackoverflow.com/q/97197/521799

 

I'm no SQL expert, so if I'm off base here let me know. But let me break it down.

Let's say I have a BookManager interface with method getBooks(). (Assume also for the moment that authors are also objects, and that no book has the same author---that more accurately describes my particular domain model.)

OK, although that shouldn't change too much for the issue at hand.

 

Conceptually that's straightforward:

List<Book> books=new ArrayList<Book>();
Cursor<BookRecord> bookCursor=dslContext.select().from(BOOK).fetchLazy;
while(bookCursor.hasNext()) {
    BookRecord bookRecord=bookCursor.fetchOne()
    Book book=new Book(bookRecord.getIsbn());
    book.setTitle(bookRecord.getTitle());
    ...
    Cursor<AuthorRecord> authorCursor=dslContext.select().from(AUTHOR).where(AUTHOR.BOOK).EQUALS(bookRecord.getKey()).fetchLazy();
    while(authorCursor.hasNext()) {
        AuthorRecord authorRecord=authorCursor.fetchOne()
        Author author=new Author(authorRecord.getURI());
        author.setFirstName(authorRecord.getFirstName());
        ...
        book.add(author);
    }
    books.add(book);
}

But look at all those queries. Wouldn't it be more efficient to do a join?

Yes, that's the N+1 problem right there, and that must be avoided. Possibly with a join.

 

Cursor<Record> joinCursor=dslContext.select().from(AUTHOR).join(AUTHOR).onKey().fetchLazy();

But doesn't that mean that in the resulting "table" I'll have multiple author rows for each book, with the book information repeated in each one? I'll have to do something like this:

Book book=null;
while(joinCursor.hasNext()) {
  Record record=joinCursor.fetchOne();
  if(book==null || !book.getISBN().equals(record.getValue(AUTHOR.ISBN))) {
    book=new Book(record.getValue(AUTHOR.ISBN)
    book.setTitle(record.getValue(BOOK.TITLE)
    ...
    books.add(book);
  }
    Author author=new Author(record.getValue(AUTHOR.URI));
    author.setFirstName(authorRecord.getValue(AUTHOR.FIRST_NAME);
    ...
    book.add(author);
}

And imagine if the instance hierarchy is three layers down---all that ugly logic to see if we've moved to a new row for which we've changed the current parent entity we're on for each level. Is there an easier way?

I think I gave you the answer in my previous E-Mail. You have these options from my point of view:

1. Use joins and implement that grouping yourself, but factor out common parts. Right now, you have a lot of redundant logic because you're explicitly tying stuff to the AUTHOR / BOOK relation. I'm sure there are elements that can be factored out generically. jOOQ provides you with foreign key meta information

2. Use joins and use jOOQ's existing Result.intoGroups() methods. It'll help doing the "grouping" (which you currently do manually, and iteratively)

3. Use several queries instead of joins, but don't fetch each author for each book. Fetch all books first, and then fetch *ALL* authors for *ALL* books

Let me know if any of these concrete suggestions are unclear.

Lukas

[Garret Wilson]

On Wednesday, July 2, 2014 11:42:40 PM UTC-7, Lukas Eder wrote:

2014-07-01 16:57 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

Hi, Lukas. I'm not sure you understood what I meant by "duplicate information",

I'm pretty sure I did: You're repeating "parent" (e.g. author) record data for each "child" (e.g. book)

 

and what I meant by "multiple queries... less efficient".

Hmm, what could you mean other than N+1 querying? :)

Ah, pardon me, so you did understand. :) I had assumed that there must be a nice and tidy jOOQ solution, and simply I must not have been explaining myself clearly. I see now that this is a larger, common issue---this is why people use Hibernate, monster that it is.
 

... You have these options from my point of view:

1. Use joins and implement that grouping yourself, but factor out common parts. Right now, you have a lot of redundant logic because you're explicitly tying stuff to the AUTHOR / BOOK relation. I'm sure there are elements that can be factored out generically. jOOQ provides you with foreign key meta information

Yeah, I can definitely refactor some of the stuff above. I was just looking for some help from jOOQ.
 

2. Use joins and use jOOQ's existing Result.intoGroups() methods. It'll help doing the "grouping" (which you currently do manually, and iteratively)

That's interesting, but from a cursory examination (pardon the unintentional pun) it doesn't seem to support cursors, meaning I'm going to get a humongous map of all those records, each of them with duplicate, duplicate, duplicate information for each of the items in the parent relationship.
 

3. Use several queries instead of joins, but don't fetch each author for each book. Fetch all books first, and then fetch *ALL* authors for *ALL* books

Yes, that would be cleaner, but I would guess that's not as efficient as a join. (I am not an SQL query expert.)

The "N+1 problem" seems to be two separate issues:

1. How to nicely map stuff from joins into objects. This is where Hibernate is light years ahead, I guess. But I would have thought that jOOQ would give me some utilities to help with this. You did give some links to some mappers, which I will look into, but I didn't see any examples offhand.
2. How to efficiently query data for a hierarchy of objects. From reading a tiny bit, this seems like a general problem of relational databases, one that Hibernate may not have solved, either.

Thanks for all the links. I'll do some more research and see where that gets me.

Garret

[Lukas Eder]

Hi Garret,

I'm sorry for the delay. I was on a quick time off on Kos island, now more than ever ready to answer questions again :)

2014-07-03 21:50 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

On Wednesday, July 2, 2014 11:42:40 PM UTC-7, Lukas Eder wrote:

2014-07-01 16:57 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

Hi, Lukas. I'm not sure you understood what I meant by "duplicate information",

I'm pretty sure I did: You're repeating "parent" (e.g. author) record data for each "child" (e.g. book)

 

and what I meant by "multiple queries... less efficient".

Hmm, what could you mean other than N+1 querying? :)

Ah, pardon me, so you did understand. :) I had assumed that there must be a nice and tidy jOOQ solution, and simply I must not have been explaining myself clearly. I see now that this is a larger, common issue---this is why people use Hibernate, monster that it is.

Hibernate's (JPA's) main reason for being is precisely to solve this kind of mapping problem. In my opinion, the mapping problem only happens in situations where the Java domain model is essential to an application and to its business logic. In more SQL-centric (relational-centric) applications, people are usually happy to keep operating on flat tabular data structures. This is summarised on this website here:

http://www.hibernate-alternative.com

Of course, this doesn't help solve your concrete problem, but it's always good to think about these things in a broader context. ORM is still an unsolved problem (maybe because it cannot be solved). If you're interested in further (academic) reading on the subject, I recommend reading Erik Meijer's and Gavin Bierman's paper "A co-Relational Model of Data for Large Shared Data Banks"

http://queue.acm.org/detail.cfm?id=1961297

 

 

... You have these options from my point of view:

1. Use joins and implement that grouping yourself, but factor out common parts. Right now, you have a lot of redundant logic because you're explicitly tying stuff to the AUTHOR / BOOK relation. I'm sure there are elements that can be factored out generically. jOOQ provides you with foreign key meta information

Yeah, I can definitely refactor some of the stuff above. I was just looking for some help from jOOQ.
 

2. Use joins and use jOOQ's existing Result.intoGroups() methods. It'll help doing the "grouping" (which you currently do manually, and iteratively)

That's interesting, but from a cursory examination (pardon the unintentional pun)

Clever ;)

 

it doesn't seem to support cursors,

Hmm, maybe you'd like to elaborate on that? Do you think there is a missing feature?

org.jooq.Cursor (obtained from fetchLazy()) is in fact a reference to an open JDBC ResultSet (CONCUR_READ_ONLY, TYPE_FORWARD_ONLY). The ResultSet itself models a cursor on the database level, which materialises new records each time it is forwarded using ResultSet.next(). There may be databases that are able to optimise data transfer through the wire to avoid transferring redundant information originating from joins, but by default, I don't think you can optimise this because your JOIN will simply denormalise your data to produce this redundancy. That's what JOINs do :)

 

meaning I'm going to get a humongous map of all those records, each of them with duplicate, duplicate, duplicate information for each of the items in the parent relationship.

Yes, that's how things usually work with joins.

I'm not sure if I have mentioned this before: We're hoping to be able to introduce a new synthetic join that works like an aggregation to produce nested tables. Simply put, the output should be:

// With Java collections

Map<AuthorRecord, List<BookRecord>>

// With jOOQ Records

Result<Record2<AuthorRecord, Result<BookRecord>>>

At some point, we'll support this kind of feature, but right now, we haven't done all the thinking about the myriad edge cases that such synthetic table nesting will introduce.

 

3. Use several queries instead of joins, but don't fetch each author for each book. Fetch all books first, and then fetch *ALL* authors for *ALL* books

Yes, that would be cleaner, but I would guess that's not as efficient as a join. (I am not an SQL query expert.)

Not necessarily. For several reasons:

1. The database may still hold the data in its buffer caches, which allows it to access the data quickly

2. Both consecutive queries are very likely to be able to profit from the same indexes

3. You can avoid transferring all that duplicate data over the wire from your database to JDBC

 

The "N+1 problem" seems to be two separate issues:

1. How to nicely map stuff from joins into objects. This is where Hibernate is light years ahead, I guess. But I would have thought that jOOQ would give me some utilities to help with this. You did give some links to some mappers, which I will look into, but I didn't see any examples offhand.
2. How to efficiently query data for a hierarchy of objects. From reading a tiny bit, this seems like a general problem of relational databases, one that Hibernate may not have solved, either.

Hibernate / JPA could only tackle this problem because they invented a *new* query language that only supports about 20% of the features that SQL has. It is (probably) impossible to map arbitrary joins to objects. Or vice versa. Again, Erik Meijer's paper sheds some light on this duality between relational models and object models.

Thanks for all the links. I'll do some more research and see where that gets me.

Great! Let us know where your research is leading you. 

Cheers

Lukas

[Lukas Eder]

One more note...

2014-07-03 21:50 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

Ah, pardon me, so you did understand. :) I had assumed that there must be a nice and tidy jOOQ solution, and simply I must not have been explaining myself clearly. I see now that this is a larger, common issue---this is why people use Hibernate, monster that it is.

We will be taking this subject seriously, even if we're not going to implement another ORM. Maybe more of a functional mapper as documented here:

http://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/

Coincidentally, there had been another discussion on Reddit going on in parallel, depicting the lack of mapping tools as one of the shortcomings of jOOQ:

http://redd.it/29yspv

Your feedback is very helpful and we'll look into how to improve mapping in the future.

Best, 
Lukas