Oracle Aunonomous Database + JDBI

datasource.properties

jdbc.url=jdbc:oracle:thin:@todos_tpurgent
driverClassName=oracle.jdbc.driver.OracleDriver
username=MyDbUsername
password=$3cR3tP4s$w0rD

DataSourceFactory.java

package com.acme.todo;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

import javax.sql.DataSource;
import java.util.Properties;

public class DataSourceFactory {
    public static DataSource createDataSource(String datasourceConfigFile) throws Exception {
        Properties properties = new Properties();
        properties.load(DataSourceFactory.class.getClassLoader().getResourceAsStream(datasourceConfigFile));
        PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
        pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        pds.setURL(properties.getProperty("jdbc.url"));
        pds.setUser(properties.getProperty("username"));
        pds.setPassword(properties.getProperty("password"));
        pds.setConnectionPoolName("JDBC_UCP_POOL");
        pds.setInactiveConnectionTimeout(10);
        return pds;
    }
}

Main.java

package com.acme.todo;

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;

import javax.sql.DataSource;
import java.io.File;
import java.util.List;

public class Main {
    public static void main(String[] args) throws Exception {
        System.setProperty("oracle.jdbc.fanEnabled", "false");

        // 0. Oracle wallet is located at the rootdir
        System.setProperty("oracle.net.tns_admin", System.getProperty("user.dir") + File.separator + "wallet");

        // 1. create DataSource
        DataSource dataSource = DataSourceFactory.createDataSource("com/acme/todo/datasource.properties");

        // 2. initialize Jdbi
        Jdbi jdbi = Jdbi.create(dataSource);
        jdbi.installPlugin(new SqlObjectPlugin());

        // 3. drop/create tables
        DatabaseBootstrap.initTables(jdbi);

        // 4. Insert some data
        jdbi.useExtension(TodoDao.class, dao -> {
            dao.create(Todo.builder().description("Write example code").build());
            dao.create(Todo.builder().description("Write blog post").build());
            dao.create(Todo.builder().description("Remember the milk").build());
        });

        // 5. Query data
        jdbi.useExtension(TodoDao.class, dao -> {
            List<Todo> todos = dao.findAll();
            System.out.println("todos.size() = " + todos.size()); // 3
            System.out.println("------------------------------------");
            todos.forEach(System.out::println);

            // mark the 1st item as done
            dao.done(todos.get(0));
            System.out.println("------------------------------------");
            dao.findAll().forEach(System.out::println);

            // let's forget the milk
            dao.findById(todos.get(2).getId()).ifPresent(dao::delete);

            System.out.println("------------------------------------");
            dao.findAll().forEach(System.out::println);

            dao.clear();
            System.out.println("------------------------------------");
            System.out.println("todos.size() = " + dao.findAll().size()); // 0
        });

        System.exit(0);
    }
}

pom.xml

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi3-bom</artifactId>
            <version>3.15.0</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

<dependencies>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>${lombok.version}</version>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-core</artifactId>
    </dependency>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-sqlobject</artifactId>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.30</version>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-oracle12</artifactId>
        <version>3.5.1</version>
        <exclusions>
            <exclusion>
                <groupId>com.oracle.jdbc</groupId>
                <artifactId>ojdbc7</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8-production</artifactId>
        <version>${oracle.jdbc.version}</version>
        <type>pom</type>
    </dependency>
</dependencies>

Todo.java

package com.acme.todo;

import lombok.Builder;
import lombok.Data;

@Data
public class Todo {
    private Long id;
    private String description;
    private boolean done;

    @Builder
    public static Todo create(String description) {
        Todo todo = new Todo();
        todo.setDescription(description);
        return todo;
    }

    public String toString() {
        return "[" + id + "] " + description + " [" + (done ? "x" : " ") + "]";
    }
}

TodoDao.java

package com.acme.todo;

import org.jdbi.v3.core.transaction.TransactionIsolationLevel;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.customizer.BindBean;
import org.jdbi.v3.sqlobject.statement.GetGeneratedKeys;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.transaction.Transaction;

import java.util.List;
import java.util.Optional;

public interface TodoDao {
    @Transaction
    @GetGeneratedKeys("id")
    @SqlUpdate("INSERT INTO todos (description, done) values (:description, 0)")
    long create(@BindBean Todo todo);

    @Transaction
    @SqlUpdate("UPDATE todos SET done = 1 WHERE id = :id")
    void done(@BindBean Todo todo);

    @Transaction
    @SqlUpdate("DELETE todos WHERE id = :id")
    void delete(@BindBean Todo todo);

    @Transaction
    @SqlUpdate("DELETE FROM todos")
    void clear();

    @Transaction(TransactionIsolationLevel.READ_COMMITTED)
    @RegisterBeanMapper(Todo.class)
    @SqlQuery("SELECT * FROM todos")
    List<Todo> findAll();

    @Transaction(TransactionIsolationLevel.READ_COMMITTED)
    @RegisterBeanMapper(Todo.class)
    @SqlQuery("SELECT * FROM todos WHERE id = :id")
    Optional<Todo> findById(@Bind("id") long id);
}