Dear group, One of the biggest improvements in jOOQ 3.0 is the introduction of row value expressions, which have already been added in an experimental way in jOOQ 2.6. Essentially, these improvements involve new types, such as Row[N]:
http://www.jooq.org/javadoc/latest/org/jooq/Row1.html http://www.jooq.org/javadoc/latest/org/jooq/Row2.html http://www.jooq.org/javadoc/latest/org/jooq/Row3.html etc... And in jOOQ 3.0 also Record[N]: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/Record1.java https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/Record2.java https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/Record3.java etc... While Row[N] is used for constructing actual SQL row value expressions, Record[N] is used to formally specify the arity/degree and concrete type of every record and thus, of every record-dependent object, such as Result<R>, Select<R> and many others. Generated records will implement appropriate Record[N] types, too. Note that Record[N] always extends the existing and more general Record type for backwards-compatibility. Here are some use-cases where these things are useful: ==================================== 1. Row value expression support: In SQL, you can write SELECT * FROM t WHERE (t.a, t.b) = (1, 2) SELECT * FROM t WHERE (t.a, t.b) OVERLAPS (date1, date2) SELECT * FROM t WHERE (t.a, t.b) IN (SELECT x, y FROM t2) UPDATE t SET (a, b) = (SELECT x, y FROM t2 WHERE ...) In jOOQ, you can then (typesafely!) write select().from(t).where(row(t.a, t.b).eq(1, 2)); select().from(t).where(row(t.a, t.b).overlaps(date1, date2)); select().from(t).where(row(t.a, t.b).in(select(t2.x, t2.y).from(t2))); update(t).set(row(t.a, t.b), select(t2.x, t2.y).where(...)); All subselects are type-checked for their degree and each element's type. ------------------------------------------------------------------------ 2. Degree 1 row value expressions Some operators and predicates expect subqueries to have a degree of exactly 1. For instance the comparison and IN predicates usually operate only on a single field. This means that the following expressions can now be compile-time checked, for typesafety: select().from(t).where(t.a.eq(select(t2.x).from(t2)); select().from(t).where(t.a.in(select(t2.x).from(t2)); It is not possible to select t2.x if t2.x's type differs from that of t.a. Also, it is not possible to select more than one field in the subselect. ------------------------------------------------------------------------ 3. Improved type-safety for unions The following will be checked by the Java compiler select(t1.a, t1.b).from(t1).union( select(t2.a, t2.b).from(t2)); t1.a and t2.a need to be of the same type, just as t1.b and t2.b need to be of the same type. The two subselects have to be of the same degree ------------------------------------------------------------------------ 4. Better interoperability with languages like Scala Scala already has tuples. jOOQ's Record2 can easily be converted into Scala's Tuple2 using implicit defs. For instance: val (a, b) = create.select(t1.a, t1.b).from(t1).fetchOne(); a and b will automatically inherit the T type in t1.a and t1.b's Field<T> declarations. ==================================== While this looks nice at first sight, it also has some drawbacks / tradeoffs: ==================================== A. There is a limited number of supported degrees I'm using Xtend to generate API source code for degrees between 1 and 22. I chose 22 as this is the highest degree currently supported by the Scala language - for some odd reason. C# and other .NET languages know degrees between 1 and 8. in jOOQ, degrees of 23 and more will be modelled using today's Row and Record types, without any degree suffix, and without the newly introduced type-safety ------------------------------------------------------------------------ B. SelectXXXStep now needs a generic <R> type parameter Today's SELECT DSL API has no generics, as it selects only org.jooq.Record. This needs to be changed, in order to create type-safe SelectXXXStep<Record2<Integer, String>> and similar types. Adding generics to this API might feel a bit clumsy to those of you who use the DSL API for dynamic SQL construction. On the other hand, the SimpleSelectXXXStep DSL API also has an <R> type parameter ------------------------------------------------------------------------ C. Record22<T1, T2, T3, ..., T22> is very verbose As Java's type inference capabilities aren't as strong as those of Scala and other languages, assigning things may become very verbose, too. Imagine: Result<Record22<Integer, Date, String, ..., Integer>> result = create.select(...).fetch(); ==================================== For those of you who are still reading... :-) This is a good time in the jOOQ 3.0 project, to interact and influence the developments. Tell me how you feel about these upgrades, about the added generics, about the type-safety, etc. etc. Note, that on github master, Record[N] support in Factory.select() has been added, not yet in Executor.select(). As a reminder, the jOOQ 3.0 Factory is used for static SQL construction only ("unattached" QueryParts), whereas Executor is used for constructing executable ("attached") queries, that can be executed using today's various fetch() and execute() methods. In a way, the static Factory.select() is specifically useful for subqueries. Cheers and thanks in advance for your feedback! Lukas
