Glassfish and JPA/SQL monitoring - without additional tools or frameworks 📎
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.