This project was imported from https://code.google.com/archive/p/log4jdbc-log4j2/

Original License: Apache License 2.0

log4jdbc-log4j2 is a modification of log4jdbc to natively use Log4j 2 (or SLF4J as usual), that supports JDBC 4.1 to JDBC 3, includes all the improvements of log4jdbc-remix, and provides new improvements on its own. log4jdbc-log4j2:

News

Advantages

One single logger

Thanks to the use of the features provided by Log4j 2, only one single logger is needed to be configured, as opposed to five in the standard implementation of log4jdbc (and six when using log4jdbc-remix). The standard log4jdbc behavior can still be easily reproduced (see below for more details). You can nevertheless use SLF4J and configure several loggers as in the standard log4jdbc implementation if you wish to do so.

log4jdbc-remix features

All the improvements provided by log4jdbc-remix are included in this implementation: can log result sets as tables, can be configured as a Datasource , can use a custom SQL formatter, is available in the sonatype maven repository. If you use Log4j 2, the logging of the result sets are still configurable with a single logger. Also note that this implementation provides an additional way of configuring a custom SQL formatter, through properties (see below).

JDBC 4.1 support

log4jdbc-log4j2 supports JDBC 4.1 (JDK 1.7), JDBC 4 (JDK 1.6), JDBC 3 (JDK 1.5). Please note that we do not provide a JDBC 3 version for JDK 1.4.

Easy dispatch in different files of different SQL operations

Thanks to the use of the features provided by Log4j 2, you can easily dispatch different SQL operations (SELECT, UPDATE, ...) or errors (Exception thrown) in different files . This is not doable using the standard implementation of log4jdbc.

Availability in the sonatype maven repository

log4jdbc-log4j2 is available through the Maven repository: group org.bgee.log4jdbc-log4j2 , artifacts log4jdbc-log4j2-jdbc3 , log4jdbc-log4j2-jdbc4 , and log4jdbc-log4j2-jdbc4.1 .

Other improvements

This implementation provides some slight improvements, for instance: execution time for connection opening and closing, calls to Statement.getGeneratedKeys() logged along with the SQL query it was performed on, etc.

Installation

Installation is almost the same as with the standard version of log4jdbc.But be careful with the slight modifications, notably:

1. Install log4jdbc-log4j2

1.1. Decide if you need JDBC 3, JDBC 4, or JDBC 4.1 support.

Depending on which JDK you use, you should install different versions of log4jdbc-log4j2:

You should make your choice based on which JDK version you use, rather than which JDBC version: for instance, the log4jdbc JDBC 4 driver can wrap a JDBC 3 or older driver, as long as you don't call any unsupported method.

1.2. Option 1: installation via direct download

Choose and download one of the driver .jar files:

Place the log4jdbc-log4j2 jar that you chose into your application's classpath.

1.3. Option 2: installation via Maven repository

Add the following to your pom.xml configuration file, replace log4jdbc-log4j2-jdbcXX by the value corresponding to the JDBC version you want to use (either log4jdbc-log4j2-jdbc4.1 , or log4jdbc-log4j2-jdbc4 , or log4jdbc-log4j2-jdbc3 ):

<dependency>
  <groupId>org.bgee.log4jdbc-log4j2</groupId>
  <artifactId>log4jdbc-log4j2-jdbcXX</artifactId>
  <version>1.16</version>
</dependency>

2. Install the logging library you want to use

2.1. If you want to use Log4j 2 (recommended choice)

Get the last version of Log4j 2, either from their download page, or from their Maven repository (this link contains instructions to configure your pom.xml file).

You will typically need to install the log4j-core and log4j-api libraries. Please note that starting from version beta5 , Log4j 2 is compiled using JDK 1.6, so that log4jdbc-log4j2 for JDBC3 is usable with no later version than Log4j 2 beta4 .

2.2. If you want to use SLF4J

Get the last version of SLF4J, either from their download page, or from their Maven repository.

