Wednesday, December 19, 2007

debugging SQL statements from EJB's

EJB's...(sigh!) I don't know what the ejb community was thinking when they went off onto this tangent. Granted that in the narrow domain of systems that genuinely need distributed transactions and tiers, and compared to the alternatives at the time for transaction management & ORM (CORBA & nothing), EJB was good.
It could have been a lot better if not for standard-itis and if SUN had thought for 5 minutes about what most applications need (instead of how they could cash in), but it (++sh) happens. The problem then was every man and his dog j2ee developer going off and architecting systems with no distributed requirements at all with full-blown EJB architecture and every J2ee pattern in the catalogue. Enter the current breed of "legacy" applications less than ten years old...

Anyway, if you are in the situation of wanting a non-proprietary way to see what SQL the container is producing on your behalf, then you're in trouble, it's not as easy as saying show_sql=true.

Having just dug around on this topic, here's my two cents:
1) use Log4JDBC
this is really the premier solution, Log4jdbc wraps your jdbc driver transparently, and you can fully configure the logging via standard log4j xml. The caveat with this method is that you need to keep in mind that the JDBC driver will be loaded in to a different ClassLoader than your application, and you need to be aware of this because it can cause some strange errors otherwise.
The basic idea with log4j is to replace all references in your config (you don't configure your Datasources in code, do you?) to your JDBC driver with the log4jdbc driver, and prepend the jdbc url with "jdbc:log4". then configure your logging and you're away!
This method is preferred, but can take a bit of configuring and hacking if you're many levels removed from the DriverManager, eg - in a managed environment.

2) The retarded cousin...
decompile your JDBC driver with JAD or similar, then *manually* add in logging statements to print SQL and parameters, then replace your existing driver with this hack version. This is an ugly hack and should only be used in the absolute last resort where all else has failed.

Ideally *any* SQL abstraction should allow the printing of the SQL it generates as it executes, to allow debugging and profiling, but unfortunately it's not always possible.

No comments: