How to use a Datasource in Quarkus

Agroal is a connection pool implementation that can be used with Quarkus to manage database connections. In this tutorial, we will go over how to use the DataSource in a Quarkus application.

First, you’ll need 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=8
quarkus.datasource.jdbc.min-size=2

Next, you can inject the 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 with H2 Database:

@Path("/hello")
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:

AgroalDataSourceMetrics metrics = ds.getMetrics();

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/datasource-quickstart