Re: Graphs and multi-table loading strategies

[Lukas Eder]

Hello,

> [...] for example by the mean of dedicated
> collection classes?

I'm not sure what you mean by "dedicated collection classes". Could
you elaborate this idea? It might just correspond to this old feature
request here:
https://github.com/jOOQ/jOOQ/issues/345

> However, while I have noticed methods for navigating through relations I
> could not find ways to avoid the well-known n+1 issue other than hand
> writing JOINS, IN queries or SubSelects when manipulating collections of
> record.

I think what you're looking for is a feature request that was recently
expressed at the JUGS in Zurich:
https://github.com/jOOQ/jOOQ/issues/1502

This involves adding support for selecting navigation paths that would
be automatically transformed to "equivalent" inner / outer /
semi-joins. I have spent some time investigating the idea, finding it
quite non-trivial to implement. Any ideas, hints are very welcome,
though!

Cheers
Lukas

2012/9/18 Stephane Cl <stefa...@gmail.com>:
> Hello,
> Jooq generated record classes make single table based operation very easy.
> However, while I have noticed methods for navigating through relations I
> could not find ways to avoid the well-known n+1 issue other than hand
> writing JOINS, IN queries or SubSelects when manipulating collections of
> record. While it is clearly not an easily solved issue, I am wondering if
> there has already been some investigation about possible ways to enable
> preloading of relations in some way, for example by the mean of dedicated
> collection classes?
>
> Best

[Adam Gent]

Lukas is right that its non trivial.

I'm sort of using Spring JDBC + jOOq + my own half assed ORM.

I got many to one to sort of work using JPA annotations (@ManyToOne + @JoinColumn) and the ResultSetMetaData to figure out how to transform the child object from the ResultSet.

One to many and many to many will be a PITA and in my mind defeats the goals of jOOQ.

If that feature (child fetching) interests you should check Ajave Ebean. 

[Adam Gent]

My comments below:

Already done a while ago, found 2 bugs during the first 30 mins of use and figured out I still needed raw SQL for many non-select scenarios. There are not that many data access solutions that I haven't evaluated. I came from the .Net world 4-5 years ago and among others, I terribly missed llblgen which had IMHO achieved the best trade-off between usability, type-safety and SQL predictability. Before I knew about jooq, the java data access world could be summarized in 2 categories :

1 - Raw SQL based or almost (jdbcTemplate, mybatis, dbutils), cumbersome, type unsafe, but predictable and efficient SQL

2 - Pojo capable full OO object oriented solutions (hibernate, toplink), less hand coding but less predictable, ton of voodoo magic and "in-your-face" features

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.

I have not found a ORM or SQL Mapping that fits my needs either (but jOOQ comes close :) ).

What I want is:

