Use of the Log4jdbc database access logging framework

Use of the Log4jdbc database access logging framework

Log4jdbc official website address

• Http://code.google.com/p/log4jdbc/
• Https://github.com/arthurblake/log4jdbc

The latest version is 1.2. JDBC3 and JDBC4 are supported.

Log4jdbc is a log framework at the jdbc layer. It combines all placeholders and parameters for display, facilitating the direct copying of SQL statements to run on the database client.

SQL statements not recorded using log4jdbc:

Select count (*) from region where id =?

SQL statements recorded using log4jdbc:

Select count (*) from region where id = 'gz'

Log4jdbc features

1. Full support for JDBC 3 and JDBC 4!

2. easy to configure. In most cases, you need to change the driver class name. net. sf. log4jdbc. driverSpy, and add "jdbc: log4jdbc" to your existing jdbc url to create your logging category.

Example: jdbc: log4jdbc: mysql: // 192.168.0.207: 3306/mall

3. Automatic SQL output. This greatly improves readability and debugging in many cases.

4. You can print the SQL Execution time to debug the SQL Execution efficiency.

5. Generate SQL connection count information to help identify connection pool or thread problems.

6. The underlying JDBC driver is compatible with JDK 1.4 or later and SLF4J 1.x

Logger set by log4jdbc

Logger description
Jdbc. sqlonly only records SQL statements.
Jdbc. sqltiming timing statistics when SQL is executed.
Jdbc. audit records all JDBC calls (except for ResultSet ). The Log volume is very large, which will affect the performance. We recommend that you disable it.
In general, this setting is not required unless a specific JDBC problem is tracked.
Jdbc. resultset is larger than audit, including the ResultSet object, which records all JDBC records. Disable
Jdbc. connection records the number of opened and closed connections. It is useful for tracking connection leaks.
 
Usage

1. Add the corresponding logger in the log4j configuration file.

2: Change the driver class name,

For example, for MYSQL, jdbc: log4jdbc: mysql: // 192.168.0.207: 3306/mall

For example, Derby: jdbc: log4jdbc: derby: // localhost: 1527 // db-derby-10.2.2.0-bin/databases/MyDatabase
 
Log4j configuration example
 
<? Xml version = "1.0" encoding = "UTF-8"?>
<! DOCTYPE log4j: configuration SYSTEM "log4j. dtd">
<Log4j: configuration xmlns: log4j = "http://jakarta.apache.org/log4j/" debug = "false">
<Appender name = "CONSOLE" class = "org. apache. log4j. leleappender">
<Layout class = "org. apache. log4j. PatternLayout">
<Param name = "conversionPattern" value = "% d % p [% c: % L]-% m % n"/>
</Layout>
<Filter class = "org. apache. log4j. varia. LevelRangeFilter">
<Param name = "LevelMin" value = "INFO"/>
</Filter>
</Appender>
<Appender name = "SQL-appender" class = "org. apache. log4j. RollingFileAppender">
<Param name = "File" value = "$ {log. dir}/logs/SQL. log"/>
<Param name = "MaxBackupIndex" value = "20"/>
<Param name = "MaxFileSize" value = "20 MB"/>
<Param name = "Append" value = "true"/>
<Layout class = "org. apache. log4j. PatternLayout">
<Param name = "ConversionPattern" value = "% d % p [% c: % L]-% m % n"/>
</Layout>
</Appender>
<Appender name = "SQL-timing-appender" class = "org. apache. log4j. FileAppender">
<Param name = "File" value = "$ {log. dir}/logs/sqltiming. log"/>
<Param name = "MaxBackupIndex" value = "20"/>
<Param name = "MaxFileSize" value = "20 MB"/>
<Param name = "Append" value = "true"/>
<Layout class = "org. apache. log4j. PatternLayout">
<Param name = "ConversionPattern" value = "% d % p [% c: % L]-% m % n"/>
</Layout>
</Appender>
<Appender name = "jdbc-appender" class = "org. apache. log4j. FileAppender">
<Param name = "File" value = "$ {log. dir}/logs/jdbc. log"/>
<Param name = "MaxBackupIndex" value = "20"/>
<Param name = "MaxFileSize" value = "20 MB"/>
<Param name = "Append" value = "true"/>
<Layout class = "org. apache. log4j. PatternLayout">
<Param name = "ConversionPattern" value = "% d % p [% c: % L]-% m % n"/>
</Layout>
</Appender>
<Appender name = "jdbc-connection" class = "org. apache. log4j. FileAppender">
<Param name = "File" value = "$ {log. dir}/logs/connection. log"/>
<Param name = "MaxBackupIndex" value = "20"/>
<Param name = "MaxFileSize" value = "20 MB"/>
<Param name = "Append" value = "true"/>
<Layout class = "org. apache. log4j. PatternLayout">
<Param name = "ConversionPattern" value = "% d % p [% c: % L]-% m % n"/>
</Layout>
</Appender>
 
<! -- Log SQL (pre-execution) plus exceptions caused by SQL -->
<Logger name = "jdbc. sqlonly" additivity = "false">
<Level value = "debug"/>
<Appender-ref = "SQL-appender"/>
</Logger>
 
<! -- Log SQL with timing information, post execution -->
<Logger name = "jdbc. sqltiming" additi.pdf = "false">
<Level value = "fatal"/>
<Appender-ref = "SQL-timing-appender"/>
</Logger>
 
<! -- Only use the two logs below to trace all jdbc information,
NOTE: This can be very voluminous! -->
<! -- Log all jdbc callexcept ResultSet CILS -->
<Logger name = "jdbc. audit" additi.pdf = "false">
<Level value = "fatal"/>
<Appender-ref = "jdbc-appender"/>
</Logger>
 
<! -- Log the jdbc ResultSet CILS -->
<Logger name = "jdbc. resultset" additivity = "false">
<Level value = "fatal"/>
<Appender-ref = "jdbc-appender"/>
</Logger>

<! -- Log connection open/close events and dump of all open connection numbers -->
<Logger name = "jdbc. connection" additi.pdf = "false">
<Level value = "fatal"/>
<Appender-ref = "connection-appender"/>
</Logger>
 
<! -- This log is for internal debugging of log4jdbc, itself -->
<! -- Debug logging for log4jdbc itself -->
<Logger name = "log4jdbc. debug" additi.pdf = "false">
<Level value = "debug"/>
<Appender-ref = "stdout-appender"/>
</Logger>
<Root>
<Level value = "info"/>
<Appender-ref = "CONSOLE"/>
</Root>
</Log4j: configuration>
 
Program call example

Public class Demo {
Private static Logger log = LoggerFactory. getLogger (Demo. class );
 
 
Public static void main (String [] args) throws SQLException {
Log.info ("START ");
Connection conn = DBUtils. getConnection ();
Conn.createstatement(cmd.exe cute ("select count (*) from xbm_region ");
JdbcUtils.exe cuteQuery ("select count (*) from xbm_region where id =? "," Xxx ");
Log.info ("finished ");
}
}

Monitor SQL and Connection running in MyBatis using Log4jdbc-log4j2

This article permanently updates the link address: