cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lukas Eder <>
Subject Re: persistence layer
Date Thu, 28 Nov 2013 11:45:47 GMT
Hi Frank,

Thank you for your detailed feedback. This is very interesting insight for
me and for Data Geekery. Let me try to address your concerns by first
categorising them into the following items (please correct me if I might
have misunderstood).

- A concern about Spring support.
- A concern about standards (JPA)
- A concern about CRUD verbosity
- A concern about fluent APIs vs. SQL
- A concern about fluent APIs vs. custom query building APIs

Spring support
It is true that we cannot offer you a "formal" affiliation with Spring (or
rather with Oliver Gierke, the lead developer behind Spring Data). I have
been observing this collaboration between Spring Data and QueryDSL and I
feel that it is a strategic alliance that mutually benefits both vendors.
QueryDSL gets some more traction by being auto-shipped to a much broader
audience, whereas Spring Data gets a fluent API for both JPA and SQL.

>From our perspective, we want jOOQ to be entirely independent of any
transaction and connection pooling API as this facilitates integration with
JTA transaction modelling and other models. We believe that by keeping
things simple and by separating concerns, you will get a stack that is
better focused on your individual needs. In that way, we believe that you
should be able to combine Spring IOC with jOOQ just as much as with JEE

Most importantly, Spring Data, much like JDO in the past and like QueryDSL
today aim for integrating NoSQL databases into a unified API, which we find
a poor fit, as transaction models, data models, query models are
fundamentally different between these various data stores. We believe that
by pursuing such unification, frameworks are easily distracted from sound
SQL support. We highly value Spring's transaction model simplification and
this has worked well with jOOQ in the past.

Standards (JPA)
Now, one of your arguments in favour of JPA is the non-lock-in. Spring has
been challenging JEE standards for quite some time and there is no other
vendor in the market to replace Spring once you build upon Spring. Even
more so, by choosing a Spring+QueryDSL combination, you get an equally
strong vendor-lockin (or even a stronger one, as the stack gets more
complex) than when choosing Spring+jOOQ. If standards are important to you,
I suggest you only use JPA and Criteria Query (Note, that JEE also supports
IoC). And I'm sure that Adam Bien will agree with me on this :-)
But even then, you will have a hard time migrating from Hibernate to
EclipseLink or OpenJPA as the implementations are subtly different in many

The decision is up to you, and I fully understand these concerns. Standards
are a good thing because many disparate things from various vendors have
been unified and hopefully matured over time. But vendors also innovate and
thus deviate from the standard again. Without such deviation, there would
be no more progress, nor innovation in a standard. Take EclipseLink for
instance, a JPA implementation that has started to "flirt" with NoSQL

The above is orthogonal to JPA and will add lots of complexity to this
particular implementation. JDO was another standard that has attempted to
integrate other, non-relational data stores in a single API, yet it has not
succeeded, leaving a pretty much dead standard.

Now, while you are absolutely right by saying that you cannot switch the
implementation of the jOOQ API at the moment, we're striving to innovate on
the standards front as we see room for various JSRs in the area of RDBMS
meta modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
general, SQL DSL modelling in particular. We believe that jOOQ is the most
innovative platform for SQL to Java integration in the market at the moment

CRUD verbosity
> In this area, JPA does the right thing.
> Author author = new Author(...);
> JPAEntityManary.persist(author);
> The later one is more Java more OOP.

Compare this to jOOQ's CRUD features:
AuthorRecord author = ctx.newRecord(AUTHOR);;

Or alternatively:
AuthorDao dao = ...
dao.insert(new Author());

Relevant sections in the manual are:

Fluent APIs vs. SQL
I fully understand this concern. Using the jOOQ fluent API is not the same
as writing SQL directly. But let me remind you of the concerns you are
having with respect to database independence. By writing SQL directly
(through JDBC, through views or through stored procedures) you will not be
able to support dozens of databases very easily. Both JPA and jOOQ solve
this problem quite well. JPA abstracts SQL entirely, while jOOQ abstracts
the subtle differences, only.

Fluent APIs vs. custom query building APIs
In addition to jOOQ's fluent API, there is also a simpler, object-oriented
SelectQuery model:

The same applies for other types of statements (InsertQuery, UpdateQuery,
etc.). Thus, compare this fluent API usage:

> create.selectFrom(BOOK)
>       .where(BOOK.PUBLISHED_IN.eq(2011))
>       .orderBy(BOOK.TITLE)

With this SelectQuery usage:

SelectQuery<Book> select = create.selectQuery(BOOK);
Result<Book> result = select.fetch();

This is also documented here:

