user3181365 user3181365 - 29 days ago 20
Java Question

Enable logging for JDBC

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

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

enter image description here

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

enter image description here

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.