Creating a full log system for statements with AspectJ

This tip is quite useful because it allows you to know all the sentences that have been executed in your application regardless of packages, classes or functions. We can get this thanks to the aspects. We will use Apache POI to save all the information in an excel file. The information that this applicaion will save in the excel is:

  • First sheet – Second sheet
  • Date and time – Current Connections
  • Package/Class – Max Connections
  • Method – Last connection created
  • Code line
  • Execution time (ms)
  • SQL statement
If you want to know more about Apache POI you can go to this article How to create an excel file with Apache POI.
In order to compile and execute the following code you need the jaspect.jar. You can find it on Eclipse webpage. Once you have already downloaded the classes don’t forget to add them to your classpath.
You will also need the POI classes to compile the code. You can download the jars from the Apache POI website. Once you have already downloaded the classes don’t forget to add them to your classpath.

The following code if the aspect which will intercept every time a statement is executed:

package log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 
 * This Aspect saves in an excel file all the statements that have been executed in our application.
 * 
 * @author ProgrammingWorkshop.net
 * @version 1.0
 * 
 */
public aspect LogDataBase {

	/*************************
	 ****** CONNECTIONS ******
	 *************************/

	int activeConnections = 0;
	int maxActiveConnections = 0;
	long lastConnectionCreated = 0;

	private static final String DATE_FORMAT = "dd/MM/yyyy HH:mm:ss";

	// We are going to intercept the getConnection function in DriverManager as well as the close in
	// the Connection class
	pointcut connectionAdded(): call(* DriverManager.getConnection(..));

	pointcut connectionReleased(): call(* Connection.close(..));

	/**
	 * We record every time that a connection is released
	 */
	after() : connectionReleased() {
		activeConnections--;
		addConnectionInfo();
	}

	/**
	 * We record every time that a connection is created
	 */
	after() : connectionAdded() {
		activeConnections++;
		if (activeConnections > maxActiveConnections) {
			maxActiveConnections++;
		}
		addConnectionInfo();
	}

	private void addConnectionInfo() {
		List<Object> list = new ArrayList<Object>();
		list.add(activeConnections);
		list.add(maxActiveConnections);
		list.add(new SimpleDateFormat(DATE_FORMAT).format(new Date()));
		new Excel().writeConnnectionSummary(list);
	}

	/************************
	 ****** STATEMENTS ******
	 ************************/

	// We intercept every time that a statement is executed
	pointcut statementExecuted(): call(* Statement.execute*(..));

	/**
	 * Before calling the execute* method in the statement we record the time. After that we call
	 * the execute method and we record the time again to know exactly how much the statement lasted
	 */
	Object around() : statementExecuted() {
		long time = System.currentTimeMillis();
		Object obj = proceed();
		time = System.currentTimeMillis() - time;

		String sql = "";
		if ((thisJoinPoint.getArgs().length > 0) && (thisJoinPoint.getArgs()[0] instanceof String)) {
			sql = (String) thisJoinPoint.getArgs()[0];
		}

		List<String> list = new ArrayList<String>();
		list.add(new SimpleDateFormat(DATE_FORMAT).format(new Date()));
		list.add(thisEnclosingJoinPointStaticPart.getSignature().getDeclaringTypeName());
		list.add(thisEnclosingJoinPointStaticPart.getSignature().getName());
		list.add(String.valueOf(thisJoinPointStaticPart.getSourceLocation().getLine()));
		list.add(String.valueOf(time));
		list.add(sql);
		new Excel().writeRow(list);

		return obj;
	}
}

We use the following code to record the data into an excel file:

package log;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 
 * This class is called by the AspectJ LogDataBase. The main objective of this class is to give
 * several methods to write all the information retrieved in an excel file using Apache POI.
 * 
 * @author ProgrammingWorkshop.net
 * @version 1.0
 * 
 */
public class Excel {

	private HSSFWorkbook book = null;
	private HSSFSheet sheet = null;
	private HSSFSheet sheet2 = null;
	private HSSFCellStyle style = null;

