I'm looking into the integration of Hibernate with SQLJ. The idea is to leverage both the programming efficiency of Hibernate and the execution efficiency of SQLJ on DB2/zOS. The proposed implementation would involve running Hibernate in two passes: the first would occur at build time and would attempt to catch all the SQL that will be generated at runtime, tranform it into SQLJ routines, and then add those SQLJ routines to the codebase. At runtime, the queries would be intercepted and diverted to the SQLJ routines; "unexpected" queries would be passed through via JDBC and would still work (albeit more slowly than the precompiled SQLJ statements).

For simple CRUD methods, this appears relatively straightforward; a pair of custom EntityPersister would be created that override load, insert, delete, and update methods. One, at build time, would capture the generated SQL; the other, at run time, would divert the routines to the SQLJ equivalents (provided the SQL still matched).

For complex queries, however, the situation is somewhat different. The custom SQL needs to be caught (post HQL translation in the case of HQL) and diverted. This appears to be best done in a custom Session class - in particular it appears that getQueries() could be overridden to provide a custom QueryTranslator class that does the same capture/divert logic as described above. However, the sticky point is that there is no way to specify a Session override. SessionImpl and SessionFactoryImpl are final, SessionFactoryImpl.openSession() is private, etc.

Clearly I can remove some of the final declarations from these classes and move the routines from private to protected, and then submit the changes back to the source. Which brings me to the point of this question: if I pursue this will the changes as outlined above be considered for inclusion in the mainstream build? If not, we will have to consider another tact - likely hand-writing out SQLJ routines.

(Note that in all cases the overrides should be pretty simple - check the SQL statement for equality, call the super() if not, call the SQLJ if)

If you just want to intercept all SQL queries generated by Hibernate, why don't you do it by just writing a JDBC driver wrapper and let Hibernate use that?

[quote="michael"]If you just want to intercept all SQL queries generated by Hibernate, why don't you do it by just writing a JDBC driver wrapper and let Hibernate use that?[/quote]

That's certainly an interesting approach. Do you know if anyone already has such a driver written and available under opensource? I'd rather leverage work than start from scratch...

There is p6spy which does sql logging and such stuff. http://www.p6spy.com/ There is also a simple driver wrapper for the mssql jdbc driver, at http://msjdbcproxy.sf.net/

Hi hablutzel

We are also looking into leveraging both hibernate and SQLJ, since within our organisation we have a very strong call to use SQLJ for various reasons, our database is also db2. I am just wondering how far you were able to go with this integration and your findings.

Also for Hibernate team do you guys have any plans of including this as part of the product or any body else out their have such a combination in works?


Thanks in advance.

MT