Last updated by prule 4 years ago

p6spy plugin

P6Spy lets you monitor the JDBC queries by proxying your database driver. In addition to logging the prepared statements, it also logs the sql with parameters in place so you can copy and paste the exact sql into your favourite database client to test the results.

This Grails plugin makes it easy to utilize P6Spy in your Grails applications.

Please create issues in JIRA if you encounter problems.

Introduction

This plugin contains 2 files - the p6spy jar and spy.properties. After installing the plugin in your Grails application, you can find them in:

  • <project directory>/plugins/p6spy-<version>/grails-app/conf/spy.properties
  • <project directory>/plugins/p6spy-<version>/lib/p6spy-1.3.jar
The install script updates your DataSource.groovy to add a commented line containing the p6spy driver class:
  • // String driverClassName = "com.p6spy.engine.spy.P6SpyDriver"

Example use

Install the plugin using

  • grails install-plugin p6spy
Create a new Grails application, add a domain class, and install the plugin: 
grails create-app spytest
cd spytest
grails create-domain-class book
grails install-plugin grails-p6spy-0.2.zip
In DataSource.groovy, comment out the HSQLDB driver and uncomment the p6spy driver: 
//   String driverClassName = "org.hsqldb.jdbcDriver"
     String driverClassName = "com.p6spy.engine.spy.P6SpyDriver"
Edit Book.groovy and add a property: 
class Book {
	String author
}
Now update BookTest.groovy to exercise the database: 
class BookTests extends GroovyTestCase {
	void testBook() {
		def book1 = new Book(author:'paul')
		book1.save()
		assertEquals(1, Book.count())
		def book2 = Book.get(book1.id)
		assertEquals('paul', book2.author)
		def books = Book.list()
		assertEquals(1, books.size())
	}
}
Now run your tests: 
grails test-app
The file spy.log is created, which contains a trace of the sql statements invoked during the test. 
20:07:16|16|1|statement||select sequence_name from information_schema.system_sequences
20:07:16|2|1|statement||create table book (id bigint generated by
default as identity (start with 1), version bigint not null, author
varchar(255) not null, primary key (id))
20:07:20|0|1|statement|insert into book (id, version, author) values
(null, ?, ?)|insert into book (id, version, author) values (null, 0,
'paul')
20:07:20|0|1|statement|call identity()|call identity()
20:07:20|2|1|statement|select count(*) as y0_ from book this_|select count(*) as y0_ from book this_
20:07:20|-1||resultset|select count(*) as y0_ from book this_|y0_ = 1
20:07:20|0|1|statement|select this_.id as id0_0_, this_.version as
version0_0_, this_.author as author0_0_ from book this_|select this_.id
as id0_0_, this_.version as version0_0_, this_.author as author0_0_
from book this_
20:07:20|-1||resultset|select this_.id as id0_0_, this_.version as version0_0_, this_.author as author0_0_ from book this_|
20:07:21|0|1|statement|delete from book|delete from book
This file has several columns, delimited by the | (pipe) character. The last column is the most interesting - it contains the SQL with the parameters inlined.

More information about configuring P6Spy can be found on the P6Spy website.

Contact paul @ javathinking.com

Reference