This article will teach you how to use a plain Datasource resource in a Quarkus application. We will create a simple REST Endpoint and inject a Datasource in it to extract the java.sql.Connection object. We will also learn how to configure the Datasource pool size.
Quarkus Connection Pool
Quarkus uses Agroal as connection pool implementation to manage database connections. Therefore, the first step will be to add the Agroal extension to your Quarkus application. You can do this by adding the following dependency to your pom.xml file:
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-agroal</artifactId>
</dependency>
Next, you’ll need to configure the DataSource by adding the application.properties in the src/main/resources folder. if you are using Zero Config Database (Zero Config Database configuration with Quarkus (DevServices) ) then you don’t need to provide information about the JDBC Connection. Some reasonable defaults will be used from your JDBC Driver extension.
On the other hand, you can include some configuration options for the Connection Pool size. For example:
quarkus.datasource.jdbc.max-size=10 quarkus.datasource.jdbc.min-size=2
Next, you can inject the javax.sql.DataSource into your application’s beans by using the following annotation:
@Inject DataSource dataSource;
Now that you have the DataSource, you can use it to execute SQL statements by using the JDBC API. For example, to return the current Timestamp:
@Path("/time")
public class GreetingResource {
@Inject
DataSource ds;
@GET
@Produces(MediaType.TEXT_PLAIN)
public String getTime() {
String toReturn=null;
try (Connection con = ds.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT CURRENT_TIMESTAMP");) {
try (ResultSet rs = ps.executeQuery();) {
rs.next();
toReturn = "Current time: " + rs.getTimestamp(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return toReturn;
}
}
Finally, consider that you can also inject the io.agroal.api.AgroalDataSource instead of the standard DataSource interface.
An AgroalDataSource can provide additional methods, for example to capture metrics programmatically:
final private AgroalDataSource dataSource;
. . . .
AgroalDataSourceMetrics metrics = dataSource.getMetrics();
long maxUsedCount = metrics.maxUsedCount();
LOGGER.tracev(prefix + " maxUsedCount: {0}, activeCount: {1}, availableCount: {2}",
maxUsedCount == Long.MIN_VALUE ? -1 : maxUsedCount, metrics.activeCount(), metrics.availableCount());
In the next section, we will show how to collect the DataSource Metrics using MicroProfile metrics extension.
Collecting DataSource Statistics
Firstly, make sure that you include the Smallrye Metrics extension in your project:
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-smallrye-metrics</artifactId>
</dependency>
Next, activate the metrics collection for the Datasource:
quarkus.datasource.metrics.enabled=true
Then, you will be able to find the Datasource metrics, under the “agroal” prefix of the “metrics” page.
curl -s http://localhost:8080/q/metrics | grep agroal
For example, if you want to check the number of Active Connections from the pool, check this metric:
# HELP vendor_agroal_active_count Number of active connections. These connections are in use and not available to be acquired.
# TYPE vendor_agroal_active_count gauge
vendor_agroal_active_count{datasource="default"} 3.0
Detecting Connection Leaks in Quarkus
If you are managing Connections by yourself, you can still incur in the problem of Leaked Connections. The simplest way to avoid this issue is to use the try with resource pattern when acquiring a Connection: Using try-with-resources to close database connections
On the other hand, to you can let Quarkus do this check for you by including the following properties in your configuration:
quarkus.datasource.jdbc.leak-detection-interval=1M quarkus.datasource.jdbc.extended-leak-report=true
In the above configuration, Quarkus will perform a leak detection of Connections every minute. If you are leaking connections you will see a message like the following one:
2023-02-02 09:32:01,463 WARN [io.agr.pool] (executor-thread-0) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 1 Statement(s)
Conclusion
This article discussed how to inject and monitor a DataSource resource in your Quarkus applications. This way you will be able to run plain JDBC Statements using only java.sql API.
Source code: https://github.com/fmarchioni/mastertheboss/tree/master/quarkus/datasource
Found the article helpful? if so please follow us on Socials
