log4jdbc is a JDBC proxy driver for logging SQL and other interesting information.

---

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

• 2023-04-03 Finally converted the ancient Google code help text to Github markdown with some minor updates. Only 8 years late! Also merged a couple of minor simple PRs. Cursory updates for JDK 1.8 / JDBC 4.3

• More News

---

Features

• Full support for JDBC 4+. (Archived versions have JDBC 3 Support.)
• Easy to configure, in most cases all you need to do is change the driver class name to net.sf.log4jdbc.DriverSpy and prepend "jdbc:log4" to your existing jdbc url, set up your logging categories and you're ready to go!
• In the logged output, for prepared statements, the bind arguments are automatically inserted into the SQL output. This greatly Improves readability and debugging for many cases. SQL timing information can be generated to help identify how long SQL statements take to run, helping to identify statements that are running too slowly and this data can be post processed with an included tool to produce profiling report data for quickly identifying slow SQL in your application..
• SQL connection number information is generated to help identify connection pooling or threading problems.
• Works with any underlying JDBC driver, with JDK 1.4 and above, and SLF4J 1.x.
• Open source software, licensed under the business friendly Apache 2.0 license: http://www.apache.org/licenses/LICENSE-2.0

---

Usage

Decide if you need JDBC 3 or JDBC 4 support.

• JDBC 4.3 support is available in the latest log4jdbc 1.4+ for JDK 1.8+.
• There is no specific JDBC 4.1 version.
• If you are using an older JDK (such as 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). This is now defunct but can be obtained as log4jdbc4-1.2.jar under Archived Downloads.
• If you are using JDK 1.4 or 1.5, you should use the JDBC 3 version of log4jdbc. This is now defunct but can be obtained as log4jdbc3-1.2.jar under Archived Downloads.

If you are using an older version of the JDBC driver with JDK 1.8+, JDBC should be able to properly wrap that driver even though log4jdbc is coded for JDBC 4.3.

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: * Log4j * java.util logging in JDK 1.4 * logback * Jakarta Commons Logging.

SLF4J is designed to de-couple your application from the java logging system so you can choose any one you want.

Latest stable version of SLF4J.

You will need two jars: slf4j-api-2.0.7.jar (or the latest available version) and whichever jar you pick depending on the java logging system you choose.

Maven central SLF4J Downloads

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!

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:

| Driver Class | Database Type | |:-----------------|:------------------| | oracle.jdbc.driver.OracleDriver | Older Oracle Driver | | oracle.jdbc.OracleDriver | Newer Oracle Driver | | com.sybase.jdbc2.jdbc.SybDriver | Sybase | | net.sourceforge.jtds.jdbc.Driver | jTDS SQL Server & Sybase driver | | com.microsoft.jdbc.sqlserver.SQLServerDriver | Microsoft SQL Server 2000 driver | | com.microsoft.sqlserver.jdbc.SQLServerDriver | Microsoft SQL Server 2005 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 | | com.mysql.cj.jdbc.Driver | Connector/J 6.0 | | org.mariadb.jdbc.Driver | MariaDB | | 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.

-Dlog4jdbc.drivers=<driverclass>[,<driverclass>...]

If you don't want to attempt to auto-load drivers at all, you can set a system property, log4jdbc.auto.load.popular.drivers to false. In this case, it will only load the drivers specified in the log4jdbc.drivers property.

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.

Set up your loggers

There are 5 loggers that are used by log4jdbc, If all 5 are turned off (or for example, set to a level less than error, such as the FATAL level in log4j), 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 5 logs are set to ERROR level or above (e.g ERROR, INFO or DEBUG) then log4jdbc will be activated, wrapping and logging activity in the JDBC connections returned by the underlying driver.

Each of these logs can be set at either DEBUG, INFO or ERROR level.

• DEBUG includes the class name and line number (if available) at which the SQL was executed. Use DEBUG level with extra care, as this imposes an additional performance penalty when in use.
• INFO includes the SQL (or other information as applicable.)
• ERROR will show the stack traces in the log output when SQLExceptions occur.

| logger | description | since | |:-----------|:----------------|:----------| | jdbc.sqlonly | Logs only SQL. 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. | 1.0 | | jdbc.sqltiming | Logs the SQL, post-execution, including timing statistics on how long the SQL took to execute. | 1.0 | | 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. | 1.0 | | jdbc.resultset | Even more voluminous, because all calls to ResultSet objects are logged. | 1.0 | | jdbc.connection | Logs connection open and close events as well as dumping all open connection numbers. This is very useful for hunting down connection leak problems. | 1.2alpha1 |

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, jdbc.resultset and jdbc.connection 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 jdbc.audit and jdbc.resultset!)

Because SLF4J can be used with many popular java logging systems, the setup for your loggers will vary depending on which underlying logging system you use. Sample configuration files for log4j are provided here: log4j.xml and log4j.properties.

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:

-Dlog4jdbc.debug.stack.prefix=<package.prefix>

Where is 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