Great discussion. I do find it a pain to construct a query in the current system (if there was a REPL, it might be easier, but oh well). +1 to JOOQ
On 12/2/13 5:27 PM, "Kelven Yang" <kelven.y...@citrix.com> wrote: >I generally in agree with what Darren has pointed. Within the context of >ACS, declarative transaction support is not as urgent as solving the >problem as we are having in writing queries. Solving basic CRUD and query >problem alone in a consistent pattern can move CloudStack persistence >layer a big step ahead. And the good part of it is that we can do it >incrementally, which I think it¹s the key for us to really move forward >quickly. > >I saw a lot of efforts trying to hide the complexity of underlying problem >but eventually end up to a more complex solution. With that said, myself >actually prefer a simple solution that is close to SQL as natural but >solves the DB-agnostic problem to a reasonable level, I don¹t like too >much of the idea trying to hide SQL completely away from developers. So >far JOOQ sounds like a good candidate to me. > >Kelven > >On 12/2/13, 3:07 PM, "Frank Zhang" <frank.zh...@citrix.com> wrote: > >>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? >> >>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 :) >> >>http://stackoverflow.com/questions/15159842/how-to-start-transaction-and- >>r >>ollback-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. >> >>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. >> >> >>> >>> 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-n >>>o >>>n- >>> 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-sche >>>m >>>a- >>> 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 >>> > >
