Spring boot jpa not printing sql logs

user_5358640 0
Viewed 7513

I am trying to print the sql statements in log file but it is not working.

Below is application-property file:

custom.datasource.jdbc-url=jdbc:mysql://127.0.0.1:3306/custom_service?useSSL=false
custom.datasource.username=root
custom.datasource.password=paytm@123
custom.datasource.driverClassName=com.mysql.jdbc.Driver
custom.hibernate.dialect=org.hibernate.dialect.MySQLDialect
custom.hibernate.hbm2ddl.auto=none
##show sql statement
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace

Log4j root level is set to debug mode. What is wrong here?

Log4j Config as below:

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


<DynamicThresholdFilter key="x-debug-enabled" onMatch="ACCEPT" onMismatch="NEUTRAL">
    <KeyValuePair key="true" value="DEBUG"/>
</DynamicThresholdFilter>

<Appenders>
    <Console name="STDOUT" target="SYSTEM_OUT">
        <PatternLayout pattern="[%level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} requestId: %X{requestId}, customerId: %X{customerId} - %msg%n"></PatternLayout>
    </Console>
    <File name="File" fileName="/var/log/custom-onboarding-service/custom-onboarding-service.log" ignoreExceptions="false">
        <PatternLayout pattern="[%level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} requestId: %X{requestId}, customerId: %X{customerId} - %msg%n"></PatternLayout>
    </File>
    <File name="FileException" fileName="/var/log/custom-onboarding-service/custom-onboarding-service-error.log" ignoreExceptions="false">
        <PatternLayout pattern="[%level] %d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} requestId: %X{requestId}, customerId: %X{customerId} - %msg%n"></PatternLayout>
    </File>
    <RollingFile name="customOnboarding" fileName="/var/log/custom-onboarding-service/custom-onboarding-service-audit.log"
                 filePattern="/var/log/custom-onboarding-service/custom-onboarding-service-audit-%d{yyyy-MM-dd}-%i.log">
        <PatternLayout>
            <Pattern>%m%n</Pattern>
        </PatternLayout>
        <Policies>
            <SizeBasedTriggeringPolicy size="250 MB"/>
        </Policies>
    </RollingFile>
</Appenders>

<Loggers>
    <Root level="debug">
        <AppenderRef ref="FileException" level="ERROR"></AppenderRef>
    </Root>
    <Logger name="com.nv.custom" level="debug" additivity="false">
        <AppenderRef ref="File" />
        <AppenderRef ref="FileException" level="ERROR"></AppenderRef>
    </Logger>
    <Logger name="JSON_LOGGER" level="info" additivity="false">
        <AppenderRef ref="customOnboarding" />
    </Logger>
</Loggers>

Below is the custom DB Config File used to configure the spring JPA Configs:

Custom DB Config File:

 @PersistenceContext(unitName = "custom")
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        @Qualifier("customDataSource") DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    factoryBean.setPersistenceUnitName("custom");
    factoryBean.setDataSource(dataSource);
    factoryBean.setPackagesToScan("com.nv.custom.db.mktcustom.entities","com.nv.custom.onboarding.entity");
    Properties jpaProperties = new Properties();
    jpaProperties.put("hibernate.dialect", env.getRequiredProperty("custom.hibernate.dialect"));
    jpaProperties.put("hibernate.hbm2ddl.auto", env.getRequiredProperty("custom.hibernate.hbm2ddl.auto"));
    jpaProperties.put("spring.jpa.properties.hibernate.format_sql", env.getRequiredProperty("custom.hibernate.show_sql"));
    jpaProperties.put("spring.jpa.properties.hibernate.show_sql", env.getRequiredProperty("custom.hibernate.format_sql"));
    factoryBean.setJpaProperties(jpaProperties);
    return factoryBean;
}
log4j2 spring-boot jpa
3 Answers

To show sql query in spring-boot u need to set below properties.

spring.jpa.show-sql=true

you using custom properties for db configuration. then add required properties in your custom configuration method.

    custom.hibernate.show_sql=true
    custom.hibernate.format_sql=true

Configuration for above two properties:

@PersistenceContext(unitName = "custom")
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        @Qualifier("customDataSource") DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    factoryBean.setPersistenceUnitName("custom");
    factoryBean.setDataSource(dataSource);
    factoryBean.setPackagesToScan("com.nv.custom.db.mktcustom.entities","com.nv.custom.onboarding.entity");
    Properties jpaProperties = new Properties();
    jpaProperties.put("hibernate.dialect", env.getRequiredProperty("custom.hibernate.dialect"));
    jpaProperties.put("hibernate.hbm2ddl.auto", env.getRequiredProperty("custom.hibernate.hbm2ddl.auto"));
    jpaProperties.put("hibernate.format_sql", env.getRequiredProperty("custom.hibernate.show_sql"));
    jpaProperties.put("hibernate.show_sql", env.getRequiredProperty("custom.hibernate.format_sql"));
    factoryBean.setJpaProperties(jpaProperties);
    return factoryBean;
}
user_68312570

In your configuration you have the root logging level as debug. But then you set the level on the FileException Appender to error, so only errors are going to be logged to that file and you have no other Appenders configured for the root logger.

If you want to print logs from Hibernate add a Logger such as 

<Logger name="org.hibernate" level="debug" additivity="false">
  <AppenderRef ref="File"/>
</Logger>
user_17092160

Please configure the below in the log4j2.xml

    <RollingFile fileName="${LOG-DIR}/SQL.log" filePattern="${LOG-DIR}/${SQL-LOG-ROLLING-FILE-NAME}" name="APPENDER1">
        <LevelRangeFilter maxLevel="TRACE" minLevel="DEBUG" onMatch="ACCEPT" onMismatch="DENY"/>
        <PatternLayout pattern="${APP-LOG-PATTERN}"/>
        <Policies>
            <TimeBasedTriggeringPolicy interval="1" modulate="true"/>
            <SizeBasedTriggeringPolicy size="2 GB"/>
        </Policies>
        <DefaultRolloverStrategy max="100"/>
    </RollingFile>
</Appenders>


    <logger additivity="false" level="DEBUG" name="org.hibernate.SQL">
        <appender-ref ref="APPENDER1"/>
    </logger>
    
    <Root level="INFO">
        <AppenderRef ref="OTHER_APPENDER"/> 
    </Root>
user_64973460
Related