5.データの更新とトランザクション

学習目標

• executeUpdate で INSERT・UPDATE・DELETE を実行できる
• 影響行数から更新の結果を判断できる
• 複数の更新を 1 つのトランザクションにまとめ、失敗時に取り消せる

---

1. データの更新

SELECT はデータを取り出す操作でした。データを追加・変更・削除するには、INSERT・UPDATE・DELETE を実行します。これらは SELECT とは別のメソッドで実行します。

1-1. executeUpdate と影響行数

SELECT は executeQuery で実行し、結果の行を ResultSet として受け取ります。INSERT・UPDATE・DELETE は結果の行を返さず、代わりに executeUpdate で実行します。executeUpdate は、その SQL で変更された行数を int で返します。

メソッド	用途	返り値
executeQuery	SELECT	ResultSet（結果の行）
executeUpdate	INSERT・UPDATE・DELETE	int（変更された行数）

変更された行数を 影響行数 と呼びます。UPDATE や DELETE で WHERE 句に一致する行がなければ、影響行数は 0 になります。返り値を見れば、SQL が想定通りに動いたかを判断できます。

1-2. INSERT

books テーブルに新しい行を追加します。値はプレースホルダで埋め、executeUpdate を呼びます。

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

// main メソッドの中
String sql = "INSERT INTO books (isbn, title, author_id, category_id, price, published_at, stock)"
           + " VALUES (?, ?, ?, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, "root", "root");
     PreparedStatement stmt = conn.prepareStatement(sql)) {
  stmt.setString(1, "9784000000000");
  stmt.setString(2, "新しい本");
  stmt.setInt(3, 1);
  stmt.setInt(4, 1);
  stmt.setInt(5, 1500);
  stmt.setString(6, "2024-04-01");
  stmt.setInt(7, 10);

  int rows = stmt.executeUpdate();
  System.out.println("追加した行数: " + rows);
}

追加した行数: 1

id 列は AUTO_INCREMENT のため、値を指定していません。INSERT した行には、DB が自動で次の連番を割り当てます。

Warning

isbn 列は重複を許さない UNIQUE 制約を持つため、同じ isbn で INSERT を 2 回実行すると 2 回目はエラーになります。再度試すときは、isbn の値を変えてください。

1-3. UPDATE

UPDATE も executeUpdate で実行し、影響行数で結果を判断します。id を指定して価格を更新します。

Main.java

String sql = "UPDATE books SET price = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, "root", "root");
     PreparedStatement stmt = conn.prepareStatement(sql)) {
  stmt.setInt(1, 2000);
  stmt.setInt(2, 1);

  int rows = stmt.executeUpdate();
  if (rows == 0) {
    System.out.println("対象の本が見つかりません");
  } else {
    System.out.println("更新した行数: " + rows);
  }
}

id = 1 の本があれば影響行数は 1、なければ 0 です。影響行数が 0 のとき「対象が見つからなかった」と判断できます。

1-4. DELETE

DELETE も同じく executeUpdate で実行します。id を指定して 1 行削除します。

Main.java

String sql = "DELETE FROM books WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, "root", "root");
     PreparedStatement stmt = conn.prepareStatement(sql)) {
  stmt.setInt(1, 1);

  int rows = stmt.executeUpdate();
  System.out.println("削除した行数: " + rows);
}

削除した行があれば影響行数は 1、id が存在しなければ 0 です。

Warning

UPDATE・DELETE で WHERE 句を書き忘れると、テーブルの全行が対象になります。DELETE FROM books は books の全行を削除します。WHERE 句が必要かを、SQL を書く時点で必ず確かめます。

2. トランザクション

2-1. 一部だけ成功する問題

1 冊の本を販売する処理を考えます。これは 2 つの更新からなります。

• sales テーブルに販売記録を 1 行 INSERT する
• books テーブルの該当する本の stock（在庫）を減らす UPDATE

この 2 つは、両方そろって初めて「販売した」と言えます。INSERT は成功したのに続く UPDATE で例外が発生すると、販売記録は残るのに在庫が減らないまま、データが矛盾した状態になります。逆に在庫だけ減って販売記録がなければ、売れた本が分からなくなります。

複数の更新が、すべて成功するかすべて失敗するかのどちらかでなければ、データは矛盾します。これを防ぐ仕組みが トランザクション です。トランザクションは、複数の更新を 1 つのまとまりとして扱い、途中で失敗したらすべてを取り消します。

Info

「すべて成功するか、すべて失敗するか」という性質を 原子性 と呼びます。トランザクションが備えるべき性質は、原子性を含む 4 つの観点を頭文字でまとめて ACID と呼ばれます。

2-2. commit と rollback

JDBC は既定で、executeUpdate を呼ぶたびに自動で変更を確定します。複数の更新を 1 つのトランザクションにまとめるには、まず setAutoCommit(false) で自動の確定を止めます。

そのうえで、すべての更新が成功したら commit で確定し、途中で失敗したら rollback で取り消します。

conn.setAutoCommit(false);  // 自動の確定を止める
conn.commit();              // すべての変更を確定する
conn.rollback();            // すべての変更を取り消す

販売処理を、トランザクションで書きます。try の中で 2 つの更新を実行し、両方成功したら commit、途中で SQLException が発生したら catch で rollback します。

Main.java

try (Connection conn = DriverManager.getConnection(url, "root", "root")) {
  conn.setAutoCommit(false);

  try {
    String insertSql = "INSERT INTO sales (book_id, customer_id, quantity, total_price, sold_at)"
                     + " VALUES (?, ?, ?, ?, NOW())";
    try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
      stmt.setInt(1, 1);
      stmt.setInt(2, 1);
      stmt.setInt(3, 1);
      stmt.setInt(4, 473);
      stmt.executeUpdate();
    }

    String updateSql = "UPDATE books SET stock = stock - 1 WHERE id = ?";
    try (PreparedStatement stmt = conn.prepareStatement(updateSql)) {
      stmt.setInt(1, 1);
      stmt.executeUpdate();
    }

    conn.commit();
    System.out.println("販売を記録しました");
  } catch (SQLException e) {
    conn.rollback();
    System.out.println("販売に失敗しました。変更を取り消しました");
    throw e;
  }
}

setAutoCommit(false) から commit までの 2 つの更新が、1 つのトランザクションになります。両方成功すれば commit で確定され、販売記録の追加と在庫の減少がまとめて反映されます。途中で例外が発生すると catch に移り、rollback がそこまでの変更をすべて取り消します。INSERT だけ成功して UPDATE で失敗した場合も、rollback で INSERT がなかったことになり、矛盾した状態は残りません。

catch では rollback のあとに例外を再び投げ、呼び出し元に失敗を伝えます。これは 例外処理の作法 で確認した、失敗を握りつぶさない扱いです。

Info

ここでは setAutoCommit・commit・rollback を自分で呼びました。フレームワークを使う開発では、こうした制御を自動で行う仕組みが用意されています。その場合も、複数の更新をまとめて確定するか取り消すかという考え方は変わりません。

3. 演習

演習: データの更新