Hibernate SQL statements are not getting logged in separate log file using log4j in spring boot application

Question

I have a web application in spring boot + Hibernate + Log4j deployed in JBOSS. All the hibernate sql queries are getting logged in server.log if we use "spring.jpa.show-sql = true" in application.properties. As per the Hibernate Log4j instruction we need to turn this property to false so that sql statements will get logged in separate log file as per Log4j configuration. If we turn it to false then also sql statements are not logged in separate log file. All other application related logs are getting logged in separate log file except hibernate logs. Please help to get this issue resolved.

Below is the entry in application.properties file.
spring.jpa.show-sql = false
spring.jpa.open-in-view=false
spring.datasource.ucm.default-auto-commit=false
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.properties.hibernate.id.new_generator_mappings=false  
spring.jackson.serialization.fail-on-empty-beans=false

# SQL statements and parameters
log4j.logger.org.hibernate.SQL=debug
log4j.logger.org.hibernate.type.descriptor.sql=trace

Log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration  monitorInterval="30">

<Properties>
    <Property name="LOG_PATTERN">%d{yyyy-MM-dd'T'HH:mm:ss.SSSZ} %p %m%n
    </Property>
    <Property name="APP_LOG_ROOT">c:/log/VendorPortal</Property>
</Properties>

<Appenders>

    <Console name="console" target="SYSTEM_OUT" follow="false">
        <PatternLayout pattern="${LOG_PATTERN}" />
    </Console>

    <!-- File Appender -->
    <RollingFile name="RollingFile"
        fileName="c:/log/vendorportal.log"
        filePattern="c:/log/vendorportal-%d{MM-dd-yyyy}.log"
        ignoreExceptions="false">
        <PatternLayout>
            <Pattern>%d %p %c{1.} [%t] %m%n</Pattern>
        </PatternLayout>
        <Policies>
            <TimeBasedTriggeringPolicy />
            <SizeBasedTriggeringPolicy size="100 MB" />
        </Policies>
    </RollingFile>

    <Console name="STDOUT" target="SYSTEM_OUT"
        ignoreExceptions="false">
        <PatternLayout pattern="%m%n" />
    </Console>

</Appenders>

<Loggers>

    <Logger name="org.springframework" additivity="false"
        level="error">
        <AppenderRef ref="RollingFile" />
    </Logger>

    <Logger name="org.hibernate" level="info" additivity="false">
        <AppenderRef ref="RollingFile" />
    </Logger>

    <Logger name="org.hibernate.SQL" level="DEBUG"
        additivity="false">
        <AppenderRef ref="RollingFile" />
    </Logger>

    <Logger name="org.hibernate.type.descriptor.sql" level="TRACE"
        additivity="false">
        <AppenderRef ref="RollingFile" />
    </Logger>

    <Root level="error">
        <AppenderRef ref="console" />
        <AppenderRef ref="RollingFile" />
    </Root>

</Loggers>

</Configuration>

pom.xml

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
             <artifactId>log4j-core</artifactId>

        </dependency>
    <dependency>
         <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
     <exclusions>
            <exclusion>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
            </exclusion>
        </exclusions>
</dependency>

soorapadman · Accepted Answer · 2020-02-25 10:05:04Z

1

You need place Log4j config file in our application. Then insert Logger tag in it:

<Configuration status="WARN" strict="true">
<Properties>
    <Property name="filename">logfile.log</Property>
</Properties>
<Appenders>
    <Appender type="Console" name="Console" target="SYSTEM_OUT">
        <Layout type="PatternLayout">
            <Pattern>%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n</Pattern>
        </Layout>
    </Appender>
    <Appender type="File" name="FileAppender" fileName="${filename}">
        <Layout type="PatternLayout">
            <Pattern>%d %p %C{1.} [%t] %m%n</Pattern>
        </Layout>
    </Appender>
</Appenders>
<Loggers>
    <Logger name="org.hibernate.SQL" level="debug" additivity="false">
        <AppenderRef ref="Console"/>
    </Logger>
    <Logger name="org.hibernate.type.descriptor.sql" level="trace" additivity="false">
        <AppenderRef ref="Console"/>
    </Logger>..........
......
....

Ya ALI

edited Feb 25, 2020 at 10:05

soorapadman

4,5177 gold badges38 silver badges49 bronze badges

answered Feb 24, 2020 at 9:36

محسن عباسی

2,51433 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Felsia Thyagaraj Over a year ago

Thanks for your reply. I am already having log4j2.xml with the above configuration. Still hibernate log alone is not getting displayed in log file getting created

Cherry Over a year ago

I am having similar problem. @Felsia How did you fix it?

Omid Over a year ago

Ali Yaret Soltan

محسن عباسی Over a year ago

chakeram......YA ALI.....

The Rocket · Accepted Answer · 2021-10-27 10:00:18Z

0

As per your query posted, your configuration seems to be corrected.

FROM:

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

<Logger name="org.hibernate.type.descriptor.sql" level="TRACE"
    ....
</Logger>

<Root level="error">
    ....
</Root>

TO:

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

<Logger name="org.hibernate.type.descriptor.sql" level="TRACE"
    ....
</Logger>

<Root level="DEBUG">
    ....
</Root>

As the logging levels are of priority => TRACE > DEBUG > INFO > ERROR > WARN > FATAL

Hope this helps to resolve the issue.

answered Oct 27, 2021 at 10:00

The Rocket

819 bronze badges

Collectives™ on Stack Overflow

Hibernate SQL statements are not getting logged in separate log file using log4j in spring boot application

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related