title: How to configure p6spy with datasource in Spring + tomcat 7 +MySQL
body: <p>I have been trying to configure p3spy logging system on my maven based spring project.But each time when i try to configure p3spy differently. i get always errors.</p>
  
  <p>Here is my following settings</p>
  
  <p>I placed p6spy-2.3.0.jar, spy.properties and mysql-connector-java-5.1.17.jar inside the tomcat/lib directory.</p>
  
  <p>tomcat/context.xml </p>
  
  <pre><code>&lt;Resource auth="Container" 
              driverClassName="com.mysql.jdbc.Driver" 
              name="jdbc/myDataSource"
              type="javax.sql.DataSource"
              url="jdbc:p6spy:mysql://localhost:3306/coexi?useUnicode=true&amp;amp;characterEncoding=utf-8" 
              username="root"
              password="" 
              maxActive="100" 
              maxIdle="50" /&gt;
  </code></pre>
  
  <p>while using this above settings.. it gives me this following error..</p>
  
  <blockquote>
    <p>Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:p6spy:mysql://localhost:3306/coexi?useUnicode=true&amp;characterEncoding=utf-8' java.sql.SQLException: No suitable driver</p>
  </blockquote>
  
  <p>Here i also tried to replacce few settings inside this resources.</p>
  
  <pre><code>&lt;Resource auth="Container" 
              driverClassName="com.p6spy.engine.spy.P6SpyDriver" 
              name="jdbc/myDataSource"
              type="javax.sql.DataSource"
              url="jdbc:p6spy:mysql://localhost:3306/coexi?useUnicode=true&amp;amp;characterEncoding=utf-8" 
              username="root"
              password="" 
              maxActive="100" 
              maxIdle="50" /&gt;
  </code></pre>
  
  <p>And by using this code, getting this following error:</p>
  
  <blockquote>
    <p>Cannot instantiate com.p6spy.engine.logging.appender.Log4jLogger, even on second attempt. 
    java.lang.ClassNotFoundException: com.p6spy.engine.logging.appender.Log4jLogger</p>
  </blockquote>
  
  <p>main/config/PersistanceConfiguration</p>
  
  <pre><code>@Bean
  public DataSource dataSource() {
      Context ctx;
      try {
          ctx = new InitialContext();
          return (DataSource) ctx.lookup("java:comp/env/jdbc/myDataSource");
      } catch (NamingException e) {
          LOGGER.info("Error to find the database : \"java:comp/env/jdbc/myDataSource\".");
      }
      return null;
  </code></pre>
  
  <p>tomcat/lib/spy.properties</p>
  
  <pre><code>driverlist=com.mysql.jdbc.Driver
  
  deregisterdrivers=true
  
  includecategories=info, statement
  excludecategories=info,debug,result,batch
  logfile     = /Users/coexi/Documents/spy.log
  
  append=true
  
  log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
  log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
  log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n
  
  log4j.logger.p6spy=INFO,STDOUT
  </code></pre>
  
  <p>/main/resources/logback.xml</p>
  
  <p></p>
  
  <pre><code>&lt;appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"&gt;
      &lt;encoder&gt;
          &lt;pattern&gt;%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
          &lt;/pattern&gt;
      &lt;/encoder&gt;
  &lt;/appender&gt;
  
  &lt;appender name="GEN_FILE"
      class="ch.qos.logback.core.rolling.RollingFileAppender"&gt;
      &lt;file&gt;${catalina.base}/logs/general.log&lt;/file&gt;
      &lt;rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy"&gt;
          &lt;!-- daily rollover --&gt;
          &lt;fileNamePattern&gt;gen_%d{yyyy-MM-dd}.log&lt;/fileNamePattern&gt;
      &lt;/rollingPolicy&gt;
      &lt;encoder&gt;
          &lt;pattern&gt;%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
          &lt;/pattern&gt;
      &lt;/encoder&gt;
  &lt;/appender&gt;
  
  &lt;category name="com.p6spy"&gt;
      &lt;priority value="INFO" /&gt;
  &lt;/category&gt;
  
  &lt;logger name="org.hibernate.SQL" additivity="false" &gt;
      &lt;level value="DEBUG" /&gt;    
      &lt;appender-ref ref="SQLROLLINGFILE" /&gt;
  &lt;/logger&gt;
  
  &lt;logger name="org.hibernate.type" additivity="false" &gt;
      &lt;level value="TRACE" /&gt;
      &lt;appender-ref ref="SQLROLLINGFILE" /&gt;
  &lt;/logger&gt;
  
  &lt;root level="info"&gt;
      &lt;appender-ref ref="STDOUT" /&gt;
  &lt;/root&gt;
  &lt;root level="error"&gt;
      &lt;appender-ref ref="ERRORS_FILE"/&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p></p>
  
  <p>main/resources/log4j.xml.backend</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" ?&gt;
  </code></pre>
  
  <p>
  http://jakarta.apache.org/log4j/'></p>
  
  <pre><code>&lt;appender name="console" class="org.apache.log4j.ConsoleAppender"&gt;
      &lt;param name="Threshold" value="INFO" /&gt;
      &lt;param name="Target" value="System.out" /&gt;
      &lt;layout class="org.apache.log4j.PatternLayout"&gt;
          &lt;param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" /&gt;
      &lt;/layout&gt;
  &lt;/appender&gt;
  
   &lt;category name="com.p6spy"&gt;
      &lt;priority value="INFO" /&gt;
    &lt;/category&gt;
  
  
  &lt;logger name="org.hibernate.SQL" additivity="false" &gt;   
   &lt;level value="DEBUG" /&gt;    
   &lt;appender-ref ref="SQLROLLINGFILE" /&gt;
  &lt;/logger&gt;
  
  &lt;logger name="org.hibernate.type" additivity="false" &gt;
   &lt;level value="TRACE" /&gt;
   &lt;appender-ref ref="SQLROLLINGFILE" /&gt;
  &lt;/logger&gt;
  
  &lt;root&gt;
      &lt;level value="INFO" /&gt;
      &lt;appender-ref ref="console" /&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p></p>
  
  <p>Fullstack error.</p>
  
  <pre><code>http://pastebin.com/3LpgtPtr
  </code></pre>