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.

Comments:

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 #

Post a Comment:
  • HTML Syntax: NOT allowed
...the last 150 posts
...the last 10 comments
License