1. Jdbi

Jdbi module.

1.1. Usage

1) Add the dependencies (hikari + jdbi):

Maven
Gradle
<!-- DataSource via HikariCP-->
<dependency>
  <groupId>io.jooby</groupId>
  <artifactId>jooby-hikari</artifactId>
  <version>2.0.5</version>
</dependency>

<!-- Jdbi Module-->
<dependency>
  <groupId>io.jooby</groupId>
  <artifactId>jooby-hibernate</artifactId>
  <version>2.0.5</version>
</dependency>

2) Add database driver (mySQL here):

Maven
Gradle
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.17</version>
</dependency>

3) Set database properties

application.conf
db.url = "jdbc:mysql://localhost/mydb"
db.user = myuser
db.password = mypass

4) Install and use Jdbi

Java
Kotlin
import io.jooby.hikari.HikariModule;
import io.jooby.jdbi.JdbiModule;

{
  install(new HikariModule());                     (1)

  install(new JdbiModule());                       (2)

  get("/", ctx -> {
    try (Handle handle = require(Handle.class)) {  (3)
      return handle.inTransaction(h -> {           (4)
        // Work inside transaction                 (5)
        return result;
      });
    }
  });
}
1 Install and creates a DataSource
2 Install and initializes Jdbi
3 Get a new Handle
4 Creates a new transaction
5 Work with EntityManager (read, write to dababase)

Handle must be explicitly closed, we did that with try-with-resources statement in Java and use in Kotlin.

1.2. Transactional Request

The TransactionalRequest decorator takes care of a lifecycle of a Handle per HTTP request. The decorator creates, bind, begin/commit/rollback transaction and finally close it, so route handler doesn’t have to deal with that boring lines of code.

TransactionalRequest
Java
Kotlin
import io.jooby.hikari.HikariModule;
import io.jooby.jdbi.JdbiModule;
import io.jooby.jdbi.TransactionalRequest;

{
  install(new HikariModule());

  install(new JdbiModule());

  decorator(new TransactionalRequest());

  post("/create", ctx -> {
    Handle handle = require(Handle.class);

    handle.createUpdate("update something ...");

    return result;
  });
}

The Handle is tied to the current HTTP request. Multiple require/injection calls produce the same Handle. It is a simple way of managed simple read/write operations.

The decorator takes care of closing the Handle and releasing the JDBC connection.

1.3. SQL Objects

Provisioning of custom SQL objects is available sqlObjects(Class) method.

SQLObjects
Java
Kotlin
import io.jooby.hikari.HikariModule;
import io.jooby.jdbi.JdbiModule;
import io.jooby.jdbi.TransactionalRequest;

{
  install(new HikariModule());

  install(new JdbiModule().sqlObjects(UserDao.class));

  post("/create", ctx -> {
    UserDao dao = require(UserDao.class);

    User user = ...;
    dao.create(user);

    return user;
  });
}

1.4. Advanced Options

Advanced Jdbi configuration is supported via a custom Jdbi instance.

Custom Jdbi
Java
Kotlin
import io.jooby.hikari.HikariModule;
import io.jooby.jdbi.JdbiModule;
import io.jooby.jdbi.TransactionalRequest;

{
  install(new HikariModule());

  install(new JdbiModule(dataSource -> {
    Jdbi jdbi = Jdbi.create(dataSource);
    return jdbi;
  });
}