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.