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 2014-06-30 15:44 GMT+02:00 Garret Wilson <[email protected]>: > 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? > > -- > 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]. > For more options, visit https://groups.google.com/d/optout. > -- 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]. For more options, visit https://groups.google.com/d/optout.
