On 12/2/13, 5:00 PM, "Frank Zhang" <frank.zh...@citrix.com> wrote:
>It's really long .... > >I don't know if it's impossible to move us to JPA but I do agree that >whatever >path we choose it's lot of work to do. > >Given you have tried JOOQ and Lukas's explanation, my only concern is if >we can leverage Spring TX with JOOQ. Especially we just moved to Spring >TX recently. > >I agree most of your statements except DAO pattern. IMO, we should >get rid of this pattern at all. No matter how Java community prefers it, >I always think DAO is an anti-pattern that should have never been >existing. > >There is no standard for implementing a DAO. But all implementation has >below problems: > >1. unnecessary >All DAO have different persistence methods for different domain objects. >For example. > >CustomerDao.saveCustomer(...) >CustomerDao.updateCustomer(...) > >OrderDao.saveOrder(...) >OrderDao.updateOrder(...) > >Why do we need so many interfaces? Why not simply use > >EntityManager.persist(CustomerVO); >EntityManager.persist(OrderVO); >EntityManager.update(CustomerVO); >EntityManager.update(OrderVO); > >If there any special interests(rare case), using an entity listener can >solve it perfectly > >2. plain wrong >Lots of DAO(not all) create method for every query. You have stated it >lacks of extensibility. >I thought it's just plain wrong. > >Combinations of query parameter is infinite, that's why people use SQL >which is a DSL to manipulate >DB because you can not enumerate all possibilities by function. Someone >may argue NoSql doesn't have >a language like SQL. But if you look into NoSQL query functions, they are >DSL in essence. DSL like SQL is >called external DSL which needs a parser, DSL like NoSql query function >is called internal DSL which uses >facilities provided by host programming language. So when we create DAO >like: > >CustomerDao.findCustomerByLastName(...) >CustomerDao.findCustomerByFirstName(...) >CustomerDao.findCustomerByFirstAndLastName(...) > >We are wrong. > >My suggestion is, once we switch to new ORM, we should never create DAO >anymore. We can have a single >interface to do CRUD, and directly use ORM like JOOQ to do the query. >Someone may argue again this will duplicate >query. That's true but doesn't matter. To get flexibility and >extensibility, duplicate code is a necessary sacrifice. >We can put the most common query that is queried by primary key in CRUD >interface, for others, just create a query criteria or >JOOQ fluent query on demand. +1. I know Alex has already added entity manager trying to get rid of unnecessary DAO methods, but due to the way on how we construct queries, we still have to rely on individual DAOs. A simple CRUD mechanism and SQL compatible query(agnostic to underlying DB) would be enough for most of persistence needs in CloudStack. > > > >> -----Original Message----- >> From: Darren Shepherd [mailto:darren.s.sheph...@gmail.com] >> Sent: Monday, December 02, 2013 9:33 AM >> To: dev@cloudstack.apache.org >> Subject: Re: persistence layer >> >> Alright, this is long.. >> >> I have to say after the 10+ years of struggling with JDBC, Hibernate, >> JbdcTemplate, JPA, QueryDSL, and countless custom solutions, I've have >>never >> found a database access solution that has impressed me as much as jooq. >>If I >> was starting from scratch today I'd definitely choose jooq. Now all of >>that >> doesn't matter though. Evaluating database access solutions in >>isolation is >> about as productive as discussing if c++ or java is a better language. >>The >> answer being "it depends." We really need to ground this discussion in >>the >> realities of what ACS is today, what are the issues and where do we >>want to go. >> Nobody is going to sign on to a complete gut and rewrite of the data >>acces >> layer (DAL), so any solution must allow a slow migration to it. >> >> Current solution >> ============ >> ACS currently has a completely custom DAL. While the VOs are annotated >>with >> JPA annotations it is dangerous to compare the custom ACS framework to >>JPA >> as the differences are substantial. It is also important to note that >>ACS does not >> have a ORM today. There is no relational mapping. >> >> The ACS DAL can be broken down to five parts. 1) DAO framework >>(GenericDao) >> 2) object mapping 3) query api 4) crud w/ change tracking 5) transaction >> management >> >> DAO framework >> ============ >> ACS has a strict 1-to-1 mapping of VO to DAO. The generic DAO provides >>the >> common methods like findById that you see in most java DAOs. The generic >> DAO uses reflection to query the VOs' JPA annotations to build up the >>metadata >> for the crud operations and query building. >> >> Object mapping >> =========== >> The metadata obtained through the GenericDao is used to map sql to the >>VOs. >> If you use the GenericDaos findX or listByX methods or the query api it >>will map >> the result to a VO. Additionally if you update a VO and persist it the >>mapper >> will generate SQL from the VO. >> >> Query API >> ======== >> ACS has a search builder api that allows you to build sql queries in a >>type safe >> manner. The functionality is mostly limited to basically conditions and >>joins. >> The api works for most of the use cases in ACS but still there a quite >>a lot of >> raw sql statements in ACS because the api is not functional enough. >> >> CRUD with changing tracking >> ===================== >> When POJOs are created by the GenericDao they are enhanced with cglib. >>All >> changes to the pojos are tracked as updates. When persist is called the >>updated >> fields are used to construct an update. >> >> Transaction management >> =================== >> Transactions are managed programmatically through the transaction api. >> Nested transactions and transaction propagation are not supported. >> Isolation level is determined at the DB pool level, so all connections >>have the >> same level which is read committed. >> >> Problems >> ======== >> The general problems of the dal in ACS is that it's 100% custom. The >>APIs, >> especially around transactions and querying, are often a stumbling >>block for >> people developing in ACS. Additionally, since this is all custom, the >> maintenance and enhancements of the DAL is solely the responsibility of >>the >> ACS community. Finally, very little documentation exists around all of >>this, and >> there is realistically about 3 people who understand the core >>implementation. >> >> You have to consider that ACS was started almost 5 years ago. I can >>understand >> the creators not wanting to use hibernate or spring and instead rolling >>their >> own solutions. The problem with rolling your own solution typically >>ends up >> being that your use cases start off simple and your custom framework >>does too. >> As the product matures and more complex use cases are tackled things >>are no >> longer simple. You are then forced to enhance your simple framework, but >> then the warts start to really show. Or, you start bypassing it because >>it is too >> limiting. >> >> DAO framework >> ============ >> I don't have major fundamental complaints with the DAOs. The API >>exposed is >> fairly consistent with most DAOs you would see in any java application. >>The >> main complaint I have is the strict 1-to-1 mapping between VO and DAO. >>In >> general I don't like that design pattern and prefer to group DAOs >>according to >> function and not tables. That's an opinion and I think the larger java >>community >> tends to disagree with me. So I concede on that point. The bigger >>problem with >> the 1-to-1 model is extensibility. Today, if you are writing a new >>plugin and >> need to execute some query that doesn't exist today, you really don't >>have >> much choice but to change the existing DAO in the core. >> This leads to people instead of doing DB logic in a DAO, they end up >>doing it in >> the services classes which is just bad. >> >> Object mapping >> =========== >> The object mapping in ACS is quite simple. Since there is no relational >>mapping, >> it just basically does the equivalent of BeanUtils.setProperty(). >> There is additional logic for database encryption. One of the down side >>of the >> current mapping is that it just works for VOs. If you want to do some >>more >> complex sql query that returns arbitrary output, you are forced to fall >>back to >> raw JDBC. The Query API, since it builds off of the object mapping, it >>also >> oriented to just a single VO. If you join across tables you are only >>joining to >> restrict the result set of the main table you are querying. If you are >>trying to >> get the values of the parent and child tables at the same time, that >>really isn't >> possible (or at least obvious how to do it). >> >> Query API >> ======== >> The query API is very problematic. It is your typical in-house SQL >>abstraction in >> that probably solved the original creators problems and made sense at >>the time, >> but to others it is obscure and difficult to understand. >> I honestly don't really know off the top of my head how to use it and >>just copy >> and paste other examples. For example, tell me if it's obvious to you >>what the >> below code is doing. >> >> SearchBuilder<NicVO> nicSearch = _nicDao.createSearchBuilder(); >> nicSearch.and("networkId", >>nicSearch.entity().getNetworkId(), >> SearchCriteria.Op.EQ); >> nicSearch.and().op("ip4Address", >>nicSearch.entity().getIp4Address(), >> SearchCriteria.Op.NNULL); >> nicSearch.or("ip6Address", >>nicSearch.entity().getIp6Address(), >> SearchCriteria.Op.NNULL); >> nicSearch.cp(); >> >> UserVmSearch = createSearchBuilder(); >> UserVmSearch.and("states", UserVmSearch.entity().getState(), >> SearchCriteria.Op.IN); >> UserVmSearch.join("nicSearch", nicSearch, >> UserVmSearch.entity().getId(), nicSearch.entity().getInstanceId(), >> JoinBuilder.JoinType.INNER); >> UserVmSearch.done(); >> >> I will be quite frank and say that the query API is the number one >>thing in ACS >> that I despise. The reason being that an IaaS orchestration platform is >>largely a >> metadata management platform. It is all about storing metadata and >>state and >> reconcilioning that state with external resources. As such, I would >>expect the >> platform to have a simple and rich API to access data that establishes >>good >> patterns. Instead we have an api that is very limited and obscures the >>real >> query going on under the hood. I also think the nature of the api has >>also lead >> to a lot of bad practices in code. Those being the scattering of DB >>logic in the >> services layer and raw JDBC and SQL strings embedded in code. >> >> CRUD with changing tracking >> ===================== >> The current change tracking is done with enhancing the VOs with cglib. >>The >> framework basically is what it is. The pattern is used by other >>frameworks and I >> don't have too much complaints. >> >> Transaction management >> =================== >> In 4.3 I already rewrote the transaction API. The previous problems >>were all >> based around the fact that the transaction management is completely >> programmatic. Developers had to call start(), commit(), and rollback(). >>The >> patterns of how those APIs were called was very inconsistent and rarely >>was >> rollback done properly. The @DB masked a lot of the underlying issues >>with >> rollback() as it would do an implicit rollback on failure. The @DB >>annotation >> was problematic in that it's meaning was not clearly understood and the >>AOP >> style it demands is not compatible with proxy based AOP like Spring. >> >> In 4.3 I've change the transaction api to closely match the semantics >>of Spring's >> programmatic transaction API. The @DB annotation has no meaning now for >> the management server (AWS and usage still require @DB). >> >> Proposed solutions >> ============== >> In general I do not wish to gut the DAL. The impact would greatly >>destabilize >> ACS. Instead I want to find a way in which new code can be done better >>and old >> code can be slowly refactored if we choose. After looking at JPA >>solutions I've >> come the conclusions that having a JPA solution running in parallel >>with the >> custom DAO stuff would be a nightmare. Much of this has to do with how >>ACS >> and something like hibernate work. I know we can't use hibernate >>because of >> the license, but hibernate is the library I know best so I will speak >>in those >> terms. (As a side note, the fact that we can't use hibernate also >>makes me >> weary of using JPA. If I had to choose I'd probably go with TopLink, >>but that just >> makes me nervous. JPA solutions are so complex and you're really >>married to >> them once you start using them. It's hard for me to agree to >>committing to one >> unless I've had substantial experience with it. It is also extremely >>difficult to >> write a fully JPA standard app. You usually fallback to using >>implementation >> specific APIs or just plain JDBC.) >> >> In ACS we track changes to VOs with cglib. In JPA, changes are really >>tracked in >> the context of a session (or entitymanager). Once you close the >>session or >> detach the object from a session it loses it's context. Since DB logic >>is scattered >> everywhere is ACS, there is no clear place to start and close sessions. >> This is >> really not completely the fault of ACS design or anything. JPA is >>really designed >> with the idea of container managed transactions and by container that >>implies >> Servlet or EJB. We obviously have no EJBs and very little logic in ACS >>happens >> in the servlet container. The majority of logic happens in >>non-container >> managed code. >> >> Additionally, the VOs annotations are not really compatible with JPA. >> There are some subtleties around inheritance that won't work. I can't >> remember the details of the issues there. Inheritance in JPA/Hibernate >>is just a >> nightmare anyhow. It does really strange things like creating temp >>tables and >> since MySQL isn't all that fancy of a DB, you run into weird deadlocks >>as >> hibernate is trying to do operations from two tables and then >>additionally a >> temp table. Regardless of the interpretation of the JPA annotation, >>the custom >> field mapping like encryption that has been done also causes issues. >> If nobody believe me what a nightmare it will be to run ACS in parallel >>with JPA, >> just go try it yourself. >> >> I propose we integrate jOOQ. jOOQ will allow us to have a much better >>API but >> not have to gut anything. We can include it to specifically address >>the areas of >> concern. I have already tested it out and we can run it seemless in >>parallel >> with the custom ACS stuff. The custom object mapping ACS does is >>completely >> compatible with jOOQ and the cglib stuff is too. >> You can query from jooq and persist with ACS genericdao. From a >>developer >> perspective all that really changes is that instead of using the >> SearchBuilder/SearchCriteria API you use the jOOQ fluent API to query. >>All of >> the existing findByX and listBy methods can remain. We will add more >> listBy() method that take a jOOQ Condition and not a SearchCriteria. >> Here's how I see the benefits of moving to jOOQ. >> >> DAO framework >> ============ >> The DAOs will generally stay the same. Since the VO metadata and >>mapping >> needs to be integrated we will pull the logic for that out of the >>GenericDaoBase >> and externalize it so that it can be used in both the context of the >>SearchBuilder >> and also jOOQ. This has the benefit that the strict 1-to-1 mapping of >>VO to >> DAO will be removed. Plugins will be free to create their own DAOs >>that don't >> conflict with the core DAOs. >> >> Object mapping >> =========== >> Object mapping will still work as it does today. What we gain is that >>for more >> complex queries that don't map to a single VO (or a VO in general) can >>be >> mapped to any regular POJOs. The mapping framework in jOOQ is very >> flexible and powerful, but for most use cases just creating a POJO with >>the right >> field names is sufficient. >> >> Query API >> ======== >> The query API is where will see the most benefit from jOOQ. We can >> deprecate the SearchBuilder/SearchCriteria and just focus on jOOQ. When >> somebody new comes to ACS we can then just point them to the excellent >> jOOQ documentation. Additionally, if they already understand SQL, >>there is >> very little to learn. Since jOOQ is so powerful we can also get rid of >>the all the >> views that were created for the API. I really, really do not like the >>views that >> we are creating these days. The problem with views is that every time >>we >> make a modification to the API it requires a DDL change. >> Additionally the view definitions are not compatible across databases. >> I've already tried to get ACS running on HSQLDB but I gave up on the >>views. >> The problem with views is just the problem with SQL in general. >> Each DB has its nuances with SQL. You need to ensure that the SQL in >>the views >> is completely cross DB compatible (which is a pain). Also note that >>jOOQ >> handles those SQL nuances so we don't need to the be SQL-92 experts. >> >> CRUD with changing tracking >> ===================== >> This largely stays as is. We will continue with the ACS cglib approach. >> We will not use jOOQ records that do change tracking themselves. From >> jOOQ's perspective we will be using just POJOs. Those POJOs will be >>mapped >> with our custom mapper and as such we will add the necessary cglib >>enhancers. >> >> Transaction management >> =================== >> I've already in the past propose that we move to Spring transaction >> management. jOOQ is fully compatible with Spring TX. Even if we don't >>use >> Spring TX, jOOQ is compatible with ACS's current TX approach. (As a >>side note, >> the discussion of Spring Data is moot. I've already somewhat discussed >>the >> idea that we will leverage Spring core IoC and Spring TX from Spring and >> nothing else. I do not wish to use Spring Data. There is not enough >>value to tie >> us specifically to that framework. Spring APIs are a can of worms. >>Once you >> starting higher level frameworks like Spring MVC or Spring Data you >>suddenly >> bind you app to a massive framework. Today if we didn't like Spring >>anymore, >> moving to something like Guice would be a small effort. Once you >>starting >> using Spring Data, for example, that ability goes away. Also, magic >> RDBMS/NoSQL abstraction frameworks are useless in my mind. You need to >> know if you are using a RDBMS or NoSQL. As a separate thread, I do >>think we >> should move the usage data to NoSQL using Apache Gora maybe. Gora is >> intending to use jOOQ under the hood too, so we can still persist usage >>to a >> RDBMS if somebody doesn't want a big data >> solution.) >> >> Okay, if you actually made it to this point without falling asleep, I >>hope you see >> that I just want to make relatively simple enhancements to the current >>DAL in >> ACS and leverage the power of jOOQ to do that. >> >> Darren >> >> >> >> On Thu, Nov 28, 2013 at 4:45 AM, Lukas Eder <lukas.e...@gmail.com> >>wrote: >> >> > 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-updatablere >> > cords/ >> > - >> > >> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere >> > cords/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-sc >> > hema-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-r >> > ecordmapper-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 >> > > >> >
