Show JPA's SQL-Statements when using Play Framework 2

Question

I'm developing a web application using the Play Framework with JPA/Hibernate. For debugging purposes, it would be great to see the SQL statements used by JPA. There already is another thread with the same question, but it is about Play 1 and the given solution does not work for me - it seems that jpa.debugSQL=true cannot be used in Play 2 (No Persistence provider for EntityManager named true).

Instead, I added the following to Play's application.conf:

db.default.logStatements=true
logger.org.hibernate=DEBUG

I'm not sure if I need both lines, but at least the SQL statements are logged now. But for large queries, Hibernate prints a ton of debug messages which make the already printed SQL statements disappear in my console window. I already tried to increase the buffer of the console window, but that didn't change much.

What do I have to do in order to inspect the statements in a comfortable way?

edit:

When setting logger.org.hibernate to INFO, I'm not getting any statements at all (even with db.default.logStatements=true).

Seymore

Answer 1

Try first with the config

db.default.logStatements=true
logger.org.hibernate=DEBUG

Find out which class logs the statements (for example org.hibernate.xxx.StatementLogger). Change back to INFO for org.hibernate and add a new line for the statement logger package:

logger.org.hibernate=INFO
logger.org.hibernate.xxx=DEBUG

Cadman

Perfect, thank you. Finally, it's logger.org.hibernate=INFO + logger.org.hibernate.SQL=DEBUG – Seymore

Answer 2

I was using Hibernate with HikariCp and above suggested changes were not working for me. Instead I used hibernate property to dump sql and it worked fine.

Add following lines in property section of your persistence.xml file.

<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
<property name="hibernate.use_sql_comments" value="true"/>

Cobden

Answer 3

Unfortunately don't know the way to do that in Hibernate, anyway in Ebean it's possible turn on and off logging directly in the code i.e. by:

Ebean.getServer(null).getAdminLogging().setDebugGeneratedSql(true);

or

Ebean.getServer(null).getAdminLogging().setDebugGeneratedSql(false);

On the quite other hand if Hibernate prints a ton of debug messages it may indicate that you should consider optimizing your queries, i.e. if you getting list of 100 records with lazy loaded relations - ORM needs to perform additional queries for missing data and instead one query with join(s) it performs n * relations, so it can be real performance killer.

Jigger

Thanks. It is already a single query with a single result-entity but with some fetch joins going on in order to eagerly load some one-to-many relationships. – Seymore

Yes I'm talking exactly about this eagerly load, try to optimize ypur query, or perform second query and join data manually, otherwise when you'll go to production i.e. to Heroku you can be massively surprised (in bad meaning of these words) – Jigger