I am having a problem on a specific EJB container managed transaction. It fails to update a database row on some occasions. So I need to have a look at what is being sent the database and check for any errors. Since I am connecting to an Oracle database, I am aware that logging through the oracle JDBC driver does not show me variable bindings on prepared statements. So I need to use another way to see what’s happening, and that’s when log4jdbc comes in.
EDIT : currently log4jdbc does not support XA. Only found this out at the end of the process.
Log4jdbc sits between the application and the real driver and gives us the chance to log JDBC calls with fine detail. In this post I will be logging just SQL statements.
First, get the libraries. We need the log4jdbc jar and the jars from the SLF4J project for abstracting away the concrete logging framework, which in this case is log4j.
So put the following jars :
in a directory (here named $LOGGING_HOME), and find the weblogic script named commEnv.sh, which should be in the directory $BEA_HOME/wlserver_10.3/common/bin.
In this file you should place the three jars in the $LOGGING_HOME directory in the beginning of the classpath defined by the WEBLOGIC_CLASSPATH variable.
Then run the WLST command line tool to update your connection pool information. Execute the following commands replacing the shell variables with the values specific for your environment :
> readDomain('$DOMAIN_HOME') ;
find the path to the descriptor with your datasource’s connection pool configuration. In this case the datasource is named ‘portalPoolDataSource’ :
If you then do an ls() you will see the configuration of the connection pool.
We want to change the properties named ‘DriverName’ and ‘URL’:
> set('DriverName', 'net.sf.log4jdbc.DriverSpy');
> set('URL', 'jdbc:log4jdbc:oracle:thin:@localhost:1521:xe');
As per the log4jdbc documentation the new database URL is equal to the old one, but putting the string ‘:log4jdbc:’ between the leading ‘jdbc’ and the ‘oracle’ substrings.
Then write the configuration to disk :
> setOption('OverwriteDomain', 'true');
, and exit the WLST tool. Now we need to configure logging. I already had a log4j configuration file in xml format called log4j.xml. In my startup procedure I am using the WLST tool to start the AdminServer, and pass system properties to the startServer command. To indicate where the log configuration is located one may define the system property :
In my case this setting was always ignored. The config file considered by weblogic was one found inside one of its jars. It was looking for the first file in the classpath with the path described in the following line extracted from the startup log :
log4j: Trying to find [resources/comdev/default-log4j.properties] using context classloader weblogic.utils.classloaders.GenericClassLoader@1dea651
This output was logged only when the system property ‘-Dlog4j.debug’ is passed on server startup.
I then proceeded to convert my log4j.xml into properties format and placing it first on the classpath, so that It could be found before any other file with a similar relative path.
In the logging configuration file I defined the log level for the jdbc.sqlonly category :
That was it. the SQL is now being logged on the appender named FILE.