9 Query API Getting started

Aurora Lahtela edited this page 2022-10-30 11:32:28 +02:00

Table of Contents

• Query API - Getting started
• ✔️ Requirements
• 🚩 Tutorial Goals
• Goal #1: Adding Plan API as a dependency
• 1.1: Add Plan repository to your project
• 1.1: Add Plan repository to your project
• Maven
• Gradle
• Other build tools
• 1.2: Add Plan API as a dependency
• Maven
• Gradle
• Other
• 1.3: Add Plan as a soft-dependency in your plugin
• Spigot, Nukkit & Bungeecord (plugin.yml)
• Sponge & Velocity (Plugin annotation)
• Goal #2: Access Plan API from your plugin
• 2.1: Create a class to separate Plan imports from your main class
• 2.2: Construct and call the PlanHook in your plugin enable.
• Goal #3: Accessing Query API - Creating a QueryAPIAccessor
• Goal #4: Query existing Plan data
• More

Query API - Getting started

This tutorial attempts to guide you through using Query API in your plugin, for more in-depth documentation about different parts of the API, see Query API.

These icons are used to aid understanding

💭 Question about possible issues (Someone has had these before)
💡 Extra stuff

✔️ Requirements

• A java plugin project for a minecraft server

🚩 Tutorial Goals

Here are the goals the tutorial aims to guide you through.
At the end of this tutorial you will have

• .. Added Plan API as a dependency to your project
  • (.. added Plan as soft-dependency to your plugin)
• .. Created 2 new classes to use the API
• .. Accessed the Plan database using the 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.

---

Goal #1: Adding Plan API as a dependency

1.1: Add Plan repository to your project

1.1: Add Plan repository to your project

Maven

• Add the repository to your <repositories>-block in pom.xml of your project

<repository>
    <id>jitpack</id>
    <url>https://jitpack.io</url>
</repository>

Gradle

• Add the repository to your repositories-block in build.gradle of your project

maven {
    url  "https://jitpack.io" 
}

Other build tools

