2013/7/28 <[email protected]> > A bit long, sorry. > > This is just my experience (partially) replacing OpenJPA with JOOQ in a > few projects that have around 250 tables. Not sure it is useful for > anybody, but the system is being used right now in the wild, so it might > count for something for some people. >
I'm aware of much larger systems using jOOQ... > I used Hibernate for a few years before switching over to OpenJPA (solely > for its compile-time enhancement ability at that time). And for the last > six months, my team has used JOOQ along side with OpenJPA. Basically, we > use OpenJPA when we need to do any insert/update/delete. We use OpenJPA > when it is easier to do, and for small queries where the query time is not > important. Of course, the old code with OpenJPA stays the same until we > feel the need to: > > 1. To speed up the query time. > 2. Whenever we need to modify any query that is in JPQL. > > Our systems have around 250 tables, so replacing all OpenJPA code with > JOOQ all at once is out of the question. And we do like the way JPA handles > cascade deletes and insertions and refreshes. The easy way to get a > transaction is good as well. > Mixing is a common use-case. After all, JPA's strenghts lie in persisting complex object graphs and jOOQ will not evolve into that direction. So jOOQ is not a replacement for those features. I never benchmarked the speed-up, but our JOOQ queries seem to be at least > one order of magnitude faster than the OpenJPA JPQL queries they replaced. > That is the reason I started using JOOQ when I first became aware of it > (when we needed to get up to 1,000 rows from queries with many joined > tables in under 2 seconds or so, and I was spending way too much time > fiddling with FetchType.EAGER and FetchType.LAZY and not on the business > logics). Our schemas are broadly in 3NF, so joining many tables together is > very common for us, sometimes with many left outer joins as well. > > But speed is not everything for us or we would have used JDBC directly. > The type-safety of JOOQ is very useful for us (except that we sometimes > have 30 to 40 columns in a select. We can't do a select() without > specifying the columns all the time because we would run out of memory as > sometimes we do need to join 20 tables at the same time, for instance. As > usual, we found out that we can't always use limits and first result row to > limit how many rows we might get back with a query. Any solution other than > specifying exactly which columns we want? We do re-use many queries, so > sometimes we get runtime errors when we miss specifying a column in the > select.). > > Ever since we started using JOOQ, we refactor the database schema even > more often as the system evolves, which is a huge advantage for us. JPQL > was really that big an inhibitor for us to change anything in the schema at > all. Adding tables, etc., is fine, but changing names and such is not fun > with JPQL queries. > > Can't emphasize enough how useful syntax errors are whenever we refactor a > database schema and rebuild the JOOQ jar we have. We are a 3-person team > with one tester, so we prefer front-loading everything as much as possible. > A syntax error is always much easier to handle than a support call from the > field because of a spelling mistake in a JPQL statement and a missed > regression test case (as said before, it is still possible to get runtime > issues in JOOQ if we miss specifying a column in a select, of course). As > it happened, I just had that dreadful feeling this morning, trying to find > all occurrences of a column in all the JPQL queries we have, just because > we changed the name of the column to something more appropriate. > Great feedback! Good to know how much this helps you! The main thing that I wish I could do easier in JOOQ is probably something > that is impossible to do in a framework like JOOQ: the ability to write > expressions involving the fields in a shorter way, and still make it > type-safe. But it is still only a one-time thing to make sure the > expression is correct. Can be difficult to figure out what it is later on > though: > > CAL_IN_WEEKLY.DAY_OF_WEEK.equal(weekday).and( > CAL_IN_WEEKLY.START_WEEK_NUMBER.lessOrEqual(startingWeekNumber).and( > (val(startingWeekNumber).sub(CAL_IN_WEEKLY.START_WEEK_NUMBER)).mod( > CAL_IN_WEEKLY.FOR_EVERY).equal(val(0)))) > > Counting the parentheses does remind me of my Lisp coding days. Of course, > if this is the wrong way to do, and there is an easier way, I am all ears. > By the way, I still prefer this than JPQL. > Yeah, you have a couple of options here, that will decrease your lispiness by 20% or so :-) 1. You could use a naming strategy to let the code generator shorten your table name references. I think I should actually implement that (i.e. an example thereof) in a future release ( https://github.com/jOOQ/jOOQ/issues/2661) 2. You can use Scala or any other JVM language that lets you omit parentheses. Of course, that can turn out to be a big mistake, too, as you cannot omit *all* parentheses, only "some" 3. You can omit val(...) in most cases, as the API is usually overloaded to accept both T and Field<T> for the same method. E.g. equal(0), instead of equal(val(0)) 4. You can reorganise your AND operators as in: CAIW.DAY_OF_WEEK.eq(weekday) .and(CAIW.START_WEEK_NUMBER.le(startingWeekNumber)) // "close the and" here .and(val(startingWeekNumber).sub(CAIW.START_WEEK_NUMBER).mod(CAIW.FOR_EVERY).eq(0)) A bit better? Note, there is no precedence between operators in jOOQ when chaining method calls. So you don't need parentheses around this expression as in Java / SQL: (startingWeekNumber - CAIW.START_WEEK_NUMBER) % CIW.FOR_EVERY You can just write A.sub(B).mod(C) If you wanted the to apply the precedence on the modulo operation, then you'd write this, explicitly: A.sub(B.mod(C)) The same applies to predicate construction, with .and(), .or(), .not(). There's no precedence apart from the one you set explicitly by the way you chain methods. This might be worth documenting somewhere... https://github.com/jOOQ/jOOQ/issues/2660 So, given what I know now and with my experience with both JPA and JOOQ, if > I had to start a project from scratch, I would use OpenJPA for > inserts/updates/deletes, and JOOQ for everything else. And absolutely no > JPQL. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
