== Jdbi

https://github.com/jdbi/jdbi[Jdbi] module.

=== Usage

1) Add the dependencies (hikari + jdbi):

[dependency, artifactId="jooby-hikari:DataSource via HikariCP, jooby-jdbi:Jdbi Module"]
.

2) Add database driver (mySQL here):

[dependency, groupId="mysql", artifactId="mysql-connector-java", version="${mysql-connector-java.version}"]
.

3) Set database properties

.application.conf
[source, properties]
----
db.url = "jdbc:mysql://localhost/mydb"
db.user = myuser
db.password = mypass
----


4) Install and use Jdbi

.Java
[source, java, role="primary"]
----
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;
      });
    }
  });
}
----

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.hikari.HikariModule
import io.jooby.jdbi.JdbiModule

{
  install(HikariModule())                          <1>
  
  install(JdbiModule())                            <2>
    
  get("/jdbi") {ctx ->
    ctx.require(Handle::class).use { handle ->     <3>
      handle.inTransaction<Any, Exception> {       <4>
        // Work inside transaction                 <5>
        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.

=== Transactional Request

The javadoc:jdbi.TransactionalRequest[artifact="jooby-jdbi"] 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
[source, java, role = "primary"]
----
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;
  });
}
----

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.hikari.HikariModule
import io.jooby.jdbi.JdbiModule
import io.jooby.jdbi.TransactionalRequest

{
  install(HikariModule())
  
  install(JdbiModule())
  
  use(TransactionalRequest())
  
  post("/create") { ctx ->
    val handle = require(Handle::class)
    
    handle.createUpdate("update something ...")
    
    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.

==== @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
javadoc:annotation.Transactional[] annotation:

.@Transactional
[source,java,role = "primary"]
----
import io.jooby.annotation.Transactional;

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

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.annotation.Transactional

@Transactional(false)
@GET("/")
fun get(ctx: Context) {
  // no automatic transaction management here
}
----

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

.TransactionalRequest disabled by default
[source, java, role = "primary"]
----
import io.jooby.ebean.TransactionalRequest;

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

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.ebean.TransactionalRequest

{
  ...
  use(TransactionalRequest().enabledByDefault(false))
  ...
}
----

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

.@Transactional
[source,java,role = "primary"]
----
import io.jooby.annotation.Transactional;

@Inject
private Handle handle;

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

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.annotation.Transactional

@Inject
private lateinit var handle: Handle

@Transactional(true)
@GET("/")
fun get(ctx: Context) {
  // work with Handle
}
----

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

.Transactional for script routes
[source,java,role = "primary"]
----


{
  get("/", ctx -> {
    ...
  }).attribute(Transactional.ATTRIBUTE, false);
}
----

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.annotation.Transactional

{
  get("/") {
    ...
  }).attribute(Transactional.ATTRIBUTE, false);
}
----

=== SQL Objects

Provisioning of custom SQL objects is available javadoc:jdbi.JdbiModule[sqlObjects, java.lang.Class..., artifact="jooby-jdbi"] method.

.SQLObjects
[source, java, role = "primary"]
----
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;
  });
}
----

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.hikari.HikariModule
import io.jooby.jdbi.JdbiModule
import io.jooby.jdbi.TransactionalRequest

{
  install(HikariModule())
  
  install(JdbiModule().sqlObjects(UserDao.class))

  post("/create") { ctx ->
    val dao = require(UserDao::class)
    
    val user = User()
    dao.create(user)
    
    user
  }
}
----

=== Advanced Options

Advanced Jdbi configuration is supported via a custom Jdbi instance.

.Custom Jdbi
[source, java, role = "primary"]
----
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;
  });
}
----

.Kotlin
[source, kt, role="secondary"]
----
import io.jooby.hikari.HikariModule
import io.jooby.jdbi.JdbiModule
import io.jooby.jdbi.TransactionalRequest

{
  install(HikariModule())
  
  install(JdbiModule { dataSource ->
    Jdbi.create(dataSource)
  })
}
----