You will need at least two libraries: the slf4j-api library, and whichever library you pick depending on the java logging system you choose (for instance, Log4j, java.util logging, logback, Jakarta Commons Logging).

3. Modification of your source code

3.1. Change your JDBC URL

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-log4j2.

3.2. Change the driver used

Set your JDBC driver class to net.sf.log4jdbc.sql.jdbcapi.DriverSpy in your application (please note that this is not the same class name as in the standard log4jdbc implementation). See the log4jdbc documentation to see the list of supported drivers, or how to add support for other drivers. log4jdbc supports almost all major drivers.

Note that it is actually not necessary to set the Driver to load (through, for instance, Class.forName("net.sf.log4jdbc.sql.jdbcapi.DriverSpy") ) if you use the JDBC 4 or JDBC 4.1 versions. The driver will be automatically discovered and loaded.

4. Set up your logger

4.1. If you use Log4j 2

Log4jdbc-log4j2 uses only one logger, called log4jdbc.log4j2 (while the standard implementation uses 5, and even 6 when using log4jdbc-remix).

Its behavior can be set, notably from a Log4j 2 configuration file. This documentation presents some examples (see "Usage" below). Almost all features provided by the standard implementations of log4jdbc and log4jdbc-remix can be reproduced by using this single logger, thanks to the use of Markers.

This allows a very simple configuration, for instance, at the ERROR level: 

<logger name="log4jdbc.log4j2" level="error" additivity="false">
  <appender-ref ref="Console"/>
</logger>

And that's it!

This logger is easily configurable via the use of Marker s. See the "Usage" section below for several examples on how to use them, or how to reproduce log4jdbc and log4jdbc-remix standard behaviors. The recommended configuration at INFO or DEBUG level is basically: 

<logger name="log4jdbc.log4j2" level="info" additivity="false">
  <MarkerFilter marker="LOG4JDBC_OTHER" onMatch="DENY" onMismatch="NEUTRAL"/>
  <appender-ref ref="Console"/>
</logger>

Note that if this logger is turned off (or for example, 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).

List of all available Markers

4.2. If you use SLF4J

4.2.1 Set the option log4jdbc.spylogdelegator.name

First, you need to tell log4jdbc-log4j2 that you want to use the SLF4J logger. You need to configure the option log4jdbc.spylogdelegator.name to the value net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator . This is done either via the log4jdbc.log4j2.properties file stored in your classpath, or via system properties.

Add to the log4jdbc.log4j2.properties file: 

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

Or use System properties:

java -Dlog4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator -classpath ./classes my.funky.Program

See below for more details about how to configure the options of log4jdbc-log4j2 (notably, the log4jdbc.spylogdelegator.name option allows to set any custom SQL formatter).

4.2.2. Configure the loggers

From the log4jdbc and log4jdbc-remix documentation: there are 6 loggers that are used. If all 6 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.

logger description
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.
jdbc.sqltiming Logs 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.
jdbc.resultsettable Log the jdbc results as a table. Level debug will fill in unread values in the result set.
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.

4.3. For both Log4j 2 or SLF4J

The levels used are the same as in the standard implementation:

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.

Usage with Log4j 2

These examples assume that you configure Log4j 2 through a log4j2.xml configuration file, placed in your classpath.

1. Basic configuration at ERROR level

An example Log4j 2 configuration file: uses the log4jdbc.log4j2 logger, and writes errors (and slow queries if enabled, see below) into the file log4jdbc.out : 
<?xml version="1.0" encoding="UTF-8"?>
<configuration status="OFF">
  <appenders>
    <Console name="Console" target="SYSTEM_OUT">
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %level - %m%n%ex%n"/>
    </Console>
    <File name="log4jdbc_file" fileName="log4jdbc.out">
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %level - %m%n%ex%n"/>
    </File>
  </appenders>
  <loggers>
    <root level="off">
      <appender-ref ref="Console"/>
    </root>
    <logger name="log4jdbc.log4j2" level="error" additivity="false">
      <appender-ref ref="log4jdbc_file"/>
    </logger>
  </loggers>
