5Configuring JAsync SQL

Micronaut SQL Libraries

Projects to support SQL Database access in Micronaut

Version: 1.1.1

1 Introduction

This project includes modules to support SQL Database access in Micronaut.

Release History

1.1.0

Support for JAsync SQL
Hibernate 5.3.7.Final → 5.4.0.Final
Hikari 2.7.9 → 3.3.0
Commons DBCP 2 2.1.1 → 2.5.0
Tomcat Pool 9.0.1 → 9.0.14
Reactive Postgres 0.10.5 → 0.11.2

2 Configuring JDBC

Java data sources can be configured for one of three currently provided implementations. Apache DBCP2, Hikari, and Tomcat are supported by default.

Configuring a JDBC DataSource

Using the CLI

If you are creating your project using the Micronaut CLI, supply one of the jdbc-tomcat, jdbc-hikari, or jdbc-dbcp features to preconfigure a simple JDBC connection in your project, along with a default H2 database driver:

$ mn create-app my-app --features jdbc-tomcat

To get started, simply add a dependency to one of the JDBC configurations that corresponds to the implementation you would like to use. Choose one of the following:

runtime 'io.micronaut.configuration:micronaut-jdbc-tomcat'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-jdbc-tomcat</artifactId>
    <scope>runtime</scope>
</dependency>

runtime 'io.micronaut.configuration:micronaut-jdbc-hikari'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-jdbc-hikari</artifactId>
    <scope>runtime</scope>
</dependency>

runtime 'io.micronaut.configuration:micronaut-jdbc-dbcp'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-jdbc-dbcp</artifactId>
    <scope>runtime</scope>
</dependency>

You also need to add a JDBC driver dependency to your classpath. For example to add the H2 In-Memory Database:

runtime 'com.h2database:h2'

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Configuring JDBC Connection Pools

All of the implementation specific parameters can be configured. Effort was made to allow basic configuration to be consistent across the implementations.

Hikari: The URL is able to be configured through url in addition to jdbcUrl. The JNDI name can be configured through jndiName in addition to dataSourceJNDI.
Tomcat: The JNDI name can be configured through jndiName in addition to dataSourceJNDI.

Several configuration options will be calculated if they are not provided.

URL

The classpath will be searched for an embedded database driver. If found, the URL will be set to the default value for that driver.

Driver Class

If the URL is configured, the driver class will be derived from the URL, otherwise the classpath will be searched for an embedded database driver. If found, the default class name for that driver will be used.

Username

If the configured database driver is embedded, the username will be set to "sa"

Password

If the configured database driver is embedded, the password will be set to an empty string.

For example:

application.yaml

datasources.default: {}

The above configuration will result in a single DataSource bean being registered with the named qualifier of default.

If for example, the H2 driver is on the classpath, it is equivalent to the following:

application.yaml

datasources:
    default:
        url: jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
        username: sa
        password: ""
        driverClassName: org.h2.Driver

For a list of other properties able to be configured, simply refer to the implementation that is being used. All setter methods are candidates for configuration.

Tomcat

PoolProperties

Hikari

HikariConfig

Apache DBCP

BasicDataSource

Configuring Multiple Data Sources

To register more than one data source, simply configure them under different names.

application.yaml

datasources:
    default:
        ...
    warehouse:
        ...

When injecting DataSource beans, the one with the name "default" will be injected unless the injection is qualified with the configured name. If no configuration is named "default", none of the beans will be primary and thus all injections must be qualified. For example:

@Inject DataSource dataSource // "default" will be injected
@Inject @Named("warehouse") DataSource dataSource // "warehouse" will be injected

JDBC Health Checks

Once you have configured a JDBC DataSource the JdbcIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the JDBC connection.

See the section on the Health Endpoint for more information.

Using Spring Transaction Management

If you wish to use Spring-based transaction management you can add the following dependencies to your application:

compile 'io.micronaut:micronaut-spring'

<dependency>
    <groupId>io.micronaut</groupId>
    <artifactId>micronaut-spring</artifactId>
</dependency>

runtime 'org.springframework:spring-jdbc'

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

