Suggested Pages

Sunday, May 20, 2012

JPA: queries with P6spy driver

Many Java developers know the property showSql that allows to see all JPA queries. Unfortunately the query format is something like this:
insert into ExampleSchema.Contacts (number, id) values (?, ?).
You can't see the real parameters, but in their place, there are question marks. In order to see all SQL queries in clear, we have to use the framework: P6spy.

The configuration of this framework requires only three steps:

Step-1: Replace the driverClass in your datasource with P6SpyDriver


Datasource without P6SpyDriver

 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  <property name="driverClass" value="org.hsqldb.jdbcDriver" />
  <property name="jdbcUrl" value="jdbc:hsqldb:mem:ex" />
  <property name="user" value="sa" />
  <property name="password" value="" />
 </bean>


Datasource with P6SpyDriver
 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
  <property name="driverClass" value="com.p6spy.engine.spy.P6SpyDriver"/> 
  <property name="jdbcUrl" value="jdbc:hsqldb:mem:SN"/> 
  <property name="user" value="sa"/> 
  <property name="password" value=""/> 
 </bean> 
As you can see, we have replaced only the driverClass.

Step-2: Put spy.properties in classe folder


In the following snippet I'll show the most common properties for customizing P6Spy framework. You have to notice in particular two properties:
  • realdriver property: it is set to value: org.hsqldb.jdbcDriver;
  • useprefix property: it is set to false because it's not used any prefix in jdbcUrl property of datasource bean.


# real driver
realdriver=org.hsqldb.jdbcDriver
useprefix=false
deregisterdrivers=true
module.log=com.p6spy.engine.logging.P6LogFactory

executionthreshold=

outagedetection=false
outagedetectioninterval=

# filter what is logged
filter=false

# comma separated list of tables to include when filtering
include     =
# comma separated list of tables to exclude when filtering
exclude     =

# sql expression to evaluate if using regex filtering
sqlexpression =


# turn on tracing
autoflush   = true

# sets the date format using Java's SimpleDateFormat routine
dateformat=yyyy-MM-dd hh:mm:ss

#list of categories to explicitly include
includecategories=

#list of categories to exclude: error, info, batch, debug, statement,
#commit, rollback and result are valid values
excludecategories=info,debug,result,batch



#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher
#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher
stringmatcher=

# prints a stack trace for every statement logged
stacktrace=false

# if stacktrace=true, specifies the stack trace to print
stacktraceclass=

# determines if property file should be reloaded
reloadproperties=false


reloadpropertiesinterval=60


#appender=com.p6spy.engine.logging.appender.Log4jLogger
appender=com.p6spy.engine.logging.appender.StdoutLogger
append=true

log4j.additivity.p6spy=false

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=DEBUG,STDOUT




Step-3: Insert p6spy dependency into your pom.xml


pom.xml
  <dependency>
   <groupId>p6spy</groupId>
   <artifactId>p6spy</artifactId>
   <version>1.3</version>
   <scope>test</scope>
  </dependency>

Supposing that your application writes on the Console, you can see SQL queries in clear.

2012-05-15 02:02:37|6|0|statement||CREATE SCHEMA ExampleSchema AUTHORIZATION DBA
2012-05-15 02:02:39|18|0|statement||select sequence_name from information_schema.system_sequences
2012-05-15 02:02:39|3|0|statement||
    create table ExampleSchema.Contacts (
        id integer not null,
        number varchar(255),
        primary key (id)
    )
------------testSaveContact(): start -------
Hibernate: 
    insert 
    into
        ExampleSchema.Contacts
        (number, id) 
    values
        (?, ?)
2012-05-15 02:02:39|1|0|statement|insert into ExampleSchema.Contacts (number, id) values (?, ?)|insert into ExampleSchema.Contacts (number, id) values ('22xxxx', 1)
2012-05-15 02:02:39|0|0|commit||
------------testSaveContact(): end -------
------------testRetrieveContact(): start -------
Hibernate: 
    select
        contact0_.id as id0_0_,
        contact0_.number as number0_0_ 
    from
        ExampleSchema.Contacts contact0_ 
    where
        contact0_.id=?
2012-05-15 02:02:39|1|0|statement|select contact0_.id as id0_0_, contact0_.number as number0_0_ from ExampleSchema.Contacts contact0_ where contact0_.id=?|select contact0_.id as id0_0_, contact0_.number as number0_0_ from ExampleSchema.Contacts contact0_ where contact0_.id=1
2012-05-15 02:02:39|-1||resultset|select contact0_.id as id0_0_, contact0_.number as number0_0_ from ExampleSchema.Contacts contact0_ where contact0_.id=1|number0_0_ = 22xxxx
2012-05-15 02:02:39|0|0|commit||
Contact [id=1, number=22xxxx]
------------testRetrieveContact(): end -------
Tests run: 2, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 5.512 sec

Results :

Tests run: 2, Failures: 0, Errors: 0, Skipped: 0

No comments :

Post a Comment

Suggested Pages