/*
 * Created tdmarti, HSLU T+A - D3S (2011)
 */
package ch.hslu.edu.loghandler;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Handler;
import java.util.logging.LogRecord;
import java.util.logging.SimpleFormatter;
 
/**
 *
 * A Loghandler which write the GlassFish Log file into a MySQL Database
 * 
 * @author tdmarti
 */
public class GlassFishMySQLHandler extends Handler {
 
    private final String driverName = "com.mysql.jdbc.Driver";
    private String connURL = "jdbc:mysql://localhost:3306/logger";  //default value
    private String username = "admin";                              //default value
    private String password = "adminadmin";                         //default value
 
    private Connection connection;
 
    protected final static String insertSQL = "insert into GlassFishLog (level,"
            + "logger,message,sequence,"
            + "sourceClass,sourceMethod,threadID,timeEntered)"
            + "values(?,?,?,?,?,?,?,?)";
    protected final static String clearSQL = "delete from GlassFishLog;";
    protected PreparedStatement psmtInsert;
    protected PreparedStatement psmtClear;
 
    /**
     * Default Constructor.
     * Create a connection to the Database. Following properties can be set
     * <code>
     * -Dch.hslu.edu.loghandler.connURL
     * -Dch.hslu.edu.loghandler.username
     * -Dch.hslu.edu.loghandler.password
     * </code>
     */
    public GlassFishMySQLHandler() {
        try {
            // check and load properties
            if ( (System.getProperty("ch.hslu.edu.loghandler.connURL")) != null) {
                connURL = System.getProperty("ch.hslu.edu.loghandler.connURL");
            }
            if ((System.getProperty("ch.hslu.edu.loghandler.username")) != null){
                username = System.getProperty("ch.hslu.edu.loghandler.username");
            }
            if ((System.getProperty("ch.hslu.edu.loghandler.password")) != null){
                password = System.getProperty("ch.hslu.edu.loghandler.password");
            }
 
            Class.forName(driverName);
 
            connection = DriverManager.getConnection(connURL, username, password);
            psmtInsert = connection.prepareStatement(insertSQL);
            psmtClear = connection.prepareStatement(clearSQL);
        } catch (ClassNotFoundException ex) {
            System.out.println("ClassNotFoundException: " + ex);
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex);
        }
    }
 
    /**
     * Write out the log entry to the database.
     * @param record
     */
    @Override
    public void publish(LogRecord record) {
        if (! isLoggable(record)) {
            return;
        }
        String message = record.getMessage();
        if (message == null) {
            return;
        }
        if ((getFormatter() == null) ||
             (getFormatter().getClass() != (new SimpleFormatter()).getClass())) {
            setFormatter(new SimpleFormatter());
        }
        try {
            message = getFormatter().formatMessage(record);
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }
 
        try {
            psmtInsert.setInt(1,          record.getLevel().intValue());
            psmtInsert.setString(2, trunc(record.getLoggerName(), 200));
            psmtInsert.setString(3, trunc(message, 255));
            psmtInsert.setLong(4,         record.getSequenceNumber());
            psmtInsert.setString(5, trunc(record.getSourceClassName(), 200));
            psmtInsert.setString(6, trunc(record.getSourceMethodName(), 200));
            psmtInsert.setInt(7,          record.getThreadID());
            psmtInsert.setLong(8,         record.getMillis() );
            psmtInsert.executeUpdate();
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }
    }
 
    /**
     * Make a commit.
     */
    @Override
    public void flush() {
        try {
            connection.commit();
        } catch (SQLException ex) {
            Logger.getLogger(GlassFishMySQLHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
    /**
     * Close the Connection.
     * If the conneciton already closed, nothing will happen.
     * @throws SecurityException
     */
    @Override
    public void close() throws SecurityException {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
    }
 
    /**
     * Truncate a String, to the max lenght.
     *
     * @param str
     * @param length
     * @return
     */
    private String trunc(String str, int length) {
        if (str == null)
            return "";
        if (str.length() &lt; length) {
            return str;
        }
        return (str.substring(0, length));
    }
 
    /**
     * truncate the table.
     * Remove all entries inside the table
     *
     */
    public void clear() {
        try {
            psmtClear.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
    }
}

  CREATE TABLE GlassFishLog (
      `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      sourceClass VARCHAR(200) DEFAULT NULL,
      sourceMethod VARCHAR(200) DEFAULT NULL,
      threadID VARCHAR(200) DEFAULT NULL,
      timeEntered BIGINT DEFAULT NULL,
      `level` VARCHAR(200) DEFAULT NULL,
      logger VARCHAR(200) DEFAULT NULL,
      message VARCHAR(255) DEFAULT NULL,
      `sequence` VARCHAR(200) DEFAULT NULL
  );

java.util.logging.ConsoleHandler, ch.hslu.edu.loghandler.GlassFishMySQLHandler

 -Dch.hslu.edu.loghandler.connURL="jdbc:mysql://localhost:3306/logger"
 -Dch.hslu.edu.loghandler.username="sa"
 -Dch.hslu.edu.loghandler.password="1234"