Hello, >From the follow-up posts on this discussion, I can see that many concerns have been resolved. So I'll merely answer the open points Frank had.
2013/12/3 Frank Zhang <frank.zh...@citrix.com> > Hi Lukas: > Thank for detailed reply, see my comments inline > > > > > 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 > > Your explanation frees my concerns from last three items. I don't > carefully read > entire JOOQ document and I think most people won't when they try to get > an overall picture of a new project. > > So if we can highlight these items(especially CRUD verbosity and custom > query builder) > somewhere, it would be more appealing to developers who have JPA/Hibernate > background. > > > > > 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. > > My whole point here is about declarative transaction management which is > the > most compelling part of Spring Data(from Spring's own words). Just did more > research that JOOQ is a DB access layer it won't involve in any transaction > management stuff. If my understanding is correct, it should automatically > work > with Spring TX? Am I right? > Yes, people on the jOOQ User Group have experienced this particular integration to be very easy thanks to Spring TX's versatility. > No matter the answer, JOOQ should give the best practice in document, > searching chapter names in http://www.jooq.org/doc/3.2/manual/ > returns nothing about transaction and I have seen people asking > similar question on Stack Overflow. I saw your answer too :) > Yes, that is true. We're working on better documentation and examples in that area. http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-rollback-with-jooq > > it's very true that we can draw a clear boundary between DB access layer > and > transaction management, but for most of time users don't care about this > because > they are searching an entire solution for DB. Even if JOOQ developers know > transaction management is not focus of this project, they still need to > tell their users > the best way to go instead of letting user figure out it themselves. > This is not what we have experienced. It is true that complete default behaviours are desireable to decrease the initial learning curve. But from our experience, things like transaction management are so complex and uniquely tied to the target architecture that imposing a transaction model would be a bad idea. Hibernate's session management for instance may easily leak out from your data access layer into your business (or even UI) logic. >From what I've read on this thread, many people appreciate Apache CloudStack's current transaction model, wanting to replace only the querying. In that sense, jOOQ not imposing a transaction model could be seen as a good thing. > I don't think JOOQ has to integrate with SpringDB like QueryDSL does, for > example, > having a JOOQTemplate. But I do think integrating with Spring TX is > important > given it's popularity. > Yes, absolutely. Best Regards, Lukas > > > > 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 > > > > I strongly agree with the point that "none lock-in" is actually in theory > most of time, > In reality, there is more or less you have to do or sacrifice when moving. > > My concern here is about SQL engine layer. The SQL engine I mean is the > part which > generates final SQL statement. For my perspective, JOOQ is DSL API + SQL > engine, > where DSL API is the main business. My understanding is the DSL API is > now tightly coupled > with SQL engine that I can not switch to another vender like Hibernate. > > As a user, I may prefer DSL API provided by JOOQ, but have some > performance concerns about > underlying SQL engine and apt to use Hibernate, then I have to make a hard > decision. > This may not be a valid point to you. First JOOQ may have better > performance than Hibernate(I saw > reads about SQL performance mentioned in JOOQ document, which hints that > JOOQ concerns much > about performance). Second, I don't know if Hibernate can be solely used > as a SQL engine. > > But decoupling the API from SQL engine provides potential flexibility for > future, and might be easier > to make the DSL API as a JSR. > We have seen this much in DB. JPA is just a set of API. Mysql splits > storage engine from server logic > (Though the storage engine still couples with some server side logic like > row locks). > > Conclusion, besides the transaction management, I don't have too much > concerns in using JOOQ in CloudStack now. > I am glad to see after many years there is still a project actively > working on solving DB problem in Java. > > > > 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-wi > > > > th- > > > > 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 > > > >
