download | news | features | usage | advanced | similiar tools | feedback
sourceforge page | JDK1.4/JDBC 3 javadoc | JDK1.6/JDBC 4 javadoc

log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for other JDBC drivers using the Simple Logging Facade For Java (SLF4J) logging system.

news

2008-04-11: log4jdbc 1.1 final release out! See CHANGES for all the release details.

2007-11-10: log4jdbc 1.1 beta 1 released. New optional timing threshold settings for honing in on slow SQL.

2007-07-25: log4jdbc 1.1 alpha 2 released. JDBC 4 support!

2007-05-29: log4jdbc 1.1 alpha 1 released. Most notable change is that the Simple Logging Facade for Java is now used instead of log4j directly.

2007-04-21: log4jdbc 1.0 has been released! Download it and give it a try!

[back to top]

features

[back to top]

usage

  1. Decide if you need JDBC 3 or JDBC 4 support. The most straightforward way to decide this is:

    • If you are using JDK 1.4 or 1.5, you should use the JDBC 3 version of log4jdbc.
    • If you are using JDK 1.6 or 1.7, you should use the JDBC 4 version of log4jdbc (even if the actual underlying JDBC driver you are using is a JDBC 3 or older driver).

    Currently there are very few actual JDBC 4 drivers on the market. (The only major one that I really know about is Apache Derby aka The Java DB distributed with JDK 1.6.) JDBC 4 support was added with the JDK 1.6 release and adds many additional features over and above JDBC 3. However, the log4jdbc JDBC 4 driver can wrap a JDBC 3 or older driver and it's recommended that if you use JDK 1.6 or above, that you use the log4jdbc JDBC 4 driver that is compiled with JDK 1.6.

    Note that JDBC 2 is not currently supported by log4jdbc (there is an older unreleased version of log4jdbc with JDBC 2 support, but I decided not to release it because JDBC 3 has been out for so long, I don't anticipate much demand for a JDBC 2 version of log4jdbc at this point), although if you are using JDK 1.4 and above, the log4jdbc 3.0 or 4.0 driver should be able to wrap an older JDBC 2 driver as well-- log4jdbc just won't work with Java 1.3 and earlier. Please email me if you really really want a JDBC 2 version of log4jdbc. If there is enough demand, I may resurrect it.

    
        Choose and download one of the driver .jar files:
    
            log4jdbc3-1.1beta1.jar      JDBC 3 support for JDK 1.4 , JDK 1.5
            log4jdbc4-1.1beta1.jar      JDBC 4 support for JDK 1.6 , JDK 1.7
    
        Place the log4jdbc jar that you choose into your application's classpath.
        
  2. Choose which java logging system you will use.

    In many cases, you already know this, because it is dictated by your existing application. log4jdbc uses the Simple Logging Facade for Java (SLF4J) which is a very simple and very flexible little library that lets you pick among many common java logging systems:

    SLF4J is designed to de-couple your application from the java logging system so you can choose any one you want. This is the same goal of Jakarta Commons Logging. However many people have had headaches and issues with classloading problems in complex environments using Jakarta Commons Logging. SLF4J solves these problems with it's much simpler design, and you can even integrate SLF4J to use Jakarta Commons Logging, if you really want to (or are required to) use it.

    
         Download the latest official SLF4J release.
    
         You will need two jars:
    
            slf4j-api-1.4.3.jar (or the latest available version) and
            whichever jar you pick depending on the java logging system you choose
    
        Place these two .jar files into your application's classpath.
        
    Please read the documentation at the SLF4J website. It's really easy to set up!
  3. Set your jdbc driver class to net.sf.log4jdbc.DriverSpy in your application's configuration.

    The underlying driver that is being spied on in many cases will be loaded automatically without any additional configuration.

    The log4jdbc "spy" driver will try and load the following popular jdbc drivers:

    
            oracle.jdbc.driver.OracleDriver              (Oracle)
            com.sybase.jdbc2.jdbc.SybDriver              (Sybase)
            net.sourceforge.jtds.jdbc.Driver             (jTDS SQL Server & Sybase driver)
            com.microsoft.jdbc.sqlserver.SQLServerDriver (Microsoft SQL Server driver)
            weblogic.jdbc.sqlserver.SQLServerDriver      (Weblogic SQL Server driver)
            com.informix.jdbc.IfxDriver                  (Informix)
            org.apache.derby.jdbc.ClientDriver           (Apache Derby client/server driver, aka the Java DB)
            org.apache.derby.jdbc.EmbeddedDriver         (Apache Derby embedded driver, aka the Java DB)
            com.mysql.jdbc.Driver                        (MySQL)
            org.postgresql.Driver                        (PostgresSQL)
            org.hsqldb.jdbcDriver                        (HSQLDB pure Java database)
            org.h2.Driver                                (H2 pure Java database)
        

    If you want to use a different underlying jdbc driver that is not already in the above supported list, set a system property, log4jdbc.drivers to the class name of the additional driver. This can also be a comma separated list of driver class names if you need more than one.

    (optional) -Dlog4jdbc.drivers=<driver>[,<driver>...] If your driver is not one of the common preloaded drivers.

  4. Prepend jdbc:log4 to the normal jdbc url that you are using.

    For example, if your normal jdbc url is

    jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase

    then You would change it to:

    jdbc:log4jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase

    to use log4jdbc.

  5. Set up your logging categories. There are 4 categories that are used by log4jdbc, If all 4 loggers are set to a level less than error (such as the FATAL level), then log4jdbc will not log anything, and in fact the actual (real) connection to the underlying database will be returned by the log4jdbc driver (thus allowing log4jdbc to be installed and available to turn on at runtime at a moment's notice, without imposing any actual performance loss when not being used). If any of the 4 logs are set to ERROR,INFO or DEBUG, then log4jdbc will be activated, wrapping and logging all activity in the jdbc connections returned by the underlying driver.

    
        Each of these logs can be set at either DEBUG, INFO or ERROR
        DEBUG includes the class name and line number (if available) at which the sql was executed.
        INFO  includes the sql (or other information as applicable)
        ERROR will show the stack traces in the log output when sql exceptions occur.
    
        jdbc.sqlonly   shows only SQL in the log.  SQL executed within a prepared statement is automatically shown with
                       it's bind arguments replaced with the data bound at that position, for greatly increased readability.
    
        jdbc.sqltiming shows the SQL, post-execution, including timing statistics on how long the sql took to execute.
    
        jdbc.audit     Logs ALL jdbc calls except for ResultSets.  This is a very voluminous output, and is not normally
                       needed unless tracking down a specific jdbc problem.
    
        jdbc.resultset Even more voluminous, because all calls to ResultSet objects are logged.
        

    Additionally, there is one logger named log4jdbc.debug which is for use with internal debugging of log4jdbc. At this time it just prints out information on which underlying drivers were found and not found when the log4jdbc spy driver loads.

    In a typical usage scenario, you might turn on only the jdbc.sqlonly logging at INFO level, just to view the sql coming out of your program.

    Then if you wanted to view how long each sql statement is taking to execute, you might use jdbc.sqltiming.

    jdbc.audit and jdbc.resultset are used for more in depth diagnosis of what is going on under the hood with jdbc as potentially almost every single call to jdbc could be logged (logs can grow very large, very quickly with this!)

    SLF4J is a logging facade that can be used with many popular java logging systems. The setup for the logging categories will vary depending on which underlying logger you use. Sample configuration files for log4j are provided here: log4j.xml and log4j.properties.

  6. Adjust debugging options. When logging at the DEBUG level, the class file and line number (if available) for the class that invoked jdbc is logged after each log statement. This is enormously useful for finding where in the code the SQL is generated. Be careful when using this on a production system because there is a small, but potentially significant penalty performance for generating this data on each logging statement.

    In many cases, this call stack data is not very useful because the calling class into log4jdbc is a connection pool, object-persistance layer or other layer between log4jdbc and your application code-- but the class file and line number information you really are interested in seeing is where in your application the SQL was generated from.

    Set the log4jdbc.debug.stack.prefix System property for log4jdc to help get around this problem:

    (optional) -Dlog4jdbc.debug.stack.prefix=<package.prefix> a String that is the partial (or full) package prefix for the package name of your application. The call stack will be searched down to the first occurence of a class that has the matching prefix. If this is not set, the actual class that called into log4jdbc is used in the debug output (in many cases this will be a connection pool class)

    For example, setting a system property such as this: -Dlog4jdbc.debug.stack.prefix=com.mycompany.myapp Would cause the call stack to be searched for the first call that came from code in the com.mycompany.myapp package or below, thus if all of your sql generating code was in code located in the com.mycompany.myapp package or any subpackages, this would be printed in the debug information, rather than the package name for a connection pool, object relational system, etc.

[back to top]

advanced

[back to top]

similiar tools

Some other tools and libraries that are similiar to log4jdbc.

[back to top]

feedback

Feedback and suggestions would be greatly appreciated. Email me at arthur_blake at users.sourceforge.net

Also, visit my blog.

[back to top]

download | news | features | usage | advanced | similiar tools | feedback
sourceforge page | JDK1.4/JDBC 3 javadoc | JDK1.6/JDBC 4 javadoc

YourKit is kindly supporting open source projects with its full-featured Java Profiler. YourKit, LLC is creator of innovative and intelligent tools for profiling Java and .NET applications. Take a look at YourKit's leading software products: YourKit Java Profiler and YourKit .NET Profiler.