• Add Plan API as a dependency in your build tool. (you can download it from here https://github.com/plan-player-analytics/Plan/packages/651264)

1.2: Add Plan API as a dependency

• Go to https://github.com/plan-player-analytics/Plan/tags and see what is the latest version number. No need to download anything.

Maven

• Add Plan API as a dependency to your <dependencies>-block in in pom.xml of your project

<dependency>
  <groupId>com.github.plan-player-analytics</groupId>
  <artifactId>Plan</artifactId>
  <version>{jitpack version}</version> <!-- Add the version number here -->
  <scope>provided</scope>
</dependency>

Gradle

• Add Plan API as a compile & test compile time dependency to your dependencies-block in build.gradle of your project.

compileOnly 'com.github.plan-player-analytics:Plan:{jitpack version}'
testCompileOnly 'com.github.plan-player-analytics:Plan:{jitpack version}'

Other

• Add Plan as a dependency in your build tool. (you can download it from here https://github.com/plan-player-analytics/Plan/releases)

1.3: Add Plan as a soft-dependency in your plugin

Spigot, Nukkit & Bungeecord (plugin.yml)

• Add Plan in softdepend in plugin.yml of your project

softdepend:
- Plan
# nukkit
softdepend: ["Plan"]
# bungee
softDepends:
- Plan

Sponge & Velocity (Plugin annotation)

• Add Plan as an optional dependency to the @Plugin annotation

@Plugin(
        id = ...,
        dependencies = {
                @Dependency(id = "plan", optional = true)
        }
)

✔️ Your project now includes Plan API as a dependency!

Goal #2: Access Plan API from your plugin

2.1: Create a class to separate Plan imports from your main class

In order to keep Plan as an optional dependency, all access to the Plan API should be made from a separate class. In this tutorial this will be called PlanHook, but you can call it whatever you want.

In this case we're creating QueryAPIAccessor in order to write all queries in a separate class from PlanHook.

Let's take a look at this example class:

import com.djrapitops.plan.capability.CapabilityService;
import com.djrapitops.plan.query.QueryService;

public class PlanHook {

    public PlanHook() {
    }

    public Optional<QueryAPIAccessor> hookIntoPlan() {
        if (!areAllCapabilitiesAvailable()) return Optional.empty();
        return Optional.ofNullable(createQueryAPIAccessor());
    }

    private boolean areAllCapabilitiesAvailable() {
        CapabilityService capabilities = CapabilityService.getInstance();
        return capabilities.hasCapability("QUERY_API");
    }

    private QueryAPIAccessor createQueryAPIAccessor() {
        try {
            return new QueryAPIAccessor(QueryService.getInstance());
        } catch (IllegalStateException planIsNotEnabled) {
            // Plan is not enabled, handle exception
            return null;
        }
    }
}

Creating a separate class is necessary to keep NoClassDefFoundError away from loading your plugin when Plan is not enabled!

Here is some more explanation for each section of the code in case you need more information.

hookIntoPlan()

    public Optional<QueryAPIAccessor> hookIntoPlan() {
        if (!areAllCapabilitiesAvailable()) return Optional.empty();
        return Optional.ofNullable(createQueryAPIAccessor());
    }

• This method checks if Plan has the capabilities you need, the check is similar to how some plugins ask you to check the version number.
• If the capabilities are available, the query api accessor is created (We'll look into that class next)
• Java Optional is used to tell if the created class is available https://docs.oracle.com/javase/8/docs/api/java/util/Optional.html

areAllCapabilitiesAvailable()

    private boolean areAllCapabilitiesAvailable() {
        CapabilityService capabilities = CapabilityService.getInstance();
        return capabilities.hasCapability("QUERY_API");
    }

• Checks that QUERY_API capability is available. Some features might need more capabilities, and when they do it is mentioned in the documentation. Those capabilities can then be added here.

createQueryAPIAccessor()

    private QueryAPIAccessor createQueryAPIAccessor() {
        try {
            return new QueryAPIAccessor(QueryService.getInstance());
        } catch (IllegalStateException planIsNotEnabled) {
            // Plan is not enabled, handle exception
            return null;
        }
    }

• Creates QueryAPIAccessor (We'll create that class next) with QueryService as the constructor parameter.
• IllegalStateException might be thrown if Plan has not enabled properly, so we return null that the Optional above is empty.

2.2: Construct and call the PlanHook in your plugin enable.

In this example the Spigot JavaPlugin#onEnable is used, but you can add these methods to wherever you wish, as long as it is called after Plan has been loaded & enabled.

💭 When does Plan enable?

• Spigot & Nukkit: After dependencies have enabled & worlds have been loaded
• Sponge: After dependencies on GameStartedServerEvent
• BungeeCord: After dependencies
• Velocity: After dependencies on ProxyInitializeEvent

In the next step: Creating QueryAPIAccessor

public void onEnable() {
    ... // The example plugin enables itself
    
    try {
        Optional<QueryAPIAccessor> = new PlanHook().hookIntoPlan();
    } catch (NoClassDefFoundError planIsNotInstalled) {
        // Plan is not installed
    }
}

✔️ You can now access Plan API from somewhere!

Goal #3: Accessing Query API - Creating a QueryAPIAccessor

In order to keep code maintainable, a second class called QueryAPIAccessor is created. This is then used to access Plan API's QueryService.

In this example data is stored in a new table inside the Plan database. The example is from ViaVersion Extension

Let's take a look at the class:

import com.djrapitops.plan.query.QueryService;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.atomic.AtomicBoolean;

public class QueryAPIAccessor {

    private final QueryService queryService;

    public QueryAPIAccessor(QueryService queryService) {
        this.queryService = queryService;
        createTable();
        queryService.subscribeDataClearEvent(this::recreateTable);
        queryService.subscribeToPlayerRemoveEvent(this::removePlayer);
    }

    private void createTable() {
        String dbType = queryService.getDBType();
        boolean sqlite = dbType.equalsIgnoreCase("SQLITE");

        String sql = "CREATE TABLE IF NOT EXISTS plan_version_protocol (" +
                "id int " + (sqlite ? "PRIMARY KEY" : "NOT NULL AUTO_INCREMENT") + ',' +
                "uuid varchar(36) NOT NULL UNIQUE," +
                "protocol_version int NOT NULL" +
                (sqlite ? "" : ",PRIMARY KEY (id)") +
                ')';

        queryService.execute(sql, PreparedStatement::execute);
    }

    private void dropTable() {
        queryService.execute("DROP TABLE IF EXISTS plan_version_protocol", PreparedStatement::execute);
    }

    private void recreateTable() {
        dropTable();
        createTable();
    }

    private void removePlayer(UUID playerUUID) {
        queryService.execute(
                "DELETE FROM plan_version_protocol WHERE uuid=?",
                statement -> {
                    statement.setString(1, playerUUID.toString());
                    statement.execute();
                }
        );
    }

    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();
        }
    }

    public int getProtocolVersion(UUID uuid) {
        String sql = "SELECT protocol_version FROM plan_version_protocol WHERE uuid=?";

        return queryService.query(sql, statement -> {
            statement.setString(1, uuid.toString());
            try (ResultSet set = statement.executeQuery()) {
                return set.next() ? set.getInt("protocol_version") : -1;
            }
        });
    }

    public Map<Integer, Integer> getProtocolVersionCounts() {
        UUID serverUUID = queryService.getServerUUID()
                .orElseThrow(NotReadyException::new);
        final String sql = "SELECT protocol_version, COUNT(1) as count" +
                " FROM plan_version_protocol" +
                " INNER JOIN plan_user_info on plan_version_protocol.uuid=plan_user_info.uuid" +
                " WHERE plan_user_info.server_uuid=?" +
                " GROUP BY protocol_version";
        return queryService.query(sql, statement -> {
            statement.setString(1, serverUUID.toString());
            try (ResultSet set = statement.executeQuery()) {
                Map<Integer, Integer> versions = new HashMap<>();
                while (set.next()) {
                    versions.put(set.getInt("protocol_version"), set.getInt("count"));
                }
                return versions;
            }
        });
    }
}

More information about each method

Construction

    private final QueryService queryService;

    public QueryAPIAccessor(QueryService queryService) {
        this.queryService = queryService;
        createTable();
        queryService.subscribeDataClearEvent(this::recreateTable);
        queryService.subscribeToPlayerRemoveEvent(this::removePlayer);
    }

• The constructor takes QueryService.
• The table is created using a method.
• A method is given as a listener for subscribeDataClearEvent that is fired when a user clears Plan database with a command.
• A method is given as a listener for subscribeToPlayerRemoveEvent that is fired when a user removes a Plan player with a command, or when Plan cleans that player out of the database due to inactivity.

createTable

    private void createTable() {
        String dbType = queryService.getDBType();
        boolean sqlite = dbType.equalsIgnoreCase("SQLITE");

        String sql = "CREATE TABLE IF NOT EXISTS plan_version_protocol (" +
                "id int " + (sqlite ? "PRIMARY KEY" : "NOT NULL AUTO_INCREMENT") + ',' +
                "uuid varchar(36) NOT NULL UNIQUE," +
                "protocol_version int NOT NULL" +
                (sqlite ? "" : ",PRIMARY KEY (id)") +
                ')';

        queryService.execute(sql, PreparedStatement::execute);
    }

• dbType needs to be checked because different databases can have different SQL syntax. In this case SQLite has different primary key syntax.
• Documentation about checking that the database is what you expect (Middle-click to open in new tab)
• sql is created based on what database is in use.
• The sql is executed as is using the QueryService. It is also possible to write a lambda function to set parameters ? inside the query, some of the following methods use that.
• Documentation about executing statements (Middle-click to open in new tab)

dropTable

    private void dropTable() {
        queryService.execute("DROP TABLE IF EXISTS plan_version_protocol", PreparedStatement::execute);
    }

• The sql is executed as is using the QueryService. It is also possible to write a lambda function to set parameters ? inside the query, some of the following methods use that.
• Documentation about executing statements (Middle-click to open in new tab)

recreateTable

    private void recreateTable() {
        dropTable();
        createTable();
    }

• Uses the 2 previous methods to first drop and then create the table again.

removePlayer

    private void removePlayer(UUID playerUUID) {
        queryService.execute(
                "DELETE FROM plan_version_protocol WHERE uuid=?",
                statement -> {
                    statement.setString(1, playerUUID.toString());
                    statement.execute();
                }
        );
    }

• This method executes sql with one parameter inside the query, which is set inside the lambda. Afterwards PreparedStatement#execute is called.
• Documentation about executing statements (Middle-click to open in new tab)

storeProtocolVersion

    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();
        }
    }