</configuration>

2. Recommended configuration at INFO or DEBUG level

This modified implementation uses Markers, rather than different loggers as in the standard implementation. Here is a configuration example at INFO or DEBUG level, to log all relevant information (SQL statements, Connection calls, Exceptions thrown), and to not log JDBC calls, nor result sets as tables (overwhelming logs). It is just a matter of adding one line ( <MarkerFilter... ): 
<logger name="log4jdbc.log4j2" level="info" additivity="false">
  <MarkerFilter marker="LOG4JDBC_OTHER" onMatch="DENY" onMismatch="NEUTRAL"/>
  <appender-ref ref="log4jdbc_file"/>
</logger>

3. Recommended configuration at INFO or DEBUG level, when additionally logging result sets as tables (log4jdbc-remix feature)

This configuration will log all relevant information (SQL statements, Connection calls, Exceptions thrown), as well as result sets as tables (log4jdbc-remix feature): 
<logger name="log4jdbc.log4j2" level="info" additivity="false">
  <MarkerFilter marker="LOG4JDBC_JDBC" onMatch="DENY" onMismatch="NEUTRAL"/>
  <appender-ref ref="log4jdbc_file"/>
</logger>

4. Reproducing standard log4jdbc and log4jdbc-remix behaviors

Log4jdbc and log4jdbc-remix uses 6 loggers to log different information (see the log4jdbc documentation). This modified implementation rather uses Markers to achieve the same operations. Here are the configurations to reproduce the log4jdbc and log4jdbc-remix loggers behaviors:

5. Disabling some SQL operations, or dispatching them in different files

The standard implementation of log4jdbc allows to disable the logging of some SQL operations, through the configuration of log4jdbc properties ( log4jdbc.dump.sql.select , log4jdbc.dump.sql.insert , log4jdbc.dump.sql.update , log4jdbc.dump.sql.create , log4jdbc.dump.sql.delete ).

The same feature is implemented through the use of Marker s ( LOG4JDBC_SELECT , LOG4JDBC_INSERT , LOG4JDBC_UPDATE , LOG4JDBC_CREATE , LOG4JDBC_DELETE ). This has the advantage to also allow the dispatch of different operations in different files. Note that the log4jdbc properties can still be set, and have priority over the Marker s.

Here is an example configuration file to disable the logging of select operations, while dispatching update operations in one file, all other statements in another one: 

<?xml version="1.0" encoding="UTF-8"?>
<configuration status="OFF">
  <appenders>
    <Console name="Console" target="SYSTEM_OUT">
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %level - %m%n%ex%n"/>
    </Console>
    <File name="log4jdbc_update" fileName="log4jdbc_update.out">
      <MarkerFilter marker="LOG4JDBC_UPDATE" onMatch="ACCEPT" onMismatch="DENY"/>
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %level - %m%n%ex%n"/>
    </File>
    <File name="log4jdbc_file" fileName="log4jdbc.out">
      <MarkerFilter marker="LOG4JDBC_UPDATE" onMatch="DENY" onMismatch="NEUTRAL"/>
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %level - %m%n%ex%n"/>
    </File>
  </appenders>
  <loggers>
    <root level="off">
      <appender-ref ref="Console"/>
    </root>
    <logger name="log4jdbc.log4j2" level="info" additivity="false">
      <MarkerFilter marker="LOG4JDBC_SELECT" onMatch="DENY" onMismatch="NEUTRAL"/>
      <appender-ref ref="log4jdbc_file"/>
      <appender-ref ref="log4jdbc_update"/>
    </logger>
  </loggers>
</configuration>

In a similar way, SQLException s can be sent to a separate file, using the Marker LOG4JDBC_EXCEPTION (this is useful when the feature of logging slow queries with an ERROR level is activated; in this case, an error is not always associated to an Exception ).

Configure a DataSource

Option 1: configure Driver class name and JDBC URL