1. CRUD Immutable POJOs (that is all private fields are final with a constructor that fills them)
2. READ hiearchy of POJOs (that is Many to One's are loaded eagerly) but for WRITE only write the top POJO.
3. Once the POJO is loaded there is no magic. It is not "enhanced". It is safe to deserialize or cache.
4. Static (string) constants generated from the schema (column names, tables names) that I can use for custom SQL and Annotations.
5. Compile time Transaction Support through AspectJ
6. Transaction entity commit callbacks (if a row gets added I want an event generated).
7. Manually do One to Many (ie collections) which IMHO is the right way to do it (because there is nothing worse than accidentally pulling 1000 items).
8. Use plain SQL with the constants mentioned above.
9. Use JPA annotations to help map the SQL ResultSet to your POJOs
10. Threadsafe
11. Fluent API

I discussed more here.
http://stackoverflow.com/questions/2698665/orm-supporting-immutable-classes

jOOQ does much of what I want but it cannot do #2, and sort of does #4, #8, and #9 (factories are not threadsafe).

My inspiration comes some what from Anorm (plain SQL), EBeans (fetching children), MyBatis (Mapping), and jOOQ (Generating from Schema).

[Lukas Eder]

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

[Lukas Eder]

Adam,

> I got many to one to sort of work using JPA annotations (@ManyToOne +
> @JoinColumn) and the ResultSetMetaData to figure out how to transform the
> child object from the ResultSet.

Care to share / contribute this?

> One to many and many to many will be a PITA and in my mind defeats the goals
> of jOOQ.

Yes, there are a lot of corner cases with some of those.

[Adam Gent]

Comments below:

On Wed, Sep 19, 2012 at 12:05 PM, Lukas Eder <lukas...@gmail.com> wrote:

Adam,

> I got many to one to sort of work using JPA annotations (@ManyToOne +
> @JoinColumn) and the ResultSetMetaData to figure out how to transform the
> child object from the ResultSet.

I'm not using jOOQ for this but my own Spring RowMapper that uses column labels (ResultSetMetaData.getColumnLabel) to generate a nested Map<String,Object>.

I would put the code up but its a mess and it will take me some time to remove some of the nastiness. 

Basically I do a bunch of reflection on the top level object I want to load and determine which properties are complex (ie ManyToOne).

Then I construct SQL SELECT (automatically although you generate it manually following a dotted notation naming scheme ):

-- SQL notice "parent.test.stringProp" is bean like notation

SELECT id, 

       parent0.id AS "parent.id",

       parent0_test1.string_prop AS "parent.test.stringProp", 

       parent0_test1.long_prop AS "parent.test.longProp", 

       parent0_test1.timets AS "parent.test.timeTS" 

FROM grand_parent_bean

INNER JOIN parent_bean parent0 ON parent0.id = parent 

INNER JOIN test_bean parent0_test1 ON parent0_test1.string_prop = parent0.test 

WHERE id = ? LIMIT ? OFFSET ?

// JAVA POJOs

public class GrandParentBean {

    @Id

    private final String id;

    @ManyToOne(targetEntity=ParentBean.class)

    private final ParentBean parent;

    

    @JsonCreator

    public GrandParentBean(

            @JsonProperty("id") String id, 

            @JsonProperty("parent") ParentBean parent) {

        super();

        this.id = id;

        this.parent = parent;

    }

    

    

    public String getId() {

        return id;

    }

    

    public ParentBean getParent() {

        return parent;

    }

    

}

public class ParentBean {

    @Id

    private final String id;

    @ManyToOne(targetEntity=TestBean.class)

    private final TestBean test;

    

    @JsonCreator

    public ParentBean(

            @JsonProperty("id") String id, 

            @JsonProperty("test") TestBean test) {

        super();

        this.id = id;

        this.test = test;

    }

    

    

    public String getId() {

        return id;

    }

    public TestBean getTest() {

        return test;

    }

    

}

public class TestBean {

    

    @Id

    private final String stringProp;

    private final long longProp;

    @Column(name="timets")

    @NotNull

    private final Calendar timeTS;

    

    @JsonCreator

    public TestBean(

            @JsonProperty("stringProp") String stringProp, 

            @JsonProperty("longProp") long longProp,

            @JsonProperty("timeTS") Calendar timeTS ) {

        super();

        this.stringProp = stringProp;

        this.longProp = longProp;

        this.timeTS = timeTS;

    }

    

    public String getStringProp() {

        return stringProp;

    }

    public long getLongProp() {

        return longProp;

    }

    

    public Calendar getTimeTS() {

        return timeTS;

    }

}

 

Care to share / contribute this?

> One to many and many to many will be a PITA and in my mind defeats the goals
> of jOOQ.

Yes, there are a lot of corner cases with some of those.

--
CTO
Evocatus, Inc. (evocatus.com)
(twitter) @agentgt (cell) 781-883-5182

[Lukas Eder]

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 <stefa...@gmail.com>:
> 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.

[Lukas Eder]

Hi Adam,

> I'm not using jOOQ for this but my own Spring RowMapper that
> uses column labels (ResultSetMetaData.getColumnLabel) to
> generate a nested Map<String,Object>.
> I would put the code up but its a mess and it will take me some
> time to remove some of the nastiness.

> Basically I do a bunch of reflection on the top level object I want
> to load and determine which properties are complex
> (ie ManyToOne).

Thanks for sharing this! Looks a bit like Hibernate itself at first
sight, with the mixed-in capability of using jackson-annotated
constructors for immutable POJOs. Very nice, though!

2012/9/19 Adam Gent <adam...@evocatus.com>:

[Lukas Eder]

Hello,

> Can't say... I hate JPA annotations soooo much. However if they are
> generated, perhaps I could live with.

Well, I understand those feelings, but JPA annotations are quite
expressive. In my opinion, JPA went wrong in the way they assumed that
SQL should be drastically reduced to JPQL, which in turn can be
generated from those annotations. But the annotations themselves can
be useful (to some)

> I am not sure that graph loading strategy would fit very well in the current
> API which is fully SQL centered.

I don't see a contradiction. Note that all "graph loading" would be
performed in memory within the org.jooq.Result and org.jooq.Record
types. Already today, this is a "convenience extension" to jOOQ's main
feature, the SQL DSL. Already today, the two "models" co-exist. Most
of it is documented here in the Javadoc:
http://www.jooq.org/javadoc/latest/org/jooq/Record.html#into(java.lang.Class)

Adding more features to that into() method wouldn't change the
behaviour of the DSL. Your expressed SQL would still be very
predictable. jOOQ will always first fetch a table representation of
your data into org.jooq.Result. Only then, some transformation may
take place.

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

Precisely. The decision should remain with the jOOQ client code.

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

True, duplicate elimination could be a problem for large queries. But
as always, performance problems shouldn't be the driving force for an
API. There can still be a solution to that problem later on (e.g.
"merging" or "joining" org.jooq.Result objects in memory)

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

I see. But again, I don't think jOOQ should generate any additional
SQL just because you want to fetch "child" records (orders) or your
"main" record (customers). jOOQ users should be in full control of any
SQL executed.

An important reason why this is so is the fact that jOOQ already
renders / simulates a lot of SQL, internally. An example for this is
the simulation of the LIMIT .. OFFSET clause for databases like
Oracle, SQL Server, Sybase, DB2. I also have plans of simulating

- CUBE(), ROLLUP(), GROUPING SETS() using UNION ALL
- Oracle's CONNECT BY using recursive common table expressions
- FULL OUTER JOIN using UNION ALL
- etc...

If a result-table-to-Java mapping strategy would play into these
delicate query transformation features, I'd probably open pandora's
box.

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

Precisely. jOOQ's SQL API will stick to SQL. The "mapping" API (or
perhaps, ORM API) is only contained in org.jooq.Result and
org.jooq.Record. The two will not interfere.