• In order to update data in the table, UPDATE or INSERT is used. This keeps a single row in the database. It is also possible to keep inserting values instead if you want lots of entries.
• AtomicBoolean is created to track if the update was successful - Using atomic is recommended because QueryService#execute executes the statements on a separate thread.
• updated is set as true/false based on how many rows were updated by the update sql.
• Future#get is called on the first execution (At the // Wait). This blocks the thread until the statement finishes executing, so it is best to not call storeProtocolVersion on a server thread to avoid crashes. Do not call Future#get() inside execute - This might deadlock the whole database due to blocked transaction thread!
• updated is now checked, if the update did not update any rows, it means a row for the UUID did not exist. insert statement is executed.
• InterruptedException can be thrown due to Future#get blocking the thread, so it is caught.
• Documentation about executing statements (Middle-click to open in new tab)

💡 Batch execution

It is possible to execute batches with PreparedStatements. Set the parameters inside a for-loop, call PreparedStatement#addBatch and then call PreparedStatement#executeBatch at the end of the for-loop

getProtocolVersion

    public int getProtocolVersion(UUID uuid) {
        String sql = "SELECT protocol_version FROM plan_version_protocol WHERE uuid=?";

        return queryService.query(sql, statement -> {
            statement.setString(1, uuid.toString());
            try (ResultSet set = statement.executeQuery()) {
                return set.next() ? set.getInt("protocol_version") : -1;
            }
        });
    }

