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.

Zip File Handling with Java And Selenium WebDriver

Some time we need to compress files for sending it.Here we learn how we can handle this programmatically.

We can handle this in two ways
1. Java SDK
2. Selenium WebDriver

1.Zip Using Java SDK:

public class Compress {

static List<String> fileList=new ArrayList<String>();
public static void main(String args[]) throws IOException {
        sendToZip(new File("Our Source To Zip"), new File("Our Destination To Zip\\ZipFile.ZIP"));
}
##########################################
#This Method is Created for List down Of All Files
# within the Source Path and put the Absolute Path 
#into the List
##########################################
    private static List<String> addFiletoList(File inputDir)
    {
        File[] listOfFile=inputDir.listFiles();
        for (File file : listOfFile) {
        if(file.isFile())
            fileList.add(file.getAbsolutePath());
        else
            addFiletoList(file);
        }
    return fileList;    
    }

##########################################
#This Method is Created to Zip the all File and 
#Save it to the Destination Path
###########################################
    private  static void sendToZip(File zipInput,File zipOutPut) throws FileNotFoundException, IOException
    {  
        List<String> fileList=new ArrayList<String>();
        fileList=addFiletoList(zipInput);
        FileOutputStream fos=new FileOutputStream(zipOutPut);
        ZipOutputStream zos=new ZipOutputStream(fos);
        for(String filePath:fileList){
            FileInputStream fis = new FileInputStream(filePath);
           
            //String name is required for showing the file name after zip same as before zip
 
            String name = filePath.substring(zipInput.getAbsolutePath().length()+1, filePath.length());
            ZipEntry entry = new ZipEntry(name);
            zos.putNextEntry(entry);

            int len;
            byte[] buffer = new byte[4096];
            while ((len = fis.read(buffer)) != -1) {
                zos.write(buffer, 0, len);
            }

            fis.close();
            zos.closeEntry();
        }
        zos.close();
        fos.close();
    }
} 

 1.Zip Using Selenium WebDriver:

Zip compress=new Zip();compress.zip(new File("Our Source To Zip"), new File("Our Destination To Zip"));





Now we can see that Zip using Selenium is more compact and easy coding but there is a problem if source and destination zip folder path is same then it creates a corrupted zip folder with in our zip folder.

So always keep in mind if we use selenium provided zip functionality then Zip folder Source and Zip folder Destination should be different.