Logging queries to H2 database with Play Framework

Question

I am using H2 database, in-memory, within Play Framework (1.2.7).

In order to have a log of all queries I added ;TRACE_LEVEL_FILE=3 at the end of these parameters in Play's application.conf:

1. db.url
2. %prod.db.url
3. %debug.db.url

But nithing happens, no file seems to be created or updated on the machine (Mac), even though there is definitely activity with the DB (when I browse to H2's web interface, I can see that many records have been written).

What am I missing? How can I get the log to be written?

Answer 1

I don't know about TRACE_LEVEL_FILE but you could try using p6spy.

Add the following to your dependencies.yml

- p6spy -> p6spy 2.1.2:
    exclude:
      - p6spy -> p6spy-signedjar-test

The run play deps

Now edit your `application.conf' as follows:

# Comment out the default test URL
#%test.db.url=jdbc:h2:mem:play;MODE=MYSQL;LOCK_MODE=0
# Use the p6spy driver
%test.db.driver=com.p6spy.engine.spy.P6SpyDriver
%test.db.url=jdbc:p6spy:h2:mem:play;MODE=MYSQL;LOCK_MODE=0
# Tell the p6spy driver to use the H2 JPA dialect
%test.jpa.dialect=org.hibernate.dialect.H2Dialect

Finally, create conf/spy.properties and put the following in it:

appender=com.p6spy.engine.spy.appender.StdoutLogger
realdatasourceclass=org.h2.Driver

That's it, you're good to go. Start your app with play test and you'll see all database queries logged to stdout.

There are plenty of other options that can be configured in spy.properties.