Java sql2oでSQLを簡単に実行する方法（参照系・更新系）

sql2o を使って、データベースに接続して SQL を実行する方法を書いていきます。

sql2o について

sql2o の特徴は以下の通りです。

Java の軽量なライブラリです。
SQL を簡単に実行することができます。
SQL の実行結果を POJO にマッピングしてくれます。
パフォーマンスが優れていると言われています。

バージョン

ブログ執筆時の製品バージョンは以下の通りです。

Java 17
Maven 3.8.6
PostgreSQL 15.1
sql2o 1.6.0
pgJDBC 42.5.1（JDBC Driver）
Lombok 1.18.24

1. ビルドファイルの作成

プロジェクトのフォルダの下に、ビルドファイルを作成します。

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.sample</groupId>
  <artifactId>sql2o-postgresql</artifactId>
  <version>1.0.0</version>
  <packaging>jar</packaging>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.sql2o</groupId>
      <artifactId>sql2o</artifactId>
      <version>1.6.0</version>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.5.1</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
      <scope>provided</scope>
    </dependency>
  </dependencies>
</project>

JDBC ドライバーは必須で、Getter や Setter などを省略するために Lombok を追加しています。

2. モデルの作成

以下の POJO クラスを作成します。

src/main/java/org/sample/model/Task.java

package org.sample.model;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import java.util.Date;

@Getter @Setter @ToString
public class Task {
  private Long id;
  private String title;
  private Boolean isImportant;
  private Date dueDate;
}

3. SELECT文の実行方法

以下の Javaプログラムを作成します。

src/main/java/org/sample/Select.java

package org.sample;

import org.sample.model.Task;
import org.sql2o.Connection;
import org.sql2o.Sql2o;

import java.util.List;

public class Select {
  public static void main(String[] args) {
    // localhost の 5432 ポート、test データベースに、
    // ユーザー usr、パスワード pass で接続
    Sql2o sql2o = new Sql2o(
      "jdbc:postgresql://localhost:5432/test", "usr",  "pass"
    );
    // 重要なタスクを取得して表示
    List<Task> tasks = getTasks(sql2o, true);
    for (Task task: tasks) {
      System.out.println(task);
    }
    // ID が 1 のタスクを取得して表示
    Task task = getTask(sql2o, 1);
    System.out.println();
    System.out.println(task);
  }
  // SQL のエイリアス（AS）で、
  // テーブルの列名をモデルのプロパティ名に変更
  private static final String SELECT_IMPORTANT =
    "SELECT " +
      "id, title, is_important AS isImportant, due_date AS dueDate " +
    "FROM tasks WHERE is_important = :isImportant";
  private static List<Task> getTasks(Sql2o sql2o, boolean isImportant) {
    try (Connection con = sql2o.open()) {
      return con.createQuery(SELECT_IMPORTANT)
        .addParameter("isImportant", isImportant)
        .executeAndFetch(Task.class);
    }
  }
  // sql2o の addColumnMapping(String, String) で、
  // テーブルの列名とモデルのプロパティ名のマッピングを追加
  private static final String SELECT_ID =
    "SELECT " +
      "id, title, is_important, due_date " +
    "FROM tasks WHERE id = :id";
  private static Task getTask(Sql2o sql2o, long id) {
    try (Connection con = sql2o.open()) {
      return con.createQuery(SELECT_ID)
        .addParameter("id", id)
        .addColumnMapping("is_important", "isImportant")
        .addColumnMapping("due_date", "dueDate")
        .executeAndFetchFirst(Task.class);
    }
  }
}

3.1. マッピングについて

テーブルの列名とモデルのプロパティ名が違う場合は、上のプログラムのように、以下の対応が必要になります。

SQL で列名をプロパティ名に変更
sql2o のマッピング定義を追加

マッピング定義は、Map を使って追加することもできます。詳細は以下のリンク先を参照して頂ければと思います。

Column mappings - sql2o

4. UPDATE文の実行方法

以下のソースコードを作成します。

src/main/java/org/sample/Update.java

package org.sample;

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

public class Update {
  private static final String UPDATE_TITLE =
    "UPDATE tasks SET title = :title WHERE id = :id";
  public static void main(String[] args) {
    // localhost の 5432 ポート、test データベースに、
    // ユーザー usr、パスワード pass で接続
    Sql2o sql2o = new Sql2o(
      "jdbc:postgresql://localhost:5432/test", "usr",  "pass"
    );
    // ID が 2 のタスクを更新
    try (Connection con = sql2o.beginTransaction()) {
      con.createQuery(UPDATE_TITLE)
        .addParameter("title", "箸置きを買う")
        .addParameter("id", 2)
        .executeUpdate();
      // トランザクションのコミット
      // コミットしない場合はロールバック（デフォルト）
      con.commit();
    }
  }
}

4.1. トランザクションについて

トランザクションは、commit() か rollback() が実行されないと、自動的にロールバックされます。

5. 動作確認

5.1. テーブルの作成

PostgreSQL に接続して、以下の SQL を実行します。

CREATE TABLE tasks (
  id bigserial PRIMARY KEY,
  title varchar(100) NOT NULL,
  is_important boolean NOT NULL DEFAULT false,
  due_date date NOT NULL DEFAULT current_date
);

5.2. データの登録

以下の SQL を実行して、データを投入します。

INSERT INTO tasks
  (title, is_important, due_date)
VALUES
  ('お米を買う', true, '2022-12-10'),
  ('お茶碗を買う', false, '2022-12-11'),
  ('メモリを増設する', true, '2022-12-12'),
  ('ストレージを増設する', false, '2022-12-13');

5.3. SELECT文の実行

クラス SELECT を実行すると、SQL で取得したデータが表示されます。

5.4. UPDATE文の実行

クラス UPDATE を実行して、以下の SQL を実行します。

SELECT * FROM tasks WHERE id = 2;

実行すると、更新されたデータを確認できます。

Web系開発メモ

Java, C#, HTML, CSS, JavaScript のことなどを書いてます。