print complete query of PreparedStatement with loggers

Question

How to print complete query of PreparedStatement with loggers?

Using JDBC Driver as: com.microsoft.sqlserver.jdbc.SQLServerDriver

log4.properties contains
log4j.appender.logfile=org.apache.log4j.RollingFileAppender
log4j.appender.logfile.File=logs/ProductDataLibrary.log
log4j.appender.logfile.MaxFileSize=10240KB
log4j.appender.logfile.MaxBackupIndex=0
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{DATE}~ %5p ~ %C:%L ~ %x ~ %m%n

I have tried with preparedstatement.toString() method, but it is printing some @object not the complete SQL query ?

Why is your question tagged mysql, when you talk about the SQL Server driver? — Mark Rotteveel, Dec 05 '13 at 10:25
http://stackoverflow.com/questions/2382532/how-can-i-get-the-sql-of-a-preparedstatement — Prabhakaran Ramaswamy, Dec 05 '13 at 10:25
possible duplicate of [Get query from java.sql.PreparedStatement](http://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement) — Rahul Singh, Dec 05 '13 at 10:27

lance-java · Answer 1 · 2013-12-05T12:07:56.953

If you're using an abstraction layer (such as hibernate) you are probably best to use their built in logging.

If you want to roll your own, you should wrap the java.sql.Connection using the delegate pattern. Eg:

public class WrappedConnection implements Connection {
    private Connection delegate;
    public WrappedConnection(Connection delegate) { this.delegate = delegate; }
    public PreparedStatement prepareStatement(String sql) {
       PreparedStatement ps = delegate.prepareStatement(sql);
       return new WrappedPreparedStatement(sql, ps);
    }
    // delegate all Connection methods to the delegate
}

public class WrappedPreparedStatement implements PreparedStatement {
    private String sql;
    private PreparedStatement delegate;
    public WrappedPreparedStatement(String sql, PreparedStatement delegate) { 
       this.sql = sql; 
       this.delegate = delegate;
    }
    public String toString() {
        return sql;
    }
    public ResultSet executeQuery() throws SQLException {
        // perhaps you might want to do some logging here?
        return delegate.executeQuery();
    }
    // delegate all PreparedStatement methods to the delegate
}

Using this approach, you'll likely wrap Connection.createStatement(*) and Connection.prepareCall(*) too.

edit

It looks like someone's already done the hard work for you, take a look at log4jdbc

vzamanillo · Answer 2 · 2013-12-05T15:05:38.353

Add DEBUG or TRACE level to java.sql.PreparedStatement in log4j.properties

log4j.logger.java.sql.PreparedStatement=DEBUG

or

log4j.logger.java.sql.PreparedStatement=TRACE

If you want to get a complete JDBC operations logging add DEBUG level to java.sql

log4j.logger.java.sql=DEBUG

or if you want a concrete logging for individual JDBC class add

log4j.logger.java.sql.theclass=DEBUG

an example

log4j.logger.java.sql.Connection=DEBUG    
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG

edit: My mistake, I forgot SQLServer JDBC driver does not use log4j

You cant configure logging via java.util.logging.LogManager with a properties file

handlers= java.util.logging.FileHandler


    # Default global logging level.
    .level= ALL

    # default file output
    java.util.logging.FileHandler.pattern = /tmp/sql.log
    java.util.logging.FileHandler.limit = 5000000
    java.util.logging.FileHandler.count = 20
    java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
    java.util.logging.FileHandler.level = ALL

    # Facility specific properties.
    com.microsoft.sqlserver.jdbc.level=ALL
    com.microsoft.sqlserver.jdbc.Statement.level=ALL
    com.microsoft.sqlserver.jdbc.SQLServerStatement.level=ALL

And get it at runtime

LogManager.getLogManager().readConfiguration(new FileInputStream("c:/tmp/sql.properties"));

More info

Tracing Driver Operation

print complete query of PreparedStatement with loggers

2 Answers2