16

How to display SQL Statements in the log ? I'm using EBeans and it fails to insert for some reasons but I can't see what's the problem.

I tried to edit my config to:

db.default.logStatements=true

and add this to logback.xml 

<logger name="com.jolbox" level="DEBUG" />

to follow some answers I found online, but it doesn't seem to work for 2.4…

Saeed Zarinfam
  • 8,782
  • 7
  • 55
  • 66
Gonzague
  • 322
  • 3
  • 16

4 Answers4

30

Logging has changed with Play 2.4. Starting from now, to display the SQL statements in the console, simply add the following line to the conf/logback.xml file:

<logger name="org.avaje.ebean.SQL" level="TRACE" />

It should work just fine.

As @Flo354 pointed out in the comments, with Play 2.6 you should use:

<logger name="io.bean" level="TRACE" />
wleao
  • 2,208
  • 1
  • 16
  • 17
Nicolas B.
  • 376
  • 1
  • 4
  • 9
  • 1
    It's possible do it in code? Only activate when Play.isDev()! – Pedro Lopes Jul 16 '15 at 17:13
  • 5
    To avoid logging SQL statements in production, you could use two different files: the default one for development (logback.xml) and another one for production (logback-production.xml). Then, to use the production one, add -Dlogger.resource=conf/logback-production.xml. – Nicolas B. Jul 18 '15 at 20:56
  • 2
    Question is for Play 2.4, but as of Play 2.6, **org.avaje.ebean** is now **io.ebean** – Flo354 Sep 12 '17 at 14:57
9

From Play 2.5 Logging SQL statements is very easy, Play 2.5 has an easy way to log SQL statements, built on jdbcdslog, that works across all JDBC databases, connection pool implementations and persistence frameworks (Anorm, Ebean, JPA, Slick, etc). When you enable logging you will see each SQL statement sent to your database as well as performance information about how long the statement takes to run.

The SQL log statement feature in Play 2.5 can be configured by database, using logSql property:

db.default.logSql=true

After that, you can configure the jdbcdslog-exp log level by adding this lines to logback.xml:

  <logger name="org.jdbcdslog.ConnectionLogger" level="OFF"  /> <!-- Won' log connections -->
  <logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->
  <logger name="org.jdbcdslog.ResultSetLogger"  level="OFF"  /> <!-- Won' log result sets -->
Saeed Zarinfam
  • 8,782
  • 7
  • 55
  • 66
5

FYI, there's nice video tutorial on Ebean's new doc page showing the way to capture SQL statements only for selected areas of the code.

Thanks to this you can log statements only in problematic places while developing and/or use the logged statements for performing tests as showed in video.

In short: add latest avaje-ebeanorm-mocker dependency to your built.sbt as usually, so later you can use it in your code like:

LoggedSql.start();
User user = User.find.byId(123);
// ... other queries
List<String> capturedLogs = LoggedSql.stop();

Note you don't even need to fetch the List of statements if you do not need to process them as they are displayed in the console as usually. So you can use it like this as well:

if (Play.isDev()) LoggedSql.start();
User user = User.find.byId(345);
// ... other queries
if (Play.isDev()) LoggedSql.stop();
biesior
  • 54,554
  • 10
  • 118
  • 177
  • This isn't working for me. Play 2.4.2 and "com.typesafe.sbt" % "sbt-play-ebean" % "1.0.0", "org.avaje.ebeanorm" % "avaje-ebeanorm-mocker" % "6.15.1" I am getting empty set. Am I missing anything obvious ? – Smith Feb 16 '16 at 11:01
0

I had success using jdbcdslog. As @Saeed Zarinfam mentioned here, Play 2.5 includes this by default.

Unlike this answer, this solution shows the parameter values instead of question marks.

Here are the steps I followed to get it working for Play 2.4 and MySQL:

Add to build.sbt:

"com.googlecode.usc" % "jdbcdslog" % "1.0.6.2"

Add to logback.xml:

<logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->

Create conf/jdbcdslog.properties file containing:

jdbcdslog.driverName=mysql
jdbcdslog.showTime=true

Change db.default.url (example):

jdbc:mysql://127.0.0.1:3306/mydb

changes to jdbc:jdbcdslog:mysql://127.0.0.1:3306/mydb;targetDriver=com.mysql.jdbc.Driver

Change db.default.driver:

org.jdbcdslog.DriverLoggingProxy
FrancisA
  • 136
  • 8