How to use log4jdbc with WSO2 products to log JDBC calls

log4jdbc is a nice tool to log JDBC call details of an application. Here are the steps to use it in WSO2 products. Here I'm using WSO2 APIM.

1) Download log4jdbc driver from here, and place it in <API_HOME>/repository/components/lib/ directory.

Note: Make sure to have the JDBC driver corresponding to your database server too in the same directory.

2) In master-datasources.xml,

     i) Change JDBC url like this. (note :log4jdbc: part)

<url>jdbc:log4jdbc:mysql://localhost:3306/AM_DB</url>

     ii) Set JDBC driver class like this.

<driverClassName>net.sf.log4jdbc.DriverSpy</driverClassName>

3)  Add following to log4j.properties file.

log4j.logger.jdbc.sqlonly=INFO
log4j.logger.jdbc.sqltiming=INFO
log4j.logger.jdbc.audit=OFF
log4j.logger.jdbc.resultset=ERROR
log4j.logger.jdbc.connection=ERROR 

4) Restart the server, and you'll see logs like this.

[2016-07-28 21:25:27,719] INFO - sqltiming SELECT ID, NAME, IS_ENABLED, DISPLAY_NAME FROM IDP_AUTHENTICATOR WHERE IDP_ID = 1
{executed in 5 msec}
[2016-07-28 21:25:27,720] INFO - sqltiming SELECT PROPERTY_KEY, PROPERTY_VALUE, IS_SECRET FROM IDP_AUTHENTICATOR_PROPERTY WHERE AUTHENTICATOR_ID = 1
{executed in 0 msec}