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
- A java plugin project for a minecraft server
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.
- Add the repository to your <repositories>-block in pom.xml of your project
<repository>
<id>jitpack</id>
<url>https://jitpack.io</url>
</repository>- Add the repository to your repositories-block in build.gradle of your project
maven {
url "https://jitpack.io"
}- 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)
- Go to https://github.com/plan-player-analytics/Plan/tags and see what is the latest version number. No need to download anything.
- 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>- 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}'- Add Plan as a dependency in your build tool. (you can download it from here https://github.com/plan-player-analytics/Plan/releases)
- Add Plan in softdepend in plugin.yml of your project
softdepend:
- Plan
# nukkit
softdepend: ["Plan"]
# bungee
softDepends:
- Plan- 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!
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
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.
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.
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!
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.
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)
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)
private void recreateTable() {
dropTable();
createTable();
}- Uses the 2 previous methods to first drop and then create the table again.
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)
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)
getProtocolVersion💡 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
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)
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
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
- 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