Wednesday 1 October 2014

Debug problems in JDBC

DriverManager class provides 'setLogWriter' method, which Sets the logging /tracing PrintWriter object that is used by the DriverManager and all drivers.

If you are using DataSource, XADataSource classes, similar method is available for logging.

/* Step 1: Import sql package */
import java.sql.*;
import java.io.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException, FileNotFoundException{
        PrintWriter pw;
        pw = new PrintWriter(new FileOutputStream("debug.txt"));
        
        DriverManager.setLogWriter(pw);
        
        Connection conn = getConnection();
        conn.setAutoCommit(false);
          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement();
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(3, \"Joel\")";
        stmt.execute(query);
        
        System.out.println("Calling roll back operation");
        
        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
    rs.close();
        stmt.close();
        conn.close();
        pw.close();
    }
}


Output
Loading/Registering driver
Connecting to database
Calling roll back operation
1 Krishna
2 Arjun
3 Joel
Dropping table employee

PrintWriter pw;
pw = new PrintWriter(new FileOutputStream("debug.txt"));
  Above statement creates a PrintWriter object.

DriverManager.setLogWriter(pw);
   Sets the logging/tracing PrintWriter object that is used by the DriverManager and all drivers.

After running above application, debug.txt contains below information.

registerDriver: com.mysql.jdbc.Driver@1f792cf
DriverManager.getConnection("jdbc:mysql://localhost/world")
    trying org.apache.derby.jdbc.AutoloadedDriver40
    trying org.apache.derby.jdbc.ClientDriver40
    trying com.mysql.jdbc.Driver
getConnection returning com.mysql.jdbc.Driver


Prevoius                                                 Next                                                 Home

No comments:

Post a Comment