Where to see the logged sql statements in play2?

Question

I found there is such a configuration in application.conf:

# If enabled, log SQL statements being executed.
db.default.logStatements=true

I've enabled it, but I can't find any log file which logged executed sqls.

Where can I find it, or do I miss something?

Lithesome

Play and Scala are extremely version sensitive -- adding version numbers to all questions will help newcomers to the play/scala world form tripping over version specific advise that won't (ever) work for them. – Euratom

Answer 1

1. application.conf

make sure:

db.default.logStatements=true

This config is actually a setting of bonecp which is connection pool used in play2

2. custom logger

Add a custom logger configuration to conf/logger.xml.

The content may be:

<configuration>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%-5level - %msg%n</pattern>
        </encoder>
    </appender>

    <logger name="com.jolbox.bonecp" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

    <logger name="play" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

    <logger name="application" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

</configuration>

The com.jlbox.bonecp is for bonecp, and play and application are for play2.

3. disable logger settings in application.conf

Comment the logger settings in application.conf:

# Logger
# ~~~~~
# You can also configure logback (http://logback.qos.ch/), by providing a logger.xml file in the conf directory .

# Root logger:
# logger.root=ERROR

# Logger used by the framework:
# logger.play=INFO

# Logger provided to your application:
# logger.application=DEBUG

Restart play, and you will see all executed SQLs(including parameter values).

Lithesome

Works perfect in play 2.3.4 – Taboret

This doesn't seem to work in 2.4.2. They pulled anorm out of the core dist so I'm guessing something else is going on. – Amathist

db.default.logSql=true appears to be working in play 2.5.3 – Euratom

Answer 2

This no longer works in Play 2.4.2 from what I can tell. The default connection pool engine was changed over to HikariCP.

Add this to your application.conf and follow the directions below. Things should work:

Application.conf

db.default.pool = "bonecp"
db.default.bonecp.logStatements=true

conf/logger.xml Add a custom logger configuration to conf/logger.xml.

<configuration>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%-5level - %msg%n</pattern>
        </encoder>
    </appender>

    <logger name="com.jolbox.bonecp" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

    <logger name="play" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

    <logger name="application" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>

</configuration>

Amathist

This changes the pool back to BoneCP. Is there a way to get logging with HikariCP? (I am using Play 2.4.3) – Cero

@PatrikBeck: I tried similar approach with HikariCP's package, <logger name="com.zaxxer" level="DEBUG"/> and I do get c.z.h.p.HikariPool debug messages but not the actual SQL statements. Maybe HikariCP just doesn't support this. :-/ See also related Play 2.4 question. – Alate

For now, I'm simply doing this: use BoneCP (with above settings) while debugging some Anorm/SQL issue, and switch back to default while done. – Alate

I was able to enable logging while keeping Hikary, using log4jdbc . It wraps the JDBC driver and logs the SQL statements. – Cero

@PatrikBeck: How exactly, can you post an answer about that? Did you need to modify repository code as in https://mcmap.net/q/410831/-how-to-enable-trace-debugging-output-with-anorm-on-play-2-4-0? – Alate

github.com/brettwooldridge/HikariCP/wiki/… – Thromboembolism

Answer 3

Just add the following to application.conf (works for me in play 2.2.1)

db.default.logStatements=true

logger.com.jolbox.bonecp=DEBUG

Randalrandall

This does not appear to work under play 2.3 with scala, whereas the solution @Lithesome gave does. – Imamate

Answer 4

For HikariCP (i.e. starting with Play 2.4), see https://github.com/brettwooldridge/HikariCP/wiki/JDBC-Logging:

HikariCP does not inherently include JDBC logging at this time. This is a conscious decision, not an oversight or undeveloped future roadmap item. Nearly all major database have a JDBC driver capable of logging on its own. For those that do not, log4jdbc-log4j2 is a good option.

This wiki page documents how to enable logging for common databases, as well as log4jdbc-log4j2.

For log4jdbc-log4j2: add "org.bgee.log4jdbc-log4j2" % "log4jdbc-log4j2-jdbc4.1" % "1.16" to libraryDependencies; configuration is described at https://code.google.com/archive/p/log4jdbc-log4j2/.

Thromboembolism