Query API Getting started

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# nukkitsoftdepend: ["Plan"]# bungeesoftDepends:
- 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:

importcom.djrapitops.plan.capability.CapabilityService;
importcom.djrapitops.plan.query.QueryService;
publicclassPlanHook {
publicPlanHook() {
}
publicOptional<QueryAPIAccessor> hookIntoPlan() {
if (!areAllCapabilitiesAvailable()) returnOptional.empty();
returnOptional.ofNullable(createQueryAPIAccessor());
}
privatebooleanareAllCapabilitiesAvailable() {
CapabilityServicecapabilities = CapabilityService.getInstance();
returncapabilities.hasCapability("QUERY_API");
}
privateQueryAPIAccessorcreateQueryAPIAccessor() {
try {
returnnewQueryAPIAccessor(QueryService.getInstance());
} catch (IllegalStateExceptionplanIsNotEnabled) {
// Plan is not enabled, handle exceptionreturnnull;
}
}
}

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

publicOptional<QueryAPIAccessor> hookIntoPlan() {
if (!areAllCapabilitiesAvailable()) returnOptional.empty();
returnOptional.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()

privatebooleanareAllCapabilitiesAvailable() {
CapabilityServicecapabilities = CapabilityService.getInstance();
returncapabilities.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()

privateQueryAPIAccessorcreateQueryAPIAccessor() {
try {
returnnewQueryAPIAccessor(QueryService.getInstance());
} catch (IllegalStateExceptionplanIsNotEnabled) {
// Plan is not enabled, handle exceptionreturnnull;
}
}

• 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

publicvoidonEnable() {
... // The example plugin enables itselftry {
Optional<QueryAPIAccessor> = newPlanHook().hookIntoPlan();
} catch (NoClassDefFoundErrorplanIsNotInstalled) {
// 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:

importcom.djrapitops.plan.query.QueryService;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.util.HashMap;
importjava.util.Map;
importjava.util.UUID;
importjava.util.concurrent.ExecutionException;
importjava.util.concurrent.atomic.AtomicBoolean;
publicclassQueryAPIAccessor {
privatefinalQueryServicequeryService;
publicQueryAPIAccessor(QueryServicequeryService) {
this.queryService = queryService;
createTable();
queryService.subscribeDataClearEvent(this::recreateTable);
queryService.subscribeToPlayerRemoveEvent(this::removePlayer);
}
privatevoidcreateTable() {
StringdbType = queryService.getDBType();
booleansqlite = dbType.equalsIgnoreCase("SQLITE");
Stringsql = "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);
}
privatevoiddropTable() {
queryService.execute("DROP TABLE IF EXISTS plan_version_protocol", PreparedStatement::execute);
}
privatevoidrecreateTable() {
dropTable();
createTable();
}
privatevoidremovePlayer(UUIDplayerUUID) {
queryService.execute(
"DELETE FROM plan_version_protocol WHERE uuid=?",
statement -> {
statement.setString(1, playerUUID.toString());
statement.execute();
}
);
}
publicvoidstoreProtocolVersion(UUIDuuid, intversion) throwsExecutionException {
Stringupdate = "UPDATE plan_version_protocol SET protocol_version=? WHERE uuid=?";
Stringinsert = "INSERT INTO plan_version_protocol (protocol_version, uuid) VALUES (?, ?)";
AtomicBooleanupdated = newAtomicBoolean(false);
try {
queryService.execute(update, statement -> {
statement.setInt(1, version);
statement.setString(2, uuid.toString());
updated.set(statement.executeUpdate() > 0);
}).get(); // Waitif (!updated.get()) {
queryService.execute(insert, statement -> {
statement.setInt(1, version);
statement.setString(2, uuid.toString());
statement.execute();
});
}
} catch (InterruptedExceptione) {
Thread.currentThread().interrupt();
}
}
publicintgetProtocolVersion(UUIDuuid) {
Stringsql = "SELECT protocol_version FROM plan_version_protocol WHERE uuid=?";
returnqueryService.query(sql, statement -> {
statement.setString(1, uuid.toString());
try (ResultSetset = statement.executeQuery()) {
returnset.next() ? set.getInt("protocol_version") : -1;
}
});
}
publicMap<Integer, Integer> getProtocolVersionCounts() {
UUIDserverUUID = queryService.getServerUUID()
.orElseThrow(NotReadyException::new);
finalStringsql = "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";
returnqueryService.query(sql, statement -> {
statement.setString(1, serverUUID.toString());
try (ResultSetset = statement.executeQuery()) {
Map<Integer, Integer> versions = newHashMap<>();
while (set.next()) {
versions.put(set.getInt("protocol_version"), set.getInt("count"));
}
returnversions;
}
});
}
}

More information about each method

Construction

privatefinalQueryServicequeryService;
publicQueryAPIAccessor(QueryServicequeryService) {
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

privatevoidcreateTable() {
StringdbType = queryService.getDBType();
booleansqlite = dbType.equalsIgnoreCase("SQLITE");
Stringsql = "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

privatevoiddropTable() {
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

privatevoidrecreateTable() {
dropTable();
createTable();
}

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

removePlayer

privatevoidremovePlayer(UUIDplayerUUID) {
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

publicvoidstoreProtocolVersion(UUIDuuid, intversion) throwsExecutionException {
Stringupdate = "UPDATE plan_version_protocol SET protocol_version=? WHERE uuid=?";
Stringinsert = "INSERT INTO plan_version_protocol (protocol_version, uuid) VALUES (?, ?)";
AtomicBooleanupdated = newAtomicBoolean(false);
try {
queryService.execute(update, statement -> {
statement.setInt(1, version);
statement.setString(2, uuid.toString());
updated.set(statement.executeUpdate() > 0);
}).get(); // Waitif (!updated.get()) {
queryService.execute(insert, statement -> {
statement.setInt(1, version);
statement.setString(2, uuid.toString());
statement.execute();
});
}
} catch (InterruptedExceptione) {
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

publicintgetProtocolVersion(UUIDuuid) {
Stringsql = "SELECT protocol_version FROM plan_version_protocol WHERE uuid=?";
returnqueryService.query(sql, statement -> {
statement.setString(1, uuid.toString());
try (ResultSetset = statement.executeQuery()) {
returnset.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

publicMap<Integer, Integer> getProtocolVersionCounts() {
UUIDserverUUID = queryService.getServerUUID()
.orElseThrow(NotReadyException::new);
finalStringsql = "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";
returnqueryService.query(sql, statement -> {
statement.setString(1, serverUUID.toString());
try (ResultSetset = statement.executeQuery()) {
Map<Integer, Integer> versions = newHashMap<>();
while (set.next()) {
versions.put(set.getInt("protocol_version"), set.getInt("count"));
}
returnversions;
}
});
}

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

importcom.djrapitops.plan.query.QueryService;
importcom.djrapitops.plan.query.CommonQueries;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.util.HashMap;
importjava.util.Map;
importjava.util.UUID;
importjava.util.concurrent.ExecutionException;
importjava.util.concurrent.atomic.AtomicBoolean;
publicclassQueryAPIAccessor {
privatefinalQueryServicequeryService;
publicQueryAPIAccessor(QueryServicequeryService) {
this.queryService = queryService;
ensureDBSchemaMatch();
}
privatevoidensureDBSchemaMatch() {
CommonQueriesqueries = queryService.getCommonQueries();
if (
!queries.doesDBHaveTable("plan_sessions")
|| !queries.doesDBHaveTableColumn("plan_sessions", "uuid")
) {
thrownewIllegalStateException("Different table schema");
}
}
publiclonggetPlaytimeLast30d(UUIDplayerUUID) {
longnow = System.currentTimeMillis();
longmonthAgo = now - TimeUnit.DAYS.toMillis(30L);
UUIDserverUUID = queryService.getServerUUID()
.orElseThrow(IllegalStateException::new);
returnqueryService.getCommonQueries().fetchPlaytime(
playerUUID, serverUUID, monthAgo, now
);
}
publiclonggetPlaytimeLast30dOnAllServers(UUIDplayerUUID) {
longnow = System.currentTimeMillis();
longmonthAgo = now - TimeUnit.DAYS.toMillis(30L);
Set<UUID> serverUUIDs = queryService.getCommonQueries()
.fetchServerUUIDs();
longplaytime = 0;
for (UUIDserverUUID : serverUUIDs) {
playtime += queryService.getCommonQueries().fetchPlaytime(
playerUUID, serverUUID, monthAgo, now
);
}
returnplaytime;
}
publiclonggetSessionCount(UUIDplayerUUID) {
UUIDserverUUID = queryService.getServerUUID()
.orElseThrow(IllegalStateException::new);
Stringsql = "SELECT COUNT(1) as session_count FROM plan_sessions WHERE uuid=?";
returnqueryService.query(sql, statement -> {
statement.setString(1, playerUUID.toString());
try (ResultSetset = statement.executeQuery()) {
returnset.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