JUL to SLF4j with oracle jdbc driver

Question

I am trying to redirect java.util.logging messages logged by the oracle jdbc driver and the oracle ucp (universal connection pool) library, but not able to do so.

1. The messages logged by my application using JUL get logged but the ones logged by the oracle libraries are not getting logged.
2. My intent here is redirect JUL messages to Logback to have more fine grained logging through configuration. i.e. enabling logging at class level instead of package level which I assume is not possible in JUL configuration (java.util.config file).

Below is the sample test code. Do you have any suggestions on the above two points?

import oracle.ucp.jdbc.PoolDataSourceImpl;
import org.slf4j.bridge.SLF4JBridgeHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Logger;

public class JavaUtilToSlf4jApp {
    private static Logger logger = Logger.getLogger(JavaUtilToSlf4jApp.class.getName());

    public static void main(String[] args) {
        SLF4JBridgeHandler.install();

        startConnectionPool();

        logger.info("Info Message");
    }

    private static void startConnectionPool() {
        PoolDataSourceImpl pds = new PoolDataSourceImpl();
        try {
            pds.setConnectionPoolName("Pool Name");
            pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
            pds.setConnectionFactoryProperties(getOracleDataSourceProperties());
            pds.setDataSourceName("Datasource Name");
            pds.setServerName("machine-name");
            pds.setPortNumber(1521);

            pds.setMinPoolSize(1);
            pds.setMaxPoolSize(1);

            pds.setMaxIdleTime(1800);
            pds.setValidateConnectionOnBorrow(true);

            pds.setUser("user");
            pds.setPassword("password");

            pds.startPool();
        } catch (SQLException e) {
            throw new RuntimeException("Cannot create project datasource ", e);
        }

        try {
            Connection connection = pds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        logger.info("Connection established");
    }

    private static Properties getOracleDataSourceProperties() {
        Properties p = new Properties();
        p.put("driverType", "oci");
        p.put("networkProtocol", "tcp");
        p.put("serviceName", "servicename");
        return p;
    }

}

Answer 1

If it helps. I took this approach in a Spring boot application, using the ojdbc_g version of the driver jar file:

1. Set the system property: System.setProperty("oracle.jdbc.Trace", "true").
2. Initialize the Oracle driver/datasource with whatever mechanism you have (manual, Spring, conn pool, etc.).

Once the datasource has been initialized, programmatically set the level for the "oracle.jdbc" JUL logger:

Logger ol = Logger.getLogger("oracle.jdbc");
ol.setLevel(Level.FINE);

The remaining part would be to figure out how to map the JUL logging levels to SLF4J logging levels.

Worked inside my Spring boot application. Cheers!

Answer 2

This was a complete mess, and after I got some logging, it still didn't work that well.

First off, you definitely need to be using the "_g" version of the jar, which is the jar that was compiled with the debug option, and logging turned on. If you don't use this driver, it'll be like getting blood from a stone.

Second, you need to add the java parameter -Doracle.jdbc.Trace=true.

Third, you need to define the package in your logging file (like logback.xml):

  <logger name="oracle" level="INFO" additivity="false">
    <appender-ref ref="SERVER_FILE" />
  </logger>

This got me the following results:

11:17:45.393 [UCP-worker-thread-3] INFO  oracle.jdbc.driver - SQL: select count(*) from mytable
11:17:45.956 [main] INFO  oracle.jdbc.driver - SQL: 

        select myfield 
          from mytable 
         where myotherfield='myvalue'      

11:17:46.159 [main] INFO  oracle.jdbc.driver - SQL: begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7); end;

Please let me know if you resolved your issues, how, and if any of this makes sense. There's poor and conflicting comments all of the web on this specific issue.