MySQL-Module

Welcome developer
Today I would like to introduce you to a module that simplifies working with the MySQL database. It allows working without specifying a SQL syntax and is easy to use. As a small extra, HikariCP ( https://github.com/brettwooldridge/HikariCP) is used for the connection pool.

More information can be found here https://scarpex.biz/javadocs/mysql-module/.

The first thing you need to do is connect to the database.

private ConnectionPool connectionPool;

    public void onEnable() {
        this.connectionPool = new ConnectionPool(
                "127.0.0.1", // hostname
                3306, // port
                "codexdev", // database
                "scarpex", // username
                "ikda0Djkolda0DAmlDAc4"); // password

        this.connectionPool.setMaxPoolSize(2); // default is 5

        this.connectionPool.open();
    }

And do not forget to close the connection at the end.

public void onDisable() {
        this.connectionPool.close();
    }

Now you can easily specify a table.

private MysqlTable accounts;

    public void loadTable() {
        this.accounts = new MysqlTable(this.connectionPool, "accounts",
                new Column("id"),
                new Column("uuid", ColumnType.VARCHAR, 32, true),
                new Column("name", ColumnType.VARCHAR, 16),
                new Column("first_login", ColumnType.DATETIME),
                new Column("coins", ColumnType.INT));
     
        this.accounts.create(); // create table if not exists
    }

A table can also be deleted.

this.accounts.delete();

Inserting records is relatively easy.

public void createUser(UUID uuid, String name) {
        this.accounts.insert(new Object[]{
                0, // id is auto increment
                uuid.toString(), // uuid from user
                name, // name from user
                500 // coins
        });
    }

Records are easy to update.

public void updateNameAndCoins(UUID uuid, String newName, int newCoins) {
        this.accounts.update(new String[]{"name", "coins"}, new Object[]{newName, newCoins}, new String[]{"uuid"}, new Object[]{uuid.toString()});
    }

Records are easy to delete.

public void removeUser(UUID uuid) {
        this.accounts.remove(new String[]{"uuid"}, new Object[]{uuid.toString()});
    }

Records are easy to query.

public void getProfile(UUID uuid) {
        int id = 0, coins = 0;
        String name = null;

        try(StatementResult statementResult = this.accounts.get(new String[]{"id", "coins", "name"}, new String[]{"uuid"}, new Object[]{uuid.toString()})) {
            ResultSet result = statementResult.receive();

            while(result.next()) {
                id = result.getInt("id");
                coins = result.getInt("coins");
                name = result.getString("name");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.print(name + " have " + coins + " coins. He was number #" + id + " on the server.");
    }

... and more methods.