Hibernate sql query format in console

Question

I'm trying to log the sql queries i'm calling from a .xml file. The problem i'm facing is that when i see the log, it's not well formatted. Also, i don't know why it appears repeated...

[main] INFO org.dozer.DozerBeanMapper - Initializing a new instance of dozer bean mapper. [main] INFO org.dozer.DozerBeanMapper - Initializing a new instance of dozer bean mapper. [main] INFO org.springframework.orm.hibernate5.HibernateTransactionManager - Using DataSource [org.apache.commons.dbcp2.BasicDataSource@1f03fba0] of Hibernate SessionFactory for HibernateTransactionManager [main] INFO org.springframework.aop.framework.CglibAopProxy - Unable to proxy method [public final void com.servicios.test.TestRestauranteManager.findProveedoresByIdRestaurante() throws com.servicios.util.exceptions.AlergenosException] because it is final: All calls to this method via a proxy will NOT be routed to the target instance. [main] INFO org.springframework.test.context.transaction.TransactionContext - Began transaction (1) for test context [DefaultTestContext@436bd4df testClass = TestRestauranteManager, testInstance = com.servicios.test.TestRestauranteManager@6848a051, testMethod = findProveedoresByIdRestaurante@TestRestauranteManager, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@149b0577 testClass = TestRestauranteManager, locations = '{classpath:test-applicationContext.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]; transaction manager [org.springframework.orm.hibernate5.HibernateTransactionManager@5740ff5e]; rollback [true] select restaurant0_."ID_RESTAURANTE" as ID_RESTA1_4_0_, restaurant0_."DESCRIPCION" as DESCRIPC2_4_0_, restaurant0_."ID_CADENA_RESTAURANTE" as ID_CADEN3_4_0_ from "RESTAURANTE" restaurant0_ where restaurant0_."ID_RESTAURANTE"=? Hibernate: select restaurant0_."ID_RESTAURANTE" as ID_RESTA1_4_0_, restaurant0_."DESCRIPCION" as DESCRIPC2_4_0_, restaurant0_."ID_CADENA_RESTAURANTE" as ID_CADEN3_4_0_ from "RESTAURANTE" restaurant0_ where restaurant0_."ID_RESTAURANTE"=? binding parameter [1] as [BIGINT] - [0]

I would like to have something like

[main] INFO org.dozer.DozerBeanMapper - Initializing a new instance of dozer bean mapper. [main] INFO org.dozer.DozerBeanMapper - Initializing a new instance of dozer bean mapper. [main] INFO org.springframework.orm.hibernate5.HibernateTransactionManager - Using DataSource [org.apache.commons.dbcp2.BasicDataSource@1f03fba0] of Hibernate SessionFactory for HibernateTransactionManager [main] INFO org.springframework.aop.framework.CglibAopProxy - Unable to proxy method [public final void com.servicios.test.TestRestauranteManager.findProveedoresByIdRestaurante() throws com.servicios.util.exceptions.AlergenosException] because it is final: All calls to this method via a proxy will NOT be routed to the target instance. [main] INFO org.springframework.test.context.transaction.TransactionContext - Began transaction (1) for test context [DefaultTestContext@436bd4df testClass = TestRestauranteManager, testInstance = com.servicios.test.TestRestauranteManager@6848a051, testMethod = findProveedoresByIdRestaurante@TestRestauranteManager, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@149b0577 testClass = TestRestauranteManager, locations = '{classpath:test-applicationContext.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]; transaction manager [org.springframework.orm.hibernate5.HibernateTransactionManager@5740ff5e]; Hibernate: select p."DESCRIPCION" as "descripcion", p."ID_PROVEEDOR" as "idProveedor"
          from
              "RESTAURANTE_PROVEEDOR" rp
              inner join
                  "PROVEEDOR" p 
                      on rp."ID_PROVEEDOR" = p."ID_PROVEEDOR"
          where 
              rp."ID_RESTAURANTE" = ? binding parameter [1] as [BIGINT] - [0]

My log4j.properties is the following:

log4j.rootLogger=INFO, stdout
log4j.logger.org.hibernate=INFO
log4j.logger.org.hibernate.SQL=TRACE
log4j.logger.org.hibernate.type=ALL
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.rootConsola.layout.ConversionPattern=(%d{dd/MM/yyyy-HH:mm:ss}) %-5p: %-40c{1} - %m%n

And in my ApplicationContext.xml I have this

...
<bean id="sessionFactory"
        class="org.springframework.orm.hibernate5.LocalSessionFactoryBean"
        p:dataSource-ref="dataSource">
        <property name="packagesToScan" value="com.servicios.vo"/>
        <property name="mappingLocations">
            <list>
                <value>classpath*:hibernate/queries/**/*.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
                <prop key="hibernate.format_sql">true</prop>
            </props>
        </property>
    </bean> 
...

Thanks in advance!!

Naros Naros · Accepted Answer · 2017-01-31T23:09:27

The hibernate.format_sql setting does not apply to Exception reasons when something happens and an Exception is thrown that includes a SQL fragment.

In other words, hibernate.format_sql only applies when Hibernate writes the SQL it's about to execute to the logs, not when it's included as part of some Exception reason.

Hibernate sql query format in console

1 Answers