How to log failed sql in hibernate?

Question

I'm building a SpringBoot application with spring-data-jpa. I know how to log all sqls from this question. How to print a query string with parameter values when using Hibernate

But what if I only want to log failed sqls?

Answer 1

There are two options:

1. Configure sql logging with parameter values. Disable jdbc batching and enable flushing through hibernate means.
2. Add debug JDBC driver such as p6spy that will more or less do exactly as described above.

First, let's analyze the problem and split the query types into SELECT and INSERT/UPDATE queries.

1. SELECT queries for them you have the flushing on by default. So when an error occurs you know exactly which query has failed.
2. INSERT/UPDATE queries, here things get tricky because your flushing will be off and you have query batching which means that first when you run the query it gets delayed. Second, it gets packed up with other unrelated queries, and third, Hibernate may re-order them. So the short answer is that this is not doable for INSERT/UPDATE if you are using hibernate alone.

A solution to your problem needs to do essentially two things: 1. It should log the queries with their parameter values. This can be done the following way:

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug 

# Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace 

2. The solution needs to disable the batching of queries, otherwise, you will get a bunch of SQLs but you will not know which SQL exactly is the problematic one.

hibernate.jdbc.batch_size=1

Not sure if this will be enough to entirely disable the batching, but you need to find out.

Alternatively, you can use a jdbc driver designed for DEBUG. This would be p6spy driver which has the option to flush every single SQL statement which is exactly what you want.

Here you need to set the autoflush=true configuration parameter to ensure every single sql statement is immediately flushed to the database. https://p6spy.readthedocs.io/en/latest/configandusage.html

Answer 2

This gives you multiple log entries. In my case this was not welcome.
Here is my solution for it:

1. dependency net.ttddyy:datasource-proxy:1.6
2. Wrap Datasource in Spring Configuration

    @Bean
    public DataSource dataSource() {
      return ProxyDataSourceBuilder.create(originalDatasource())
             .logQueryBySlf4j(FLF4JLogLevel.DEBUG).build();
    }

3. Write your own LogAppender - here you can filter the logevents, so you can filter i.e for inserts only or failed executions

public class SQLAppender extends AppenderBase
[...]
    @Override
    protected void append(ILoggingEvent eventObject) {
    [...]

    }

4. configure logback.xml

<appender name="mySQLAppender" class="com.my.SQLAppender" >
</appender>
<logger name="net.ttddyy.dsproxy.listener.logging.SLF4JQueryLoggingListener"    level="DEBUG"\>
  <appender-ref ref="mySQLAppender"/>
</logger>