Spring Boot JDBC Template SQL Log

Question

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>

Iguana

Is this else {log.info("No records found:"+rs);return null; } ever executed ? – Westleigh

@Westleigh yes it is printing in the logs. – Iguana

try #18828133 – Polymorphous

Answer 1

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!

Buzzell

I created one more question related to this please answer if you can thanks.#49146667 – Iguana

this way not work for spring boot 2.1.4 . Should be this: logging.level.org.springframework.jdbc.core=TRACE – Swag

@Swag I do not see any sql statements traced in the console, placed this in the application.yml file logging.level.org.springframework.jdbc.core: TRACE – Griselgriselda

which file name should this go in ? we are using maven logging.level.org.springframework.jdbc.core=TRACE. – Griselgriselda

Answer 2

Adding the following to your properties file also works:

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

Birdt

Answer 3

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.

Grumpy

Answer 4

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

Theodoratheodore