Log4jdbc demo

 

package log4jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceTest {

    @Test
    public void testC3P0() {
        try {
            ComboPooledDataSource dataSource = new ComboPooledDataSource();
            dataSource.setUser("root");
            dataSource.setPassword("密码");
            dataSource.setDriverClass("net.sf.log4jdbc.DriverSpy");
            dataSource.setJdbcUrl("jdbc:log4jdbc:mysql://localhost:3306/实例名");
            dataSource.setMaxPoolSize(50);
            Connection conn = dataSource.getConnection();
            String sql = "select * " + "from tb_person where id=?";
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            ps.executeQuery();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<!-- An example log4j configuration xml file for log4jdbc -->
<!-- Logging levels are:                                  -->
<!-- DEBUG < INFO < WARN < ERROR < FATAL                  -->

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

  <appender name="stdout-appender" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %5p %c{1}: %m%n"/>
    </layout>
  </appender>

  <appender name="sql-appender" class="org.apache.log4j.FileAppender">
    <param name="File" value="./logs/sql.log"/>
    <param name="Append" value="false"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="-----&gt; %d{yyyy-MM-dd HH:mm:ss.SSS} &lt;%t&gt; %m%n%n"/>
    </layout>
  </appender>

  <appender name="sql-timing-appender" class="org.apache.log4j.FileAppender">
    <param name="File" value="./logs/sqltiming.log"/>
    <param name="Append" value="false"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="-----&gt; %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n"/>
    </layout>
  </appender>

  <appender name="jdbc-appender" class="org.apache.log4j.FileAppender">
    <param name="File" value="./logs/jdbc.log"/>
    <param name="Append" value="false"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n"/>
    </layout>
  </appender>

  <appender name="jdbc-connection" class="org.apache.log4j.FileAppender">
    <param name="File" value="./logs/connection.log"/>
    <param name="Append" value="false"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n"/>
    </layout>
  </appender>

  <!--
       The Following 5 logs can be turned on and off while the server is running
       LIVE in order to trace the SQL and/or all JDBC coming out of the application.

       To turn a log on, set the level value to INFO or DEBUG (to see class name and
       line number information in the log)  The DEBUG setting is much more inefficient
       but the output is much more useful.

       To turn off JDBC logging completely, you must set all 5 logs to a level higher 
       than ERROR (FATAL is suggested.)
  -->

  <!-- log SQL (pre-execution) plus exceptions caused by SQL -->
  <logger name="jdbc.sqlonly" additivity="false">
    <level value="debug"/>
    <appender-ref ref="sql-appender"/>
  </logger>

  <!-- log SQL with timing information, post execution -->
  <logger name="jdbc.sqltiming" additivity="false">
    <level value="fatal"/>
    <appender-ref 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 calls except ResultSet calls -->
  <logger name="jdbc.audit" additivity="false">
    <level value="fatal"/>
    <appender-ref ref="jdbc-appender"/>
  </logger>

  <!-- log the jdbc ResultSet calls -->
  <logger name="jdbc.resultset" additivity="false">
    <level value="fatal"/>
    <appender-ref ref="jdbc-appender"/>
  </logger>
  
  <!-- log connection open/close events and dump of all open connection numbers -->
  <logger name="jdbc.connection" additivity="false">
    <level value="fatal"/>
    <appender-ref ref="connection-appender"/>
  </logger>

  <!-- this log is for internal debugging of log4jdbc, itself -->
  <!-- debug logging for log4jdbc itself -->
  <logger name="log4jdbc.debug" additivity="false">
    <level value="debug"/>
    <appender-ref ref="stdout-appender"/>
  </logger>

  <!-- by default, log everything to the console with a level of WARN or higher -->
  <root>
    <level value="warn"/>
    <appender-ref ref="stdout-appender"/>
  </root>
</log4j:configuration>

Console输出：

log4j:ERROR No appender named [connection-appender] could be found.
2015-09-22 22:40:38.933 DEBUG debug: ... log4jdbc initializing ...
2015-09-22 22:40:38.934 DEBUG debug:   log4jdbc.properties not found on classpath
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.debug.stack.prefix is not defined
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.sqltiming.warn.threshold is not defined
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.sqltiming.error.threshold is not defined
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.dump.booleanastruefalse is not defined (using default value false)
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.dump.sql.maxlinelength is not defined (using default of 90)
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.dump.fulldebugstacktrace is not defined (using default value false)
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.statement.warn is not defined (using default value false)
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.dump.sql.select is not defined (using default value true)
2015-09-22 22:40:38.934 DEBUG debug: x log4jdbc.dump.sql.insert is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.dump.sql.update is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.dump.sql.delete is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.dump.sql.create is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.dump.sql.addsemicolon is not defined (using default value false)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.auto.load.popular.drivers is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.trim.sql is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.trim.sql.extrablanklines is not defined (using default value true)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.suppress.generated.keys.exception is not defined (using default value false)
2015-09-22 22:40:38.935 DEBUG debug: x log4jdbc.drivers is not defined
2015-09-22 22:40:38.942 DEBUG debug:   FOUND DRIVER com.mysql.jdbc.Driver
2015-09-22 22:40:38.945 DEBUG debug: ... log4jdbc initialized! ...

工程目录下logs/sql.log

-----> 2015-09-22 22:40:39.241 <main>  com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
2. select * from tb_person where id=1 

http://blog.csdn.net/alex1314159/article/details/47185365
http://sourceforge.net/projects/c3p0/?source=typ_redirect
https://github.com/arthurblake/log4jdbc