jOOQ vs. Apache Empire-DB

[Venkat Sadasivam]

Just curious to know, what would be the advantage of jOOQ over Apache Empire-DB?

[Lukas Eder]

Hi Venkat,

Good thing you asked, although my answer will be biased of course :-)

So I invite other members of this user group to join the discussion and to share their views!

Vision:

Many query object models (QOM) have surfaced in the past 10 years, all with a similar ideas and features.

jOOQ aims to model SQL as a domain-specific language in Java. In that sense, it is very unique. The things currently being discussed on this user group are very novel and innovative:

https://groups.google.com/forum/#!topic/jooq-user/mLcxnwvglf0

My vision is to eventually be able to phrase JSRs that allow for arbitrary external DSL to Java / Scala / Groovy / etc. mappings, making SQL-to-Java a show case of a broader technique. I think that the only really useful Java / SQL contribution to the Java ecosystem is one that really embeds SQL into the Java language. And jOOQ will be the leader of this vision.

Having a QOM to back these things is nice. But the QOM itself is not something very innovative. Here are some other QOM implementations (some of which with a DSL-y touch):

- http://empire-db.apache.org/empiredb

- http://quaere.codehaus.org

- http://www.h2database.com/html/jaqu.html

- http://ujorm.org

- http://www.simpleorm.org

- http://xircles.codehaus.org/projects/jequel

- http://java.net/projects/squill

- http://code.google.com/p/squiggle-sql

Non-SQL QOMs:

- http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaQuery.html

- http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html

- http://www.day.com/specs/jcr/2.0/6_Query.html

- http://docs.mongodb.org/ecosystem/tutorial/getting-started-with-java-driver/

Feature scope:

I have analysed a lot of existing Java / SQL libraries before I started implementing jOOQ, to be sure I won't be reimplementing the wheel and to be sure I will be adding real value. I have found none of them to implement so many SQL function and clause transformations and standardisations as jOOQ does. A couple of examples can be found here on my blog:

- http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions

- http://blog.jooq.org/2013/01/07/simulating-the-sql-standard

- http://blog.jooq.org/2012/12/26/row-value-expressions-and-the-between-predicate

- http://blog.jooq.org/2012/12/24/row-value-expressions-and-the-null-predicate

- http://blog.jooq.org/2012/09/21/the-is-distinct-from-predicate

- http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite

The feature scope matches jOOQ's vision. jOOQ is a DSL, i.e. a language. As a language, it models an AST. This AST is transformed by jOOQ itself, depending on your Configuration and Settings (mostly your SQL dialect). For example, the simulation of derived column lists, e.g. 

SELECT t.a, t.b

FROM (

  SELECT 1, 2 -- Or any table expression

) t(a, b) -- Derived column list: t(a, b)

Which is equivalent to this statement that can do without derived column lists

SELECT t.a, t.b

FROM (

  SELECT null a, null b WHERE 1 = 0

  UNION ALL

  SELECT 1, 2 -- Or any table expression

) t

The above is hidden transparently behind the Table.as(String, String...) method:

- http://www.jooq.org/javadoc/latest/org/jooq/Table.html#as(java.lang.String, java.lang.String...)

Empire-DB's feature scope seems a bit more limited, at least from what I see in their documentation. There are only hints about support for UNIONs, IN predicates, EXISTS predicates, row value expressions, and many more. There is a lot less typesafety in general, and a less fluent API. There is less support for vendor-specific SQL and I think that DML is not supported at all? What about procedures, I couldn't find any references?

On the other hand, Empire-DB has some support for DDL, which jOOQ hasn't...

Community and traction:

EmpireDB has quite a few committers but rather little traction in general. If you compare its visibility

On Stack Overflow: 5 hits (empire db) vs 334 hits (jOOQ)

- http://stackoverflow.com/search?q=empiredb

- http://stackoverflow.com/search?q=jooq

On mailing lists: around 500 (empire db user+dev) vs 3000 (jOOQ user)

- http://markmail.org/search/?q=jooq-user

- http://markmail.org/search/?q=empiredb

On ticketing systems: around 200 (empire db) vs 2600 (jOOQ)

- http://issues.apache.org/jira/issues/?jql=project=EMPIREDB

- http://github.com/jOOQ/jOOQ/issues

Conclusion:

I might not have really compared the two libraries in particular, but outlined how jOOQ is different and novel compared to *all* pre-existing approaches. In my opinion, you could have picked any other QOM, e.g. Ujorm or one that wasn't on my list, and I would have come to the same conclusion. In fact, I don't see a decisive long-term advantage of choosing Empire-DB over Ujorm.

I think that the market is ripe for jOOQ's visions. I think that SQL is a very powerful and expressive language, and it should be possible for a Java developer to write SQL itself directly in Java.

Hope this helps :-)

2013/6/30 Venkat Sadasivam <venka...@gmail.com>

Just curious to know, what would be the advantage of jOOQ over Apache Empire-DB?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Roger Thomas]

When looking for a solution I first found Empire-DB, but what caused me to look for an alternative (hence why I'm around here) are

   1) I found the documentation was more aimed at people who already understood it, while jOOQ's provided a far better entry in its environment.

   2) Empire-DB does not have a code generator (and I hate doing boiler plate coding). jOOQ's generater is a great solution for ad-hoc development cycles as well as larger formal projects.
       The fact that once you have the XML configured for the generater you can write code as shown here   

                                http://www.jooq.org/doc/3.0/manual/getting-started/tutorials/jooq-in-7-steps/jooq-in-7-steps-step6/

      No maintaining XML files or java classes to map the schema before it can be used.

   3) jOOQ is better at managing cross target database coding as it tries to fill in the gaps between different SQL implementations.

   4) Empire-DB does not try to emulate all of the SQL syntax so you have to understand a different syntax for say UPDATE, while jOOQ tries
       its best to map SQL syntax into the Java environment. (I have a very strong SQL history so like to scan SQL 'like' syntax).


While saying all of the above I do think that the main thing that currently gives jOOQ the advantage is the drive shown by Lukas. Fixes, updates and general communications
always makes or breaks an open source project and Lukas's commitment is A+. The differance in part I think is due to the fact that Lukas has a lot of future plans, while the team
looking after Empire-DB are more focussed on maintaining a current product than adding any major changes.

Roger

[Lukas Eder]

Thanks for your nice contribution Roger!

2013/6/30 Roger Thomas <rithom...@gmail.com>

Yes, my commitment is strong. In fact, I'm currently evaluating the "next steps" of professional OSS development, thinking about how to provide services such as:

- basic professional support

- premium professional support (later on)

- custom engineering

- on-site trainings

- "shared" trainings, such as the upcoming one in Zurich in September (in German):

http://www.ch-open.ch/wstage/workshop-tage/2013/aktuelles-programm-2013/ws-6-mit-jooq-und-opensource-datenbanken-sofort-produktiv-werden/

And much more. If you like it or not, you're going to hear 1-2 things about jOOQ in the near future :-) 

My goal is that within 3-4 years, Java / SQL developers will be asking themselves:

    Should I use JPA or jOOQ for the problem at hand?

- JPA will be the right choice for "top down", application-domain-driven engineering. 

- jOOQ will be the right choice for "bottom up", database-model-driven engineering.

I will announce all of this more officially, once I have settled for a service infrastructure and strategy.

Cheers

Lukas