# 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.