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

Do you have proper driver in classpath? 'Ensure that the debug JAR file, say ojdbc5_g.jar or ojdbc6_g.jar, is the only Oracle JDBC JAR file in the CLASSPATH environment variable.' and 'To get log output, you must use the debug JAR files, which are indicated with a "_g" in the file name, ' — Konstantin V. Salikhov, Nov 21 '14 at 11:51
@KonstantinV.Salikhov, Now I have tried using `ojdbc6_g.jar` in classpath but getting same error message, I have updated my question with the error details. — learner, Nov 21 '14 at 11:59
the mbean is registered using a different name, try using jconsole and see the mbean name and use the correct name. — Sajan Chandran, Nov 21 '14 at 12:02
@SajanChandran, I created this program in eclipse and running it as standalone program. I am new to Jconsole, I have run the command on my command prompt, can you please tell me how can I get the mbean name? — learner, Nov 21 '14 at 12:13
Have you seen [this question](http://stackoverflow.com/questions/5738181/jdbc-logging-to-file), and, specifically, code that goes with it? — mindas, Nov 25 '14 at 14:32
@mindas, yes I have also said I am following the same post and the code I am using is the same code that is posted in that question. — learner, Nov 26 '14 at 05:29
Do you have the DEV package for your Oracle driver? It seems you're missing the necessary classes to log the queries from the driver. — Yanick Rochon, Nov 28 '14 at 13:32
@YanickRochon, Thanks a lot for responding, I have the Jar file mentioned in my question in classpath, please let me know how to get the DEV package? — learner, Nov 28 '14 at 14:03
The drivers are [here](http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html) and you need the "dms" ones. — Yanick Rochon, Nov 28 '14 at 14:33
@YanickRochon, I am getting same exception, I am not able to get it how can I fix the `InstanceNotFoundException` . Also this time I am getting another exception for `ClassNotFoundException: oracle.dms.console.DMSConsole` I tried in internet to get the `dms.jar` file but I am not able to find it. I provided details in update section of my question. Can you please tell me how can I fix this? — learner, Nov 28 '14 at 15:47

score 21 · Answer 1 · edited Aug 21 '19 at 19:05

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.

score 18 · Answer 2 · edited Feb 06 '18 at 19:48

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:

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

This answer really rocks, since it allows to inspect the JDBC / Oracle behaviour of an app without changing its code at all. — Mutual Exception, Feb 17 '17 at 22:09
What is this `.level=.SEVERE` ? It gives me a bad level value error. — Stephane, Mar 03 '17 at 10:27
If you only want to see the SQL statements, use `oracle.jdbc.level = CONFIG` instead. `FINEST` will flood you with lot of messages. — ddekany, Aug 01 '18 at 09:50
`oracle.jdbc.level=CONFIG` appears to omit update and insert statements. I'm only seeing select and delete statements when I use that. I've asked a question if there's a way to get those to show up: https://stackoverflow.com/questions/69533236/ — Kaypro II, Oct 11 '21 at 22:50

Vlad Mihalcea · Accepted Answer · 2021-01-09T16:26:36.303

16

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.

edited Jan 09 '21 at 16:26

answered May 03 '16 at 08:21

Vlad Mihalcea

142,745
71
566
911

@Vlad: which tool do you use to generate the diagrams? – a3.14_Infinity Dec 24 '19 at 06:44
For others wanting answer related to diagrams, i got his answer in twitter . the tool is yEd Graph Editor. check twitter tweet - https://twitter.com/vlad_mihalcea/status/1209366642062512128?s=20 – a3.14_Infinity Dec 24 '19 at 07:00

score 3 · Answer 4 · answered Jan 29 '20 at 18:37

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

score 2 · Answer 5 · answered Nov 21 '14 at 11:48

2

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)

answered Nov 21 '14 at 11:48

jo-

234
1
9

Hi Jo, I added this line now, but getting same error. – learner Nov 21 '14 at 11:52

How to enable logging for SQL statements when using JDBC

5 Answers5

Original answer

Linked

Related