hibernate logging with spring data jpa

[harald michael]

I try to do something like in this article:

https://stackoverflow.com/questions/58697020/enable-hibernate-logging-with-spring-data

Enable hibernate logging with spring data?

But nothing is being logged. When I leave the entries in application.properties out the sql query is logged. It looks like there is a conflict between setting show_sql=true in jpaproperties and
spring.jpa.show-sql=true in application.properties . I tried setting show_sql to true, false and leaving it out completely, but query is not logged.

Here are the relevant contents:

application.properties

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
Annotation configuration:

@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() 
{
    final LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
    entityManagerFactoryBean.setJpaVendorAdapter(vendorAdaptor());
    entityManagerFactoryBean.setDataSource(dataSource());
    entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
    entityManagerFactoryBean.setPackagesToScan(this.env.getProperty("packagestoscan"));
    entityManagerFactoryBean.setJpaProperties(jpaHibernateProperties());
    return entityManagerFactoryBean;
}

protected Properties jpaHibernateProperties()
{

    final Properties properties = new Properties();

    properties.put("hibernate.dialect",org.hibernate.dialect.Oracle10gDialect);
    properties.put("hibernate.show_sql", true);
    properties.put("hibernate.format_sql", true);

    return properties;
}


@Bean
public HibernateJpaVendorAdapter vendorAdaptor()
{
    final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    return vendorAdapter;
}

Using

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
also did not log the queries.

Is there anything I am missing here ? Thank you!

[Himai Minh]

You may find this tutorial about using log4j helpful:
https://thorben-janssen.com/hibernate-tips-how-to-log-sql-statements-and-their-parameters/

[harald michael]

yes, I tried that as well, but it is the same problem. Sql statements are not logged due to a conflict between setting show_sql=true in jpaproperties and log4j.logger.org.hibernate.SQL=debug .

Here  the author says to set show=sql=false - but after doing no sql statement is logged either.

Is there a solution to that ?

[Himai Minh]

How about adding this:

logging.level.org.hibernate.type=TRACE

 ?

Reference:
https://stackoverflow.com/questions/30118683/how-to-log-sql-statements-in-spring-boot

[harald michael]

Hi,

I just tried with this option too with same result.
It looks like every setting related to queries is ignored.

The setting to log in a file is though executed - the file gets created:

logging.file.name=springboot.log
logging.pattern.console=

Additionally I have set again show_sql to false  and removed format_sql in jpaproperties as indicated in the mentioned article.
In this case the query is not logged at all...

I also removed show_sql from jpaproperties, so that only the settings in application.properties remained - in this case an unformatted query is printed, but with no parameter values. (As it is not printed to springboot.log I assume show_sql defaults to true.)

So, is there a way to make the application take these settings in application.properties into consideration?


LE: I have managed to get formatted query and parameters to the console using log4j.xml Thank you for the hint! With file appender the query is logged to console although it should be logged to a file. The file appender is though respected for the parameters. It looks as like the show_sql=true in jpaproperties has priority and loggs by default to console.
With log4j.properties it behaves similar to Springs's application properties - it simply seems to be ignored and query and parameters are not printed.  
Still I would like to use the application.properties of Spring.

Opposed to the previous link, this  one says show_sql has to be true when using the org.hibernate.SQL logger in xml or properties file.

[Himai Minh]

For logging to a file, this is a tutorial:
https://tomd.xyz/spring-boot-file-logging/#:~:text=%20Logging%20to%20a%20file%20in%20Spring%20Boot,Just%20like%20most%20Spring%20Boot%20properties%2C...%20More%20

[harald michael]

well I used those settings to log to a file , the file is created successfully, but the sql stuff is not logged in there..

[Himai Minh]

Hi, Harald Michael,
Someone on stackoverflow community has the same issue, but haven't got any solution yet:
https://stackoverflow.com/questions/60370240/hibernate-sql-statements-are-not-getting-logged-in-separate-log-file-using-log4j

[harald michael]

yes, it is indeed the same issue.
An explanation of how jpa properties interact with loggers is not easy to find..

[harald michael]

Is it possible that JBoss is taking its own logging configuration instead of what we configure for logging in application.properties for Spring Boot ?

[Rob Spoor]

How is JBoss involved? Are you deploying a Spring Boot application as a WAR file inside JBoss? If so, then probably yes.

[harald michael]

yes , we do , like in the above link on stackoverflow.
There is no application class annotated with SpringBootApplication , there is a class annotated with Webservice which is basically where it starts.

[Himai Minh]

With Spring boot application, an embedded Tomcat is used. We don't need to deploy it to Jboss.
It seems like your application being deployed to JBoss is a Spring Framework application ?

[Rob Spoor]

You can deploy Spring Boot applications in servers like JBoss and Tomcat as well. For instance, see https://www.baeldung.com/spring-boot-war-tomcat-deploy.

[Himai Minh]

Hi, Harald,
Is it possible to deploy your Spring boot application as a jar in embedded Tomcat instead of as a war in JBoss
and see if it can log the Hibernate statements?

[harald michael]

no, that is not something I can decide