Can't make hibernate stop showing SQL using Spring JPA Vendor Adapter

Question

Hibernate is continuing to spew SQL traces to stdout, and I can't figure out how to change a Hibernate configuration property when it's hidden behind a JPA adapter. This is the Spring bean for the entityManagerFactory:

<bean id="entityManagerFactory" 
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="ssapDataSource"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" >
            <property name="showSql" value="false"/>
        </bean>
    </property>
</bean>

Even with the showSql property set to false, Hibernate keeps printing SQL.

I've tried making a hibernate.properties file in my classpath with "hibernate.show_sql=false", but it didn't pick that up either.

Incept

I also tried setting a system property: hibernate.show_sql=false. Still no joy. It insists on spewing SQL statements. – Incept

How about specifying <property name="hibernate.show_sql" value="false"/> ? – Nagoya

I'm pretty sure that Hibernate doesn't do this by default, so I suspect that somewhere else in your environment you have something that has turned showSql on, and this is taking precedence over your attempts to turn it off. – Nagoya

Answer 1

Try setting it in persistance.xml

<persistence>
  <persistence-unit name="PU">
    <properties>
      <property name="hibernate.show_sql" value="false"/>
    </properties>
  </persistence-unit>
</persistence>

Moffett

The turns out to be exactly the issue. My "platform" library jar included a persistence.xml file that turns on show_sql, and isn't overridden at run time or Spring wire-up time. – Incept

Also look for @DataJpaTest(showSql = true) annotation on a "main" class. That's something out of Spring Boot though: org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest – Prevision

Answer 2

<property name="jpaProperties">
    <props>
        <prop key="hibernate.show_sql">false</prop>
    </props>
</property>

this will also work

Fluorocarbon

Answer 3

As far as I know, Hibernate will also log SQL statements if logging for org.hibernate.SQL happens at DEBUG or ALL level, so you could try disabling that (for example with log4j.logger.org.hibernate.SQL=info when using Log4J).

Hitchhike

Thanks, unfortunately I already have org.hibernate.SQL logging set to ERROR in log4j. :( – Incept

Answer 4

If you are using spring make sure that you don't have the showSql property set to true

I was doing this myself

<bean id="vendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="showSql" value="false"/>
</bean>

Jukebox

Answer 5

add this to your log4j.properties

log4j.logger.org.hibernate = WARN

Forfend

Answer 6

This worked for me:

spring.jpa.show-sql=false

Grout

Correction: Add the following to your application.properties: logging.level.org.hibernate.SQL=OFF – Grout

If you have a correction, make that edit to your post, don't just throw it in as a comment. – Rabid

Your answer does not work for me in the brand new era of Spring in 2020. I added this and it worked. "spring.jpa.properties.hibernate.show_sql=false" – Luciferin

Answer 7

Here are three ways (they are probably others) to show or hide hibernate sql queries :

1. In logger config (level DEBUG for logback) :

   <logger name="org.hibernate.SQL" level="DEBUG" />
   

2. In Spring config (for instance in java config) :

   @Bean
   public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
   final LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
   //...
   final Properties jpaProperties = new Properties(); // java.util
   //TODO replace "true" by a variable
   jpaProperties.setProperty("hibernate.show_sql", "true"); 
   lef.setJpaProperties(jpaProperties);
   return lef;
   }
   

3. In persistence.xml

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

It seems that the logger configuration is independant from the persistence configuration. As for the persistence configuration it appears that spring overrides the persistence.xml (I tested it with a persistence.xml loaded by spring, if this is not the case I don't know what will be the behavior).

A difference between the two configurations is the result logging, with logback it will look like this :

2016-08-25 16:05:39,436 DEBUG org.hibernate.SQL(92) - alter table ...

With the persistence configuration :

Hibernate: alter table ...

Infirmity

Actually the question was about how to stop SQL logging. – Fatling