How to enable logging for SQL statements when using JDBC

If you are using Spring framework, then the datasource-proxy framework is very convenient. You can basically wrap around any DataSource and just add the logging behavior.

If you're using Java EE, then P6spy is a good alternative:

Behind the scenes, p6spy provides the statement interceptor at the Driver level, which is much more convenient for Java EE applications because the DataSource is provided by the application server.

Assuming you're using Log4j, just do this:

Use ojdbc_g as mentioned in Oracle JDBC Developer's Guide: Diagnosability in JDBC
Note, the 19.3 drivers are in Maven now, or just download manually.

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8_g</artifactId>
            <version>19.3.0.0</version>
        </dependency>

You will also need Log4j JDK Logging Adapter

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-jul</artifactId>
            <version>2.13.0</version>
        </dependency>

Set these System properties:
-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager
-Doracle.jdbc.Trace=true

Configure your Loggers in log4j2.xml

        <Logger name="oracle.jdbc" level="TRACE">
            <AppenderRef ref="Console"/>
        </Logger>
        <Logger name="oracle.sql" level="TRACE">
            <AppenderRef ref="Console"/>
        </Logger>

Note that level="ALL" will give the finest level of logging

Very old topic, I know, but what has not been mentioned yet is that for Oracle a solution exists which does not require any change in the application code, just by using the required trace-enabled Oracle JDBC driver and enabling the logging through JVM properties at startup.

Oracle themselves have described this here, and after some trial and error I got it to work:

1. Place the trace-enabled ojdbc jar file in your classpath. Quote from the linked Oracle page: "To get log output, you must use the debug JAR files, which are indicated with a "_g" in the file name, like ojdbc5_g.jar or ojdbc6_g.jar." My Oracle 11g installation contained

2. Create a logging.properties file as described on the linked Oracle page, and adjust the logging levels to your needs. Example:

   .level=SEVERE oracle.jdbc.level=FINEST oracle.jdbc.handlers=java.util.logging.FileHandler java.util.logging.FileHandler.level=FINEST java.util.logging.FileHandler.pattern=jdbc.log java.util.logging.FileHandler.count=1 java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter

3. Add the JVM properties "-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=logging.properties" to the java startup command for your JDBC application.

The JDBC application should now produce a file named jdbc.log which should contain the desired information. In some cases it can be necessary to specify the full path to the logging.properties file.

2019 update: log4jdbc has not been maintained since 2015. p6spy still seems to be actively maintained.

Original answer

There are lot of Spy frameworks available for this purpose , please check log4jdbc , I fell this is what you are looking for.

Features

• Full support for JDBC 3 and JDBC 4!
• Easy to configure, in most cases all you need to do is change the driver class name to net.sf.log4jdbc.DriverSpy and prepend "jdbc:log4" to your existing jdbc url, set up your logging categories and you're ready to go!
• In the logged output, for prepared statements, the bind arguments are automatically inserted into the SQL output. This greatly Improves readability and debugging for many cases.
• SQL timing information can be generated to help identify how long SQL statements take to run, helping to identify statements that are running too slowly and this data can be post processed with an included tool to produce profiling report data for quickly identifying slow SQL in your application.
• SQL connection number information is generated to help identify connection pooling or threading problems. Works with any underlying JDBC driver, with JDK 1.4 and above, and SLF4J 1.x.
• Open source software, licensed under the business friendly Apache 2.0 license

Usage

• Place the log4jdbc jar (based on the JDK version) into your application's classpath.
• choose logging system to use, log4j, logback, commons logging..etc are supported
• Set your JDBC driver class to net.sf.log4jdbc.DriverSpy in your application's configuration. The underlying driver that is being spied on in many cases will be loaded automatically without any additional configuration.

• Prepend jdbc:log4 to the normal jdbc url that you are using.

  For example, if your normal jdbc url is jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase then You would change it to: jdbc:log4jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase

• Set up your loggers.

  jdbc.sqlonly: Logs only SQL. SQL executed within a prepared statement is automatically shown with it's bind arguments replaced with the data bound at that position, for greatly increased readability. 1.0

  jdbc.sqltiming: Logs the SQL, post-execution, including timing statistics on how long the SQL took to execute. 1.0

  jdbc.audit: Logs ALL JDBC calls except for ResultSets. This is a very voluminous output, and is not normally needed unless tracking down a specific JDBC problem. 1.0

  jdbc.resultset: Even more voluminous, because all calls to ResultSet objects are logged. 1.0

  jdbc.connection: Logs connection open and close events as well as dumping all open connection numbers. This is very useful for hunting down connection leak problems.