p6spy is a JDBC proxy that will log the SQL statements passed to a database. It is a relatively old project (last release is from 2003 but I only heard about it today, as I was looking for a way to trace SQL queries sent by dbUnit to my Oracle database.
The bad news, of course, is that usage of p6spy along with dbUnit and Maven is not documented. Since it took me a while to figure it out, I thought I’d share it here.
Setup: you should already have dbUnit integrated in your pom.xml. You should also have configured dbunit-maven-plugin. All in all, something reasonably right should already happen when running mvn dbunit:export.
Here is what you need to do to get the SQL traces from dbUnit:
<dependency> <groupid>p6spy</groupid> <artifactid>p6spy</artifactid> <version>1.3</version> <scope>test</scope> </dependency>
spy.properties file in your project directory (the one where you run maven from). You are supposed to be able to put it anywhere in your classpath. The easiest way to create this file is by copying the example on p6spy’s website.edit this file and ensure that at least one realdriver property is set to your actual db driver (other properties can be left as they are). For Oracle, it looks like that:
realdriver=oracle.jdbc.driver.OracleDriver
go back to your pom.xml and look for the configuration of dbunit-maven-plugin. Replace the driver with p6spy; do *not* change the other driver properties:
<configuration> <driver>com.p6spy.engine.spy.P6SpyDriver</driver> <url><leave as is></leave></url> <username><leave as is></leave></username> <password><leave as is></leave></password> </configuration>
lastly, add a new dependency for dbunit-maven-plugin. You should already have one for the current db driver. Keep it and add the one for p6spy:
<dependencies> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.1.0</version> </dependency> <dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>1.3</version> </dependency> </dependencies>
Eventually, using dbUnit to export the database looks like that (warning: it actually fails when reading the db; which is why I needed p6spy in the first place):
<plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>dbunit-maven-plugin</artifactId> <executions> <execution> <phase>test</phase> <goals> <goal>export</goal> </goals> <configuration> <format>xml</format> <dest>target/dbunit/export.xml</dest> </configuration> </execution> </executions> <dependencies> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.1.0</version> </dependency> <dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>1.3</version> </dependency> </dependencies> <configuration> <driver>com.p6spy.engine.spy.P6SpyDriver</driver> <url>jdbc:oracle:thin:@130.98.9.94:1521:marketlab</url> <username>usermkl</username> <password>usermkl</password> </configuration> </plugin>
On to fix the error I can now see in spy.log…
I have been using p6spy since last year to troubleshoot problems when SQLs have gone wrong. It’s really useful. :-)
Thanks for the sharing anyway.
Cheers,
Yuen-Chi Lian
This works fine:
<profiles>
<profile>
<id>DBUNIT</id>
<build>
<plugins>
<plugin>
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>jdbc</artifactId>
<version>10</version>
</dependency>
</dependencies>
<groupId>org.codehaus.mojo</groupId>
<artifactId>dbunit-maven-plugin</artifactId>
<version>1.0-beta-3</version>
<configuration>
<format>xml</format>
<dest>src\test\resources\dataset.xml</dest>
</configuration>
<executions>
<execution>
<id>DB</id>
<phase>test</phase>
<goals>
<goal>export</goal>
</goals>
<configuration>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@hostname:portNumber:dbName</url>
<username>userName</username>
<password>password</password>
<schema>schemaName</schema>
<skipOracleRecycleBinTables>true</skipOracleRecycleBinTables>
<queries>
<query>
<name>QUERY_FOO</name>
<sql>SELECT * from YOUR_TABLE</sql>
</query>
</queries>
</configuration>
</execution>
<execution>
<id>INVENTARIO_SIGRES</id>
<phase>test</phase>
<goals>
<goal>export</goal>
</goals>
<configuration>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@hostname:portNumber:dbName</url>
<username>userName</username>
<password>password</password>
<schema>schemaName</schema>
<skipOracleRecycleBinTables>true</skipOracleRecycleBinTables>
<queries>
<query>
<name>QUERY_ID</name>
<sql>SELECT * FROM YOUR_TABLE</sql>
</query>
<query>
<name>QUERY_ID_1</name>
<sql>SELECT * FROM YOUR_TABLE_NAME/sql>
</query>
<query>
<name>QUERY_ID_2</name>
<sql>SELECT * FROM YOUR_TABLE_NAME</sql>
</query>
<query>
<name>QUERY_ID_3</name>
<sql>SELECT * FROM YOUR_TABLE_NAME</sql>
</query>
</queries>
</configuration>
</execution>
<!–
<execution>
<id>YOUR_CONFIG_ID</id>
<phase>test</phase>
<goals>
<goal>export</goal>
</goals>
<configuration>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@hostname:portNumber:dbName</url>
<username>userName</username>
<password>password</password>
<schema>schemaName</schema>
<skipOracleRecycleBinTables>true</skipOracleRecycleBinTables>
<queries>
<query>
<name>YOUR_QUERY_NAME</name>
<sql>SELECT * FROM YOUR_TABLE</sql>
</query>
</queries>
</configuration>
</execution>
<execution>
<id>BANCO_RED</id>
<phase>test</phase>
<goals>
<goal>export</goal>
</goals>
<configuration>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@hostname:portNumber:dbName</url>
<username>userName</username>
<password>password</password>
<schema>schemaName</schema>
<skipOracleRecycleBinTables>true</skipOracleRecycleBinTables>
<queries>
<query>
<name>YOUR_QUERY_NAME</name>
<sql>SELECT * FROM YOUR_TABLE</sql>
</query>
</queries>
</configuration>
</execution>
–>
</executions>
</plugin>
</plugins>
</build>
</profile>
</profiles>