Cannot create JDBC driver of class P6SpyDriver

[xjBean]

I've changed my repository to use p6spy (see below), and added
the driver:

applicationContext-db.xml :

<beans>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<!-- <value>com.mysql.jdbc.Driver</value> -->
<value>com.p6spy.engine.spy.P6SpyDriver</value>
</property>
<property name="url">
<!-- <value>jdbc:mysql://localhost:3306/cieinfo?useUnicode=true&amp;characterEncoding=UTF-8&amp;mysqlEncoding=utf8</value> -->
<value>jdbc:mysql://localhost:3306/cieinfo</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>mysql</value>
</property>
</bean>
</beans>

spy.properties:
...

# oracle driver
# realdriver=oracle.jdbc.driver.OracleDriver

# mysql Connector/J driver
realdriver=com.mysql.jdbc.Driver
...
deregisterdrivers=true
...



But i always get the error:


2007-08-23 17:23:50,328 INFO [org.hibernate.connection.ConnectionProviderFactory] - Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider
2007-08-23 17:23:50,406 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: null
2007-08-23 17:23:50,406 ERROR [org.hibernate.util.JDBCExceptionReporter] - Cannot create JDBC driver of class 'com.p6spy.engine.spy.P6SpyDriver' for connect URL 'jdbc:mysql://localhost:3306/cieinfo'
2007-08-23 17:23:50,406 WARN [org.hibernate.cfg.SettingsFactory] - Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.p6spy.engine.spy.P6SpyDriver' for connect URL 'jdbc:mysql://localhost:3306/cieinfo'
at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:780)
at org.apache.commons.dbcp.BasicDataSource.getConnect ion(BasicDataSource.java:540)
at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:81)
at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:72)
at org.hibernate.cfg.Configuration.buildSettings(Conf iguration.java:1463)
at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1004)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.newSessionFactory(LocalSessionFactoryBean .java:825)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.afterPropertiesSet(LocalSessionFactoryBea n.java:751)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:1091)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:396)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:233)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:145)
at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:277)
at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:313)
at org.springframework.web.context.support.AbstractRe freshableWebApplicationContext.refresh(AbstractRef reshableWebApplicationContext.java:139)
at org.springframework.web.context.ContextLoader.crea teWebApplicationContext(ContextLoader.java:252)
at org.springframework.web.context.ContextLoader.init WebApplicationContext(ContextLoader.java:190)
at org.springframework.web.context.ContextLoaderListe ner.contextInitialized(ContextLoaderListener.java: 49)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContext.java:3764)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4216)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase.java:760)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:740)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:544)
at org.apache.catalina.startup.HostConfig.deployDirec tory(HostConfig.java:920)
at org.apache.catalina.startup.HostConfig.deployDirec tories(HostConfig.java:883)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:492)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1138)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java:311)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(LifecycleSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1022)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:736)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1014)
at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:448)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:700)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:433)
Caused by: java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getDriver(Unknown Source)
at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:773)
... 41 more.......


Any ideas?
What have i missd?

Thanks,
xjBean (from China).

[Marten Deinum]

You might find this blogpost interesting. It uses a slightly different configuration mechanism.

[xjBean]

Thanks Marten Deinum .

But i can't open the link :
http://templth.blogspot.com/2004/11/...th-spring.html

[Marten Deinum]

That's weird. Works like a charm here...

P6Spy is a tool to debug JDBC interaction with a database. It's a wrapper
of all JDBC elements ( Connection, PreparedStatement, ResultSet... ) and
it has a powerful feature in order to log all informations about those
interactions.

JDBC wrapper is described in the "Java Performance Tuning" book of Jack Shirazi ( O'Reilly) in the chapter 16.


All elements wrappers have a constructor with the element to wrap. We can use it but
there is another mode for datasource. This last mode must be used to wrap a datasource
defined in an application server for example.

However since we can manage our objects ( beans ) in Spring ( and datasources
too ), it's possible to add a P6Spy datasource and link it with the real datasource
with constructor injection.



<?xml version="1.0" encoding="ISO-8859-1"?>



<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"

"http://www.springframework.org/dtd/spring-beans.dtd">



<beans>

<bean id="myDataSourceTarget"

class="org.apache.commons.dbcp.BasicDataSource"

destroy-method="close">

<property name="driverClassName">

<value>org.hsqldb.jdbcDriver</value>

</property>

<property name="url">

<value>jdbc:hsqldb:hsql://localhost:9001</value>

</property>

<property name="username"><value>sa</value></property>

<property name="password"><value></value></property>

</bean>



<bean id="myDataSource" class="com.p6spy.engine.spy.P6DataSource"

destroy-method="close">

<constructor-arg>

<ref local="myDataSourceTarget"/>

</constructor-arg>

</bean>



</beans>




Moreover a p6spy.log file must be add to the classpath of the application
to specify the location of the log file.



module.log=com.p6spy.engine.logging.P6LogFactory



executionthreshold=

outagedetection=false

outagedetectioninterval=

filter=false

include =

exclude =

sqlexpression =

autoflush = true

dateformat=

includecategories=

excludecategories=info,debug,result,batch



stringmatcher=

stacktraceclass=



reloadproperties=false

reloadpropertiesinterval=60



useprefix=false



appender=com.p6spy.engine.logging.appender.FileLog ger

logfile = c:/spy.log



append=true



log4j.appender.STDOUT=org.apache.log4j.ConsoleAppe nder

log4j.appender.STDOUT.layout=org.apache.log4j.Patt ernLayout

log4j.appender.STDOUT.layout.ConversionPattern=p6s py - %m%n



log4j.logger.p6spy=INFO,STDOUT




The output can be graphically view with a tool like Iron Track SQL.



Advantages of this solution are the following:

# It isn't necessary to modify the configuration of the application
server datasource in order to integrate P6Spy.

# The use of P6Spy is much easier. No deep knowledge of the format
of the P6Spy configuration file is required ( only specify the location
of log file ).

The whole post then...

[dejanp]

Instead of p6Spy I'd suggest using Craftsman Spy. It's much easier to configure.

[xjBean]

Mdeinum :
Thanks for your help.I have resolved the problem with the method you supply.

And Craftsman Spy that i will try it later