You can either configure your DataSource to use the log4jdbc-log4j2 driver (net.sf.log4jdbc.sql.jdbcapi.DriverSpy), and to use the modified JDBC URL (prepending jdbc:log4 to the normal JDBC URL).

Option 2: wrap into the log4jdbc DataSource

Or you can wrap your own DataSource into the log4jdbc-log4j2 DataSource . For instance (from the log4jdbc-remix documentation), if you have: 

  <bean id="dataSource" class="...">
    <property name="driverClass" value="${datasource.driverClassName}"/>
    <property name="jdbcUrl" value="${datasource.url}"/>
    <property name="user" value="${datasource.username}"/>
    <property name="password" value="${datasource.password}"/>
    ...
  </bean>

Change this to

  <bean id="dataSourceSpied" class="...">
    <property name="driverClass" value="${datasource.driverClassName}"/>
    <property name="jdbcUrl" value="${datasource.url}"/>
    <property name="user" value="${datasource.username}"/>
    <property name="password" value="${datasource.password}"/>
    ...
  </bean>

  <bean id="dataSource" class="net.sf.log4jdbc.sql.jdbcapi.DataSourceSpy">
    <constructor-arg ref="dataSourceSpied" />
  </bean>

Custom SQL formatter

As with log4jdbc-remix, it is possible to provide you own custom log formatter. Your logger must implement the interface net.sf.log4jdbc.log.SpyLogDelegator . To make log4jdbc-log4j2 to use it, you have different options:

Configure log4jdbc-log4j2 options

log4jdbc-log4j2 can be configured in the exact same way than log4jdbc (see below). Please note two changes as compared to log4jdbc:

Configuration file or System properties

From the log4jdbc documentation: you can define any of the log4jdbc settings either from a file named log4jdbc.log4j2.properties stored in the classpath, or via System properties. log4jdbc will look for properties both in the configuration file (if it exists) and in the system properties, with the file taking precedence for any properties defined in both locations.

For instance, if you want to set the option log4jdbc.spylogdelegator.name , you can either: add to the log4jdbc.log4j2.properties file: 

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

Or: use System properties:

java -Dlog4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator -classpath ./classes my.funky.Program

log4jdbc-log4j2 options

