How to configure p6spy with data source in Spring + tomcat 7 + MySQL

Question

I am trying to set up a p3spy logging system on my maven based spring project. But every time I try to configure p3spy differently. I always get errors.

Here are my next settings

I placed p6spy-2.3.0.jar, spy.properties and mysql-connector-java-5.1.17.jar inside the tomcat / lib directory.

tomcat / context.xml

<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;characterEncoding=utf-8" 
            username="root"
            password="" 
            maxActive="100" 
            maxIdle="50" />

using these settings above. This gives me the following error.

Cannot create JDBC driver of class' com.mysql.jdbc.Driver 'to connect the URL' jdbc: p6spy: mysql: // localhost: 3306 / coexi? useUnicode = true & characterEncoding = utf-8 'java.sql. SQLException: no suitable driver.

Here I also tried to replace several parameters inside these resources.

<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;characterEncoding=utf-8" 
            username="root"
            password="" 
            maxActive="100" 
            maxIdle="50" />

, :

com.p6spy.engine.logging.appender.Log4jLogger, . java.lang.ClassNotFoundException: com.p6spy.engine.logging.appender.Log4jLogger

//PersistanceConfiguration

@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;

/Library/spy.properties

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

/main/resources/logback.xml

<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
        <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
        </pattern>
    </encoder>
</appender>

<appender name="GEN_FILE"
    class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>${catalina.base}/logs/general.log</file>
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <!-- daily rollover -->
        <fileNamePattern>gen_%d{yyyy-MM-dd}.log</fileNamePattern>
    </rollingPolicy>
    <encoder>
        <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
        </pattern>
    </encoder>
</appender>

<category name="com.p6spy">
    <priority value="INFO" />
</category>

<logger name="org.hibernate.SQL" additivity="false" >
    <level value="DEBUG" />    
    <appender-ref ref="SQLROLLINGFILE" />
</logger>

<logger name="org.hibernate.type" additivity="false" >
    <level value="TRACE" />
    <appender-ref ref="SQLROLLINGFILE" />
</logger>

<root level="info">
    <appender-ref ref="STDOUT" />
</root>
<root level="error">
    <appender-ref ref="ERRORS_FILE"/>
</root>

//log4j.xml.backend

<?xml version="1.0" encoding="UTF-8" ?>

http://jakarta.apache.org/log4j/' >

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

 <category name="com.p6spy">
    <priority value="INFO" />
  </category>


<logger name="org.hibernate.SQL" additivity="false" >   
 <level value="DEBUG" />    
 <appender-ref ref="SQLROLLINGFILE" />
</logger>

<logger name="org.hibernate.type" additivity="false" >
 <level value="TRACE" />
 <appender-ref ref="SQLROLLINGFILE" />
</logger>

<root>
    <level value="INFO" />
    <appender-ref ref="console" />
</root>

Fullstack.

http://pastebin.com/3LpgtPtr

Answer 1

appender spy.properties. log4j 2.X, slf4j. - com.p6spy.engine.spy.appender.Slf4JLogger.

. docs.

BTW. spy.properties P6Spy 1.3. 2.X( , ).

Answer 2

p6spy spring + hibernate + mysql + slf4j + log4j , sql console, .

: p6spy: db config url ( , Db P6DataSource ), :

- applicationContext.xml beans @Profile (...) @Configuration spring :

applicationContext.xml :

<beans profile="dev"> 
   <jee:jndi-lookup id="dataSourceReal" jndi-name="db_jndi_name" resource-ref="true"/>
    <bean name="dataSource" class="com.p6spy.engine.spy.P6DataSource">
        <constructor-arg name="delegate" ref="dataSourceReal"/>
    </bean>        
</bean>

<beans profile="!dev">
     <jee:jndi-lookup id="dataSource" jndi-name="db_jndi_name" resource-ref="true"/>  
 </beans>

db , maven p6spy ( - 3.0.0-alpha-1):

<dependency>
        <groupId>p6spy</groupId>
        <artifactId>p6spy</artifactId>
        <version>3.0.0-alpha-1</version>
</dependency>

spy.properties :

realdriver=com.mysql.jdbc.Driver
appender=com.p6spy.engine.spy.appender.Slf4JLogger

, , hibernate fomatter :

package com.my.package.logging;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;

public class P6spyHibernateSQLFormatter implements MessageFormattingStrategy {
    public static Formatter formatter = new BasicFormatterImpl();

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        return "#" + now + " | took " + elapsed + "ms | " + category + " | connection " + connectionId
                + "\n" + "Hibernate query with params:" + formatter.format(sql)  +";";
    }
}

spy.properties:

logMessageFormat=com.my.package.logging.P6spyHibernateSQLFormatter

... !