How to Configure to Log4J to Log Messages to CUBRID Database

One of the features of Log4J framework () is to log messages to a JDBC-enabled database. Since CUBRID supports JDBC access, the configuration is pretty straightforward.

Prerequisites

In order to make this example work you will have to install on your system the following components:

• CUBRID database – This can be downloaded from here https://www.cubrid.org/downloads
• Java Development Kit (JDK) 1.6 from here http://www.oracle.com/technetwork/java/javase/downloads/index.html
• Log4J framework that can be downloaded from here – http://logging.apache.org/log4j/1.2/download.html

Example

Create a new directory where we will set up our project. In this directory copy:

• log4j-1.2.17.jar – This can be found in log4j distribution
• cubrid_jdbc.jar – You can find the CUBRID JDBC driver in jdbc directory from CUBRID server installation.

Now let's create a simple Java class that does some logging. Open your editor and create a new file called Tutorial.java

import org.apache.log4j.Logger;
import org.apache.log4j.LogManager;

public class Tutorial {
    public static void main(String[] args) {
        Logger log = LogManager.getLogger(Tutorial.class);
        log.info("Application successfully initialized");
        log.warn("Application is about to finish");
        try {
            if (1 / 0 == 0) {
                System.out.println("Fry the FPU");
            }
        } catch (Exception ex) {
            log.error("Some exception has been thrown", ex);
        }
        log.fatal("Application has finished");
    }
}

 

Configuring Log4J

In order to enable Log4J we will have to create the configuration file. In this configuration, we will define three appenders: Console, File, and JDBC. This way, the messages will be written to all these appenders. Open a text editor and put the following configuration (please note that CUBRID JDBC specific code is written in bold). Save as log4j.properties in the same directory with Java file

log4j.rootCategory=DEBUG, Console, File, CUBRID

# Console appender
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=[%d{ISO8601}]%5p%6.6r[%t]%x - %C.%M(%F:%L) - %m%n

# File
log4j.appender.File=org.apache.log4j.RollingFileAppender
log4j.appender.File.file=tutorial.log
log4j.appender.File.MaxFileSize=100KB
log4j.appender.File.layout=org.apache.log4j.PatternLayout

# CUBRID Database
log4j.appender.CUBRID = org.apache.log4j.jdbc.JDBCAppender
log4j.appender.CUBRID.driver = cubrid.jdbc.driver.CUBRIDDriver
log4j.appender.CUBRID.user = dba
log4j.appender.CUBRID.password =
log4j.appender.CUBRID.URL = jdbc:cubrid:172.31.201.15:33010:demodb:public::
log4j.appender.CUBRID.sql = INSERT INTO LOGS VALUES('%x','%d{yyyy-MM-dd  HH:mm:ss.SSS}','%C','%p','%m')
log4j.appender.CUBRID.layout=org.apache.log4j.PatternLayout

 

As you can see, configuring the JDBC appender requires specifying JDBC URL and also the connection credentials. Additionally, we have set the format of the timestamp into log4j.appender.CUBRID.sql in order to create a compatible DATETIME value.

 

Next, before we can use JDBC logging with CUBRID we will have to create a new table to store the logs. By default, CUBRID comes with a sample database named “demodb” that we connect with user “dba” and without password. We will use this database to store our logging table. Open up a console and execute the following statement, using csql utility:

$>csql -u dba -c 'CREATE TABLE logs (user_id VARCHAR(20) NOT NULL, dated DATETIME NOT NULL, logger VARCHAR(50) NOT NULL, "level" VARCHAR(10) NOT NULL, message VARCHAR(1000) NOT NULL);' demodb

 

Compile our source file into the class with the following command, this will output the Tutorial.class file.

$> javac -cp log4j-1.2.17.jar Tutorial.java

   

Now we will run the class file and see the output:

$> java -cp log4j-1.2.17.jar:cubrid_jdbc.jar:. Tutorial

 

If we look into the database with csql utility we will see that logs appear there too.    

csql -u dba -c "SELECT * FROM logs" demodb

 

Conclusion

Setting up log4j with CUBRID is very simple and straightforward. However, please note that Log4J framework is unable to log exceptions into the JDBC appender. Before deciding to use JDBC appender with any database consult the documentation. Log4J website states the following:

WARNING: This version of JDBCAppender is very likely to be completely replaced in the future. Moreover, it does not log exceptions.

http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/jdbc/JDBCAppender.html

References:

• JDBCAppender javadoc - http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/jdbc/JDBCAppender.html
• Setting up JDBC logging with MySQL - http://www.tutorialspoint.com/log4j/log4j_logging_database.htm