LoginSignup
13
13

More than 5 years have passed since last update.

@5zm

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

Last updated at Posted at 2018-04-01

1. はじめに

今回はJavaのデータベースアクセス用のライブラリであるSql2oの基本的な使い方について説明したいと思います。
そもそもSql2oを知ったのはSparkFrameworkのガイドラインでDBアクセスにSql2oを利用していたからです。
シンプルなマイクロサービスのためのフレームワークであるSparkFrameworkが使うのであればSql2oもシンプルなのでは、ということで試しに利用してみました。
SparkFrameworkについては「Spark Frameworkで簡単マイクロサービス！」を参照ください。

1.1. Sql2oとは

非常にシンプルなデータベースアクセス用のJavaのライブラリで、公式サイトでは以下のように説明されています。

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

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

2. 事前準備

2.1. ライブラリの用意

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

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. テーブルの用意

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

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の機能で自動生成すると楽です。

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を利用する際のフローを中心に説明したいと思います。

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. パラメータを設定する」を参照してください。

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

3.2. 単一レコードを取得する

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

// 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になります。

// 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等がオーバーロードで定義済み）
// 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メソッドで件数を取得することができます。

// 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メソッド
// 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メソッドでトランザクションを開始した場合、コミットもロールバックも行わなかった場合、自動でロールバックが実行されます。
これはガイドラインにも記載されています。

4. さいごに

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

13
13
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
13