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>3.5.3</version>
</dependency>

<!-- Jdbi Module-->
<dependency>
  <groupId>io.jooby</groupId>
  <artifactId>jooby-jdbi</artifactId>
  <version>3.5.3</version>
</dependency>

2) Add database driver (mySQL here):

Maven
Gradle
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>${mysql-connector-java.version}</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());

  use(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.2.1. @Transactional

If you simply install the decorator it becomes enabled by default, this means that each route in its scope become transactional. You can exclude an MVC route by annotating it with the Transactional annotation:

@Transactional
Java
Kotlin
import io.jooby.annotation.Transactional;

@Transactional(false)
@GET("/")
public void get(Context ctx) {
  // no automatic transaction management here
}

You also have the option to invert this logic by disabling the decorator by default:

TransactionalRequest disabled by default
Java
Kotlin
import io.jooby.ebean.TransactionalRequest;

{
  ...
  use(new TransactionalRequest().enabledByDefault(false));
  ...
}

Then you can enable it for the selected routes using @Transactional(true):

@Transactional
Java
Kotlin
import io.jooby.annotation.Transactional;

@Inject
private Handle handle;

@Transactional(true)
@GET("/")
public void get(Context ctx) {
  // work with Handle
}

This feature is not limited to MVC routes. For script routes use the constant Transactional.ATTRIBUTE:

Transactional for script routes
Java
Kotlin
{
  get("/", ctx -> {
    ...
  }).attribute(Transactional.ATTRIBUTE, false);
}

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;
  });
}