• This example shows how to query one row from the database.
• QueryService#query blocks the thread.
• The lambda expression gets a PreparedStatement that can be then used to query.
• try-with-resources is used for ResultSet to close it after query is finished.
• set.next() checks if the query got any rows as the result
• Documentation about performing queries (Middle-click to open in new tab)

getProtocolVersionCounts

    public Map<Integer, Integer> getProtocolVersionCounts() {
        UUID serverUUID = queryService.getServerUUID()
                .orElseThrow(NotReadyException::new);
        final String sql = "SELECT protocol_version, COUNT(1) as count" +
                " FROM plan_version_protocol" +
                " INNER JOIN plan_user_info on plan_version_protocol.uuid=plan_user_info.uuid" +
                " WHERE plan_user_info.server_uuid=?" +
                " GROUP BY protocol_version";
        return queryService.query(sql, statement -> {
            statement.setString(1, serverUUID.toString());
            try (ResultSet set = statement.executeQuery()) {
                Map<Integer, Integer> versions = new HashMap<>();
                while (set.next()) {
                    versions.put(set.getInt("protocol_version"), set.getInt("count"));
                }
                return versions;
            }
        });
    }

• This example shows how to query more rows, and how to get the server UUID of the current server from QueryService.
• queryService.getServerUUID() returns Optional<UUID>, that is empty if Plan has enabled improperly. NotReadyException in this case, but you can use your own exception if you wish. (NotReadyException is part of the DataExtension API)
• The query sql JOINs plan_user_info table in order to filter the results of the current server.
• Documentation on Plan database schema (Middle-click to open in new tab)
• while (set.next()) is used to loop through all rows the query returns.
• Documentation about performing queries (Middle-click to open in new tab)

✔️ You can now use Plan API to store and query your own data

Goal #4: Query existing Plan data

This goal is for a different kind of use of Query API, so we'll create another version of QueryAPIAccessor class.

Let's take a look:

import com.djrapitops.plan.query.QueryService;
import com.djrapitops.plan.query.CommonQueries;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.atomic.AtomicBoolean;

public class QueryAPIAccessor {

    private final QueryService queryService;

    public QueryAPIAccessor(QueryService queryService) {
        this.queryService = queryService;

        ensureDBSchemaMatch();
    }

    private void ensureDBSchemaMatch() {
        CommonQueries queries = queryService.getCommonQueries();
        if (
            !queries.doesDBHaveTable("plan_sessions")
            || !queries.doesDBHaveTableColumn("plan_sessions", "uuid")
        ) {
            throw new IllegalStateException("Different table schema");
        }
    }

    public long getPlaytimeLast30d(UUID playerUUID) {
        long now = System.currentTimeMillis();
        long monthAgo = now - TimeUnit.DAYS.toMillis(30L);
        UUID serverUUID = queryService.getServerUUID()
                .orElseThrow(IllegalStateException::new);
        return queryService.getCommonQueries().fetchPlaytime(
            playerUUID, serverUUID, monthAgo, now
        );
    }

    public long getPlaytimeLast30dOnAllServers(UUID playerUUID) {
        long now = System.currentTimeMillis();
        long monthAgo = now - TimeUnit.DAYS.toMillis(30L);
        Set<UUID> serverUUIDs = queryService.getCommonQueries()
                .fetchServerUUIDs();

        long playtime = 0;
        for (UUID serverUUID : serverUUIDs) {
            playtime += queryService.getCommonQueries().fetchPlaytime(
                playerUUID, serverUUID, monthAgo, now
            );
        }
        return playtime;
    }

    public long getSessionCount(UUID playerUUID) {
        UUID serverUUID = queryService.getServerUUID()
                .orElseThrow(IllegalStateException::new);

        String sql = "SELECT COUNT(1) as session_count FROM plan_sessions WHERE uuid=?";
        return queryService.query(sql, statement -> {
            statement.setString(1, playerUUID.toString());
            try (ResultSet set = statement.executeQuery()) {
                return set.next() ? set.getLong("session_count") : -1L;
            }
        });
    }

• This version uses CommonQueries in order to obtain some data from Plan, and a custom query for other data.
• Documentation for Database Schema

✔️ You can now use Plan API to query your Plan data

More

• 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.

For more in-depth details about Query API, see Query API documentation

Documentation

• Javadocs
• API
  • DataExtension API
  • Query API
  • PageExtension API
• Database Schema
• Contributing to the Project
  • Project Setup
  • Project Architecture
  • Architectural Decision Records
• Supported Plugins

Tutorials

Configuration

• Game server Set-up
• Network Set-up
• Game server Configuration
• Network Configuration
• Login / HTTPS Set-Up
  • Web permissions
• Commands & Permissions
• Database management
• PlaceholderAPI

Customization

• Localization
• Themes
• Html Customization
• External Webserver Use
  • web files
  • reverse-proxy

How-to's

• How to update the plugin
• How to configure X
• Get Started

Wiki index