- Advance:
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:
<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 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.logLinux: logfile = /srv/log/spy.log
Step 6) Configure slow querys warning (Optional)
outagedetection=trueoutagedetectioninterval= ? 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){
}
}
}
}
