Usage

Note: the following examples will deal with asynchronous usage.

Let's assume we are working with a simple table:

We need to represent tables by using Java classes, but this is simple either:

public class User {

    private int id;
    private String name;
    private int score;

    public User(int id, String name) {
        this.id = id;
        this.name = name;	
    }

    // getters, setter, constructor(s)
}

This section will only show examples on methods whose parameters take:

• SQL statements (static or with "?" placeholders);
• Array of objects representing the SQL statement's parameters (needed with parametrized SQL statements only) and array of Types representing parameters' types;
• Lambda functions (ResultSetRowMapper) which hold a mapping logic for supplying results (query methods only);
• Two other interfaces used for batch updates. They will be discussed in the batch updates section.

Query

If you need to query the database you can use two methods: queryForList and queryForObject.

The first one gets a list of results, the second one gets one result. Use it when you are sure that the query will supply exactly one result.

Query methods need a ResultSetRowMapper implementation. A ResultSetRowMapper implementation maps a result for each ResultSet row (we don't worry about exceptions or empty ResultSets).

Example on getting a list by using a static SQL statement:

CompletableFuture<List<User>> future = asyncDb.queryForList("SELECT * FROM users", (resultSet, rowNumber) -> {
    /* We use this ResultSetRowMapper implementation to work with ResultSet's rows.
    *  For example, if we want to get users with 0 score only we can do the following:
    */
    if (resultSet.getInt("score") == 0) {
        User user = new User();
        user.setId(resultSet.getInt("id"));
        user.setName(resultSet.getString("name"));
        return user;
    }
    return null;
});

// Just wait for the query to complete. When it's time, whenComplete method is executed
future.whenComplete((users, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    // "users" is the list of results, extracted from ResultSet with ResultSetRowMapper (users with 0 score)
    // note that the list can be empty, but never null
    for (User user : users) 
        player.sendMessage(user.getId() + " - " + user.getName());
});

Example on getting a single result by using an SQL statement with single parameter:

// make sure to import java.sql.Types
String sql = "SELECT * FROM users WHERE id = ?";
CompletableFuture<User> future = asyncDb.queryForObject(sql, new Integer[] {1}, (resultSet, rowNumber) -> {
    // Code inside this lambda will be executed once
    return new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3));
}, Types.INTEGER);

// Same logic as before
future.whenComplete((user, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    // Warning: a single result can be null
    if (user != null)
        player.sendMessage("Score of " + user.getName() + ": " + user.getScore());
});

Example on getting a single result by using an SQL statement with multiple parameters:

String sql = "SELECT * FROM users WHERE id = ? OR score > ?";
// If parameter types are different we must use new Object[] {...}
// e.g. new Object[] {1, "HelloSecondParam", 4.4, otherRandomVariable}
CompletableFuture<User> future = asyncDb.queryForObject(sql, new Integer[] {1, 10}, (resultSet, rowNumber) -> {
    return new User(resultSet.getInt(1), resultSet.getString(2));
}, Types.INTEGER, Types.INTEGER);

// Same logic as before
future.whenComplete((user, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    // Warning: a single result can be null
    if (user != null) {
        // things
    }
});

ATTENTION: SQL Types in methods' parameters are not mandatory. Avoiding SQL types will let Mystral to use the PreparedStatement#setObject method.
This method's behavior depends on the JDBC Driver you're using. Each JDBC Driver has its own PreparedStatement class' implementation. Be careful when not specifying SQL Types: this could lead to unpredictable results.

Single update (delete, insert, update, create, drop...)

These methods can handle every type of update statement (static or not).

Every update method returns the number of the affected rows. By setting getGeneratedKeys argument on true, the method will return the primary key of the generated row (if it was really created). Note: right now, this works with numeric primary keys only. "getGeneratedKeys" is useless when you are not using an INSERT statement.

The usage of these methods is as simple as the query ones. Here are some examples.

Update with parametrized SQL statement:

String sql = "INSERT INTO users VALUES(?, ?, ?)";
CompletableFuture<Integer> future = asyncDb.update(sql, new Object[] {3, "ErMandarone", 10}, false, Types.INTEGER, Types.VARCHAR, Types.INTEGER);

