How to enable logging for SQL statements when using JDBC

Question

I am trying to enable logs using my JDBC program by connecting to Oracle database in eclipse IDE.

I have gone through this SO post JDBC logging to file then I have created below java program and running it from my eclipse IDE, but I was not able to see any logs generated by the JDBC driver classes.

import java.io.File;
import java.io.FileInputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.LogManager;
import java.util.logging.Logger;

public class Logging {

    static Logger log = Logger.getLogger(Logging.class.toString());
    static Connection con = null;

    public static void main(String[] args) throws SQLException,
            ClassNotFoundException {
        System.setProperty("oracle.jdbc.Trace", Boolean.TRUE.toString());
        System.setProperty("java.util.logging.config.file",
                "OracleLog.properties");
        log.info("Test Message");
        enableLogging(false);
        getConnection();
        closeConnection();
    }

    static private void enableLogging(boolean logDriver) {
        try {
            oracle.jdbc.driver.OracleLog.setTrace(true);

            // compute the ObjectName
            String loader = Thread.currentThread().getContextClassLoader()
                    .toString().replaceAll("[,=:\"]+", "");
            javax.management.ObjectName name = new javax.management.ObjectName(
                    "com.oracle.jdbc:type=diagnosability,name=" + loader);

            // get the MBean server
            javax.management.MBeanServer mbs = java.lang.management.ManagementFactory
                    .getPlatformMBeanServer();

            // find out if logging is enabled or not
            System.out.println("LoggingEnabled = "
                    + mbs.getAttribute(name, "LoggingEnabled"));

            // enable logging
            mbs.setAttribute(name, new javax.management.Attribute(
                    "LoggingEnabled", true));

            File propFile = new File("path/to/properties");
            LogManager logManager = LogManager.getLogManager();
            logManager.readConfiguration(new FileInputStream(propFile));

            if (logDriver) {
                DriverManager.setLogWriter(new PrintWriter(System.err));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException,
            ClassNotFoundException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", "test_app");
        connectionProps.put("password", "test");

        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection(
                "jdbc:oracle:thin:@"+HOST_IP+":1521:"+SID,
                connectionProps);
        System.out.println("Connected to database");
        return con;
    }

    public static void closeConnection() throws SQLException {
        if (con != null) {
            con.close();
        }
    }

}

and I have below content in my OracleLog.properties file:

.level=SEVERE
oracle.jdbc.level=INFO
oracle.jdbc.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=INFO
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

But when I run my program by placing ojdbc6-11.2.0.3.jar in classpath then I am getting exception as:

INFO: Test Message
javax.management.InstanceNotFoundException: com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@73d16e93
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1095)
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:643)
    at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:678)
    at myjdbc.Logging.enableLogging(Logging.java:45)
    at myjdbc.Logging.main(Logging.java:24)
Connected to database

If I have ojdbc6_g.jar in classpath then also I am getting same exception.

Please let me know how can I enable logging for my JDBC program? basically I am expecting to see the logs generated by the internal JDBC code.

Update: Now I placed ojdbc6dms.jar file in classpath, my program is giving below exception:

Nov 28, 2014 9:09:02 PM jdbc.chap2.Logging main
INFO: Test Message
javax.management.InstanceNotFoundException: com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@73d16e93
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1095)
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:643)
    at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:678)
    at jdbc.chap2.Logging.enableLogging(Logging.java:45)
    at jdbc.chap2.Logging.main(Logging.java:24)
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole
    at oracle.jdbc.driver.DMSFactory.<clinit>(DMSFactory.java:48)
    at oracle.jdbc.driver.PhysicalConnection.createDMSSensors(PhysicalConnection.java:2121)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:730)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:433)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:608)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at jdbc.chap2.Logging.getConnection(Logging.java:70)
    at jdbc.chap2.Logging.main(Logging.java:25)
Caused by: java.lang.ClassNotFoundException: oracle.dms.console.DMSConsole
    at java.net.URLClassLoader$1.run(URLClassLoader.java:372)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:361)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:360)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 10 more

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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

Solution 5

Have you tried setting the corresponding system property? eg. for TRACE:

System.setProperty( "oracle.jdbc.Trace", Boolean.TRUE.toString() );

(from https://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm)

View more solutions