APIv5 Query API

Plan API version 5 - Query API

This page is about API that is available in version 4.9.0 and above.
API can be called on all platforms.

This API requires QUERY_API capability.

See APIv5 for dependency information

• Getting started with Query API

💭 What is this API for?

Query API is for accessing the Plan database from within your plugin. This can be used to store data in the database, or to write custom queries against the database.

Table of contents

• Obtaining QueryService
• Checking that database is what you expect
• Performing Queries
  • Available pre-made queries
• Executing Statements
• Recap of Thread blocking

Obtaining QueryService

QueryService can be obtained like so:

try {
    QueryService queryService = QueryService.getInstance();
} catch (IllegalStateException planIsNotEnabled) {
    // Plan is not enabled, handle exception
} 

Checking that database is what you expect

Database schema might change due to optimizations or additions so it might be necessary to check if the schema matches what you need.

• Database Schema

You can check if a table exists like this:

boolean hasTable = queryService.getCommonQueries().doesDBHaveTable(tableName);

You can check if a table has a column like this:

boolean hasColumn = queryService.getCommonQueries().doesDBHaveTableColumn(tableName, columName);

Any method called in CommonQueries blocks the thread.

In order to perform queries in the correct SQL dialect, you might need the following code.

String dbType = queryService.getDBType(); // SQLITE, MYSQL

Performing queries

Queries can be done with QueryService#query method. Queries block the thread.

Here is an example of a completed query

String result = queryService.query(
    "SELECT * FROM plan_users WHERE uuid=?",
    (PreparedStatement) statement -> {
        statement.setString(1, playerUUID.toString());
        try (ResultSet results = statement.executeQuery()) {
            return results.next ? results.getString("name") : null;
        }
    }
);

Available pre-made queries

Some queries can be performed without the query method above, they are available with QueryService#getCommonQueries

Any method called in CommonQueries blocks the thread.

• You can find the methods here

Executing statements

Statements can be executed with QueryService#execute method.

Here is an example of a executed statement

Future<?> done = queryService.execute(
    "INSERT INTO custom_table (uuid, value) VALUES (?, ?)",
    (PreparedStatement) statement -> {
        statement.setString(1, playerUUID.toString());
        statement.setString(2, value);
        statement.execute();
    }
);
done.get(); // (Optional) Block thread until transaction was executed

Note: Transactions

execute does not block the thread, because the SQL is executed on another thread concurrently. If you would like to wait for the statement to execute you can call Future#get() to block the thread.

Do not call Future#get() inside execute - This might deadlock the whole database due to blocked transaction thread!

Example of update - insert if not exist procedure

    public void storeProtocolVersion(UUID uuid, int version) throws ExecutionException {
        String update = "UPDATE plan_version_protocol SET protocol_version=? WHERE uuid=?";
        String insert = "INSERT INTO plan_version_protocol (protocol_version, uuid) VALUES (?, ?)";
        
        AtomicBoolean updated = new AtomicBoolean(false);
        try {
            queryService.execute(update, statement -> {
                statement.setInt(1, version);
                statement.setString(2, uuid.toString());
                updated.set(statement.executeUpdate() > 0);
            }).get(); // Wait
            if (!updated.get()) {
                queryService.execute(insert, statement -> {
                    statement.setInt(1, version);
                    statement.setString(2, uuid.toString());
                    statement.execute();
                });
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }

Recap on thread blocking

Blocking the server-thread usually leads to a crash. If something blocks a thread it is best to be executed inside an asynchronous task or on a separate thread.

• QueryService#execute does not block the thread.
• The Future returned by QueryService#execute can be used to block the thread until SQL executes with Future#get.
• QueryService#query blocks the thread.
• All methods in CommonQueries block the thread.