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 components. 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 ways. 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 support: http://wiki.eclipse.org/EclipseLink/Examples/JPA/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); author.store(); Or alternatively: AuthorDao dao = ... dao.insert(new Author()); Relevant sections in the manual are: - http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/ - http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/simple-crud/ - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/ 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: http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html 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); select.addCondition(BOOK.PUBLISHED_IN.eq(2011)); select.addOrderBy(BOOK.TITLE); Result<Book> result = select.fetch(); This is also documented here: http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-non-dsl/ Conclusion ======== 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: http://www.hibernate-alternative.com 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: http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/ Schema and table mapping for multi-tenancy support: http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-mapping/ Record mapping in order to provide custom mapping algorithms from jOOQ records to your domain model: http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-recordmapper-provider/ 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, Lukas 2013/11/27 Frank Zhang <frank.zh...@citrix.com> > 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 >
