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
(http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-spring/)
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:
SELECT * FROM BOOK
WHERE BOOK.PUBLISHED_IN = 2011
ORDER BY BOOK.TITLE
JOOQ:
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:
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
AND BOOK.PUBLISHED > '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
LIMIT 2
OFFSET 1
FOR UPDATE
JOOQ:
create.select(AUTHOR.FIRST_NAME, 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"))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.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 [mailto:lukas.e...@gmail.com]
> Sent: Tuesday, November 26, 2013 3:58 AM
> To: dev@cloudstack.apache.org
> 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 <alex.hu...@citrix.com>
>
> > 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
> DUPLICATE KEY UPDATE)
> - 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]: http://blog.jooq.org/2012/08/05/mysql-bad-idea-384/
> [2]:
> http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/
> [3]: http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness/
> [4]: http://stackoverflow.com/q/9234021/521799
>
> 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:
> http://www.hibernate-alternative.com
>
> 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:
>
> -
> http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-
> spring/
> - http://blog.jooq.org/2012/09/19/a-nice-way-of-using-jooq-with-spring/
> - http://blog.uws.ie/2013/04/using-jooq-with-spring-transactions/
> - http://stackoverflow.com/q/4474365/521799
>
> 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
