How to monitor the data source connections and sql queries in jboss

To allow us to debug our jboss server for possible connection leaks, wrong sql queries normally we do 2 changes:

1.) In persistence.xml, set hibernate.show_sql to true

2.) In jboss's standalone.xml, we set the hibernate logger to DEBUG

<logger category="org.hibernate">
 <level name="DEBUG"/>
</logger>

But there's another not so popular way I guess, enabling spy setting in jboss's datasource.
1.) In your datasource setting add spy="true", example:

<datasource jta="true" jndi-name="java:jboss/datasources/czetsuyaDS" pool-name="czetsuyaPool" enabled="true" use-java-context="true" spy="true" use-ccm="true">

2.) Add jboss.jdbc.spy logger in the logging section of jboss

<logger category="jboss.jdbc.spy">
 <level name="TRACE"/>
</logger>

After that restart jboss and you should see logs similar to:

19:43:40,006 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getCatalog()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:40,041 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createClob()
19:43:40,042 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] close()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getAutoCommit()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] getMetaData()
19:43:41,256 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createStatement()
19:43:41,257 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Statement] executeQuery(select relname from pg_class where relkind='S')
19:43:41,257 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [ResultSet] next()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [ResultSet] close()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Statement] close()
19:43:41,258 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 64) java:jboss/datasources/czetsuyaDS [Connection] createStatement()
...
executeUpdate(...)
...