The MySQL addon is a bit different to the other modules RPF has, and doesn't necessarily change any of the core functionality (apart from adding a new manager type), but rather acts as an API. There are 4 parts of the API, MySQLUtils, MySQLComponents, Tables, and MySQLManagers.

On the application's startup, a file called mysql.json will be created, where you can configure your mysql connection, including a field for username, password, database, and host. By default, the host will use the port 3306, you can specify otherwise manually however.

You'll also need to specify, the names of your tables, in the same order as declared in your schema.sql.

The save-interval field is how often your managers will save their data to their tables.

I don't recommend directly interacting with MySQLUtils, as it may be confusing, however it is open in the api. No need to get an instance via guice or any other means, all the public methods inside are static.

MySQLComponents are just a collection of classes, that allow you to construct queries in a user friendly way, in a builder-like format (chaining methods). For example,

RowGetter.builder("table").location(KeyValueSet.builder().key("test").value("test").build()).build().get();

The above would return a CompletableFuture of a row with the column "test" equal to the value "test".

Although this is a step above interacting directly with MySQLUtils, you still shouldn't directly interact with it, instead use a Table.

Tables are the highest level connection to your database you'll have in your project, and provide easy access to all MySQLComponent Builders, pre-populating them with your table name, and even providing default methods for some trivial tasks, such as getting all rows, and saving a type to the database.

When extending table, there's 3 things you'll need to implement. The table name, which is provided in the super constructor, a rowToType method, which converts a DbRow into your type (provided to Table via generics), and a typeToRow method, which converts your type to a KeyValueSet.

Here's an example of a table, for a database containing peoples' name, and age.

public final class PeopleTable extends Table<Person> {
    public PeopleTable() {
        super("people");
    }

    @Override
    protected Person dataToType(KeyValueSet row) {
        return new Person((String) row.get("name"), (int) row.get("age"));
    }

    @Override
    protected KeyValueSet typeToData(Person person) {
        return KeyValueSet.builder()
                .key("name").value(person.getName())
                .key("age").value(person.getAge())
                .build();
    }
}

Something to note, as you can see Table accepts (and requires) a generic type, which should extend SearchUtils.Searchable as tables are made to be used with managers.

MySQL managers add two new functionalities to the core's SearchableManager implementation.

• Optional auto population
• Automatic syncing to corresponding table at a configurable interval

To start off, you'll extend MySQLManager, supplying your searchable object as the generic, as the underlying implementation is SearchableManager. You'll then need to supply an instance of your table in the super constructor. Keep in mind, you do not need to inject your table instance, as it shouldn't have any injections inside of it. If you do happen to have injections inside of it, you can inject the table via constructor injection, but you should also consider redesigning your application as this scenario should never happen.

public final class PeopleManager extends MySQLManager<Person> {
    public PeopleManager() {
        super(new PeopleTable());
        options.autoPopulate(false);
    }

    // ...
}

Auto population is set to true by default

The addon will automatically generate your tables and their schematics, if the config is provided with the table names, and a schema.sql file is embedded into the jar's root. The schema.sql should be standard SQL format, but split the table creations by a -. For example,

CREATE TABLE `people` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT,
  `name` TEXT NULL,
  `age` TINYINT NULL,
  PRIMARY KEY (`id`)
) COLLATE = 'utf8_general_ci' ENGINE = InnoDB;

-

CREATE TABLE `animals` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT,
  `name` TEXT NULL,
  `eye-count` TINYINT NULL,
  PRIMARY KEY (`id`)
) COLLATE = 'utf8_general_ci' ENGINE = InnoDB;

Then, in your mysql.json, the tables field would look like

"tables": [
  "people", "animals"
]