Venkat Sadasivam

Ours is a legacy application, we are migrating from desktop to JavaEE web based application. Since it is desktop application database triggers, functions and procedures are heavy used to do lots important functionality. Some of our team members were proposing JPA to move trigger, function and procedure logic to Java layer but many of us (including me) felt is too risky for our business, time to market and lots of data needs to be migrated.

I started looking for a simple alternative framework for JPA which can help us to retain database objects at the same time simplify our Java code like JPA . Here are my findings

Apache Empire-db

Empire-db is a lightweight yet powerful relational database abstraction layer based on JDBC. It is Open Source and provided under the Apache 2.0 license.

I like the Empire-db approach where it represents all the tables and columns as Java objects which provides compile-time safety and easy to refactor code based on database change. Empire-db solves the JPA limitation on complex select query issue but insert will require more code than JPA.

Java Object Oriented Querying (jOOQ)

jOOQ is the next solution I came across at first it looks similar to Empire-db but as I started creating some prototype I am impressed with its design and technique how it resolves limitation of JPA/Hibernate and simplify Java development for legacy application. Here are the advantages and disadvantages of jOOQ

Advantage

1. Like Empire-db you can write type safe SELECT queries in Java.
2. It generates POJO and UpdatableRecord to simply INSERT and all column select.
3. It lets application developer to handle the transaction which is big benefit for us.
4. It doesn’t have level 1 and level 2 cache which helps lot since we do INSERT/UPDATE on stored procedures.

Disadvantage

1. It doesn’t have power API like Hibernate Interceptor to do automatic check and updates.
2. No id generation logic. JPA’s @SequenceGenerator is handy for us as we use Oracle sequence for primary key generation. (Since SQL Server 2012 also support sequence we planned to use to similar approach for SQL Server as well)

Conclusion

If you are creating a brand new application JPA / Hibernate is a good solution. Like us if you are looking for API to access your existing database objects then jOOQ is a good fit.