Only show effective SQL string P6Spy

Question

I'm using p6spy to log the sql statements generated by my program. The format for the outputted spy.log file looks like this:

current time|execution time|category|statement SQL String|effective SQL string

I'm just wondering if anyone knows if there's a way to alter the spy.properties file and have only the last column, the effective SQL string, output to the spy.log file? I've looked through the properties file but haven't found anything that seems to support this.

Thanks!

Answer 1

In spy.properties there is a property called logMessageFormat that you can set to a custom implementation of MessageFormattingStrategy. This works for any type of logger (i.e. file, slf4j etc.).

E.g.

logMessageFormat=my.custom.PrettySqlFormat

An example using Hibernate's pretty-printing SQL formatter:

package my.custom;

import org.hibernate.jdbc.util.BasicFormatterImpl;
import org.hibernate.jdbc.util.Formatter;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;

public class PrettySqlFormat implements MessageFormattingStrategy {

    private final Formatter formatter = new BasicFormatterImpl();

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        return formatter.format(sql); 
    }

}

Answer 2

There is no such option provided to achieve it via configuration only yet. I think you have 2 options here:

• fill a new bug/feature request report (which could bring benefit to others using p6spy as well) on: https://github.com/p6spy/p6spy/issues?state=open or
• provide custom implementation.

For the later option, I believe you could achieve it via your own class (depending on the logger you use, let's assume you use Log4jLogger).

Well, if you check relevant part of the Log4jLogger github as well as sourceforge version, your implementation should be rather straightforward:

spy.properties:

appender=com.EffectiveSQLLog4jLogger

Implementation itself could look like this:

package com;

import com.p6spy.engine.logging.appender.Log4jLogger;

public class EffectiveSQLLog4jLogger extends Log4jLogger {

  public void logText(String text) {
    super.logText(getEffectiveSQL(text));
  }

  private String getEffectiveSQL(String text) {
    if (null == text) {
      return null;
    }

    final int idx = text.lastIndexOf("|");

    // non-perfect detection of the exception logged case
    if (-1 == idx) {
      return text;
    }

    return text.substring(idx + 1); // not sure about + 1, but check and see :)
  }
}

Please note the implementation should cover github (new project home, no version released yet) as well as sourceforge (original project home, released 1.3 version).

Please note: I didn't test the proposal myself, but it could be a good starting point and from the code review itself I'd say it could work.

Answer 3

I agree with @boberj, we are used to having logs with Hibernate formatter, but don't forget about batching, that's why I suggest to use:

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;

/**
 * Created by Igor Dmitriev on 1/3/16
 */
public class HibernateSqlFormatter implements MessageFormattingStrategy {

    private final Formatter formatter = new BasicFormatterImpl();

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        if (sql.isEmpty()) {
            return "";
        }
        String template = "Hibernate: %s %s {elapsed: %sms}";
        String batch = "batch".equals(category) ? ((elapsed == 0) ? "add batch" : "execute batch") : "";
        return String.format(template, batch, formatter.format(sql), elapsed);
    }
}

Answer 4

In p6Spy 3.9 this can be achieved quite simply. In spy.properties set

customLogMessageFormat=%(effectiveSql)

Answer 5

You can patch com.p6spy.engine.spy.appender.SingleLineFormat.java removing the prepared element and any reference to P6Util like so:

package com.p6spy.engine.spy.appender;
public class SingleLineFormat implements MessageFormattingStrategy {
  @Override
  public String formatMessage(final int connectionId, final String now, final long elapsed, final String category, final String prepared, final String sql) {
    return now + "|" + elapsed + "|" + category + "|connection " + connectionId + "|" + sql;
  }
}

Then compile just the file javac com.p6spy.engine.spy.appender.SingleLineFormat.java

And replace the existing class file in p6spy.jar with the new one.