	public static final String FILE_NAME = "c:\\statements.xls";

	/**
	 * 
	 * The constructor checks if the file exists. If the file exists it load all the information in
	 * our book. Otherwise it will create a new instance for HSSFWorkbook. It also create the sheets
	 * and call the different functions to create the styles and headers.
	 * 
	 */
	public Excel() {
		File file = new File(FILE_NAME);
		if (file.exists()) {
			try {
				book = new HSSFWorkbook(new FileInputStream(file));
				createStyle();
				sheet = book.getSheetAt(0);
				sheet2 = book.getSheetAt(1);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			book = new HSSFWorkbook();
			createStyle();
			sheet = book.createSheet("Statements executed");
			sheet2 = book.createSheet("Connection sumary");
			header();
		}
	}

	/**
	 * 
	 * This method creates the sheet style including font and background
	 * 
	 */
	void createStyle() {
		style = book.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
	}

	/**
	 * 
	 * This method establish the header for our excel sheet
	 * 
	 */
	public void header() {
		HSSFRow fila = sheet.createRow(0);
		HSSFCell cell = null;

		int i = 0;

		cell = fila.createCell(i);
		cell.setCellValue("Date and time");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 20 * 256);

		cell = fila.createCell(i);
		cell.setCellValue("Package/Class");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 30 * 256);

		cell = fila.createCell(i);
		cell.setCellValue("Method");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 20 * 256);

		cell = fila.createCell(i);
		cell.setCellValue("Code line");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 10 * 256);

		cell = fila.createCell(i);
		cell.setCellValue("Execution time (ms)");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 20 * 256);

		cell = fila.createCell(i);
		cell.setCellValue("SQL statement");
		cell.setCellStyle(style);
		sheet.setColumnWidth(i++, 90 * 256);
	}

	/**
	 * 
	 * This method writes the connection summary in the second sheet of our book
	 * 
	 * @param rowData
	 *            This is the information we are going to save in the second sheet. The information
	 *            is in this order: Current connections, Max connections and last connection created
	 * 
	 */
	public void writeConnnectionSummary(List<?> rowData) {
		HSSFCellStyle style = book.createCellStyle();
		style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		sheet2.setColumnWidth(0, 30 * 256);
		sheet2.setColumnWidth(1, 25 * 256);

		HSSFRow row = sheet2.createRow(1);
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("Current Connections");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		cell.setCellValue((Integer) rowData.get(0));

		row = sheet2.createRow(2);
		cell = row.createCell(0);
		cell.setCellValue("Max Connections");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		cell.setCellValue((Integer) rowData.get(1));

		row = sheet2.createRow(3);
		cell = row.createCell(0);
		cell.setCellValue("Last connection created");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		cell.setCellValue((String) rowData.get(2));

		writeFile();
	}

	/**
	 * 
	 * This method save our book into the specified file
	 * 
	 */
	public void writeFile() {
		try {
			FileOutputStream elFichero = new FileOutputStream(FILE_NAME);
			book.write(elFichero);
			elFichero.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * This method records a row into the first sheet
	 * 
	 * @param rowData
	 *            This is a row in our first sheet. The information that stores is in this order:
	 *            Date and time, Package/Class, Method, Code line, Execution time (ms) and SQL
	 *            statement.
	 * 
	 */
	public void writeRow(List<String> rowData) {
		HSSFRow fila = sheet.createRow(sheet.getLastRowNum() + 1);
		for (int i = 0; i < rowData.size(); i++) {
			HSSFCell celda = fila.createCell(i);
			celda.setCellValue(rowData.get(i));
		}
		writeFile();
	}
}

Now, if we open our statement.xls file we will see this in the first sheet:

aspectj1

The second sheet will give us information about the connection:

aspectj2

Aspects are amazing, aren’t they?. I hope this code can be helpful for someone.

Tagged on:

Leave a Reply

Your email address will not be published. Required fields are marked *


× 5 = forty five

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>