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