如何在Spring + tomcat 7 + MySQL中使用数据源配置p6spy [英] How to configure p6spy with datasource in Spring + tomcat 7 +MySQL
问题描述
我一直在尝试在基于Maven的spring项目中配置p3spy日志记录系统.但是每次我尝试不同地配置p3spy时.我总是出错.
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.
这是我的以下设置
我在tomcat/lib目录中放置了p6spy-2.3.0.jar，spy.properties和mysql-connector-java-5.1.17.jar.
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
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&characterEncoding=utf-8"
username="root"
password=""
maxActive="100"
maxIdle="50" />
在使用上面的设置时..它给了我以下错误..
while using this above settings.. it gives me this following error..
无法为连接URL'jdbc:p6spy:mysql://localhost:3306/coexi?useUnicode = true& characterEncoding = utf-8'java.sql创建类'com.mysql.jdbc.Driver'的JDBC驱动程序. SQLException:没有合适的驱动程序
Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:p6spy:mysql://localhost:3306/coexi?useUnicode=true&characterEncoding=utf-8' java.sql.SQLException: No suitable driver
在这里，我还尝试替换此资源中的一些设置.
Here i also tried to replacce few settings inside this 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&characterEncoding=utf-8"
username="root"
password=""
maxActive="100"
maxIdle="50" />
并通过使用此代码，得到以下错误:
And by using this code, getting this following error:
即使再次尝试也无法实例化com.p6spy.engine.logging.appender.Log4jLogger. java.lang.ClassNotFoundException:com.p6spy.engine.logging.appender.Log4jLogger
Cannot instantiate com.p6spy.engine.logging.appender.Log4jLogger, even on second attempt. java.lang.ClassNotFoundException: com.p6spy.engine.logging.appender.Log4jLogger
main/config/PersistanceConfiguration
main/config/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;
tomcat/lib/spy.properties
tomcat/lib/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
/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>
main/resources/log4j.xml.backend
main/resources/log4j.xml.backend
<?xml version="1.0" encoding="UTF-8" ?>
http://jakarta.apache.org/log4j/'>
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>
全栈错误.
http://pastebin.com/3LpgtPtr
推荐答案
我在 spring + hibernate + mysql + slf4j + log4j 上配置 p6spy 时遇到了类似的问题 我的目标是使用参数将完整的sql记录到ide控制台中，而不是休眠查询日志记录.
I had similar problem with configuring p6spy on spring + hibernate + mysql + slf4j + log4j My aim was to log full sql with params to ide console instead hibernate query logging.
在数据库配置URL中插入:p6spy:并没有帮助我(我在启动应用程序时出错，无法在类路径上加载数据库驱动程序P6DataSource)，我找到了解决方法:
Inserting :p6spy: into db config url didn't help me(i got error during starting app with can't load db driver P6DataSource on the classpath) and i found workaround:
您可以将类似的内容添加到applicationContext.xml中，或使用@Profile(...)和@Configuration弹簧注释创建bean:
you can add something similar to this into your applicationContext.xml or create beans using @Profile(...) and @Configuration spring annotations:
在applicationContext.xml中添加以下内容:
in applicationContext.xml add this:
<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>
所有数据库url和其他属性都是旧的，我只是为p6spy添加了maven依赖项(对我而言，当前的最新版本是3.0.0-alpha-1):
all db urls and others properties are old, i just added maven dependency for p6spy (current last version for me is 3.0.0-alpha-1):
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.0.0-alpha-1</version>
</dependency>
添加了带有以下属性的spy.properties:
added spy.properties with following properties:
realdriver=com.mysql.jdbc.Driver
appender=com.p6spy.engine.spy.appender.Slf4JLogger
一切正常，但是我有单行记录，使用基于休眠模式的格式化程序更改，如下所示:
and all works fine, but i had single line logging that i change with my formatter based on hibernate fomatter like this:
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:
and added formatter to spy.properties:
logMessageFormat=com.my.package.logging.P6spyHibernateSQLFormatter
并且我也禁用了休眠日志记录... 现在对我来说一切正常！
and also i disable hibernate logging... All works fine for me right now!
这篇关于如何在Spring + tomcat 7 + MySQL中使用数据源配置p6spy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
