How to configure p6spy for mssql server with hibernate?

Question

In our web application we are using spring, hibernate & sql server 2016 as db. We are using jndi to connect to the database. To record all the queries executed by hibernate I am trying to implement the p6spy.

Here are the changes I have made.

Changed the resource information from

<Resource name="jdbc/eportalcore" auth="Container" 
            type="javax.sql.DataSource" 
            driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
            url="jdbc:sqlserver://localhost:1433;databaseName=eportal-core;" 
            username="eportaldbadmin" 
            password="P@ssw0rd" 
            maxTotal="100" 
            maxIdle="20" 
            minIdle="5" 
            maxWaitMillis="10000" />

to

<Resource name="jdbc/eportalcore" auth="Container" 
            type="javax.sql.DataSource" 
            driverClassName="com.p6spy.engine.spy.P6SpyDriver" 
            url="jdbc:p6spy:sqlserver://localhost:1433/eportal-core" 
            username="eportaldbadmin" 
            password="P@ssw0rd" 
            maxTotal="100" 
            maxIdle="20" 
            minIdle="5" 
            maxWaitMillis="10000" />

and added the spy.properties file under lib folder of tomcat directory. Also I have placed the p6spy-3.0.0.jar too inside the lib folder.

But after this my application is not getting connected to the DB. What mistake I am doing here? If I remove this changes then it is working fine.

Any suggestions?

Can you post the contents of spy.properties along with any error messages you are getting? — quintonm, Aug 16 '17 at 22:17

score 0 · Answer 1 · answered May 07 '18 at 17:18

I had the same question and here is what I did to make it work. Note my Database connection properties are in a property file (shouldn't be an issue) and that I use tomcat as an Application Server. You can also add P6Spy as a Maven dependency in your project.

Before : db.properties

db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://${db.server}/${db.name};useNTLMv2=true;domain=XX

After : Download(latest version when writing this response) the project. Put the p6spy-3.7.0.jar and spy.properties in the tomcat/lib folder. Change the spy.properties and application properties as noted under. Restart application and you should find a spy.log where your logs are printed normally.

db.properties

db.driver=com.p6spy.engine.spy.P6SpyDriver
db.url=jdbc:p6spy:jtds:sqlserver://${db.server}/${db.name};useNTLMv2=true;domain=XX

spy.properties

driverlist=net.sourceforge.jtds.jdbc.Driver

How to configure p6spy for mssql server with hibernate?

1 Answers1