CODEWITHSUNDEEP
javaspring-bootlog4jspring-jdbc
sunleo
sunleo

Reputation: 10943

Spring Boot JDBC Template SQL Log

I am trying log SQL queries with params for Spring Boot JDBC but it is not printing the details in log.I am using Spring Boot 1.5.8 version.Please help me to solve this.

application.properties:

spring.datasource.url=url
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

logging.level.org.springframework.jdbc.core.JdbcTemplate=debug

spring.datasource.type = com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=2

Repository:

@Repository
public class DataRepository {
    private static Logger log = LoggerFactory.getLogger(DataRepository.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public Data findDataObjet() throws Exception {

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("id1", "mike");
        parameters.put("id2", new Long(1));

        String sqlString = "select * from table1 where id1 = ":id" and id2 = :id2";
        log.info("Query:" + sqlString);//this log is printing

        Data extObj = jdbcTemplate.query(sqlString, parameters, (rs) -> {
            if (rs != null && rs.next()) {
                Data innerObj = new Data();
                innerObj.setName(rs.getString("name"));             
                return innerObj;
            } else {
                log.info("No records found:"+rs);
                return null;
            }
        });

        return extObj;

    }
}

logback-spring.xml:

<appender name="dailyRollingFileAppender"
    class="ch.qos.logback.core.rolling.RollingFileAppender">
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <FileNamePattern>${logsPath}DATA%d{MMddyyyy}.log
        </FileNamePattern>
        <maxHistory>4</maxHistory>
    </rollingPolicy>

    <encoder>
        <Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level
            %logger{35}-%msg %n</Pattern>
    </encoder>
</appender>
<root level="INFO">
    <appender-ref ref="dailyRollingFileAppender" />
</root>

Upvotes: 19

Views: 60428

Answers (4)

CGS
CGS

Reputation: 2872

Try

log4j.category.org.springframework.jdbc.core = TRACE

The above statement will print SQL queries with inbound parameters as well.

Incase you need to log only the query use the following

log4j.category.org.springframework.jdbc.core = DEBUG

You can enable in your logback file with the following

<logger name="org.springframework.jdbc.core.JdbcTemplate">
  <level value="debug" />
</logger>

<logger name="org.springframework.jdbc.core.StatementCreatorUtils">
  <level value="debug" />
</logger>

Update : For Springboot 2.x , it would be 

logging.level.org.springframework.jdbc.core=TRACE

Thanks zhuguowei!

Upvotes: 55

the hand of NOD
the hand of NOD

Reputation: 1769

Since Spring-Boot 2.1.x you have to set the property: logging.level.org.springframework.jdbc.core=TRACE to log the statement and the parameters.

Upvotes: 1

Pouriya Zarbafian
Pouriya Zarbafian

Reputation: 528

Adding the following to your properties file also works:

logging.level.org.springframework.jdbc.core = TRACE

Upvotes: 9

Elarbi Mohamed Aymen
Elarbi Mohamed Aymen

Reputation: 1700

try those statement in application properties

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.format_sql = true

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Upvotes: -4

Related Questions