Tutorial: Log errors in a database

In this tutorial, we will use Speedment to log exceptions in a single-table database. First off, we need to create the schema.

CREATE DATABASE IF NOT EXISTS `errors` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `errors`;

We then need to create the table to store our errors in.

CREATE TABLE IF NOT EXISTS `error` (

    `id` bigint(20) NOT NULL 
        AUTO_INCREMENT,

    `timestamp` timestamp NOT NULL 
        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    `message` text NOT NULL,
  
    PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Since every client must have access to the database to make changes, we will create a new MySQL user and only grant it writing rights to this single table.

CREATE USER 'logger'@'%' IDENTIFIED BY 'pass1234';

GRANT INSERT ON `errors`.`error` 
    TO  'logger'@'%'
    IDENTIFIED BY  'pass1234'
    WITH MAX_QUERIES_PER_HOUR 0 
    MAX_CONNECTIONS_PER_HOUR 0 
    MAX_UPDATES_PER_HOUR 0 
    MAX_USER_CONNECTIONS 0
;

And now we can start with the java part. Open up your favorite IDE and add the Speedment dependency and Speedment Maven Plugin to your pom.xml-file. A complete guide for how to do this is available here.

Go into your src/main/json/ folder, creating it if it doesn't exist, and create a new file called speedment.json. Enter the following details into the file:

{
  "config" : {
    "name" : "errors",
    "dbmses" : [
      {
        "schemas" : [
          {
            "name" : "errors"
          }
        ],
        "name" : "db0",
        "ipAddress" : "127.0.0.1",
        "port" : 3306,
        "typeName" : "MySQL",
        "username" : "root"
      }
    ],
    "enabled" : true
  }
}

If your database is located somewhere else than on localhost, remember to change the info before you generate. When the file looks correct, run the reload maven goal and supply the database user password as follows.

mvn speedment:reload -Ddbms.password="!!!ROOT PASSWORD HERE!!!"

The password to the database needs to be supplied on the command line. The json file will be expanded with information about the entities in the database. Note that the json-file needs access to the database structure when the code is generated, but you can set more limited credentials in your runtime-application. To generate the java code, invoke the mvn speedment:generate goal.

Three packages should be created. The location of these can be controlled in the .groovy-file.

Now when all the database code has been generated it is easy to add logging to the application. To make sure the Speedment platform is started before any logging we can use the Singleton-pattern. Create a new java-singleton-class as follows:

public final class ErrorLogger {

    private final ErrorsApplication speed;
    private final ErrorManager errors;

    private ErrorLogger() {
        speed = new ErrorsApplicationBuilder()
                .withBundle(MySqlBundle.class)
                .withUsername("logger")
                .withPassword("pass1234")
                .build();

        errors = speed.getOrThrow(ErrorManager.class);
    }

    public void log(Throwable thrw) {
        errors.create()
                .setMessage(thrw.getMessage())
                .persist(errors);
    }

    public static ErrorLogger inst() {
        return Holder.INST;
    }

    private static class Holder {
        private static final ErrorLogger INST = new ErrorLogger();
    }
}

Now we can call the log method to persist the message in our database!

try {
    int i = 10 / 0;
} catch (Exception ex) {
    ErrorLogger.inst().log(ex);
    System.exit(-1);
}