> Well it is just an idea thrown on the table but sometimes, things are better
> when they stay separated...

Don't worry.

> PS: I have just realized you're near Zurich, You have my greetings from the
> Valais ;-)

Great! You should join me at the JUGS in Berne, then! The one in
Zurich was quite interesting:
http://www.jug.ch/html/events/2012/jooq.html

I'm not sure about the date though, yet.

Cheers
Lukas

[Adam Gent]

On Fri, Sep 21, 2012 at 7:04 AM, Lukas Eder <lukas...@gmail.com> wrote:

Hello,

> Can't say... I hate JPA annotations soooo much. However if they are
> generated, perhaps I could live with.

Well, I understand those feelings, but JPA annotations are quite
expressive. In my opinion, JPA went wrong in the way they assumed that
SQL should be drastically reduced to JPQL, which in turn can be
generated from those annotations. But the annotations themselves can
be useful (to some)

I agree 

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

Precisely. jOOQ's SQL API will stick to SQL. The "mapping" API (or
perhaps, ORM API) is only contained in org.jooq.Result and
org.jooq.Record. The two will not interfere.

I think what I would need out of jOOQ ResultSet mapping  are the ability to map children from the result set automagically.

I did this before with result set column "dotted" naming conventions and the Jackson ObjectMapper.

I was contemplating generating constants from the java beans to make this column labels easier ie ( table1.col1 AS "parentBean.childBean.childProperty").

I guess what I'm saying (and I think Lukas is also) is jOOQ does not need its SQL generation changed. 

We just need better ResultSet mapping and maybe some utility libraries to help generate the ManyToOne SQL (like the constants mentioned above).

Right now its rather difficult to load objects with ManyToOne (ie the table has column with ID referencing another table) with jOOQ.

I propose a jOOQ extension (that I already wrote for Spring as a RowMapper) that will translate the ResultSet's to a hierarchy of Maps.

BUT I don't see where I can inject custom data conversion (ie Calendar to Timestamp) etc.

I think loading ManyToOne paths would fit most peoples needs considering most people with modern Web Apps are returning JSON which is just a hierarchy of maps (except for the OneToMany lists). This could be done outside the query generation and in the result set handling.

[Lukas Eder]

> I think what I would need out of jOOQ ResultSet mapping are the ability to
> map children from the result set automagically.

If anything is missing right now, you can always write your own.
Either, you transform the org.jooq.Result (which is nothing but a
List<Record>) to your own custom type, or you can use jOOQ's
RecordHandler, which works much like Spring's RowMapper:
http://www.jooq.org/doc/2.5/manual/sql-execution/fetching/recordhandler/

> I did this before with result set column "dotted" naming conventions and the
> Jackson ObjectMapper.
> I was contemplating generating constants from the java beans to make this
> column labels easier ie ( table1.col1 AS
> "parentBean.childBean.childProperty").

