使用 JDBC 时如何为 SQL 语句启用日志记录 [英] How to enable logging for SQL statements when using JDBC

问题描述

我试图通过在 Eclipse IDE 中连接到 Oracle 数据库来使用我的 JDBC 程序启用日志.

我已经浏览了这篇 SO 帖子

如果您使用的是 Java EE，那么 P6spy 是一个不错的选择:

在幕后，p6spy提供了Driver级别的语句拦截器，因为DataSource是由应用服务器提供的，所以对于Java EE应用来说更加方便.

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

解决方案

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.

这篇关于使用 JDBC 时如何为 SQL 语句启用日志记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！