// Same logic as before
future.whenComplete((integer, exception) -> {
    if (exception != null) {
        return; // you can handle the error
    }
    System.out.println(integer); // Expected 1
}

Update with static SQL statement:

String sql = "INSERT INTO users VALUES(null, 'Helo', 50)";
CompletableFuture<Integer> future = asyncDb.update(sql, true, Types.NULL, Types.VARCHAR, Types.INTEGER);

// Same logic as before
future.whenComplete((integer, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    System.out.println(integer); // Expected the primary key of this new row
}

Batch update (delete, insert, update, create, drop...)

These methods perform multiple updates by using the same SQL statement.

Right now, no results are supplied by Mystral's batch update methods. Anyway, you can handle possible exceptions.

Usage of these interfaces is encouraged when you are using these methods:

• BatchSetter;
• ParametrizedBatchSetter.

Also, you don't have to specify SQL Types when you're using these interfaces.
Read their documentations for further information.

Example with BatchSetter:

// Let's prepare 100 insert statements
List<User> users = new ArrayList<>();
for (int i = 0; i < 100; i++) 
    users.add(new User(i, "Test" + 1, 0));

String sql = "INSERT INTO users VALUES(?, ?, ?)";

CompletableFuture<Void> future = asyncDb.batchUpdate(sql, new BatchSetter() {
    @Override
    public void setValues(@NotNull PreparedStatement ps, int i) throws SQLException {
	    User user = users.get(i);
        ps.setInt(1, user.getId());
	    ps.setString(2, user.getName());
	    ps.setInt(3, 0); 
    }

    @Override
    public int getBatchSize() {
        return users.size();
    }
});

//Same logic as before
future.whenComplete((unused, exception) -> {
    if (exception != null) {
        // you can handle the error
    }
});

Example with ParametrizedBatchSetter:

List<User> users = coolMethodFor100Users(); // Let's assume that "users" is a list containing 100 different users
String sql = "INSERT INTO users VALUES(?, ?, ?)";

CompletableFuture<Void> future = asyncDb.batchUpdate(sql, users, (ps, user) -> {
    ps.setInt(1, user.getId());
    ps.setString(2, user.getName());
    ps.setInt(3, user.getScore());
});

//Same logic as before
future.whenComplete((unused, exception) -> {
    if (exception != null) {
        // you can handle the error
    }
});

OrElseGet queries

As told in the Javadoc, "forObject" query methods throw an IncorrectDataSizeException if the query doesn't return exactly one result.

Knowing this, you may still want to retrieve one (and only one) result, even if it's nullable.
We created an "orElseGet" version, which allows you to trap that exception by implementing a Supplier. If the query return a null result, such implementation will supply the specified return value.

Attention: if the query returns more than 1 result, Mystral will throw an IncorrectDataSizeException.

String sql = "SELECT * FROM users WHERE id = ?";
Integer[] args = new Integer[] {1};
Supplier<User> sup = () -> new User(Integer.MIN_VALUE, null); // A default object
CompletableFuture<User> future = asyncDb.queryForObjectOrElseGet(sql, args, (resultSet, rowNumber) -> {
    return new User(resultSet.getInt(1), resultSet.getString(2));
}, sup, Types.INTEGER, Types.INTEGER);

// Same logic as before
future.whenComplete((user, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    // Warning: the supplier return value may be null
    if (user != null) {
        if (user.getId() == Integer.MIN_VALUE) {
            // User from supplier
        } else {
            // An existing user
        }
    }
});

Such technique is valid with "queryForList" methods too.

CompletableFuture<List<User>> future = asyncDb.queryForList("SELECT * FROM users", (resultSet, rowNumber) -> {
    /* We use this ResultSetRowMapper implementation to work with ResultSet's rows.
    *  For example, if we want to get users with 0 score only we can do the following:
    */
    if (resultSet.getInt("score") == 0) {
        User user = new User();
        user.setId(resultSet.getInt("id"));
        user.setName(resultSet.getString("name"));
        return user;
    }
    return null;
}, ArrayList::new); // instead of a new empty list, you could use another user list

// Just wait for the query to complete. When it's time, whenComplete method is executed
future.whenComplete((users, exception) -> {
    if (exception != null) {
        // you can handle the error
        return;
    }
    // "users" is the list of results, extracted from ResultSet with ResultSetRowMapper (users with 0 score)
    // note that the list can be empty, but it could be null too. It depends on the supplier implementation
    for (User user : users) 
        player.sendMessage(user.getId() + " - " + user.getName());
});