Go to Qiita Advent Calendar 2024 Top
LoginSignup
KeiFugaku
@KeiFugaku (fugaku kei)

Are you sure you want to delete the question?

If your question is resolved, you may close it.

Leaving a resolved question undeleted may help others!

We hope you find it useful!

H2 DatabaseでのSEQUENCEのCYCLEオプションについて

Q&A

JavaSQLPostgreSQLh2database

H2のPostgreSQLモードのINSERTコマンドでつまずく。

Javaで組み込みタイプのデータベースを利用することにしました。長年の実績で選ぶとしたらSQLiteが有力だと思いましたが、Javaで実装されていてサイズも小型で軽量なH2も良さそうだったので採用しました。

H2の特長
1. 単体で組み込み・サーバーどちらのタイプでも運用可能。
2. PostgreSQLおよびMySQLをエミュレーションするモードがある。
3. WEBベースの管理ツールを内蔵している。

表題の件を、サンプルのデータベースを作成するところから、順を追って

レコードを追加する際、連番でプライマリキーを付与していき、キーが上限に達したら、初期値に戻して、レコードを上書きすることにより、循環して使い回して運用するのが目的です。

例えば下記のようにテーブルとシーケンスを作成するとします。
create.png

続いてキーの上限までレコードを追加します。
image.png

さらに追加しようとするとキーが初期値の位置に戻り、すでにレコードが存在するため下図のようにエラーになります。
image.png

H2にはPostgreSQLのようにINSERTコマンドにON CONFRICT DO UPDATEというオプションが用意されていないので代わりにプログラム側でUPDATEコマンドに変えてみますが、
image.png
このように、更新数: 0となり上書き出来ません。

そこでひと手間かけて下図のように
image.png
2ステップかけて更新すれば、今度は大丈夫です。

なぜ2ステップにしなければならず、1ステップではうまくいかないのかが分かりません。何か勘違いしているところがあるのでしょうか？

それにしても、サイクリックなシーケンスを利用できるのに、INSERT ～　ON CONFLICT DO UPDATE ～という構文が使えないというのは切ないものがあります。

もし使えれば
INSERT INTO city_list SET code=(SELECT NEXT VALUE FOR code_seq), name='Paris'
ON CONFLICT DO UPDATE city_list SET name='Paris' WHERE code=(SELECT CURRENT VALUE FOR code_seq);
とコーディングすればよいだけだと思いますので。

どなたかデータベースに精通した方にご教授頂けると、大変ありがたいことと存じます。

0

1Answer

saka1_p
@saka1_p

リリースノートなどを読み込んだわけではないので、推測ですが、H2のバグかもしれません。

質問中のスクリーンショットから推察するに、H2 1.4.200を利用なさっているのだと思いますが、確かにそのバージョンだと期待した動作にはなりませんでした。
現在最新のH2 2.0.204にて、以下の一連のSQLを流した結果（の最後の方）を貼ります。こちらは期待通りの挙動だと思います。

drop table if exists t;
drop sequence if exists seq;

create table if not exists t (
  id int primary key,
  name varchar not null
);
insert into t(id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
create sequence seq start with 1 increment by 1 minvalue 1 maxvalue 3 cycle;

select * from t;
update t set name='xxx' where id=(select next value for seq);
select * from t;
SQL実行結果

確信はないですが、古いH2にバグがあり、最新のH2だと修正されているのだと考えると、辻褄は合うように思います。

0Like

Comments

  1. @KeiFugaku

    Questioner

    早速のご回答ありがとうございます。
    先月ですか、まさかこんなに最近にバージョンしていたとはつゆ知らず...
    確認不足で申し訳ございません。
    すぐにでも新バージョンに入れ替えて試してみたいと思います。
    非常に的を得たご指摘をいただき、大変助かりました。

Your answer might help someone💌

LoginSign Up