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で642の評価があります。(2018/4/1時点)
このページに書かれていますがSELECT
のパフォーマンスが非常に高いことを謳っています。
他の主要なデータベースアクセス用のライブラリ(Apache DbUtils
,MyBatis
,Spring JdbcTemplate
等々)との比較結果が載っています。興味のある方はぜひ見てみてください。
2. 事前準備
2.1. ライブラリの用意
sql2oのライブラリはmavenリポジトリから取得できます。
利用するRDBMSのjdbcドライバについても一緒に依存関係に追加します。
サンプルではPostgreSQLを利用することにします。
<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 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の機能で自動生成すると楽です。
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
を利用する際のフローを中心に説明したいと思います。
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
演算子で生成します。
コンストラクタの引数は以下の通りです。
- 第1引数 : 利用するデータベースのURL
- 第2引数 : データベースのユーザ
- 第3引数 : データベースのユーザのパスワード
以降、ここで生成した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
でパラメータとして埋め込みたい値を設定するだけです。
- 第1引数 : パラメータ名
- 第2引数 : パラメータとして埋め込みたい値(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の基本的な使い方について説明しました。非常にシンプルな機能および利用方法だったかと思います。
エンタープライズ用の複雑なデータベースアクセス処理には少し機能不足ですが、シンプルなマイクロサービスを作成するにはちょうどよいのでないでしょうか。