I hope I could put jOOQ in a different light to you. If JPA is sufficient
for your use-cases, then use JPA (or JPA+jOOQ). There are some problems
that jOOQ will never solve, which are solved very well in JPA. We have
outlined these differences here:

However, by using jOOQ, you not only gain a very high level of typesafety
and thus an increase of productivity through the usage of jOOQ's code
generator which helps you detect errors early, you also get access to a
very rich set of service provider interfaces, which were off-topic in this
discussion so far. Some examples:

ExecuteListeners for custom query execution lifecycle handling:

Schema and table mapping for multi-tenancy support:

Record mapping in order to provide custom mapping algorithms from jOOQ
records to your domain model:

A query transformation listener that is still being worked on, allowing for
arbitrary SQL transformation on the jOOQ AST. This can be very useful for
features like row-level security.

As I said, we believe that there is currently no other software in the Java
market that takes your SQL so seriously, and we're highly committed to
improve this even further in the near future.

Best Regards,

2013/11/27 Frank Zhang <>

> Hi Lukas:
>         Glad to see developer behind JOOQ here, I originally recommended
> using QueryDSL instead of JOOQ,
> there are some of my concerns:
> - Spring support. You know CloudStack is based on Spring IOC, the most
> compelling thing of Spring DB is its transaction
> management framework. QueryDSL has been integrated into Spring for two
> years. But there is only a simple page
> (
> demonstrating JOOQ and Spring,  however,
> the code just shows how to load JOOQ in IOC, it doesn't show any advanced
> feature of Spring DB for example declarative
> transaction management. If JOOQ is not compatible with Spring DB, then we
> loss a widely used feature.
> - It's not JPA based on. This might be an argument that JOOQ may claim as
> its biggest advantage. I agree JPA does have
> some bad thing(e.g. criteria API), but  the rest part is doing very well.
> The most important thing here is JPA is just API so I can choose
> underlying vendor by my preference. This non-lock-in feature is very
> appealing. For example, Hibernate has been proven by industry
> for 10 years, I can fairly trust its quality. And vendor may have some
> advanced feature like secondary cache I may need someday.
> Instead, using JOOQ means I have to rely on its underlying implementation
> with no choice.
> You know almost all JPA vendors support different SQL dialects. And fluent
> API(or DSL) is not attractive to me. Frankly speaking,
> IMO, for insert/update/delete clause the fluent API makes thing
> unnecessary complex that you are writing something which is even
> more complicated than SQL, for example in JOOQ,
> == persistence ===
> create.insertInto(AUTHOR)
>       .set(AUTHOR.ID, 100)
>       .set(AUTHOR.FIRST_NAME, "Hermann")
>       .set(AUTHOR.LAST_NAME, "Hesse")
>       .newRecord()
>       .set(AUTHOR.ID, 101)
>       .set(AUTHOR.FIRST_NAME, "Alfred")
>       .set(AUTHOR.LAST_NAME, "Döblin");
> In this area, JPA does the right thing.
> Author author = new Author(...);
> JPAEntityManary.persist(author);
> The later one is more Java more OOP.
> === single table query ====
> Speaking of query, for single table query, fluent API is not attractive
> too. For example:
> Raw SQL:
> create.selectFrom(BOOK)
>       .where(BOOK.PUBLISHED_IN.eq(2011))
>       .orderBy(BOOK.TITLE)
> CloudStack search criteria built on JPA:
> Qeury<Book> q = new Qeury<Book>();
> q.add(Book_.PublishedIn. Op.eq, 2011);
> q.orderBy(Book_.title);
> List<Book> books = q.list();
> The last way is more Java for sure, people who even has no SQL knowledge
> can write the search.
> === multiple tables query ===
> The only place fluent API shining is querying multiple tables:
>      AND BOOK.PUBLISHED > '2008-01-01'
>   HAVING COUNT(*) > 5
>    LIMIT 2
>   OFFSET 1
>, AUTHOR.LAST_NAME, count())
>       .from(AUTHOR)
>       .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
>       .where(BOOK.LANGUAGE.equal("DE"))
>       .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
>       .having(count().greaterThan(5))
>       .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
>       .limit(1)
>       .offset(2)
>       .forUpdate();
> JPA does very badly in this area. However, you notice you are actually
> translating raw SQL into JOOQ DSL. In fact
> writing such a query is harder in JOOQ than in raw SQL, because you must
> not only know SQL knowledge but also know
> JOOQ dialects.  The only gain is type-safe. I know Java developers are
> used to static type too much, they are not willing
> to write any code that cannot be checked by compiler. Type safe is the
> reason that no ORM is perfect, and it can never be.
> In summary, I don't think dropping in any ORM can solve problem perfectly,
> if I did it, I would do:
> - Using JPA based framework.
> - building persistent interface(create/update/delete) and single table
> search criteria using JPA
> - for multiple tables query, either using JPA JPQL(if you don't mind type
> safe) or dropping in some fluent API framework if you can not stand for any
> type unsafe thing.
> > -----Original Message-----
> > From: Lukas Eder []
> > Sent: Tuesday, November 26, 2013 3:58 AM
> > To:
> > Subject: Re: persistence layer
> >
> > Dear CloudStack developers,
> >
> > This thread has caught my attention and I thought I might chime in and
> give
> > you some background information about jOOQ, JDBC, RDBMS in general (I
> > work for Data Geekery, the company behind jOOQ). I've already had the
> > pleasure to talk to Darren on the jOOQ User Group and on the phone.
> >
> > Here are some answers to open questions I've seen in this thread:
> >
> > 2013/11/26 Alex Huang <>
> >
> > > Has anyone actually tried dropping in a different jdbc driver and see
> > > if CS can use another DB?  I don't think the current CS DB layer
> > > prevents anyone from doing that.
> > >
> > > This is different from MariaDB which, as othes have pointed out, is
> > > drop-in replacement for MySQL.  I'm talking about stuff like derby or
> > > sqlserver or oracle or db2.
> > >
> >
> > MariaDB and MySQL are diverging quickly, as Oracle invests quite some
> > development efforts into MySQL. While the drop-in replacement argument is
> > valid right now, it might not be two years from now.
> >
> > In general, SQL dialects are very different when it comes to the
> subtleties of
> > syntax or feature sets. Since you're comparing MySQL with Derby, SQL
> Server,
> > Oracle, or DB2, let me give you some ideas:
> >
> > - MySQL has a lot of vendor-specific clauses that no one else has (e.g.
> ON
> > - MySQL bends the SQL syntax and accepts what is otherwise illegal
> syntax (e.g.
> > GROUP BY semantics [1])
> > - MySQL doesn't correctly implement the SQL standard NOT IN semantics [2]
> > - Derby and DB2 (prior to version 9.7) are extremely type-safe. For
> instance,
> > CAST(NULL AS INT) and CAST(NULL AS VARCHAR) are two entirely different
> > things. This can lead to tedious work when binding null as a bind
> variable. Some
> > background info [3]
> > - DB2 has a lot of peculiarities when it comes to memory management of
> > VARCHAR [4]
> > - Case-sensitivity of schema, table, and column names is an eternal
> hassle
> > between RDBMS
> > - JDBC drivers implement things very differently in some areas. Fetching
> an
> > inserted ID is really a pain.
> >
> > [1]:
> > [2]:
> >
> > [3]:
> > [4]:
> >
> > If you want cross-database support, these things show that you should
> really
> > consider moving away from using plain JDBC and use an abstraction that
> will
> > take care of these incompatibilities for you. You might be choosing
> between
> > Hibernate and jOOQ (or both) depending on the way you plan to interact
> with
> > your RDBMS. Our point of view is illustrated here:
> >
> >
> > Some larger jOOQ customers currently use jOOQ with Oracle, Sybase, SQL
> > Server, MySQL, and H2 from the same application.
> >
> > > Frank Zhang Fri, 22 Nov 2013 10:42:09 -0800 I recommend QueryDSL.
> > > Having a quick look at JOOQ, it's very similar to QueryDSL.
> > > QueryDSL has been integrated into Spring for 2 years, and JOOQ haven't
> > had
> > > official doc for Spring.
> > > Besides Sql, QueryDSL also gets fair credit on manipulating NoSql
> > > which
> > is an
> > > additional plus.
> >
> > jOOQ and Spring work together quite nicely, even if there is not an
> "official"
> > affiliation between the two stacks:
> >
> > -
> >
> > spring/
> > -
> > -
> > -
> >
> > We're aware of QueryDSL offering SQL and NoSQL support through a single
> API.
> > In our opinion, such greater unification attempts will always come with
> an
> > impedance mismatch at some point. In other words, you'll be losing on SQL
> > functionality in favour of greater standardisation / abstraction.
> > From our experience with jOOQ, SQL standardisation is already a very hard
> > problem. NoSQL databases all work fundamentally differently. The added
> > complexity by supporting NoSQL in the same API as SQL databases is
> significant.
> >
> > Of course, these things depend on whether replacing MySQL for any NoSQL
> > database in CloudStack is really a desired, near-term option.
> >
> > ----
> > I'll be following this thread for another while and I'm more than happy
> to
> > answer any questions you may have related to jOOQ, SQL, JDBC, etc.
> >
> > Best Regards,
> > Lukas

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message