How to use a Datasource in Quarkus

by

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)

Finally, it is worth mentioning that you might have Connections which don’t have a leak but require a greater acquisition time. For example, if the network is slow. You can tune the Datasource acquisition timeout as in the following example, which sets a timeout of 1 minute:

quarkus.datasource.integration_api.jdbc.acquisition-timeout: PT1M

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