QueryDSL vs jOOQ
Lukas Eder
I have seen that you are following me on twitter. Don't expect too much info there, as I'm hardly using it :-)
I have noticed your library QueryDSL some time ago. It looks like an excellent approach of "simulating" LINQ behaviour in Java. Surely one of the best implementations I have seen in the field!
With jOOQ I am trying to go a slightly different path. The DSL is only a side-effect of my main goals: To provide standardisation and ease of access to many vendor-specific SQL features, such as stored procedures, UDTs etc. Also, I want to be able to express the full SQL feature set, including all types of complex query constructs, such as UNION's, nested selects, aliasing, etc.
For these reasons, integration with JPA/Hibernate, etc is not a primary goal, as these abstraction layers completely hide / ignore advanced SQL constructs, for reasons of simplicity and because they're true OR-Mappers, unlike jOOQ. Nevertheless, supporting JPA2/EJB3.0 will be a nice-to-have for jOOQ as well, in the near future. If you agree, I would like to evaluate whether QueryDSL and its JPAQuery might be a good match for such an implementation!
Cheers
Lukas
Timo Westkämper
> Hi Timo!
>
> I have seen that you are following me on twitter. Don't expect too
> much info there, as I'm hardly using it :-)
No problem ;)
>
> I have noticed your library QueryDSL some time ago. It looks like an
> excellent approach of "simulating" LINQ behaviour in Java. Surely one
> of the best implementations I have seen in the field!
Thanks.
>
> With jOOQ I am trying to go a slightly different path. The DSL is only
> a side-effect of my main goals: To provide standardisation and ease of
> access to many vendor-specific SQL features, such as stored
> procedures, UDTs etc. Also, I want to be able to express the full SQL
> feature set, including all types of complex query constructs, such as
> UNION's, nested selects, aliasing, etc.
Querydsl SQL has a similar goal. We also try to support SQL in full form
with engine specific extensions. UNION support, nested queries and
aliasing included.
Support for stored procedures is on the roadmap.
>
> For these reasons, integration with JPA/Hibernate, etc is not a
> primary goal, as these abstraction layers completely hide / ignore
> advanced SQL constructs, for reasons of simplicity and because they're
> true OR-Mappers, unlike jOOQ. Nevertheless, supporting JPA2/EJB3.0
> will be a nice-to-have for jOOQ as well, in the near future. If you
> agree, I would like to evaluate whether QueryDSL and its JPAQuery
> might be a good match for such an implementation!
Querydsl has multiple modules
JPA - for JPA based backends using annotated domain classes for class
generation
JDO - for JDO backends
Lucene - for Lucene
Mongodb - for Mongodb via Morphia
Collections - for collections
SQL - for JDBC based backends (comparable to jOOQ)
IMHO Querydsl SQL has really similar goals like jOOQ. We started with
the JPA module for a JPA 2 Criteria replacement, but we are using
Querydsl SQL also in production systems, where full-blown ORM is not the
right tool.
The general approach of jOOQ feels quite good, but I believe that your
usage of a static metamodel results in verbose alias constructs.
Querydsl uses dynamic typed paths for everything, which is conceptually
simpler and I believe in the end also syntactically lighter.
Here is an alias example in jOOQ :
// Create table aliases
Table<TTreeRecord> parent= TTree.T_TREE.as("parent");
Table<TTreeRecord> child= TTree.T_TREE.as("child");
// Create field aliases from aliased table
Field<String> parentName= parent.getField(TTree.NAME).as("parent_name");
Field<String> childName= child.getField(TTree.NAME).as("child_name");
// Execute the above select
Record record= create.select(parentName, childName)
.from(parent)
.join(child).on(parent.getField(TTree.ID).equal(child.getField(TTree.PARENT_ID)))
.fetchAny();
In Querydsl it would be something like this
QTree parent = new QTree("parent");
QTree child = new QTree("child");
Tuple record = query()
.from(parent)
.innerJoin(child).on(parent.id.eq(child.parentId))
.uniqueResult(new QTuple(parent.name, child.name));
String parentName = record.get(parent.name);
String childName = record.get(child.name);
Just an example that Querydsl is not only about JPA ;) Anyway it's great
to have new players in the field.
Querydsl supports also DML clauses like jOOQ and has optional
lightweight ORM/Bean projection extensions for Querydsl SQL.
Cheers
Timo
P.S. Deutsch ist meine Muttersprache, also wir k�nnen auch auf Deutsch
schreiben.
>
> Cheers
> Lukas
Lukas Eder
P.S. Deutsch ist meine Muttersprache, also wir können auch auf Deutsch schreiben.
Ah, I was mislead by your living in Finland. We could, but this comparison is interesting for a greater audience, so I prefer English... :-)
Querydsl SQL has a similar goal. We also try to support SQL in full form with engine specific extensions. UNION support, nested queries and aliasing included.
Support for stored procedures is on the roadmap.
Feel free to get inspiration from jOOQ. We're quickly advancing in that field.
The general approach of jOOQ feels quite good, but I believe that your usage of a static metamodel results in verbose alias constructs. Querydsl uses [...]
You are right about that. This is one of my major concerns with jOOQ. Your approach is clearly easier to use. I am planning to improve my implementation by generating methods rather than static fields. It won't be for the next release, though.
Thanks for your feedback!
I'm sure we'll see each other around, in the future ;-)
Cheers
Lukas
timowest
I could try to translate your jOOQ example queries into Querydsl SQL
queries for a better comparison.
The serialization of Querydsl SQL is quite extensible and we have
tried various SQL extensions of MySQL, Oracle and SQL Server with
success.
I think there are lots of opportunities for cooperation. I for sure
will study the jOOQ code when implementing stored procedure support.
Br,
Timo Westkämper
Lukas Eder
I have given this another thought:
The general approach of jOOQ feels quite good, but I believe that your usage of a static metamodel results in verbose alias constructs. Querydsl uses dynamic typed paths for everything, which is conceptually simpler and I believe in the end also syntactically lighter.
The reason I originally wanted a static meta model is to enable the use of static imports. That's a minor but effective advantage for the "static" approach, since you mention verbosity. Once that was established, I have built aliasing around tables using the aforementioned getField(Field<?>) methods. In my design, I wanted to absolutely prevent users from instanciating objects at any time using the new keyword. This is for two reasons:
- I have full control over the DSL and all objects jOOQ provides. This doesn't mean, that custom objects are impossible. Every public interface (Condition, Field, etc) can be implemented by client code if used appropriately.
- I suppose it will be easier to change jOOQ without breaking anything, as more implementation logic is hidden.
To get back to aliasing: This design was necessary to allow for aliasing inner selects (or similar constructs) such as:
SELECT a.x, a.z FROM (SELECT t.x, f(t.y) z FROM t) a
Which will be translated into something like (might not be entirely correct, syntactically):
Field<?> z = f(T.Y).as("z"); // f() in this case is a generated stored function...
Table<?> a = create().select(T.X, z).from(T).asTable("a");
create().select(a.getField(T.X), a.getField(z)).from(a);
Of course the example makes little sense, it should just illustrate a generic problem. How have you solved this aliasing a query and selecting from it with QueryDSL? The problem here is that a.x, a.z cannot be known in advance in any meta model. I have given quite some thought to these things, and unfortunately, I couldn't come up with a less verbose solution...
To decrease verbosity of my aliasing physical tables, however, I will probably opt for generated member methods X() and Y() in the generated table class T. These methods will invoke getField(X) or getField(Y) on the table. The as(String) method on those tables will then return an object of the same type as the table. Let's see how this continues!
Cheers
Lukas
timowest
SELECT a.x, a.z FROM (SELECT t.x, f(t.y) z FROM t) a
QTable t = QTable.table; // statically typed table reference
PathBuilder a = new PathBuilder(Object.class, "a"); // dynamic path
query().from(
subquery().from(t).list(t.x, f(t.y).as("z")).as(a) )
.list(a.get("x"), a.get("z")
Subquery aliasing makes the query structure quite dynamic, but this is
also easily supported by Querydsl.
On Jan 5, 10:51 am, Lukas Eder <lukas.e...@gmail.com> wrote:
> Hi Timo,
>
> I have given this another thought:
>
> > The general approach of jOOQ feels quite good, but I believe that your
> > usage of a static metamodel results in verbose alias constructs. Querydsl
> > uses dynamic typed paths for everything, which is conceptually simpler and I
> > believe in the end also syntactically lighter.
>
> The reason I originally wanted a static meta model is to enable the use of
> static imports. That's a minor but effective advantage for the "static"
> approach, since you mention verbosity. Once that was established, I have
> built aliasing around tables using the aforementioned
> prevent users from
> instanciating objects at any time using the *new *keyword. This is for two
> reasons:
>
> 1. I have full control over the DSL and all objects jOOQ provides. This
> appropriately.
> 2. I suppose it will be easier to change jOOQ without breaking anything,
>
> But maybe these two ideas are not as relevant as I imagined...
>
> To get back to aliasing: This design was necessary to allow for aliasing
> inner selects (or similar constructs) such as:
>
> SELECT a.x, a.z FROM (SELECT t.x, f(t.y) z FROM t) a
>
>
>
> Which will be translated into something like (might not be entirely correct,
> syntactically):
>
> > Field<?> z = f(T.Y).as("z"); // f() in this case is a generated stored
> > function...
> > Table<?> a = create().select(T.X, z).from(T).asTable("a");
>
> create().select(a.getField(T.X), a.getField(z)).from(a);
>
>
>
> Of course the example makes little sense, it should just illustrate a
> generic problem. How have you solved this aliasing a query and selecting
Lukas Eder
No worries about the follow-up. At first I thought, it was because Querydsl can't do it :-)
Great, though. So there really aren't too many fundamental differences between jOOQ and Querydsl for SQL. Congrats to that, I didn't think that it would be easy to handle several backends as different as JPA and SQL in the same API.
How is Querydsl maintained? Is that something you guys at Mysema developed for yourselves and then decided to go public and open source with it? Are you providing consulting for it? Or are there any other means of how you earn money with it? How do you decide on the roadmap and priorities (e.g. you mentioned stored procedure support earlier). I'm wondering because if there are so many things in common, we might be able cooperate, instead of creating two times the same...
My focus currently is on implementing full data type support, such as UDT's, arrays, multi-dimensional arrays, arrays of UDT's, arrays of UDT's as stored procedure parameters, etc, etc. There is a lot of work involved in that, as JDBC does not go very far in standardising these things, which is the main reason - in my opinion - that these strong concepts are used so scarcely in the Java world. Is that something Querydsl might be interested to focus on as well?
Cheers
Lukas