That sounds like a nice idea if you generate SQL from the POJO
classes. I'm guessing that you somehow know when to "start a new
parentBean" as opposed to when to "reuse an existing parentBean" and
append to its childBean list?

> I guess what I'm saying (and I think Lukas is also) is jOOQ does not need
> its SQL generation changed.
> We just need better ResultSet mapping and maybe some utility libraries to
> help generate the ManyToOne SQL (like the constants mentioned above).

Precisely

> I propose a jOOQ extension (that I already wrote for Spring as a RowMapper)
> that will translate the ResultSet's to a hierarchy of Maps.

Yes, as soon as these data hierarchies are possible for jOOQ to
handle, the existing Result.intoMap() and Result.intoGroups() methods
would need to be adapted (the latter is being implemented for jOOQ
2.6.0)

> BUT I don't see where I can inject custom data conversion (ie Calendar to
> Timestamp) etc.

This is documented here:
http://www.jooq.org/doc/2.5/manual/sql-execution/fetching/data-type-conversion/

And here:
http://www.jooq.org/doc/2.5/manual/code-generation/custom-data-types/

> I think loading ManyToOne paths would fit most peoples needs considering
> most people with modern Web Apps are returning JSON which is just a
> hierarchy of maps (except for the OneToMany lists). This could be done
> outside the query generation and in the result set handling.

That is an interesting thought. In fact, jOOQ's Result.formatJSON()
and Result.formatXML() methods should be adapted accordingly, once
arbitrary "grouping" of Result objects is implemented. I wish Java 8
was available already. These transformations could be expressed quite
neatly with lambda expressions...

Cheers
Lukas

[Adam Gent]

On Sunday, September 23, 2012 5:45:48 AM UTC-4, Lukas Eder wrote:

> I think what I would need out of jOOQ ResultSet mapping  are the ability to
> map children from the result set automagically.

If anything is missing right now, you can always write your own.
Either, you transform the org.jooq.Result (which is nothing but a
List<Record>) to your own custom type, or you can use jOOQ's
RecordHandler, which works much like Spring's RowMapper:
http://www.jooq.org/doc/2.5/manual/sql-execution/fetching/recordhandler/

The problem with transform the Record is the annoying boilerplate and loss of fluent style. I also just don't like having Record (as an import) all over the place. 

I feel like jOOQ's SQL building should be decoupled from its fetching operations. I have do this in my own library like:

SqlObjectDao<ParentBean> parentDao = new SqlObjectDao<ParentBean>(mock, ParentBean.class);

// Assume Parent bean is a immutable with a ManyToOne to child bean called TestBean

// TestBean has two properties stringProp and longProp JPA annotated.

parentDao.select() // RootClause<SomeFetcher<ParentBean>>

    .where()

    .property("test.stringProp").eq("Hello")

    .orProperty("test.longProp").eq(1L)

    .query() // Here is where The hand off to the "fetcher" happens ie we are now SomeFetcher<ParentBean>

    .forList();

verify(mock).query(

        "SELECT parent_bean.id, " +

        "_test.string_prop AS \"test.stringProp\", " +

        "_test.long_prop AS \"test.longProp\", " +

        "_test.timets AS \"test.timeTS\" " +

        "FROM parent_bean " +

        "INNER JOIN test_bean _test ON _test.string_prop = parent_bean.test " +

        "WHERE _test.string_prop = ? OR _test.long_prop = ?"

        , new Object[] {"Hello", 1L}, parentDao.getObjectRowMapper());

I guess what I'm saying is that (IMHO) it seems that the *Step interfaces in jOOQ should be parameterized with a fetcher ie

SelectWhereStep<T>.

Thus "public interface Select<R extends Record> extends ResultQuery<R>, TableLike<R>, FieldLike, FieldProvider "

I think would be less bloated and better serving it was:

public interface Select<I> extends TableLike<R>, FieldLike, FieldProvider {

    public I query(); // This is where you could hand off to the result query.

    

    public interface SelectHandoff<K extends Select<I>, I> {

        I Handoff(K clause);

    }

// etc

}

[Lukas Eder]

I must say, I'm a bit confused by this e-mail :-)
Let's go through this e-mail step-by-step

> The problem with transform the Record is the annoying boilerplate and loss
> of fluent style.

Yes, unfortunately, Java 8's lambda expressions aren't available yet.
Note, though, that jOOQ 2.6.0 will also have a RecordMapper, in
addition to the RecordHandler, for a "little" more fluency:
https://github.com/jOOQ/jOOQ/issues/1756

