[ + ]

Table of Contents

Micronaut SQL Libraries

Projects to support SQL Database access in Micronaut

Version:

1 Introduction

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

Release History

2.3.0

  • New Maven Group ID: io.micronaut.sql

2.2.0

  • Improved GraalVM Support

2.1.0

  • Support for Oracle UCP

  • Support for Hibernate BeanContainer and attribute converters defined as Micronaut beans

2.0.1

  • Support for Oracle UCP

2.0.0

  • Micronaut Transaction Management by default

  • Support for Jdbi

1.3.0

  • Support for Vertx MySQL and Postgres

  • Micronaut 1.2.x minimum version

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, jdbc-dbcp, or jdbc-ucp 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.sql:micronaut-jdbc-tomcat")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-tomcat</artifactId>
    <scope>runtime</scope>
</dependency>

runtime("io.micronaut.sql:micronaut-jdbc-hikari")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-hikari</artifactId>
    <scope>runtime</scope>
</dependency>

runtime("io.micronaut.sql:micronaut-jdbc-dbcp")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-dbcp</artifactId>
    <scope>runtime</scope>
</dependency>

runtime("io.micronaut.sql:micronaut-jdbc-ucp")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-ucp</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

To use Oracle UCP, provide a configuration similar to the following:

application.yaml
datasources:
  default:
    url:
    connectionFactoryClassName: oracle.jdbc.pool.OracleDataSource
    username:
    password:
    minPoolSize: 1
    maxPoolSize: 10

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

Oracle UCP

PoolDataSource

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:

implementation("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:

implementation("io.micronaut.sql:micronaut-hibernate-jpa")
<dependency>
    <groupId>io.micronaut.sql</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

Since 2.0, by default Micronaut-based transaction management is enabled. Thus on any service you can use the io.micronaut.transaction.annotation.TransactionalAdvice annotation (or the javax.transaction.Transactional annotation if you include the micronaut-data-processor dependency in your annotation processor configuration).

If you wish to use instead replace this with Spring-based transaction management you should add the following dependency:

implementation("io.micronaut.sql:micronaut-hibernate-jpa-spring")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-hibernate-jpa-spring</artifactId>
</dependency>

Which will enable configuration of the Spring transaction management instead.

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 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:

implementation("io.micronaut.sql:micronaut-jasync-sql")
<dependency>
    <groupId>io.micronaut.sql</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.

5 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:

implementation("io.micronaut.sql:micronaut-jooq")
<dependency>
    <groupId>io.micronaut.sql</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:

GraalVM native image

Micronaut by default deactivates mapping to proxies with ProxyMapper to enable native image generation. It also provides reflection configuration for basic type mappings but it is necessary to declare the Record classes for reflection. The easiest way to do it is configure jOOQ to annotate the generated classes with the JPA annotations enabling the option jpaAnnotations. This way Micronaut will be able to detect them and automatically generate the reflection configuration that GraalVM needs.

For example, if you are using this gradle plugin you can add the following:

jooq {
    devDb(sourceSets.main) {
        ...
        generator {
            ...
            generate {
                jpaAnnotations = true (1)
            }
        }
    }
}
1 Configure jOOQ to generate the JPA annotations.

There is also built-in support for using SimpleFlatMapper with jOOQ in a native-image. No additional configuration is needed, just adding the SimpleFlatMapper dependency:

implementation("org.simpleflatmapper:sfm-jdbc:8.2.3")
<dependency>
    <groupId>org.simpleflatmapper</groupId>
    <artifactId>sfm-jdbc</artifactId>
    <version>8.2.3</version>
</dependency>

Find more information in the jOOQ documentation.

6 Configuring Jdbi

Micronaut supports automatically configuring Jdbi library for convenient, idiomatic access to relational data.

Configuring Jdbi

To configure the Jdbi library you should first add the jdbi module to your classpath:

implementation("io.micronaut.sql:micronaut-jdbi")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbi</artifactId>
</dependency>

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

  • Jdbi - the Jdbi instance

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

Configuring additional provider beans

You can define additional beans which will be used when the Jdbi object is created. Only beans with a Named qualifier name with the same name as the data source name will be used.

Micronaut will look for the following bean types:

7 Configuring Reactive MySQL Client

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

Configuring the MySQL Vertx Client

Using the CLI

If you are creating your project using the Micronaut CLI, supply the vertx-mysql-client feature to configure the MySQL Vertx client in your project:

$ mn create-app my-app --features vertx-mysql-client

To configure the MySQL Vertx client you should first add vertx-mysql-client module to your classpath:

implementation("io.micronaut.sql:micronaut-vertx-mysql-client")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-mysql-client</artifactId>
</dependency>

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

application.yml
vertx:
	mysql:
	    client:
            port: 3306
            host: the-host
            database: the-db
            user: test
            password: test
            maxSize: 5
You can also connect to MySQL using uri instead of the other properties.

Once you have the above configuration in place then you can inject the io.vertx.reactivex.mysqlclient.MySQLPool bean. The following is the simplest way to connect:

result = client.query('SELECT * FROM foo').rxExecute().map({ RowSet<Row> rowSet -> (1)
    RowIterator<Row> iterator = rowSet.iterator()
    int id = iterator.next().getInteger("id")
    return "id: ${id}"
}).blockingGet()
1 client is an instance of the io.vertx.reactivex.mysqlclient.MySQLPool bean.

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

MySQL Health Checks

When the vertx-mysql-client module is activated a MySQLClientPoolHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the MySQL connection.

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

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

8 Configuring Reactive Postgres Client

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

Configuring the Pg Vertx Client

Using the CLI

If you are creating your project using the Micronaut CLI, supply the vertx-pg-client feature to configure the Postgres Vertx client in your project:

$ mn create-app my-app --features vertx-pg-client

To configure the Postgres Vertx client you should first add vertx-pg-client module to your classpath:

implementation("io.micronaut.sql:micronaut-vertx-pg-client")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-pg-client</artifactId>
</dependency>

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

application.yml
vertx:
	pg:
	    client:
            port: 3306
            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.vertx.reactivex.pgclient.PgPool bean. The following is the simplest way to connect:

result = client.query('SELECT * FROM pg_stat_database').rxExecute().map({ RowSet<Row> rowSet -> (1)
    int size = 0
    RowIterator<Row> iterator = rowSet.iterator()
    while (iterator.hasNext()) {
        iterator.next()
        size++
    }
    return "Size: ${size}"
}).blockingGet()
1 client is an instance of the io.vertx.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 vertx-pg-client.

Postgres Health Checks

When the vertx-pg-client module is activated a PgClientPoolHealthIndicator 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.vertx.pg.client.enabled key.

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

9 Repository

You can find the source code of this project in this repository:

https://github.com/micronaut-projects/micronaut-sql