Hello,
Can't say... I hate JPA annotations soooo much. However if they are
generated, perhaps I could live with.
I think jooq already does a great job objectizing SQL, everytime complex
sql is needed (union, group by or non-trivial 1:1 table mapping) there's a
convenient and predictable way to express the query.
I am not sure that graph loading strategy would fit very well in the
current API which is fully SQL centered.
When dealing with entities, say you want customers with their orders, you
know that if any joins are needed, you'll most likely need left joins or
perhaps INNER in case there's a condition involving another table, if you
want your queries to fit in a graph model in some predictable way you can't
really give total freedom regarding the SQL. My advice then would be to
always use IN or Subqueries to load a graph in order to avoid cartesian
products and excessive duplicate elimination. In llblgen, relations were
always loaded using suqueries even the one-to-one where JOINs would have
been more efficient, it was not often a big drawback and even so you could
use typed queries or views when performance was the main concern.
Because jooq current API is SQL centered and does its job well perhaps it
should stick to that, I would say that an llblgen style entity/graph system
built on top of that may actually be better than adapting the current API
to add the necessary logic. In some ways it could even be a different part
of the project which just use current jooq code under the trunk. You could
then solve common problems using entities, and still have direct access to
pure jooq SQL when reporting queries or anything that requires advanced,
fine-grained SQL, is needed.
Well it is just an idea thrown on the table but sometimes, things are
better when they stay separated...
Best
PS: I have just realized you're near Zurich, You have my greetings from the
Valais ;-)
On Friday, September 21, 2012 11:03:07 AM UTC+2, Lukas Eder wrote:
>
> Hello
>
> > [...] 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.
>
> Thanks for that detailed explanation. Well, in essence LLblgen seems
> to be doing something similar to what I have in mind for the next
> couple of versions of jOOQ, just with a different API. But I can see
> how auto-fetching / transforming flat tables into one-to-many entity
> relationships can be useful a lot of times.
>
> The jOOQ approach will be to let you hand-write your JOIN expressions
> as jOOQ doesn't want to assume whether INNER / (FULL) OUTER /
> PARTITIONED / EQUI / etc. JOIN is the most suitable way of joining
> things for you. Also, jOOQ doesn't expect you to always think in terms
> of pre-defined entities, that map to tables 1-1. Often you will phrase
> complex SQL queries involving GROUP BY, UNION, and other operations,
> which will create something like ad-hoc entities.
>
> Anyway, in short, there are two features that jOOQ should have in the
> future:
>
> - A way to express "paths" in jOOQ's API and to use them. Even if path
> resolution will involve quite a bit of voodoo, from what I can say now
> ;-)
> - A way to transform flat org.jooq.Result objects into composed Java
> POJO graphs, which model @OneToMany relationships (and possibly
> others). As it looks now, the most appropriate way to declare those
> relationships within POJOs is probably by re-using existing JPA
> annotations:
>
> @Table(name = "customer")
> class Customer {
>
> @Column(name="id")
> int id;
>
> @OneToMany
> @JoinColumn(name="customer_id", referencedColumnName="id")
> List<Order> orders;
> }
>
> It would be possible, to generate some of that @OneToMany code as
> well, later on. In any case, the fetching is done according to what
> you really select through the jOOQ API. If you don't need the orders,
> then don't join the orders table, and List<Order> will stay empty
>
> 2012/9/21 <[email protected] <javascript:>>:
> > 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.
>
