2025年1月12日のkintone API アップデートで、kintone API はUPSERT モードを正式にサポートしました。これを受けてCData kintone Drivers も本API を使ったUPSERT 文に対応しました。本記事ではkintone ドライバーでUPSERT 文を使う方法を紹介します。
UPSERT とは
SQL のUPSERT 文はデータベース操作の一つで、レコードが存在しない場合は挿入(INSERT)し、存在する場合は更新(UPDATE)する便利な機能を持ちます。例えば、レコードの更新時に当該レコードの存在有無を事前に確認し、存在する場合はUPDATE、存在しない場合はINSERT といった一連の処理を行いたいとき、従来は条件分岐によって2つの操作を使い分けていましたが、UPSERT 文を使うことでこの処理を1つの文で済ませることができます。これにより開発効率の向上やエラーの削減など様々なメリットがあります。
例えば以下のように3つの商品レコードを持つProducts テーブルを例に説明します。このテーブルでは「キー」カラムの値でレコードの照合を行います。
以下のようにキーが存在しないレコードがUPSERT されたときはそのレコードを挿入します。
以下のようにキーが存在するレコードがUPSERTされたときはレコードの更新を行います。
kintone でUPSERT 文を使う大きな効果として、API コール数の削減が挙げられます。下図にINSERT 文とUPDATE 文を駆使してUPSERT 処理を実現するフローと、UPSERT 文でこれを実現するフローを示します。UPSERT 文を使わないアプローチでは更新するレコードを一件一件確認してINSERT 文とUPDATE 文に分岐します。更新するレコード数だけAPI の呼び出しが発生するため、レコードの数が多いと更新の時間がかかったり、API のリクエスト制限値に抵触したりします。一方UPSERT 文を使ったアプローチは基本的に一回のAPI 呼び出しでUPSERT 処理を行うため、通信にかかる時間はレコード数に関わらず一定となります。
kintoneのUPSERT文
kintone はバッチ処理でのUPSERTをサポートしています。UPSERT 文の書式は以下の通りです。
UPSERT INTO Table(Column1, Column2, ... , KeyColumn) values(更新値1, 更新値2, ... , キー値);
Column1, Column2, ... は更新するカラム、KeyColumn (キーカラム)はUPSERT のキーを指定するカラムです。UPSERT 機能ではRecordId または「値の重複を禁止する」がチェックされているカラムをキーカラムとみなします。
例として以下のProducts アプリを作成しました。このアプリではCode フィールドに「値の重複を禁止する」が設定されています。
以下にProducts テーブルに対してUPSERT 文を実行し、製品名(Name)を更新またはレコードを挿入するJava コードの例を示します。このコードでは既に存在するCode = Product2 のName を「新商品2」更新し、Code = Product4 のレコードを追加します。
Connection conn = DriverManager.getConnection("jdbc:kintone:",prop);
String query = "UPSERT INTO Products(Name,Code) VALUES(?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
// Product2の更新
pstmt.setString(1, "新商品2");
pstmt.setString(2, "Product2");
pstmt.addBatch();
// Product4の挿入
pstmt.setString(1, "商品4");
pstmt.setString(2, "Product4");
pstmt.addBatch();
pstmt.executeBatch();
上記コードの実行結果を以下に示します。
上で示したように、一つのUPSERT 文でレコードの更新と追加を同時に行うことができました。
バッチ処理をSQL クライアントで実現する場合、更新するレコードを一時的に保持する一時テーブル(テーブル名に#TEMPを付与)を使用します。以下の例では上で示したJava コードと同じ処理をSQL で記述しています。はじめにINSERT 文を使い、Products#TEMP という一時テーブルに対して、更新するレコードを一つ一つ登録していきます。最後のUPSERT INTO SELECT 文で、一時テーブルが保持するレコードの値をもとにUPSERT 処理を実行します。
Insert into Products#TEMP (Name, Code) VALUES ('新商品2', 'Product2');
Insert into Products#TEMP (Name, Code) VALUES ('商品4', 'Product4');
UPSERT INTO Products (Name, Code) SELECT Name, Code FROM Products#TEMP;
制限事項
UPSERT 文には以下の制限がありますのでご注意ください。
- キーカラムとして指定できるフィールドは重複禁止を設定した「文字列（1行）」または「数値」フィールドのみです。
- 複数のキーカラムを指定した場合、最後に指定したキーのみが参照されます。
- キーカラムを更新対象とすることはできません。
おわりに
本記事では、kintone ドライバでレコードの挿入と更新を1つの文で実行するUPSERT文を使う方法を説明しました。データの更新処理を構築する際はUPSERT を使うことで生産性や性能が大きく上がります。kintoneドライバは30日間無料で試用できます。kintone との連携をご検討の方はぜひお試しください
