SQL Trace Listeners

Payara Server provides support for custom SQL Trace Listeners.
A SQL Trace Listener is registered against a data source
and is called after each method call made on the JDBC connection
pool.

SQL Trace Listeners allows developers to track all calls to the
database and can be used to develop custom auditing, error handling or
monitoring components. SQL Trace Listeners can be enabled globally on a
data source if the class that implements it is on the server’s classpath,
or can be enabled on application specific data sources by including them in
the application’s WAR or EAR.

To write a custom trace listener you need to implement the interface and
override the sqlTrace method. The SQLTraceRecord object contains
information about the execute call to the JDBC connection pool. The following
is the list of properties that can be queried using their respective getter
methods:

The implementation class must be on the server’s classpath for
it to work correctly

Using asadmin commands

SQL Trace Listeners can be enabled on a JDBC connection pool using with an
asadmin commands by using the set command to set the
fish.payara.lsql-trace-listeners to the fully qualified class name of
your listener implementation, like on the following example:

asadmin set domain.resources.jdbc-connection-pool.__TimerPool.sql-trace-listeners=fish.payara.examples.payaramicro.datasource.example.CustomSQLTracer

Application Deployment

In Java EE 7 applications, a JDBC data source can be deployed by adding the
@DataSourceDefinition annotation to a managed component (like and @Stateless
EJB bean for example). SQL Trace Listener classes can be configured using this
annotation, like in the following example:

The Data source definition can also be added to a deployment descriptor of
an application, for example in the web.xml standard deployment descriptor:

<data-source><name>java:global/ExampleDataSource</name><class-name>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</class-name><server-name>localhost</server-name><port-number>3306</port-number><database-name>mysql</database-name><user>test</user><password>test</password><!-- Example of how to use a Payara specific custom connection pool setting --><property><name>fish.payara.sql-trace-listeners</name><value>fish.payara.examples.payaramicro.datasource.example.CustomSQLTracer</value></property></data-source>

Payara Micro Support

Payara Micro also supports SQL Trace Listeners which brings powerful
operational diagnostics to your micro-services platform. To add a SQL
Trace Listener to your data source used by applications deployed on Payara Micro,
use the annotation or deployment descriptor methods described in the
previous section.