JDBC logging to file

Question

I need to log all the queries to an Oracle database in my project to a log file.

What would be a good solution to achieve this? Some sample usage would be appreciated.

I have looked at SLF4J with jdbcdslog, but I'm not sure how I can log to a file with it. Moreover, I would need to "filter" some of the logs (because I don't need to know when some getxxxx method get's invoked)

Preferably, I'd prefer to use java.util.logging but it is not a requirement.

Thanks.

** UPDATE **

I found this Oracle article, however it does not really tell how to programatically do the same thing.

Answer 1

After much reading, this is how I got things working :

---

NOTE : Fore more information, read the Oracle Diagnosability in JDBC document

---

Properties prop = new Properties();
prop.put ("user", USER);
prop.put ("password", PASS);
// prop.put(propname, propValue);

Class.forName("oracle.jdbc.driver.OracleDriver");

enableLogging(false);

conn = DriverManager.getConnection("jdbc:oracle:thin:@"+HOST+":"+PORT+":"+SID, prop);

And here's the magic :

static private void enableLogging(boolean logDriver) 
throws MalformedObjectNameException, NullPointerException, 
       AttributeNotFoundException, InstanceNotFoundException, 
       MBeanException, ReflectionException, InvalidAttributeValueException, 
       SecurityException, FileNotFoundException, IOException 
{
    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));
    }
}

The properties file (from Oracle's documentation) :

.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

Basically, this is where the handlers are declared

oracle.jdbc.handlers=java.util.logging.ConsoleHandler

Declares the ConsoleHandler to be used by Oracle's JDBC driver. Any and any number of handlers can be declared here, one per line, with the class' full qualified name :

oracle.jdbc.handlers=java.util.logging.ConsoleHandler
oracle.jdbc.handlers=java.util.logging.FileHandler
...

One can provide their own custom made handlers with the same rule. The following lines are to setup the handler

java.util.logging.ConsoleHandler.level=INFO

will call the methode setLevel(Level.INFO) of the ConsoleHandler handler instance.

com.my.own.project.logging.handler.MyHandler.foo=Bar

will call the method setFoo("Bar") of the MyHandler handler instance. And that's it.

Happy logging!

Answer 2

suggest you look at jdbcdslog's user guide and discussion group.

A quick look at the user guide suggests that you can wrap (decorate) any JDBC connection with one of jdbcdslog's special logging wrappers, and it will log to whatever place you configure.

Furthermore it says it uses slf4j which supports logging to several logging engines including java.util.logging, so what you suggest seems very possible.

(But I'm not familiar with this jdbcdslog so I'm not sure how to get things configured.)