JavaのSql2oで簡単データベースアクセス

## 1. はじめに

今回はJavaのデータベースアクセス用のライブラリである[Sql2o](https://www.sql2o.org/)の基本的な使い方について説明したいと思います。
そもそも`Sql2o`を知ったのは`SparkFramework`のガイドラインで[DBアクセスに`Sql2o`](http://sparkjava.com/tutorials/sql2o-database)を利用していたからです。
シンプルなマイクロサービスのためのフレームワークである`SparkFramework`が使うのであれば`Sql2o`もシンプルなのでは、ということで試しに利用してみました。
`SparkFramework`については「[Spark Frameworkで簡単マイクロサービス！](https://qiita.com/5zm/items/0332250e5d2775198242)」を参照ください。

### 1.1. Sql2oとは

非常にシンプルなデータベースアクセス用のJavaのライブラリで、[公式サイト](https://www.sql2o.org/)では以下のように説明されています。

> Sql2o is a small Java library, that makes it easy to execute sql 
> statements against your JDBC compliant database. 

[githubで:star:642の評価](https://github.com/aaberg/sql2o)があります。(2018/4/1時点)
このページに書かれていますが`SELECT`のパフォーマンスが非常に高いことを謳っています。
他の主要なデータベースアクセス用のライブラリ（`Apache DbUtils`,`MyBatis`,`Spring JdbcTemplate`等々）との比較結果が載っています。興味のある方はぜひ見てみてください。

## 2. 事前準備

### 2.1. ライブラリの用意

sql2oのライブラリはmavenリポジトリから取得できます。
利用するRDBMSのjdbcドライバについても一緒に依存関係に追加します。
サンプルではPostgreSQLを利用することにします。

```xml:pom.xml
<dependency> 
    <groupId>org.sql2o</groupId> 
    <artifactId>sql2o</artifactId> 
    <version>1.5.4</version> 
</dependency> 
<dependency> 
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId> 
    <version>9.4-1201-jdbc41</version> 
</dependency>
```

### 2.2. テーブルの用意

サンプルで利用するテーブルを作成します。

```ddl:create_table.ddl
CREATE TABLE todo
(
  todo_id character(36) NOT NULL,
  todo_title character(100),
  created_at timestamp without time zone NOT NULL,
  finished character(1) NOT NULL,
  CONSTRAINT todo_pk PRIMARY KEY (todo_id)
)
```

### 2.3. テーブルに対応するクラスの定義

テーブルのデータを保持するクラスを定義します。特別なことはなくPOJOとして実装します。
コンストラクタと`toString()`メソッドはEclipseの機能で自動生成すると楽です。

```java:Todo.java
package com.example.spark.demo;

import java.io.Serializable;
import java.util.Date;

public class Todo implements Serializable {

    private static final long serialVersionUID = 1L;
    private String todoId;
    private String todoTitle;
    private Date createdAt;
    private boolean finished;
    
    public Todo() {
        
    }

    public Todo(String todoId, String todoTitle, Date createdAt,
            boolean finished) {
        super();
        this.todoId = todoId;
        this.todoTitle = todoTitle;
        this.createdAt = createdAt;
        this.finished = finished;
    }

    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Todo [todoId=");
        builder.append(todoId);
        builder.append(", todoTitle=");
        builder.append(todoTitle);
        builder.append(", createdAt=");
        builder.append(createdAt);
        builder.append(", finished=");
        builder.append(finished);
        builder.append("]");
        return builder.toString();
    }
    
    // setter, getter omitted
}
```

## 3. Sql2oの使い方

### 3.1. 基本的な使い方

まずはじめに最もシンプルな「単一レコードを取得する」を例として、ここでは`Sql2o`を利用する際のフローを中心に説明したいと思います。

```java:Sql2oDemo.java
package com.example.spark.demo;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.sql2o.Connection;
import org.sql2o.Query;
import org.sql2o.Sql2o;

public class Sql2oDemo2 {

    public static void main(String[] args) {
        // ★ポイント1
        // 1. create sql2o instance
        Sql2o sql2o = new Sql2o("jdbc:postgresql://127.0.0.1:5432/demodb", // url
                "username",  // user
                "password"); // pass

        // ★ポイント2
        // 2. set default column mappings (optional)
        Map<String, String> colMaps = new HashMap<String, String>();
        colMaps.put("TODO_ID", "todoId");
        colMaps.put("TODO_TITLE", "todoTitle");
        colMaps.put("CREATED_AT", "createdAt");
        sql2o.setDefaultColumnMappings(colMaps);

        // ★ポイント3
        // 3. get connection
        try (Connection connection = sql2o.open()) {

            // ★ポイント4
            // 4. create query
            String queryText = "SELECT todo_id, todo_title, created_at, finished FROM todo"
                    + " WHERE created_at = (SELECT max(created_at) FROM todo)";
            Query query = connection.createQuery(queryText);

            // ★ポイント5
            // 5. execute query
            Todo todo = query.executeAndFetchFirst(Todo.class);
            System.out.println(todo);
        }
    }
}
```

**★ポイント1**
`Sql2o`のオブジェクトを`new`演算子で生成します。
コンストラクタの引数は以下の通りです。

* 第１引数　：　利用するデータベースのURL
* 第２引数　：　データベースのユーザ
* 第３引数　：　データベースのユーザのパスワード

以降、ここで生成した`Sql2o`のオブジェクトを利用します。

**（注意）
コンストラクタには`DataSource`を引数にとるものもあります。Webアプリで`Sql2o`を利用する場合はこちらを利用することを推奨します。**

**★ポイント2**
アクセスするテーブルのカラム名とクラスのフィールド名が一致している場合、（データ型が変換可能であれば）`Sql2o`が自動でマッピングを行ってくれます。
名前が異なる場合はSQLの`AS`句で名前を一致させる必要がありますが、`setDefaultColumnMappings`を利用して、この名前変換ルールを事前に設定することができます。

* キー　：　テーブルのカラム名
* 値　：　マッピングするクラスのフィールド名

**★ポイント3**
★ポイント1で生成した`Sql2o`のオブジェクトから`open()`メソッドを利用し、コネクションを取得します。
ガイドラインでも推奨していますが、自動でクローズさせるため`try-with-resources`でコネクションを取得しましょう。
ここまでが`Sql2o`を利用する際のお決まりの処理フローになります。

**★ポイント4**
実行するSQLを定義する`org.sql2o.Query`のオブジェクトを生成します。
参照系でも更新系でも定義方法は同じで、発行するSQLを`String`として定義します。
★ポイント3で取得したコネクションから`createQuery()`メソッドを利用して`Query`オブジェクトを生成します。
SQLにパラメータを設定する場合は「[3.4. パラメータを設定する](#34-パラメータを設定する)」を参照してください。

**★ポイント5**
SQLの実行は★ポイント4で生成した`Query`オブジェクトのメソッドを呼び出すことで行われます。
発行するSQLによって利用するメソッドが異なります。主要なメソッドについては以降で説明します。

### 3.2. 単一レコードを取得する
単一レコードを取得する場合は`executeAndFetchFirst`メソッドを利用します。
引数には戻り値のデータ型（クラス）を指定します。
★ポイント2で正しくマッピングを行っていれば、取得レコードを簡単にオブジェクトとして取得することができます。
注意点しては`executeAndFetchFirst`の名前の通り、仮にSQLの実行結果が複数レコードであったとしてもエラーにならず、最初のレコードを返す仕様だということです。

```java
// 4. create query
String queryText = "SELECT todo_id, todo_title, created_at, finished FROM todo"
        + " WHERE created_at = (SELECT max(created_at) FROM todo)";
Query query = connection.createQuery(queryText);

// 5. execute query
Todo todo = query.executeAndFetchFirst(Todo.class);
System.out.println(todo);
```

### 3.3. 複数レコードを取得する
複数レコードを取得する場合は`createQuery`メソッドを利用します。
引数には戻り値のデータ型（クラス）を指定します。
戻り値は引数で指定したデータ型（クラス）の`java.util.List`になります。
なお、検索結果が0件の場合、要素数が0の`List`になります。

```java
// 4. create query
String queryText2 = "SELECT todo_id, todo_title, created_at, finished FROM todo";
Query query2 = connection.createQuery(queryText2);

// 5. execute query
List<Todo> todoList = query2.executeAndFetch(Todo.class);
System.out.println(todoList);
```

### 3.4. パラメータを設定する
SQLの文字列を組み立てる際にパラメータを埋め込む方法ではSQLインジェクションが発生する可能性があります。
`Sql2o`ではパラメータを設定する方法として`Query`に`addParameter`メソッドを用意しています。
方法は簡単でSQLのパラメータを利用したい箇所に`:パラメータ名`を記述し、`addParameter`でパラメータとして埋め込みたい値を設定するだけです。

* 第１引数　：　パラメータ名
* 第２引数　：　パラメータとして埋め込みたい値（Integer、String、Timestamp等がオーバーロードで定義済み）

```java
// 4. create query
String queryText3 = "SELECT todo_id, todo_title, created_at, finished FROM todo WHERE todo_id=:todoId";
Query query3 = connection.createQuery(queryText3)
        .addParameter("todoId", "e8a57ac5-0e79-4444-be8a-3a281a5c0943");
// 5. execute query
Todo todo2 = query3.executeAndFetchFirst(Todo.class);
System.out.println(todo2);
```

### 3.5. 更新系のSQLを実行する
更新系のSQLを実行する場合は`executeUpdate`メソッドを利用します。
このメソッドは戻り値として`Sql2o`のコネクションを返します。
更新系のSQLでは変更されたレコード件数が必要な場合があるかと思います。
その場合、コネクションの`getResult`メソッドで件数を取得することができます。

```java
// 4. create query
String updateQueryText = "INSERT INTO todo(todo_id, todo_title, created_at, finished)"
        + " VALUES (:todoId, :todoTitle, :createAt, :finished)";
Query query4 = connection.createQuery(updateQueryText)
        .addParameter("todoId", UUID.randomUUID().toString())
        .addParameter("todoTitle", "world!")
        .addParameter("createAt", new Date())
        .addParameter("finished", "0");
// 5. execute query
int effectiveCount = query4.executeUpdate().getResult();
System.out.println(effectiveCount);
```

### 3.6. トランザクションの制御
ここまでトランザクションの話をしてきませんでしたが、実際のシステム開発では注意しなければならないポイントです。
`Sql2o`では宣言的トランザクションの機能は用意されていません。
明示的にトランザクションを制御するメソッドが用意されているので、実装者はこれを利用してトランザクション制御を行います。

* トランザクションの開始　：　`Sql2o`の`beginTransaction`メソッド（トランザクションを開始したコネクションを取得する）
* トランザクションのコミット　：　コネクションの`commit`メソッド
* トランザクションのロールバック　：　コネクションの`rollback`メソッド


```java
// management transaction
try (Connection connection = sql2o.beginTransaction()) {
    // first sql
    String updateQuery = "UPDATE todo SET todo_title=:todoTitle WHERE todo_id = :todoId";
    Query query1 = connection.createQuery(updateQuery)
            .addParameter("todoTitle", "googbye.")
            .addParameter("todoId",
                    "e7801fe3-6e67-41ee-abb9-4f01841a3bf0");
    int updateCount = query1.executeUpdate().getResult();
    System.out.println(updateCount);

    // If you want to try it, let's raise an exception here.
    // sql2o.open() : first sql is effective(committed)
    // sql2o.beginTransaction() : first sql is ineffective(uncommitted)

    // second sql
    String deleteQuery = "DELETE FROM todo WHERE todo_title = :todoTitle";
    Query query2 = connection.createQuery(deleteQuery)
            .addParameter("todoTitle", "world!");
    int deleteCount = query2.executeUpdate().getResult();
    System.out.println(deleteCount);
    
    // commit transaction
    connection.commit();
}
```

**（注意）
`try-with-resources`において`beginTransaction`メソッドでトランザクションを開始した場合、コミットもロールバックも行わなかった場合、自動でロールバックが実行されます。**
これは[ガイドラインにも記載](https://github.com/aaberg/sql2o/wiki/Transactions)されています。

## 4. さいごに

今回はJavaのデータベースアクセス用のライブラリである[Sql2o](https://www.sql2o.org/)の基本的な使い方について説明しました。非常にシンプルな機能および利用方法だったかと思います。
エンタープライズ用の複雑なデータベースアクセス処理には少し機能不足ですが、シンプルなマイクロサービスを作成するにはちょうどよいのでないでしょうか。