-
-
Notifications
You must be signed in to change notification settings - Fork 4
Database
We provide several utilities for database connections.
The query object can be used to extend a class which should handle SQL connections.
Each query object is owned by a plugin.
public class MySqlQueries extends QuerObject {
public MySqlQueries(Plugin plugin, DataSource dataSource) {
super(plugin, dataSource);
}
}Logging can be done by calling QueryObject#logDbError(String, SQLException).
This will log the error with the provided logger of the plugin.
You can obtain a QueryBuilder directly inside a QueryObject via QueryBuilder#queryBuilder(Class<T>).
Read more at the QueryBuilder section.
You can directly retrieve a connection from the provided data source via QueryObject#getConnection().
DBUtil proves methods for exception formatting to log them properly.
The QueryBuilder class allows you to easily call your database and reduces boilerplate code.
It uses a builder like pattern to guide you while calling your database.
You will need a QueryBuilderConfig to use a QueryBuilder. You can create your own configuration or use the default
config via QueryBuilderConfig#DEFAULT.
Otherwise you can call QueryBuilderConfig#builder() to obtain a builder for the config.
By default the query builder will catch all SQL Exceptions and log them properly.
If you want to log them by yourself you should call QueryBuilderConfig.Builder#throwing() on the builder.
By default the query builder will execute all queries in one atomic transaction. This has the effect, that the data will
only be changed if all queries were executed succesfully. This is especially usefull, when executing multiple queries.
If you dont want this call QueryBuilderConfig.Builder#notAtomic(). Tbh there is no real reason why you would want
this.
The query builder is used to write and read data. If you want to read data you have to provide the class of the object you want to read. It doesn't matter if you want to read multiple entries or get a single entry.
A QueryBuilder can be optained via three different methods.
If your class extends QueryObject you can simply call QueryObject#builder(Class<T>).
This will give you a query builder with the plugin and datasource of your QueryObject.
The simple way is to call the static factory methods on the QueryBuilder.
The Factory methods all accept your Plugin and a DataSource.
If you want to read something from the database you should use the method with a class:
QueryBuilder#builder(Plugin, DataSource, Class<T>)
If you just want to write data your can use the builder without a class: QueryBuilder#builder(Plugin, DataSource)
The QueryBuilderFactory has one general advantage. It will apply a configuration for you and skip the ConfigrationStage.
To create a QueryBuilderFactory call the constructor and cache the Factory where you need it:
QueryBuilderFactory(QueryBuilderConfig, DataSource, Plugin)
The QueryBuilder uses a stage system to guilde you through the creation of your calls.
If you didnt used the QueryBuilderFactor to obtain your builder, you will start in the ConfigurationStage and otherwise in the QueryStage
The ConfigurationStage allows you to set your QueryBuilderConfig.
You can apply your configuration here or just use the default config.
The QueryStage allows you to set your query with parameter for a PreparedStatement.
If you dont have parameter you can call QueryStage#queryWithoutParams(String) to skip the StagementStage.
The StagementStage allows you to invoke methods on the PreparedStatement.
The ResultStage allows you to define what your query does.
If you want to read data you have to call ResultStage#readRow(ThrowingFunction<T, ResultSet, SQLException> mapper).
Now you have to map the current row to a object of your choice. This has to be the class you provided on creation of the QueryBuilder.
Note: Do only map the current row. Dont ever modify the ResultSet by calling ResultSet#next() or something else.
Calling these functions will return a RetrievalStage.
All these operations are just updates anyway in sql context. It doesn't matter what you call.
Calling these functions will return a UpdateStage.
You can also append another query by calling StatementStage#append().
This will return a QueryStage and allows you to set another query.
All queries will be executed in the order they were appended and with a single connection.
The RetrievalStage allows you to actually call the queries you entered before.
If you want to retrieve the first or only one result call the RetrievalStage#first or RetrievalStage#firstSync method.
This will return the rows as a list mapped via the function provided in the ResultStage.
If you want to retrieve all entries in the ResultSet call RetrievalStage#all or RetrievalStage#allSync
Methods not suffixed with Sync will return a BukkitFutureResult.
These methods will provide the list or single result to you.
Methods suffixed with Sync will block the thread until the result is retrieved from the database.
Do not use blocking methods unless you really know what you ar doing.
The update stage allows you to update entries in your table.
Methods not suffixed with Sync will return a BukkitFutureResult.
These methods will provide the amount of changed rows.
Methods suffixed with Sync will block the thread until the update is done.
These methods will provide the amount of changed rows.
Do not use blocking methods unless you really know what you ar doing.
Update a table async and get the inserted auto increment.
QueryBuilder.builder(plugin, dataSource, Long.class)
.defaultConfig()
.query("INSERT INTO table(first, second) VALUES(?, ?)")
.params(stmt -> {
stmt.setString(1, "newVal");
stmt.setString(2, "some");
})
.append()
.queryWithoutParams("SELECT LAST_INSERT_ID()")
.readRow(r -> r.getLong(1))
.first()
.whenComplete(id -> {
System.out.println("Inserted new entry with id " + id);
});Reading multiple rows async.
QueryBuilder.builder(plugin, dataSource, String.class)
.defaultConfig()
.query("SELECT something FROM table WHERE key = ?")
.params(stmt -> stmt.setString(1, "foo"))
.readRow(rs -> rs.getString("something"))
.all()
.whenComplete(results -> {
List<String> resultList = results;
});