[ + ]

Table of Contents

Micronaut SQL Libraries

Projects to support SQL Database access in Micronaut

Version: 1.2.3

1 Introduction

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

Release History

1.2.0

  • Support for JOOQ (Thanks to Vladimir Kulev)

  • Improved Entity Scan support

  • GraalVM metadata

  • Integration with Micronaut 1.2 Validator

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:

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:
        entity-scan:
            packages:
                - '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.

Entity Scan Configuration

Since 1.2 of this library Entity scan configuration is more flexible and it is possible to do reflection free scanning that works on GraalVM substrate.

The default configuration will look for all classes compiled by Micronaut that include the @Entity annotation.

If you wish to limit the packages to include in a particular JPA entity manager you can do so with the entity-scan configuration option:

Limiting Entity Scan
jpa:
    default:
        entity-scan:
            packages:
                - 'foo.bar'

The above configuration limits the search to only classes in the foo.bar package. Note that if classes are not compiled by Micronaut they will not be found. There are two ways to resolve this, one is to generate introspection metadata for the external classes. For example you can place in this on your Application class:

Generating Introspection Metadata for External Classes
@Introspected(packages="foo.bar")

This will generate introspection metadata for all classes in the foo.bar package.

If this option doesn’t work for you, you can instead enable full classpath scanning using the classpath property:

Enabling Full Classpath Scanning
jpa:
    default:
        entity-scan:
            classpath: true
            packages:
                - 'foo.bar'

Note that this approach has the following disadvantages:

  • It is slower, since Micronaut has to search through JAR files and scan class files with ASM

  • It does not work in GraalVM substrate.

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.

6 Configuring jOOQ

Micronaut supports automatically configuring jOOQ library for fluent, typesafe SQL query construction.

Configuring jOOQ

To configure jOOQ library you should first add jooq module to your classpath:

compile 'io.micronaut.configuration:micronaut-jooq'
<dependency>
    <groupId>io.micronaut.configuration</groupId>
    <artifactId>micronaut-jooq</artifactId>
</dependency>

You should then configure one or many DataSources. For each registered DataSource, Micronaut will configure the following jOOQ beans using JooqConfigurationFactory:

If Spring transaction management is in use, it will additionally create the following beans :

Configuring SQL dialect

Micronaut will attempt to detect database SQLDialect automatically.

If this does not work as desired, SQL dialect can be provided manually via configuration properties. The following example configures dialect for default datasource:

Configuring SQL dialect
jooq:
    datasources:
        default:
            sql-dialect: 'POSTGRES'

Configuring additional provider beans

You can define additional beans which will be used when jOOQ Configuration is created. Only beans with the same name qualifier as the data source name will be used.

Micronaut will look for the following bean types: