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 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.
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.
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.
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.