Micronaut will automatically configure a DataSourceTransactionManager and wrap the DataSource in a TransactionAwareDataSourceProxy for each configured DataSource.

You should then use Micronaut’s @Transactional annotation to ensure low-overhead, compile-time transaction management is applied to your classes.

3 Configuring Hibernate

Setting up a Hibernate/JPA EntityManager

Using the CLI

If you are creating your project using the Micronaut CLI, supply the hibernate-jpa feature to include a Hibernate JPA configuration in your project:

$ mn create-app my-app --features hibernate-jpa

Micronaut features built in support for configuring a Hibernate / JPA EntityManager that builds on the SQL DataSource support.

Once you have configured one or many DataSources to use Hibernate, you will need to add the hibernate-jpa dependency to your build configuration:

compile 'io.micronaut.configuration:micronaut-hibernate-jpa'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-hibernate-jpa</artifactId>
</dependency>

And that is it. For each registered SQL DataSource, Micronaut will configure the following beans using EntityManagerFactoryBean:

StandardServiceRegistry - The Hibernate StandardServiceRegistry
MetadataSources - The Hibernate MetadataSources
SessionFactoryBuilder - The Hibernate SessionFactoryBuilder
SessionFactory - The Hibernate SessionFactory bean which also implements the JPA EntityManagerFactory interface.

Injecting an EntityManager or Hibernate Session

You can use the javax.persistence.PersistenceContext annotation to inject an EntityManager (or Hibernate Session). To do so you need to make sure the JPA annotations are on the annotationProcessor path in your build:

Adding the JPA dependency to annotationProcessor in Gradle

annotationProcessor "javax.persistence:javax.persistence-api:2.2"

Using @PersistenceContext

@PersistenceContext
EntityManager entityManager;

@PersistenceContext(name = "other")
EntityManager otherManager;

Micronaut will inject a compile time scoped proxy that retrieves the EntityManager associated with the current transaction when using @Transactional (see "Using Spring Transaction Management" below).

Note the examples above use field injection, since the @PersistenceContext annotation does not support declaration on a parameter of a constructor or method argument. Therefore if you wish to instead use constructor or method injection you must use the @CurrentSession instead:

Using @CurrentSession for constructor injection

public MyService(@CurrentSession EntityManager entityManager) {
     this.entityManager = entityManager;
}

Customizing Hibernate / JPA Configuration

There are several different ways you can customize and configure how the SessionFactory is built. The easiest way is via configuration in application.yml. The following configuration demonstrates an example:

Configuring Hibernate Properties

datasources:
    default:
        name: 'mydb'
jpa:
    default:
        packages-to-scan:
            - 'foo.bar'
            - 'foo.baz'
        properties:
            hibernate:
                hbm2ddl:
                    auto: update
                show_sql: true

The above example configures the packages to be scanned and sets properties to be passed to Hibernate. As you can see these are done on a per DataSource basis. Refer to the JpaConfiguration configuration class for the possible options.

If you need even further control over how the SessionFactory is built then you can register BeanCreatedEventListener beans that listen for the creation of the SessionFactoryBuilder, MetadataSources etc. and apply your custom configuration in the listener.

You may also optionally create beans of type Integrator and Interceptor and these will be picked up and injected automatically.

Using Spring Transaction Management

Micronaut’s Hibernate integration will also automatically provide a Spring HibernateTransactionManager bean so you can use Spring-based transaction management.

You should use Micronaut’s @Transactional annotation to ensure low-overhead, compile-time transaction management is applied to your classes.

Understanding LazyInitializationException

Micronaut is built on Netty which is based on a non-blocking, event loop model. JDBC and Hibernate are blocking APIs and hence when they are used in a Micronaut application the work is shifted to a blocking I/O thread pool.

When using @Transactional the Hibernate Session will only be open for the duration of this method execution and then will automatically be closed. This ensures that the blocking operation is kept as short as possible.

There is no notion of OpenSessionInView (OSIV) in Micronaut and never will be, since it is sub-optimal and not recommended. You should optimize the queries that you write to return all the necessary data Micronaut will need to encode your objects into JSON either by using the appropriate join queries or using a data transfer object (DTO).

