Configure P6Spy - Debugger logger

• Advance: 

P6Spy is a library, very easy to set up, to monitor the interaction between your application and the database. It allows you to logging of your PL / SQL statements.

It supports many ORMS and of course including Hibernate. 

P6Sqp can show you all the values ​​of the parameters JDBC statements, reducing the developers’ debugging time

• Explanation:

Step 1) Starting from a maven project, we have to add to our pom.xml the next dependency as follow:

<dependency>
        <groupId>p6spy</groupId>
        <artifactId>p6spy</artifactId>
        <version>1.3</version>
 </dependency>

Step 2)  We have to change the mysql driver to P6SpyDriver

   //Class.forName("com.mysql.jdbc.Driver").newInstance();
   //Changed mysql driver to P6SpyDriver
   Class.forName("com.p6spy.engine.spy.P6SpyDriver").newInstance();

Step 3)  We create now the P6Spy properties file – “spy.properties” in the project classpath

Step 4) We open the file, and replace the “real driver” with the original JDBC driver

realdriver=com.mysql.jdbc.Driver

Step 5) Change the log file location in logfile property, all SQL statements will be logged into this file.

Windows : logfile = c:/spy.log

Linux:  logfile = /srv/log/spy.log

Step 6) Configure slow querys warning (Optional)

outagedetection=true
outagedetectioninterval= ? integer time in seconds

• Outcome

INSERT INTO Vehicles(idVehicle,name,description) values(?,?,?)|INSERT INTO Vehicles(idVehicle,name,description) values(1,'Car','Motor vehicle small or medium size, used for carrying people and can accommodate no more than nine seats.') 

SELECT idVehicle,name,description FROM Vehicles WHERE idVehicle = ?|SELECT idVehicle,name,description FROM Vehicles WHERE idVehicle =1 

• Code sample running

Complete project: https://gazpacho-examples.googlecode.com/svn/trunk/p6spy

package geekzpacho.examples.log.p6spy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GazpachoP6spy {

 public static void loadDriver() {
  
  try {
   //Class.forName("com.mysql.jdbc.Driver").newInstance();
   //Changed mysql driver to P6SpyDriver
   Class.forName("com.p6spy.engine.spy.P6SpyDriver").newInstance();
   
  } catch (Exception ex) {
   System.out.println("Error Exception loading Driver:" + ex);
  }
 }

 // ------ SQL ----------
 //CREATE SCHEMA `geekpacho` ;
 //
 //CREATE TABLE `vehicles` (
 //   `idVehicle` int(11) NOT NULL,
 //   `name` varchar(45) DEFAULT NULL,
 //   `description` varchar(150) DEFAULT NULL,
 //   PRIMARY KEY (`idVehicle`)
 // ) 
 
 public static void main(String... a) {

  GazpachoP6spy.loadDriver();

  // Define SQL Java object variables
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement pstmt1 = null;
  PreparedStatement pstmt2 = null;

  // Configure Connection parameters
  String user = "root";
  String password = "root";
  String url = "jdbc:mysql://localhost:3306/geekzpacho";

  try {
   //Open Connection to DB
   conn = DriverManager.getConnection(url, user, password);
   
   //Insert a record in BD
   String insert = "INSERT INTO Vehicles(idVehicle,name,description) values(?,?,?)";
   
   pstmt1 = conn.prepareStatement(insert);
   pstmt1.setInt(1, 1);
   pstmt1.setString(2,"Car");
   pstmt1.setString(3,"Motor vehicle small or medium size, used for carrying people and can accommodate no more than nine seats.");
   
   pstmt1.executeUpdate();
   System.out.println("information entered...");
   
   //Select the record in BD
   String query = "SELECT idVehicle,name,description FROM Vehicles " +
   "WHERE idVehicle = ?";
   
   pstmt2 = conn.prepareStatement(query);
   pstmt2.setInt(1, 1);
   rs = pstmt2.executeQuery();
   
   while(rs.next()) {
    System.out.println("idVehicle: " + rs.getInt(1));
    System.out.println("name: " + rs.getString(2));
    System.out.println("description: " + rs.getString(3));
    System.out.println();
   }
   
  } catch (Exception ex) {
   System.err.println("Error DriverManager.getConnection(): " + ex);
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
    if (pstmt1 != null) {
     pstmt1.close();
    }
    if (pstmt2 != null) {
     pstmt2.close();
    }
    if (conn != null) {
     conn.close();
    }
   } catch(SQLException ex){ 
   }
  }
 }
}