Oracle Aunonomous Database + MyBatis

DatabaseBootstrap.java

package com.acme.todo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class DatabaseBootstrap {
    private static final Logger LOG = LoggerFactory.getLogger(DatabaseBootstrap.class);

    public static void initTables(DataSource dataSource) {
        URL url = DatabaseBootstrap.class
            .getClassLoader()
            .getResource("com/acme/todo/schema.ddl");

        try (Scanner sc = new Scanner(url.openStream());
             Connection c = dataSource.getConnection();
             Statement statement = c.createStatement()) {
            sc.useDelimiter("#");
            while (sc.hasNext()) {
                String line = sc.next().trim();
                statement.execute(line);
            }
        } catch (IOException | SQLException e) {
            LOG.error("An error occurred when reading schema DDL from " + url, e);
        }
    }
}

datasource.properties

jdbc.url=jdbc:oracle:thin:@todos_tpurgent
driverClassName=oracle.jdbc.driver.OracleDriver
username=ADMIN
password=Password0987

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.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

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 MyBatis
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("development", transactionFactory, dataSource);
        Configuration configuration = new Configuration(environment);
        configuration.addMapper(TodoMapper.class);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

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

        // 4. Insert some data
        try (SqlSession session = sqlSessionFactory.openSession(true)) {
            TodoMapper mapper = session.getMapper(TodoMapper.class);
            mapper.create(Todo.builder().description("Write example code").build());
            mapper.create(Todo.builder().description("Write blog post").build());
            mapper.create(Todo.builder().description("Remember the milk").build());
        }

        // 5. Query data
        try (SqlSession session = sqlSessionFactory.openSession(true)) {
            TodoMapper mapper = session.getMapper(TodoMapper.class);

            List<Todo> todos = mapper.findAll();
            System.out.println("todos.size() = " + todos.size()); // 3
            System.out.println("------------------------------------");
            todos.forEach(System.out::println);

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

            // let's forget the milk
            Todo milk = mapper.findById(todos.get(2).getId());
            mapper.delete(milk);

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

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

        System.exit(0);
    }
}

pom.xml

<properties>
    <lombok.version>1.18.12</lombok.version>
    <mybatis.version>3.5.6</mybatis.version>
    <oracle.jdbc.version>19.7.0.0</oracle.jdbc.version>
</properties>

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

    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>${mybatis.version}</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.30</version>
    </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" : " ") + "]";
    }
}

TodoMapper.java

package com.acme.todo;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface TodoMapper {
    @Insert("INSERT INTO todos (description, done) values (#{description}, 0)")
    long create(Todo todo);

    @Update("UPDATE todos SET done = 1 WHERE id = #{id}")
    void done(Todo todo);

    @Delete("DELETE todos WHERE id = #{id}")
    void delete(Todo todo);

    @Delete("DELETE FROM todos")
    void clear();

    @Select("SELECT * FROM todos")
    List<Todo> findAll();

    @Select("SELECT * FROM todos WHERE id = #{id}")
    Todo findById(long id);
}