$ mn create-app my-app --features jdbc-tomcat
Table of Contents
Micronaut SQL Libraries
Projects to support SQL Database access in Micronaut
Version: 6.0.0-SNAPSHOT
1 Introduction
This project includes modules to support SQL Database access in Micronaut.
2 Release History
For this project, you can find a list of releases (with release notes) here:
3 Breaking Changes
Micronaut SQL 5.8.0
In Micronaut SQL 5.7.0, you could disable data sources if you set datasources.enabled
to false
. Since Micronaut SQL 5.8.0, it is no longer supported. If you have multiple data sources, you have to disable them all individually. For example if you had two data sources named a
and b
, you have to set datasources.a.enabled=false
and datasources.b.enabled=false
.
4 Configuring JDBC
Java data sources can be configured for one of four currently provided implementations. Apache DBCP2, Hikari, Tomcat, and Oracle Universal Connection Pool are supported by default.
Using the CLI
If you are creating your project using the Micronaut CLI, supply one of the |
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:
runtimeOnly("io.micronaut.sql:micronaut-jdbc-tomcat")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-jdbc-tomcat</artifactId>
<scope>runtime</scope>
</dependency>
runtimeOnly("io.micronaut.sql:micronaut-jdbc-hikari")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-jdbc-hikari</artifactId>
<scope>runtime</scope>
</dependency>
runtimeOnly("io.micronaut.sql:micronaut-jdbc-dbcp")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-jdbc-dbcp</artifactId>
<scope>runtime</scope>
</dependency>
runtimeOnly("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:
runtimeOnly("com.h2database:h2")
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
4.1 Disable Micronaut JDBC Data Sources
You can disable Micronaut Data Sources, for example in a test, by setting datasources.<datasource-name>.enabled
to false
.
Please note that such test should have @MicronautTest(transactional = false)
because required transaction beans won’t be available because there is no datasource and connection available in the context.
Micronaut SQL 5.7.0 allowed disabling datasources via setting datasources.enabled to `false. That it is no longer supported. If you have multiple dataousrces you have to disable them all individually.
|
4.2 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 tojdbcUrl
. The JNDI name can be configured throughjndiName
in addition todataSourceJNDI
. -
Tomcat: The JNDI name can be configured through
jndiName
in addition todataSourceJNDI
.
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:
datasources.default: {}
["datasources.default"]
datasources.default {
}
{
"datasources.default" {
}
}
{
"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:
datasources.default.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
datasources.default.username=sa
datasources.default.password=
datasources.default.driverClassName=org.h2.Driver
datasources:
default:
url: jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
username: sa
password: ""
driverClassName: org.h2.Driver
[datasources]
[datasources.default]
url="jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
username="sa"
password=""
driverClassName="org.h2.Driver"
datasources {
'default' {
url = "jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
username = "sa"
password = ""
driverClassName = "org.h2.Driver"
}
}
{
datasources {
default {
url = "jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
username = "sa"
password = ""
driverClassName = "org.h2.Driver"
}
}
}
{
"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:
datasources.default.url=null
datasources.default.connectionFactoryClassName=oracle.jdbc.pool.OracleDataSource
datasources.default.username=null
datasources.default.password=null
datasources.default.minPoolSize=1
datasources.default.maxPoolSize=10
datasources:
default:
url:
connectionFactoryClassName: oracle.jdbc.pool.OracleDataSource
username:
password:
minPoolSize: 1
maxPoolSize: 10
[datasources]
[datasources.default]
connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
minPoolSize=1
maxPoolSize=10
datasources {
'default' {
url = null
connectionFactoryClassName = "oracle.jdbc.pool.OracleDataSource"
username = null
password = null
minPoolSize = 1
maxPoolSize = 10
}
}
{
datasources {
default {
url = null
connectionFactoryClassName = "oracle.jdbc.pool.OracleDataSource"
username = null
password = null
minPoolSize = 1
maxPoolSize = 10
}
}
}
{
"datasources": {
"default": {
"url": null,
"connectionFactoryClassName": "oracle.jdbc.pool.OracleDataSource",
"username": null,
"password": null,
"minPoolSize": 1,
"maxPoolSize": 10
}
}
}
The Oracle UCP is managed by the UniversalConnectionPoolManager. The manager can be disabled byt setting ucp-manager.enabled
to false
. Additionally you can enable the JMX-Based Management by setting the ucp-manager.jmx.enabled
to true
and providing the ojdbc8dms.jar
and dms.jar
dependencies.
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 |
|
Hikari |
|
Apache DBCP |
|
Oracle UCP |
4.3 Configuring Multiple Data Sources
To register more than one data source, simply configure them under different names.
datasources.default=...
datasources.warehouse=...
datasources:
default:
...
warehouse:
...
[datasources]
default="..."
warehouse="..."
datasources {
'default' = "..."
warehouse = "..."
}
{
datasources {
default = "..."
warehouse = "..."
}
}
{
"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
4.4 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.
5 Configuring Hibernate
Setting up a Hibernate/JPA EntityManager
Using the CLI
If you are creating your project using the Micronaut CLI, supply the $ 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 the Micronaut Data Transaction Hibernate dependency:
implementation("io.micronaut.data:micronaut-data-tx-hibernate")
<dependency>
<groupId>io.micronaut.data</groupId>
<artifactId>micronaut-data-tx-hibernate</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 JPAEntityManagerFactory
interface.
5.1 Disable Micronaut Hibernate JPA
You can disable Micronaut Hibernate JPA, for example in a test, by setting jpa.enabled
to false
.
5.2 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:
annotationProcessor
in GradleannotationProcessor "jakarta.persistence:jakarta.persistence-api:2.2"
@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:
@CurrentSession
for constructor injectionpublic MyService(@CurrentSession EntityManager entityManager) {
this.entityManager = entityManager;
}
5.3 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. The following configuration demonstrates an example:
datasources.default.name=mydb
jpa.default.entity-scan.packages[0]=foo.bar
jpa.default.entity-scan.packages[1]=foo.baz
jpa.default.properties.hibernate.hbm2ddl.auto=update
jpa.default.properties.hibernate.show_sql=true
datasources:
default:
name: 'mydb'
jpa:
default:
entity-scan:
packages:
- 'foo.bar'
- 'foo.baz'
properties:
hibernate:
hbm2ddl:
auto: update
show_sql: true
[datasources]
[datasources.default]
name="mydb"
[jpa]
[jpa.default]
[jpa.default.entity-scan]
packages=[
"foo.bar",
"foo.baz"
]
[jpa.default.properties]
[jpa.default.properties.hibernate]
show_sql=true
[jpa.default.properties.hibernate.hbm2ddl]
auto="update"
datasources {
'default' {
name = "mydb"
}
}
jpa {
'default' {
entityScan {
packages = ["foo.bar", "foo.baz"]
}
properties {
hibernate {
hbm2ddl {
auto = "update"
}
show_sql = true
}
}
}
}
{
datasources {
default {
name = "mydb"
}
}
jpa {
default {
entity-scan {
packages = ["foo.bar", "foo.baz"]
}
properties {
hibernate {
hbm2ddl {
auto = "update"
}
show_sql = true
}
}
}
}
}
{
"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.
5.4 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:
jpa.default.entity-scan.packages[0]=foo.bar
jpa:
default:
entity-scan:
packages:
- 'foo.bar'
[jpa]
[jpa.default]
[jpa.default.entity-scan]
packages=[
"foo.bar"
]
jpa {
'default' {
entityScan {
packages = ["foo.bar"]
}
}
}
{
jpa {
default {
entity-scan {
packages = ["foo.bar"]
}
}
}
}
{
"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:
@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:
jpa.default.entity-scan.classpath=true
jpa.default.entity-scan.packages[0]=foo.bar
jpa:
default:
entity-scan:
classpath: true
packages:
- 'foo.bar'
[jpa]
[jpa.default]
[jpa.default.entity-scan]
classpath=true
packages=[
"foo.bar"
]
jpa {
'default' {
entityScan {
classpath = true
packages = ["foo.bar"]
}
}
}
{
jpa {
default {
entity-scan {
classpath = true
packages = ["foo.bar"]
}
}
}
}
{
"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.
5.5 Configuring Hibernate Reactive
It’s possible to use Hibernate Reactive by adding following dependency:
implementation("io.micronaut.sql:micronaut-hibernate-reactive")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-hibernate-reactive</artifactId>
</dependency>
and the Micronaut Data Transaction Hibernate dependency:
implementation("io.micronaut.data:micronaut-data-tx-hibernate")
<dependency>
<groupId>io.micronaut.data</groupId>
<artifactId>micronaut-data-tx-hibernate</artifactId>
</dependency>
Hibernate Reactive requires Java 11 |
To enable reactive session factory JPA configuration needs to have reactive
property set to true
.
The reactive implementation doesn’t use traditional JDBC drivers, but instead it uses Vertx Drivers.
You can add one of:
implementation("io.vertx:vertx-mysql-client")
<dependency>
<groupId>io.vertx</groupId>
<artifactId>vertx-mysql-client</artifactId>
</dependency>
implementation("io.vertx:vertx-pg-client")
<dependency>
<groupId>io.vertx</groupId>
<artifactId>vertx-pg-client</artifactId>
</dependency>
implementation("io.vertx:vertx-mssql-client")
<dependency>
<groupId>io.vertx</groupId>
<artifactId>vertx-mssql-client</artifactId>
</dependency>
implementation("io.vertx:vertx-oracle-client")
<dependency>
<groupId>io.vertx</groupId>
<artifactId>vertx-oracle-client</artifactId>
</dependency>
And configure it using properties:
jpa.default.reactive=true
jpa.default.properties.hibernate.connection.url=jdbc:postgresql:database
jpa.default.properties.hibernate.connection.username=myUsername
jpa.default.properties.hibernate.connection.password=myPassword
jpa:
default:
reactive: true
properties:
hibernate:
connection:
url: jdbc:postgresql:database # Use JDBC style url
username: myUsername
password: myPassword
[jpa]
[jpa.default]
reactive=true
[jpa.default.properties]
[jpa.default.properties.hibernate]
[jpa.default.properties.hibernate.connection]
url="jdbc:postgresql:database"
username="myUsername"
password="myPassword"
jpa {
'default' {
reactive = true
properties {
hibernate {
connection {
url = "jdbc:postgresql:database"
username = "myUsername"
password = "myPassword"
}
}
}
}
}
{
jpa {
default {
reactive = true
properties {
hibernate {
connection {
url = "jdbc:postgresql:database"
username = "myUsername"
password = "myPassword"
}
}
}
}
}
}
{
"jpa": {
"default": {
"reactive": true,
"properties": {
"hibernate": {
"connection": {
"url": "jdbc:postgresql:database",
"username": "myUsername",
"password": "myPassword"
}
}
}
}
}
}
The other option is to include one of the existing Micronaut SQL support libraries:
implementation("io.micronaut.sql:micronaut-vertx-mysql-client")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-vertx-mysql-client</artifactId>
</dependency>
implementation("io.micronaut.sql:micronaut-vertx-pg-client")
<dependency>
<groupId>io.micronaut.sql</groupId>
<artifactId>micronaut-vertx-pg-client</artifactId>
</dependency>
And configure the client:
vertx.pg.client.port=5432
vertx.pg.client.host=the-host
vertx.pg.client.database=the-db
vertx.pg.client.user=user
vertx.pg.client.password=secret
vertx.pg.client.max-size=10
vertx:
pg:
client:
port: 5432
host: 'the-host'
database: 'the-db'
user: 'user'
password: 'secret'
max-size: 10
[vertx]
[vertx.pg]
[vertx.pg.client]
port=5432
host="the-host"
database="the-db"
user="user"
password="secret"
max-size=10
vertx {
pg {
client {
port = 5432
host = "the-host"
database = "the-db"
user = "user"
password = "secret"
maxSize = 10
}
}
}
{
vertx {
pg {
client {
port = 5432
host = "the-host"
database = "the-db"
user = "user"
password = "secret"
max-size = 10
}
}
}
}
{
"vertx": {
"pg": {
"client": {
"port": 5432,
"host": "the-host",
"database": "the-db",
"user": "user",
"password": "secret",
"max-size": 10
}
}
}
}
The integration will automatically integrate Vertx driver instance of io.vertx.sqlclient.Pool
found in the bean context.
5.6 Using compile-time Hibernate proxies
Hibernate uses a proxy object to implement lazy loading with a default implementation generating a proxy during the runtime.
This has a few disadvantages:
-
Runtime class generation can affect startup and runtime performance
-
Environments like GraalVM don’t support it
If you wish to use lazy entity associations and avoid runtime proxies you can enable compile-time proxies:
jpa:
default:
compile-time-hibernate-proxies: true
Compile-time proxies require for an entity which needs to have a proxy to be annotated with @GenerateProxy
:
For example:
@Entity
public class Pet {
@ManyToOne(fetch = FetchType.LAZY)
private Owner owner;
//...
}
The entity Owner
needs to be annotated with @GenerateProxy
to have a proxy generated and the compile-time.
@Entity
@GenerateProxy
public class Owner {
//...
}
Compile-time proxies are enabled by default for GraalVM environment. |
5.7 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.
6 Configuring JAsync SQL
Micronaut supports asynchronous access to PostgreSQL and MySQL using jasync-sql, allowing to handle many database connections with a single thread.
6.1 Configuring jasync-sql Client
Using the CLI
If you are creating your project using the Micronaut CLI, supply the $ 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:
jasync.client.port=5432
jasync.client.host=the-host
jasync.client.database=the-db
jasync.client.username=test
jasync.client.password=test
jasync.client.maxActiveConnections=5
jasync:
client:
port: 5432
host: the-host
database: the-db
username: test
password: test
maxActiveConnections: 5
[jasync]
[jasync.client]
port=5432
host="the-host"
database="the-db"
username="test"
password="test"
maxActiveConnections=5
jasync {
client {
port = 5432
host = "the-host"
database = "the-db"
username = "test"
password = "test"
maxActiveConnections = 5
}
}
{
jasync {
client {
port = 5432
host = "the-host"
database = "the-db"
username = "test"
password = "test"
maxActiveConnections = 5
}
}
}
{
"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.
To use Jasync query interceptors register beans of type com.github.jasync.sql.db.interceptor.QueryInterceptor
.
6.2 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.
7 Configuring jOOQ
Micronaut supports automatically configuring jOOQ library for fluent, typesafe SQL query construction.
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:
-
Configuration - jOOQ
Configuration
-
DSLContext - jOOQ
DSLContext
If Spring transaction management is in use, it will additionally create the following beans :
-
JooqExceptionTranslatorProvider for each
DataSource
-
SpringTransactionProvider for each Spring
PlatformTransactionManager
7.1 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:
jooq.datasources.default.sql-dialect=POSTGRES
jooq:
datasources:
default:
sql-dialect: 'POSTGRES'
[jooq]
[jooq.datasources]
[jooq.datasources.default]
sql-dialect="POSTGRES"
jooq {
datasources {
'default' {
sqlDialect = "POSTGRES"
}
}
}
{
jooq {
datasources {
default {
sql-dialect = "POSTGRES"
}
}
}
}
{
"jooq": {
"datasources": {
"default": {
"sql-dialect": "POSTGRES"
}
}
}
}
7.2 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:
7.3 Using JsonMapper to convert JSON(B) types
If you don’t register bean of type ConverterProvider and provide following configuration, JsonConverterProvider
will be used, which uses Micronaut configured JsonMapper
for converting from and to types JSON and JSONB.
jooq:
datasources:
default:
json-converter-enabled: true
7.4 GraalVM native image
To use JOOQ in a native image 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. |
8 Configuring Jdbi
Micronaut supports automatically configuring Jdbi library for convenient, idiomatic access to relational data.
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 :
-
SpringTransactionHandler for each Spring
PlatformTransactionManager
8.1 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:
9 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.
Using the CLI
If you are creating your project using the Micronaut CLI, supply the $ 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
:
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.
9.1 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.
10 Configuring Reactive PostgreSQL Client
Micronaut supports reactive and non-blocking client to connect to PostgreSQL using vertx-pg-client, allowing to handle many database connections with a single thread.
Using the CLI
If you are creating your project using the Micronaut CLI, supply the $ mn create-app my-app --features vertx-pg-client |
To configure the PostgreSQL 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 PostgreSQL server you wish to communicate with:
vertx.pg.client.port=3306
vertx.pg.client.host=the-host
vertx.pg.client.database=the-db
vertx.pg.client.user=test
vertx.pg.client.password=test
vertx.pg.client.maxSize=5
vertx:
pg:
client:
port: 3306
host: the-host
database: the-db
user: test
password: test
maxSize: 5
[vertx]
[vertx.pg]
[vertx.pg.client]
port=3306
host="the-host"
database="the-db"
user="test"
password="test"
maxSize=5
vertx {
pg {
client {
port = 3306
host = "the-host"
database = "the-db"
user = "test"
password = "test"
maxSize = 5
}
}
}
{
vertx {
pg {
client {
port = 3306
host = "the-host"
database = "the-db"
user = "test"
password = "test"
maxSize = 5
}
}
}
}
{
"vertx": {
"pg": {
"client": {
"port": 3306,
"host": "the-host",
"database": "the-db",
"user": "test",
"password": "test",
"maxSize": 5
}
}
}
}
You can also connect to PostgreSQL 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.
10.1 PostgreSQL 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.
11 Repository
You can find the source code of this project in this repository: