SADU Queries
SADU provides a query builder, which allows easy guidance through requesting data from your database. Import into your
project to use it.
repositories {
maven("https://eldonexus.de/repository/maven-public")
}
dependencies {
implementation("de.chojo.sadu", "sadu-queries", "<version>")
}Before I give you a long talk about how much nicer the syntax and code is let me simple show you a comparison.
Without the query builder your code would ideally look like this:
class MyQueries {
DataSource dataSource;
MyQueries(DataSource dataSource){
this.dataSource = dataSource;
}
public CompletableFuture<Optional<Result>> getResultOld(int id) {
return CompletableFuture.supplyAsync(() -> {
try (Connection conn = source().getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT result FROM results WHERE id = ?")) {
stmt.setInt(id);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return Optional.of(new Result(rs.getString("result"));
}
} catch (SQLException e) {
logger.error("Something went wrong", e);
}
return Optional.empty();
});
}
}But using the query builder your code becomes this:
class MyQueries extends QueryFactory {
MyQueries(DataSource dataSource){
super(dataSource);
}
public CompletableFuture<Optional<Result>> getResultNew(int id) {
return builder(Result.class)
.query("SELECT result FROM results WHERE id = ?")
.parameter(stmt -> stmt.setInt(id))
.readRow(rs -> new Result(rs.getString("result")))
.first();
}
}Beautiful isnt it? The query builder will enforce try with resources, set parameters in the order defined by you, read the result set and additionally handle the exceptions for you.
The query builder guides you through different stages. These stages will allow you to only call methods which make sense in the current context. It is for exampe not possible to read a row without defining a query first.
We will set a default configuration for our query builder. That will make it much easier for us and will avoid of code repetitions. The config will be applied to all created query builders. No matter where or when they are created.
import static org.slf4j.LoggerFactory.getLogger;
public class Main {
private static final org.slf4j.Logger log = getLogger(Main.class);
public static void main(String[] args) {
QueryBuilderConfig.setDefault(QueryBuilderConfig.builder()
.withExceptionHandler(err -> {
log.error("An error occured during a database request",err);
})
.withExecutor(Executors.newCachedThreadPool())
.build());
}
}The excecutor for the completable futures can be set via QueryBuilderConfig.Builder#withExecutor()
Make sure to add an exception handler. Otherwise error will be silent.
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. As an
alterantive you can set a LoggingAdapter in the QueryBuilderConfig
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.
To create a query class we will extend the QueryFactory.
As an alternative you can also call a QueryFactory instance directly.
You can also initialise the StaticQueryAdapter and use the builder method from there.
Now we can create functions which use the query builder.
// Our class will extend the QueryFactory.
// This allows us to simply create preconfigured builder.
public class MyQueries extends QueryFactory {
/**
* Create a new queries object.
*
* @param dataSource data source used to query data from a database
*/
public MyQueries(DataSource dataSource) {
super(dataSource);
}
/**
* Retrieve a result by id.
*
* @param id id to retrieve
* @return An optional holding a result if found.
*/
public CompletableFuture<Optional<Result>> getResult(int id) {
// We want to have a class of type Result.
return builder(Result.class)
// We define our query
.query("SELECT result FROM results WHERE id = ?")
// We set the first parameter. No need to define the index.
.parameter(stmt -> stmt.setInt(id))
// We map our current row to a result.
.readRow(rs -> new Result(rs.getString("result")))
// We retrieve only the first result we get.
.first();
}
/**
* Retrieve a list of all results in the result table.
*
* @return list of results
*/
public CompletableFuture<List<Result>> getResults() {
// We want to have a class of type Result.
return builder(Result.class)
// We define our query
.query("SELECT result FROM results")
// We skip the parameter assignment
.emptyParams()
// We map our current row to a result.
.readRow(rs -> new Result(rs.getString("result")))
// We retrieve only the first result we get.
.all();
}
/**
* Delete a result.
*
* @param id the id to delete
* @return true if result was present and got deleted
*/
public CompletableFuture<Boolean> deleteResult(int id) {
// We want to delete. We leave the expected class empty.
return builder()
// We define our query
.query("DELETE FROM results WHERE id = ?")
// We set the first parameter. No need to define the index.
.parameter(stmt -> stmt.setInt(id))
// We say that we want to execute a deletion
.delete()
// We execute the query asynchronously
.send()
// We check if a row was changed
.thenApply(UpdateResult::changed);
}
/**
* Delete a result.
*
* @param id the id to delete
* @return true if result was present and got updated
*/
public CompletableFuture<Boolean> updateResult(int id, String newValue) {
// We want to update. We leave the expected class empty.
return builder()
// We define our query
.query("UPDATE results SET result = ? WHERE id = ?")
// The param builder will set the parameters in the order you define them
.parameter(stmt -> stmt.setString(newValue).setInt(id))
// We say that we want to execute an update
.update()
// We execute the query asynchronously
.send()
// We check if a row was changed
.thenApply(UpdateResult::changed);
}
/**
* Delete a result.
*
* @param result the result to add
* @return returns the id of the new result
*/
public CompletableFuture<Optional<Long>> addResult(String result) {
// We want to insert. We leave the expected class empty.
return builder()
// We define our query
.query("INSERT INTO results(result) VALUES(?)")
// We set the first parameter. No need to define the index.
.parameter(stmt -> stmt.setString(result))
// We say that we want to execute an insertion
.insert()
// We execute the query asynchronously and get the created key.
.key();
}
}The QueryBuilder uses a stage system to guilde you through the creation of your calls. If you didnt used
the QueryBuilderFactory to obtain your builder, you will start in the ConfigurationStage and
otherwise in the QueryStage
Here is a "small" overview about the paths you can take in the query builder.
Overview
You can simply call the Query Builder class directly or let your class extend a QueryFactory to get a
preconfigured builder. This will also no longer require to pass a datasource every time.
The ConfigurationStage allows you to set your QueryBuilderConfig.
You can apply your configuration here or just use the default config. It is recommended to define your own default config above.
This stage is only available when using the builder directly. It is highly recommended to extend your class with
QueryFactory and use the provided builder() method.
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 ParamBuilder allows you to set the parameter in the order you defined them in your query.
public void createUser(String name, int age){
builder().query("INSERT INTO user(name, arge) VALUES(?,?)")
.parameter(p -> p.setString(name).setInt(age))
.insert()
.send();
}
The ResultStage allows you to define what your query does.
You can insert which will give you an Insert Stage.
You can also read rows of your result set when you have a SELECT or RETURNING statement in your query.
If you want to execute and additional query, call append and add another one.
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. Do not modify the ResultSet by calling ResultSet#next() or something else.
Calling these functions will return a RetrievalStage.
These methods will simply execute the query.
Calling these functions will return a UpdateStage.
Update and delete will give you an UpdateResult
These methods will insert the data into your database.
Calling this method will return a InsertStage.
Insert will give you an UpdateResult or the created keys
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
The update stage allows you to update entries in your table.
Calling the send methods will give you an update result
The udpate result contains the modified rows and a method which will return a boolean when at least one row changed.
The insert stage offers the same as the UpdateStage, but allows you to retrieve created keys as well. This is not fully supported by all databases.
Methods not suffixed with Sync will return a CompleteableFuture.
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.