> I also just don't like having Record (as an import) all
> over the place.

It is up to you how far you want to let Record leak into your
application. You can immediately transform it into anything else
before letting it leave a DAO

> I feel like jOOQ's SQL building should be decoupled from its fetching
> operations. I have do this in my own library like:

But it is decoupled, isn't it? How are the two parts entangled?

> parentDao.select() // RootClause<SomeFetcher<ParentBean>>
> .where()
> .property("test.stringProp").eq("Hello")
> .orProperty("test.longProp").eq(1L)
> .query() // Here is where The hand off to the "fetcher" happens ie we
> are now SomeFetcher<ParentBean>
> .forList();

This means that the SQL building API would make a lot of assumptions
about how fetched Records would later be mapped onto POJO types. In my
opinion, this would couple fetching with SQL building more closely.
Additionally, I think it is hard to get the property() and
orProperty() "selectors" right, as the ParentBean.class would need to
be introspected in order to generate correct SQL, just like in HQL. In
fact, why not just use HQL (or CriteriaQuery?).

Please note that jOOQ is about the "database-first" way of thinking.
Mapping *tables* to beans / POJOs should be a secondary concern, the
SQL query and the tables are the primary concern. If you wish to go
the other way round, you probably shouldn't use jOOQ. Of course, the
jOOQ way of thinking doesn't exclude mapping tables onto beans, but
only once the tables are fetched.

> I guess what I'm saying is that (IMHO) it seems that the *Step interfaces in
> jOOQ should be parameterized with a fetcher ie

> SelectWhereStep<T>. [...]

From what you provided, I'm not sure where this is supposed to be
going. Could you show me a working API and call-site example
illustrating the usefulness of adding such a parameter to all the
*Step interfaces?

But please note that the various fetchXXX() methods in ResultQuery are
just convenience for their corresponding counterparts in
org.jooq.Result. Maybe that was the source of confusion?

Cheers
Lukas

[Adam Gent]

On Fri, Sep 28, 2012 at 1:28 PM, Lukas Eder <lukas...@gmail.com> wrote:

I must say, I'm a bit confused by this e-mail :-)
Let's go through this e-mail step-by-step

> The problem with transform the Record is the annoying boilerplate and loss
> of fluent style.

Yes, unfortunately, Java 8's lambda expressions aren't available yet.
Note, though, that jOOQ 2.6.0 will also have a RecordMapper, in
addition to the RecordHandler, for a "little" more fluency:
https://github.com/jOOQ/jOOQ/issues/1756

> I also just don't like having Record (as an import) all
> over the place.

It is up to you how far you want to let Record leak into your
application. You can immediately transform it into anything else
before letting it leave a DAO

Fair enough.

 

But it is decoupled, isn't it? How are the two parts entangled?

Because the base interface (Select) has like 47 methods on that cannot be replaced or implemented.

Those methods have to do with fetching.

For example changing how ResultQuery does "intoPOJO" requires quite a bit of work (subclassing) all those steps.

I guess what I'm saying is that I cannot change how ResultQuery works and its included in the SQL building process.

So what I mean is Select<ResultQuery> s = should build me a select that will hand off to ResultQuery;

ResultQuery r = s.buildSomeSql().handoff() 

Now I have  ResultQuery object that I can fetch stuff or I can plug my own ResultQuery object that may not even be a ResultQuery.

 

This means that the SQL building API would make a lot of assumptions
about how fetched Records would later be mapped onto POJO types. In my
opinion, this would couple fetching with SQL building more closely.
Additionally, I think it is hard to get the property() and
orProperty() "selectors" right, as the ParentBean.class would need to
be introspected in order to generate correct SQL, just like in HQL. In
fact, why not just use HQL (or CriteriaQuery?).

Ignore my example. It was a horrible example which confuses the point I am making.

What I would prefer jOOQ did is help me compose or build SQL statements that I can than pass around.

My example was trying to show how I build a SQL AST (abstract syntax tree) I then pass that AST off to the parameterized contained fetcher.

The fetcher/executor than uses the visitor pattern to turn that SQL into real SQL and then execute.

I know that SQL composition is not the intent of the library... It was merely an idea. 

But the handoff idea I think could be valid where you hand off the predetermined SQL to the next phase (ResultQuery).

Please note that jOOQ is about the "database-first" way of thinking.
Mapping *tables* to beans / POJOs should be a secondary concern, the
SQL query and the tables are the primary concern. If you wish to go
the other way round, you probably shouldn't use jOOQ. Of course, the

