log4jdbc Documentation

log4jdbc is a JDBC proxy driver that intercepts JDBC calls to log SQL statements, timing information, and other metadata. It uses the SLF4J (Simple Logging Facade for Java) system to delegate actual logging to your preferred logging framework (such as Logback, Log4j, or java.util.logging).

By wrapping your existing JDBC driver, it can automatically inject bind arguments into prepared statements for better readability and provide timing statistics to help identify slow queries.

Select your version based on your Java Development Kit (JDK) requirements:

• JDK 1.8+: Use the latest log4jdbc (1.4+). This provides full JDBC 4.3 support. It can also wrap older JDBC drivers.
• JDK 1.6 or 1.7: Use the JDBC 4 version (log4jdbc4-1.2.jar).
• JDK 1.4 or 1.5: Use the JDBC 3 version (log4jdbc3-1.2.jar).

Since log4jdbc uses SLF4J, you must include the SLF4J API and a specific logging implementation in your application's classpath.

1. Add slf4j-api-2.0.7.jar (or the latest version) to your classpath.
2. Add the JAR for your chosen logging system (e.g., logback-classic, log4j, etc.).

Since log4jdbc does not currently have direct support for DataSource objects, you can manually wrap the Connection object returned by your data source.

This approach has the advantage of not requiring you to change your JDBC URL or initialize the net.sf.log4jdbc.DriverSpy driver, as the wrapping happens directly in your application code where connections are obtained.

// get connection from datasource
Connection conn = dataSource.getConnection();

// wrap the connection with log4jdbc
conn = new net.sf.log4jdbc.ConnectionSpy(conn);

// now use Connection as normal (but it will be audited by log4jdbc)

To activate the proxy, you must prepend jdbc:log4jdbc: to your existing JDBC connection URL.

Example Transformation:

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

log4jdbc options are controlled via system properties. You can set them using the java -D command line option.

Alternatively, starting with version 1.2 beta 2, you can define these settings in a file named log4jdbc.properties located in your classpath. If both a properties file and system properties are present, the file takes precedence for any overlapping keys.

java -Dlog4jdbc.drivers=my.funky.DriverClass -classpath ./classes my.funky.Program

To use log4jdbc, change your application's JDBC driver class to net.sf.log4jdbc.DriverSpy.

Auto-loading popular drivers

The DriverSpy automatically attempts to load several popular drivers (Oracle, MySQL, PostgreSQL, H2, etc.).

Using custom or additional drivers

If your driver is not in the supported list, set the log4jdbc.drivers system property with the driver class name(s):

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

To disable the automatic loading of popular drivers and only use those you explicitly specify, set:

-Dlog4jdbc.auto.load.popular.drivers=false

-Dlog4jdbc.drivers=com.example.CustomDriver

When logging at the DEBUG level, log4jdbc includes the class and line number of the caller. Often, this is a connection pool or ORM class rather than your application code.

To find the actual source of the SQL, set the log4jdbc.debug.stack.prefix system property to your application's package prefix. log4jdbc will then search the stack trace for the first occurrence of a class matching that prefix.

Example: If your code is in com.mycompany.myapp, use:

-Dlog4jdbc.debug.stack.prefix=com.mycompany.myapp

log4jdbc includes an experimental tool to post-process SQL timing logs (generated from the jdbc.sqltiming log category). The tool produces profiling reports with statistics and identifies the slowest SQL statements.

To use it, run the provided scripts from the scripts folder, passing the filename of your timing log as an argument.

# For Unix/Linux
./scripts/profsql.sh path/to/your/sqltiming.log

# For Windows
profsql.cmd path/to/your/sqltiming.log

log4jdbc uses 5 primary loggers. If all are set to a level lower than ERROR, log4jdbc remains inactive and imposes no performance penalty. If any are set to ERROR or higher, log4jdbc activates and wraps the connections.

Note: log4jdbc.debug is used for internal debugging of the DriverSpy loading process.