Web系開発メモ

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

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

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

sql2o について

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

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

バージョン

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

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. マッピングについて

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

  1. SQL で列名をプロパティ名に変更
  2. 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;

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