How to Log SQL Statements in Spring Boot

Spring Boot is a popular framework for building Java-based applications. One of the features it provides is the ability to easily configure and manage a variety of data sources. However, when working with databases, it can be useful to log the SQL statements that are executed by your application. In this blog post, we will show you how to do this in a Spring Boot application.



Log SQL Statement

First, you will need to add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Next, you will need to create a new configuration file called logging.properties in the src/main/resources directory of your project. In this file, you will need to add the following line:

logging.level.org.hibernate.SQL=debug

This will configure the logging level for the Hibernate SQL logger to be set to debug. This means that any SQL statements executed by Hibernate will be logged at the debug level.

Now, you can run your application and you should see the SQL statements being logged in the console. However, if you want to output the logs to a file, you can do that by modifying the logging.properties file.

logging.file=sql.log
logging.level.org.hibernate.SQL=debug

This will send the logs to a file called sql.log in the root of your project.

In addition to logging SQL statements, you can also log other information, such as the execution time of each statement. To do this, you will need to add the following line to the logging.properties file:

logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n

This will configure the console output to include the date and time, the log level, the logger, and the message.

In case you want to log SQL statement for specific DataSource you can use

@Bean
public DataSource dataSource() {
    DataSource dataSource = new DataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/test");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    dataSource.setConnectionProperties("logging=true;");
    return dataSource;
}

Here we are setting logging property of DataSource to true.



Solution 1

Another way to log SQL statements in Spring Boot is to use the Spring JDBC template. The JDBC template provides a convenient way to interact with databases and it can be configured to log SQL statements. To use the JDBC template, you will need to add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
</dependency>

Once you have added the dependency, you can create a new instance of the JDBC template by injecting the DataSource. Here is an example of how to do this:

@Autowired
private DataSource dataSource;

@Bean
public JdbcTemplate jdbcTemplate() {
    return new JdbcTemplate(dataSource);
}

Now you can use the JDBC template to execute SQL statements. To log the SQL statements, you will need to configure the logging level for the org.springframework.jdbc.core.JdbcTemplate logger. To do this, add the following line to your logging.properties file:

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

This will configure the JDBC template to log SQL statements at the debug level. You should now see the SQL statements being logged in the console.



Solution 2

Another way to log SQL statements is to use a logging interceptor. A logging interceptor is a class that can be configured to intercept the execution of SQL statements and log them. To use a logging interceptor, you will need to create a new class that implements the org.springframework.jdbc.core.JdbcInterceptor interface. Here is an example of a simple logging interceptor:

public class LoggingInterceptor implements JdbcInterceptor {
    private Logger logger = LoggerFactory.getLogger(LoggingInterceptor.class);

    @Override
    public void afterPropertiesSet() {
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        Statement stmt = (Statement) args[0];
        String sql = stmt.toString();
        logger.debug("Executing SQL statement: {}", sql);
        return invocation.proceed();
    }
}


Solution 3

This class intercepts the execution of SQL statements and logs them using the logger. To use the logging interceptor, you will need to add it to the JDBC template. Here is an example of how to do this:

@Autowired
private LoggingInterceptor loggingInterceptor;

@Bean
public JdbcTemplate jdbcTemplate() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.setInterceptors(new Object[] { loggingInterceptor });
    return jdbcTemplate;
}

In this example, we are adding the logging interceptor to the JDBC template. Now, when the JDBC template is used to execute SQL statements, the logging interceptor will be invoked and the SQL statements will be logged.



Leave a Reply