Hello,
On Wednesday, September 19, 2012 6:03:19 PM UTC+2, Lukas Eder wrote:
>
> Hello,
>
> I've stumbled upon LLblgen a couple of times now, I'm sure I'll find
> some good inspiration for jOOQ in there. However,
>
> > //Relations to load (customer -> orders -> orderdetails and customer ->
> > Address)
> > IPrefetchPath prefetchPath = new PrefetchPath();
> >
> prefetchPath.Add(CustomerEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);
>
>
> >
> > prefetchPath.Add(CustomerEntity.PrefetchPathAddress);
>
> At first sight, this syntax looks somewhat verbose to me, for the
> little features it offers. In fact, it just avoids 1-2 join clauses,
> if I understand this correctly?
Entities in llblgen have accessors for all foreign entities, you may add or
disable relations you don't need using their designer tool... Unless you
use selfservicing, those are just plain C# objects that are serializable
and remotable, unlike hibernate or such tools, they aren't proxies that
references a database connection. They are mostly data holders.
Typically, an Order Entity may have a "CustomerEntity" or a
"List<OrderDetailEntity>" among its properties if corresponding foreign
keys are defined.
PrefetchPath are there to explain which parts of the object graph you want
the dataAdapter to load. The system may seem a bit awkward at first look,
but consider this use case :
You want a list of customers from one region, you're also going to need
their orders, their past purchases along with the order details and product
of those. Loading these with one single query and JOINS, may give you so
many duplicates in the resultset that you could quickly end up with one
hundred of thousand rows so you'd prefer subqueries.
Your first SELECT from customer where region=x returns 350 customers. Now
for those 350, you want to load their orders in one single query, then the
order details of those in another subquery, you'd better love big routines
and Hashmaps if you want to hand code that yourself. Even if you had some
HQL like join logic, you'd still have to manually distribute the query
result to the main collection, or you'd need a session cache which is
probably not an attractive solution (after all, we are all using Jooq
because we have grown sick and tired of hibernate-like voodoo frameworks
right ;-) ?).
Using the prefetchPath API, you can do that in just a few lines of code,
you d'write
IPrefetchPath prefetchPath = new PrefetchPath();
prefetchPath.Add(CustomerEntity.PrefetchPathOrders)
.SubPath.Add(OrderEntity.PrefetchPathOrderDetails)
.SubPath.Add(OrderDetailEntity.PrefetchPathProduct)
prefetchPath.Add(CustomerEntity.PrefetchPathPastPurchases)
.SubPath.Add(PastPurchasEntity.PrefetchPathPastPurchasesDetails)
.SubPath.Add(PastPurchaseDetailsEntity.PrefetchPathProduct)
You pass the prefetchPath object to the dataloader along with other stuff
("where" filters) and it will populate graph branches stated above
automatically by using subselects or IN queries depending on the number of
rows returned. Then you just have to iterate the resulting entity
collection and everything you asked for is there, without any N+1. It's
extremely powerful and saves a lot of coding.
Unlike hibernate and HQL, it does not rely on a session cache to handle the
graph magic, it just does what you ask. It's perfectly safe to pass the
resulting objects to some service or to display it in the UI.
Besides, I'd really prefer not to
> expose some implementation internals (the notion of a path that is
> "prefetched") in an API. If I'm going to go down that path, I'd really
> love the resulting jOOQ code look something like this:
>
> -----------------------------------------------------
> // Query 1
> create.select(FIRST_NAME, LAST_NAME)
> .from(AUTHORS)
> .where(AUTHORS.BOOKS.TITLE.equal("1984"))
>
> // Query 2
> create.select(TITLE)
> .from(BOOKS)
> .where(BOOKS.AUTHORS.LAST_NAME.equal("Orwell"))
> -----------------------------------------------------
>
> which would render this SQL
>
> -----------------------------------------------------
> -- Query 1
> SELECT first_name, last_name
> FROM authors
> WHERE EXISTS (
> SELECT 1 FROM books
> WHERE books.author_id = author.id
> AND books.title = '1984'
> )
>
> -- Query 2
> SELECT title
> FROM books
> JOIN authors ON authors.id = books.author_id
> WHERE authors.last_name = 'Orwell'
> -----------------------------------------------------
>
> As indicated in https://github.com/jOOQ/jOOQ/issues/1502. This would
> resemble a typesafe HQL
>
> > Because I never trusted this "full OO domain over relational" approach
> > advertised by bookwriters and saw a lot of projects where category 2 led
> to
> > disaster, I choose "1" (mybatis) and had success doing so. Now I think
> that
> > Jooq, thanks to its code generation approach does a great job reducing
> the
> > drawbacks of category "1". I am just wondering if it could go even
> further
> > with some common issues such as loading relations.
>
> Yes, it most certainly could.
>