Actually thats exactly the way I want to go... I want you to give me some nice valid SQL but I execute and do the POJO mapping myself.

 

jOOQ way of thinking doesn't exclude mapping tables onto beans, but
only once the tables are fetched.

And yeah I can do the POJO mapping sort of myself but its not elegant, and breaks the fluent chain.

I could write a custom Record Handler but how can I elegantly plug that in for all my factories?

So while:

https://github.com/jOOQ/jOOQ/issues/1756 is  nice I still have to pass in the handler all over the place.

Hopefully some time next week I can put up my little library so that you can see the SQL AST handoff I'm talking about.

 

Cheers
Lukas

Cheers

-Adam

[Lukas Eder]

> For example changing how ResultQuery does "intoPOJO" requires quite a bit of
> work (subclassing) all those steps.
>
> I guess what I'm saying is that I cannot change how ResultQuery works and
> its included in the SQL building process.
>
> So what I mean is Select<ResultQuery> s = should build me a select that will
> hand off to ResultQuery;

OK, I understand now. Well, I'm not sure if such a "fetcher" type
should be part of the DSL API for these reasons:
1. The separation I've mentioned would be breached
2. This would be a major incompatible change
3. The importance of the change would be in no proportion to the added value

Anyway, jOOQ has frequent releases with new fetch methods in every
release. I guess that waiting for the jOOQ 2.6.0 RecordMapper will be
your best option here:
https://github.com/jOOQ/jOOQ/issues/1756

An example:
-----------------------------------------
public class MyMapper<E> implements RecordMapper<Record, E> {
final Class<E> type;
public MyMapper(Class<E> type) {
this.type = type;
}
@Override
public E map(Record record) {
return myOwnPOJOMapping(record);
}
}

// And then
List<BaseBean> result =
create.select()
.from(MY_TABLE)
.where(...)
.fetch(new MyMapper<>(BaseBean.class));
-----------------------------------------

That should probably be fluent enough for you...? In the mean time,
try using the RecordHandler, which is a bit less "fluent"

-----------------------------------------
public class MyHandler<E, R extends Record> implements RecordHandler<R> {
final Class<E> type;
final List<E> result;
public MyHandler(Class<E> type) {
this.type = type;
this.result = new ArrayList<>();
}
@Override
public void next(Record record) {
result.add(myOwnPOJOMapping(record));
}
public List<E> getMyResult() {
return result;
}
}

// And then
List<BaseBean> result =
create.select()
.from(MY_TABLE)
.where(...)
.fetchInto(new MyHandler<BaseBean, MyTableRecord>(BaseBean.class))
.getMyResult();
-----------------------------------------

> What I would prefer jOOQ did is help me compose or build SQL statements that
> I can than pass around.
> My example was trying to show how I build a SQL AST (abstract syntax tree) I
> then pass that AST off to the parameterized contained fetcher.
> The fetcher/executor than uses the visitor pattern to turn that SQL into
> real SQL and then execute.

Introducing such features in jOOQ is out of scope for 3.0, as the
roadmap is already quite full.

> Actually thats exactly the way I want to go... I want you to give me some
> nice valid SQL but I execute and do the POJO mapping myself.

This: "but I execute [...] myself", is news to me... How would you
want to do your own custom execution, while at the same time remain
within jOOQ's fluent API?

> And yeah I can do the POJO mapping sort of myself but its not elegant, and
> breaks the fluent chain.

I just think you want too much from the jOOQ API. :-) Instead you
should implement some of the "missing" things at the call-site

> I could write a custom Record Handler but how can I elegantly plug that in
> for all my factories?

By passing it manually (see the example above). Or write your own Executor:

-----------------------------------------
public class Executor<E, R extends Record> {
final Class<E> type;
public Executor(Class<E> type) {
this.type = type;
}
public List<E> fetch(ResultQuery<R> query) {
query.fetchInto(new MyHandler<E, R>(type)).getMyResults();
}
}
-----------------------------------------

> So while:
> https://github.com/jOOQ/jOOQ/issues/1756 is nice I still have to pass in
> the handler all over the place.

Yes. You will have to understand, that I cannot add all possible types
to the API. Other users may want <E>, <T> (no pun intended), etc. If
everything goes into the fluent API, we'll end up with

Select<I, H, E, T, R extends Record, T2 extends Table<R>>

To wrap things up: The "fetching mode" should not be a first-class
citizen in the DSL API, as the DSL API is about SQL building, not
about fetching.

Cheers
Lukas