Glassfish and JPA/SQL monitoring - without additional tools or frameworks
Sometimes it is hard to say, whether an OR-mapper will perform well or
not. One point, which has to be considered, is the quality of the
generated SQL-statements.
During
the development phase it is also useful to intercept the SQL statements
for debugging purposes. Some strange errors can happen in case reserved
DB-keywords are used as attributes or JPA/CMP names. In the past I
used tools like P6SPY,
which is a level four JDBC driver. It acts as a proxy and is able so to
log the whole traffix between the JDBC-client (appserver) and the
database. It works well, but has one drawback - either you have to
reconfigure your Datasource, or change the JNDI-name of the
datasource in your deployment descriptor.
With glassfish it is very easy to log the SQL statement and binding
variables to a log file. To do this you need only an additional setting
in the persistence.xml file which decreases the log level.
<persistence>
<persistence-unit name="special_name">
<jta-data-source>jdbc/Derby</jta-data-source>
<properties>
<property
name="toplink.ddl-generation" value="drop-and-create-tables"/>
<property name="toplink.logging.level" value="FINE"/>
</properties>
</persistence-unit>
</persistence>
This feature is of course proprietary (it is not a part of the
standard), but very useful. Extending the deployment descriptor and
redeploying the application enables the logging of the SQL:
[...]|INSERT
INTO T_ADDRESS (ADDRESSID, ROAD, ZIPCODE, TOWN) VALUES (?, ?, ?, ?)
bind => [1, java road, 85551, Munich]|#]
[...]|SELECT
ADDRESSID, ROAD, ZIPCODE, TOWN FROM T_ADDRESS|#]
[...]|UPDATE
T_ADDRESS SET ROAD = ? WHERE (ADDRESSID = ?)
bind => [java ee 5 road, 1]|#]
[...]|DELETE
FROM T_ADDRESS WHERE (ADDRESSID = ?)
[...]|SELECT
ADDRESSID, ROAD, ZIPCODE, TOWN FROM T_ADDRESS|#]
The output is comparable with the output of P6SPY, only the content of
the binding variables appears in the next line. The advantage of this
approach is: you do not have to reconfigure the datasource and
JDBC-driver - only change the deployment descriptor.
I'm only curious whether it is possible to change the log level,
without redeploying the application - and will try it next. I will also
try to redirect the SQL-output to another file - it should be possible.
Again - I really suprised about the non-functional quality of glassfish.
This is IMO not Glassfish related, this can be done with any EJB3 container. Its equally easy to add some lines to your logging config to achieve that. This way it also works with non-EJB containers like Spring. Something like p4spy is normally not needed for simple tracing. Even if your ORM tool cant log that, what is unlikely, most JDBC drivers are also able to log statements. But you will most likely notice that the SQLs from ORM tools are created quite well from a performance standpoint. I am using JDBC tracing more in cases where i am not sure if my mappings are correct.
Posted by Marc Logemann on August 29, 2006 at 03:34 PM CEST #
Hi Marc,
thank your for comment. The interesting point are the values of the binding variables. They were not always written to a log file (it was the reason for P6Spy usage in the past).
SQL-logging is of course not glassfish specific, but is supported out off the box - which is great :-).
Posted by Adam Bien on August 29, 2006 at 05:52 PM CEST #