Logging prepared sql statements in postgres jdbc driver

Question

I want to log all prepared sql statements in my java application. I'm using the standard postgres jdbc driver org.postgresql.Driver. This driver has a parameter called " loglevel" which can be set to 1 (INFO) or 2 (DEBUG). The point is if the parameter is set to 1 it's almost logging nothing, if set to 2 it's tracing too much like

...
20:59:05.608 (2)  FE=> Bind(stmt=null,portal=null,$1=<'5'>,$2=<'13'>)
20:59:05.609 (2)  FE=> Describe(portal=null)
20:59:05.609 (2)  FE=> Execute(portal=null,limit=1)  
20:59:05.609 (2)  FE=> Sync
20:59:05.648 (2)  <=BE ParseComplete [null]
20:59:05.649 (2)  <=BE BindComplete [null]
20:59:05.649 (2)  <=BE NoData
20:59:05.649 (2)  <=BE CommandStatus(UPDATE 1)
...

Is there a way to only log the statements + parameters?

BalusC · Accepted Answer · 2011-08-25 19:24:43Z

10

You're lucky that you're using PostgreSQL. The PreparedStatement implementation of the PostgreSQL JDBC driver (at least, since 8.x or something) has its toString() overridden that way so you could see the entire SQL statement with all parameters filled in the right places. So you could just do something like:

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, value1);
preparedStatement.setString(2, value2);
// ...
logger.debug(preparedStatement); // Will show entire SQL with all values.

(where logger is just your logger, e.g. slf4j/logback or something)

answered Aug 25, 2011 at 19:24

BalusC

1.1m377 gold badges3.7k silver badges3.6k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pointy Over a year ago

I'm not seeing this in the 9.1 version of the driver.

Sofia Pahaoja Over a year ago

Works for me with 9.3.

Thilo Over a year ago

If you have access to the application code, yes. Any idea how to log the statement for third-party code that you cannot change (but configure logging)?

Collectives™ on Stack Overflow

Logging prepared sql statements in postgres jdbc driver

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related