Log4J JDBC Configuration For DataBase Log

Some time we need to use DataBase for automation where we can try to validate UI(FrontEnd) with DataBase(BackEnd). So there are lots of database communication is needed and we need to track this what happened with the database.

Log4J JDBC is use for this reason by which we can track the all database communication.

Log4J JDBC use the SLF4J for logging but it does not have the capacity for writing the log into the file so we use Log4J with this.

Now we will go for the configuration for this.

Step 1:

a.Download Log4 JDBC form here
b.Download SLF4J from here
c.Download Log4j from here

Step 2:

Put this jars into the class path of our project.

slf4j-api-1.7.7.jar
slf4j-log4j12-1.7.7.jar
log4jdbc4-1.2.jar
log4j-1.2.17.jar

Step 3:

Put this Log4J configuration file into the project directory. The File looks like that


# Logging levels are:
# DEBUG < INFO < WARN < ERROR < FATAL

# turn on the internal log4j debugging flag so we can see what it is doing
log4j.debug=true

# JDBC API layer call logging :
# INFO shows logging, DEBUG also shows where in code the jdbc calls were made,
# setting DEBUG to true might cause minor slow-down in some environments.
# If you experience too much slowness, use INFO instead.

# Log all JDBC calls except for ResultSet calls
log4j.logger.jdbc.audit=INFO,jdbc
log4j.additivity.jdbc.audit=false

# Log only JDBC calls to ResultSet objects
log4j.logger.jdbc.resultset=INFO,jdbc
log4j.additivity.jdbc.resultset=false

# Log only the SQL that is executed.
log4j.logger.jdbc.sqlonly=DEBUG,sql
log4j.additivity.jdbc.sqlonly=false

# Log timing information about the SQL that is executed.
log4j.logger.jdbc.sqltiming=DEBUG,sqltiming
log4j.additivity.jdbc.sqltiming=false

# Log connection open/close events and connection number dump
log4j.logger.jdbc.connection=FATAL,connection
log4j.additivity.jdbc.connection=false


# the appender used for the JDBC API layer call logging above, sql only
log4j.appender.sql=org.apache.log4j.FileAppender
log4j.appender.sql.File=./logs/sql.log
log4j.appender.sql.Append=false
log4j.appender.sql.layout=org.apache.log4j.PatternLayout
log4j.appender.sql.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n

# the appender used for the JDBC API layer call logging above, sql timing
log4j.appender.sqltiming=org.apache.log4j.FileAppender
log4j.appender.sqltiming.File=./logs/sqltiming.log
log4j.appender.sqltiming.Append=false
log4j.appender.sqltiming.layout=org.apache.log4j.PatternLayout
log4j.appender.sqltiming.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n

# the appender used for the JDBC API layer call logging above
log4j.appender.jdbc=org.apache.log4j.FileAppender
log4j.appender.jdbc.File=./logs/jdbc.log
log4j.appender.jdbc.Append=false
log4j.appender.jdbc.layout=org.apache.log4j.PatternLayout
log4j.appender.jdbc.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n

# the appender used for the JDBC Connection open and close events
log4j.appender.connection=org.apache.log4j.FileAppender
log4j.appender.connection.File=./logs/connection.log
log4j.appender.connection.Append=false
log4j.appender.connection.layout=org.apache.log4j.PatternLayout
log4j.appender.connection.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n

# Log levels
log4j.rootLogger=DEBUG,CONSOLE,R,HTML,TTCC
# Rolling File Appender
log4j.appender.TTCC=org.apache.log4j.RollingFileAppender
# Layout for Rolling File Appender
log4j.appender.TTCC.layout=org.apache.log4j.TTCCLayout
log4j.appender.TTCC.layout.DateFormat=ISO8601
# Path and file name to store the log file
log4j.appender.TTCC.File=./logs/testlog1.log
log4j.appender.TTCC.MaxFileSize=200KB
# Number of backup files
log4j.appender.TTCC.MaxBackupIndex=2
# Appender Configuration
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
# Pattern to output the caller's file name and line number
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
# Rolling File Appender
log4j.appender.R=org.apache.log4j.RollingFileAppender
# Path and file name to store the log file
log4j.appender.R.File=./logs/testlog.log
log4j.appender.R.MaxFileSize=200KB
# Number of backup files
log4j.appender.R.MaxBackupIndex=2
# Layout for Rolling File Appender
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%d - %c - %p - %m%n
# Define the HTML file appender
log4j.appender.HTML=org.apache.log4j.FileAppender
# Path and file name to store the log file
log4j.appender.HTML.File=./logs/application.html
# Define the html layout for file appender
log4j.appender.HTML.layout=org.apache.log4j.HTMLLayout
#Define Title of the HTML page
log4j.appender.HTML.layout.Title=Application logs
#Define the log location of application class file
log4j.appender.HTML.layout.LocationInfo=true

 Step 4:

Now we write a database related code to test this, code looks like that

//STEP 1. Import required packages

import java.sql.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class DataBaseWork {
  
  private static final Logger logger = Logger.getLogger("Log4JSettings");
 
   // JDBC driver name and database URL
   // For Logging We need to change this as given below
  
   static final String DB_URL = "jdbc:log4jdbc:derby://localhost:1527/sample";

   //  Database credentials
   static final String USER = "app";
   static final String PASS = "app";
 
   public static void main(String[] args) {
     
   PropertyConfigurator.configure("log4j.properties");
   Connection conn = null;
   Statement stmt = null;
   try{
     
       //STEP 2: Register JDBC driver
      //For Logging we need to set the driver as below

      Class.forName("net.sf.log4jdbc.DriverSpy");
     
      //STEP 3: Open a connection
     
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
     
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "select CUSTOMER_ID from APP.CUSTOMER";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
     
        while(rs.next()){
       
        //Retrieve by column name
        
         int id  = rs.getInt("CUSTOMER_ID");
        String first = rs.getString("CUSTOMER_ID");


         //Display values
        
          System.out.print("ID: " + id);
          System.out.print("\n");
      }
     
      //STEP 6: Clean-up environment
    
       rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){      
    //Handle errors for JDBC
   
     se.printStackTrace();
   }catch(Exception e){
    //Handle errors for Class.forName
    
       e.printStackTrace();
   }finally{
      //finally block used to close resources
     
      try{
         if(stmt!=null)
            stmt.close();
         logger.debug("Test Debug");
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end FirstExample


Now we see the log about the database activity.