property default description
log4jdbc.drivers One or more fully qualified class names for JDBC drivers that log4jdbc should load and wrap. If more than one driver needs to be specified here, they should be comma separated with no spaces. This option is not normally needed because most popular JDBC drivers are already loaded by default-- this should be used if one or more additional JDBC drivers that (log4jdbc doesn't already wrap) needs to be included.
log4jdbc.auto.load.popular.drivers true Set this to false to disable the feature where popular drivers are automatically loaded. If this is false, you must set the log4jdbc.drivers property in order to load the driver(s) you want.
log4jdbc.debug.stack.prefix A REGEX matching the package name of your application. The call stack will be searched down to the first occurrence of a class that has the matching REGEX. 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.

Please note that the behavior of this property has changed as compared to the standard log4jdbc implementation. This property is now a REGEX, instead of being just the package prefix of the stack trace. So, for instance, if you want to target the prefix org.mypackage, the value of this property should be: ^org\.mypackage.*.
log4jdbc.sqltiming.warn.threshold Millisecond time value. Causes SQL that takes the number of milliseconds specified or more time to execute to be logged at the warning level in the sqltiming log. Note that the sqltiming log must be enabled at the warn log level for this feature to work. Also the logged output for this setting will log with debug information that is normally only shown when the sqltiming log is enabled at the debug level. This can help you to more quickly find slower running SQL without adding overhead or logging for normal running SQL that executes below the threshold level (if the logging level is set appropriately.)
log4jdbc.sqltiming.error.threshold Millisecond time value. Causes SQL that takes the number of milliseconds specified or more time to execute to be logged at the error level in the sqltiming log. Note that the sqltiming log must be enabled at the error log level for this feature to work. Also the logged output for this setting will log with debug information that is normally only shown when the sqltiming log is enabled at the debug level. This can help you to more quickly find slower running SQL without adding overhead or logging for normal running SQL that executes below the threshold level (if the logging level is set appropriately.)
log4jdbc.dump.booleanastruefalse false When dumping boolean values in SQL, dump them as 'true' or 'false'. If this option is not set, they will be dumped as 1 or 0 as many databases do not have a boolean type, and this allows for more portable sql dumping.
log4jdbc.dump.sql.maxlinelength 90 When dumping SQL, if this is greater than 0, than the dumped SQL will be broken up into lines that are no longer than this value. Set this value to 0 if you don't want log4jdbc to try and break the SQL into lines this way. In future versions of log4jdbc, this will probably default to 0.
log4jdbc.dump.fulldebugstacktrace false If dumping in debug mode, dump the full stack trace. This will result in EXTREMELY voluminous output, but can be very useful under some circumstances when trying to track down the call chain for generated SQL.
log4jdbc.dump.sql.select true Set this to false to suppress SQL select statements in the output. Note that if you use the Log4j 2 logger, it is also possible to control select statements output via the marker LOG4JDBC_SELECT (see section "Disabling some SQL operations, or dispatching them in different files" above). The use of this property prepend the use of the marker.
log4jdbc.dump.sql.insert true Set this to false to suppress SQL insert statements in the output. Note that if you use the Log4j 2 logger, it is also possible to control insert statements output via the marker LOG4JDBC_INSERT (see section "Disabling some SQL operations, or dispatching them in different files" above). The use of this property prepend the use of the marker.
log4jdbc.dump.sql.update true Set this to false to suppress SQL update statements in the output. Note that if you use the Log4j 2 logger, it is also possible to control update statements output via the marker LOG4JDBC_UPDATE (see section "Disabling some SQL operations, or dispatching them in different files" above). The use of this property prepend the use of the marker.
log4jdbc.dump.sql.delete true Set this to false to suppress SQL delete statements in the output. Note that if you use the Log4j 2 logger, it is also possible to control delete statements output via the marker LOG4JDBC_DELETE (see section "Disabling some SQL operations, or dispatching them in different files" above). The use of this property prepend the use of the marker.
log4jdbc.dump.sql.create true Set this to false to suppress SQL create statements in the output. Note that if you use the Log4j 2 logger, it is also possible to control create statements output via the marker LOG4JDBC_CREATE (see section "Disabling some SQL operations, or dispatching them in different files" above). The use of this property prepend the use of the marker.
log4jdbc.dump.sql.addsemicolon false Set this to true to add an extra semicolon to the end of SQL in the output. This can be useful when you want to generate SQL from a program with log4jdbc in order to create a script to feed back into a database to run at a later time.
log4jdbc.spylogdelegator.name net.sf.log4jdbc.log.log4j2.Log4j2SpyLogDelegator The qualified class name of the SpyLogDelegator to use. Note that if you want to use log4jdbc-log4j2 with SLF4J rather than Log4j 2, you must set this property to: net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator. This is a new property, not present in the standard log4jdbc implementation.
log4jdbc.statement.warn false Set this to true to display warnings (Why would you care?) in the log when Statements are used in the log. NOTE, this was always true in releases previous to 1.2alpha2. It is false by default starting with release 1.2 alpha 2.
log4jdbc.trim.sql true Set this to false to not trim the logged SQL. (Previous versions always trimmed the SQL.)
log4jdbc.trim.sql.extrablanklines true Set this to false to not trim extra blank lines in the logged SQL (by default, when more than one blank line in a row occurs, the contiguous lines are collapsed to just one blank line.) (Previous versions didn't trim extra blank lines at all.)
log4jdbc.suppress.generated.keys.exception false Set to true to ignore any exception produced by the method, Statement.getGeneratedKeys() (Useful for using log4jdbc with Coldfusion

Of note, an additional property allows to set the name of the property file:

property default description
log4jdbc.log4j2.properties.file log4jdbc.log4j2.properties Set the name of the property file to use

Slight modifications as compared to the standard implementation

To do