If you encounter a LazyInitializationException when returning a Hibernate entity from a method it is an indication that your query is suboptimal and you should perform a join.

4 Configuring Reactive Postgres

Micronaut supports reactive and non-blocking client to connect to Postgres using reactive-pg-client, allowing to handle many database connections with a single thread.

Configuring the Reactive Postgres Client

Using the CLI

If you are creating your project using the Micronaut CLI, supply the postgres-reactive feature to configure the Reactive Postgres client in your project:

$ mn create-app my-app --features postgres-reactive

To configure the Reactive Postgres client you should first add postgres-reactive module to your classpath:

compile 'io.micronaut.configuration:micronaut-postgres-reactive'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-postgres-reactive</artifactId>
</dependency>

You should then configure the URI or PoolOptions of the Postgres server you wish to communicate with in application.yml:

application.yml

postgres:
	reactive:
	    client:
            port: 5432
            host: the-host
            database: the-db
            user: test
            password: test
            maxSize: 5

You can also connect to Postgres using uri instead of the other properties.

Once you have the above configuration in place then you can inject the io.reactiverse.reactivex.pgclient.PgPool bean. The following is the simplest way to connect:

result = client.rxQuery('SELECT * FROM pg_stat_database').map({ PgRowSet pgRowSet -> (1)
    int size = 0
    PgIterator iterator = pgRowSet.iterator()
    while (iterator.hasNext()) {
        iterator.next()
        size++
    }
    return "Size: ${size}"
}).blockingGet()

1	`client` is an instance of the `io.reactiverse.reactivex.pgclient.PgPool` bean.

For more information on running queries on Postgres using the reactive client please read the "Running queries" section in the documentation of reactive-pg-client.

Postgres Health Checks

When the postgres-reactive module is activated a PgPoolHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the Postgres connection.

The only configuration option supported is to enable or disable the indicator by the endpoints.health.postgres.reactive.enabled key.

See the section on the <Health Endpoint for more information.

5 Configuring JAsync SQL

Micronaut supports asynchronous access to PostgreSQL and MySQL using jasync-sql, allowing to handle many database connections with a single thread.

Configuring jasync-sql Client

Using the CLI

If you are creating your project using the Micronaut CLI, supply the jasync-sql feature to configure the Jasync PostgreSQL and MySQL client in your project:

$ mn create-app my-app --features jasync-sql

To configure the Jasync client you should first add jasync-sql module to your classpath:

compile 'io.micronaut.configuration:micronaut-jasync-sql'

<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-jasync-sql</artifactId>
</dependency>

You should then configure the PoolOptions of the database server you wish to communicate with in application.yml:

application.yml

jasync:
    client:
        port: 5432
        host: the-host
        database: the-db
        username: test
        password: test
        maxActiveConnections: 5

Once you have the above configuration in place then you can inject the com.github.jasync.sql.db.Connection bean. The following is the simplest way to connect:

result = client.sendQuery('SELECT * FROM pg_stat_database').thenApply({ QueryResult resultSet -> (1)
    return "Size: ${resultSet.rows.size()}"
}).get()

1	`client` is an instance of the `com.github.jasync.sql.db.Connection` bean.

For more information on running queries on using the client please read the "Running queries" section in the documentation of jasync-sql.

Database Health Checks

When the jasync-sql module is activated a JasyncHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the connection.

The only configuration option supported is to enable or disable the indicator by the endpoints.health.jasync.enabled key.

See the section on the Health Endpoint for more information.

Table of Contents

Micronaut SQL Libraries

1 Introduction

Release History

1.1.0

2 Configuring JDBC

Configuring a JDBC DataSource

Configuring JDBC Connection Pools

Configuring Multiple Data Sources

JDBC Health Checks

Using Spring Transaction Management

3 Configuring Hibernate

Setting up a Hibernate/JPA EntityManager

Injecting an EntityManager or Hibernate Session

Customizing Hibernate / JPA Configuration

Using Spring Transaction Management

Understanding LazyInitializationException

4 Configuring Reactive Postgres

Configuring the Reactive Postgres Client

Postgres Health Checks

5 Configuring JAsync SQL

Configuring jasync-sql Client

Database Health Checks