java - 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

Answer 1

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.