PL/SQL と Spring Data JPA で学ぶ Oracle の IDENTITY 列

こんにちは、技術戦略部の水口です。

Oracle Database で自動採番といえば、SEQUENCE を思い浮かべる方が多いのではないでしょうか。Oracle Database 12c で追加された IDENTITY 列は、ISO の SQL 標準に対応し、SEQUENCE と同様に自動採番を実現してくれる機能です。Oracle Database 23ai がリリースされた現在、IDENTITY 列は各種ツールやライブラリなどで対応が進み、そろそろ普及してくる頃合いかと思うので、その使い勝手を検証しながら学んでいきます。

※検証は、Oracle Database 19c で行っています。

IDENTITY 列を持つテーブルを定義する

親子関係にある注文テーブルと注文明細テーブルを例に、IDENTITY 列を持つテーブルを定義してみます。
注文テーブルと注文明細テーブルの id 列が自動採番の対象になります。

CREATE TABLE orders -- 注文テーブル
(
  id NUMBER(10) GENERATED AS IDENTITY PRIMARY KEY, -- 自動採番
  customer_name VARCHAR2(20) NOT NULL,
  ordered_at DATE NOT NULL
);

CREATE TABLE order_details  -- 注文明細テーブル
(
  id NUMBER(12) GENERATED AS IDENTITY PRIMARY KEY,  -- 自動採番
  order_id NUMBER(10) NOT NULL,
  item_name VARCHAR2(20) NOT NULL,
  quantity number(5) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

id 列に GENERATED AS IDENTITY を指定することで IDENTITY 列であることを表しています。IDENTITY 列では内部で SEQUENCE を使っているので、CREATE SEQUENCE 文と同じパラメータを指定できます。詳細は公式マニュアルをご確認ください。

【補足１】IDENTITY 列の内部で使われている SEQUENCE

CREATE TABLE などで、IDENTITY 列を定義すると、SEQUENCE が自動で作成されます。all_tab_identity_cols というデータ・ディクショナリ・ビューで、その SEQUENCE が確認できます。

SELECT
  sequence_name
FROM
  all_tab_identity_cols
WHERE
  table_name = 'ORDERS'
;

SEQUENCE_NAME
ISEQ$$_309998

IDENTITY 列を持つテーブルを以下のように削除しパージすると、SEQUENCE も自動で削除されます。

DROP TABLE order_details PURGE;
DROP TABLE orders PURGE;

IDENTITY 列では、明示的に SEQUENCE を作成したり削除したりする必要が無いので、運用面での負担はありません。

【補足２】カラムのデフォルト値で指定する SEQUENCE の NEXTVAL

今回の検証から少し脱線しますが、Oracle Database 12c では、以下の例のように、カラムのデフォルト値に SEQUENCE の NEXTVAL が指定できるようになりました。

CREATE SEQUENCE orders_seq;
CREATE TABLE orders
(
  id NUMBER(10) DEFAULT orders_seq.nextval PRIMARY KEY,
  customer_name VARCHAR2(20) NOT NULL,
  ordered_at DATE NOT NULL
);

CREATE SEQUENCE order_details_seq;
CREATE TABLE order_details
(
  id NUMBER(12) DEFAULT order_details_seq.nextval PRIMARY KEY,
  order_id NUMBER(10) NOT NULL,
  item_name VARCHAR2(20) NOT NULL,
  quantity number(5) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

自動採番で SEQUENCE を利用している既存の DB で IDENTITY 列の採用が難しい場合、こちらの機能を使う選択肢もありかと思います。表のINSERTトリガーを作成する必要が無いので、より良いパフォーマンスで事前作成したSEQUENCEから採番できるようになります。

PL/SQL で IDENTITY 列を持つテーブルにデータを登録する

次に、先ほど定義したテーブルに、PL/SQL を使った２通りの方法でデータを登録してみます。

RETURNING INTO 句を利用する場合

ポイントは、①INSERT 文で自動採番する列は省略する②親テーブルのデータ登録で自動採番された id は RETURNING INTO 句で取得する、です。

DECLARE
  last_insert_order_id orders.id%TYPE;
BEGIN
  INSERT INTO orders (customer_name, ordered_at) VALUES ('customer1', SYSDATE)
    RETURNING id INTO last_insert_order_id;
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (last_insert_order_id, 'item1', 4);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (last_insert_order_id, 'item2', 10);

  INSERT INTO orders (customer_name, ordered_at) VALUES ('customer2', SYSDATE)
    RETURNING id INTO last_insert_order_id;
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (last_insert_order_id, 'item2', 7);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (last_insert_order_id, 'item3', 2);

  COMMIT;
END;

SEQUENCE を利用する場合

ポイントは、①INSERT 文で自動採番する列は省略する②親テーブルのデータ登録で自動採番された id は SEQUENCE で取得する、です。
先ほど説明した通り、IDENTITY 列の内部で使われている SEQUENCE の名前は、all_tab_identity_cols というデータ・ディクショナリ・ビューで確認できます（今回の例では、「ISEQ$$_309998」とします）。

BEGIN
  INSERT INTO orders (customer_name, ordered_at) VALUES ('customer1', SYSDATE);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (ISEQ$$_309998.currval, 'item1', 4);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (ISEQ$$_309998.currval, 'item2', 10);

  INSERT INTO orders (customer_name, ordered_at) VALUES ('customer2', SYSDATE);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (ISEQ$$_309998.currval, 'item2', 7);
  INSERT INTO order_details (order_id, item_name, quantity) VALUES (ISEQ$$_309998.currval, 'item3', 2);

  COMMIT;
END;

Spring Data JPA で IDENTITY 列を持つテーブルにデータを登録する

PL/SQL で簡単にデータが登録できたので、次はメジャーなフレームワークである、Spring Data JPA （JPA プロバイダはデフォルトの Hibernate）で検証してみたいと思います。

※検証は、Java 17、Spring Boot 3.3 系で行っています。

Entitiy

ポイントは、id列に指定するアノテーション、@GeneratedValue(strategy = GenerationType.IDENTITY)、です。シンプルですね。
@GeneratedValueや@GeneratedValue(strategy=GenerationType.AUTO)だと、strategy が SEQUENCE になってしまい、期待通りの結果にならないので注意してください。
それ以外は、JPA のお作法に則っています。

@Entity
@Table(name = "orders")
@Data
public class Order {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String customerName;
  private Date orderedAt;

  @OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
  private List<OrderDetail> orderDetails = new ArrayList<>();

  public Order(String customerName, Date orderedAt) {
    this.customerName = customerName;
    this.orderedAt = orderedAt;
  }

  protected Order() {
  }

  public void addOrderDetail(OrderDetail orderDetail) {
    orderDetails.add(orderDetail);
    orderDetail.setOrder(this);
  }
}

@Entity
@Table(name = "order_details")
@Data
public class OrderDetail {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String itemName;
  private Integer quantity;

  @ToString.Exclude
  @ManyToOne
  private Order order;

  public OrderDetail(String itemName, Integer quantity) {
    this.itemName = itemName;
    this.quantity = quantity;
  }

  protected OrderDetail() {
  }
}

Repository

Repository では、IDENTITY 列固有の実装は不要です。

public interface OrderRepository extends CrudRepository<Order, Long> {
}

Service

先ほど、PL/SQL で示した例と同様に、IDENTITY 列を持つテーブルにデータを登録しています。親テーブルで自動採番された id はフレームワーク側で上手く処理してくれるので、子テーブルへのデータ登録はシンプルな実装になっています。

@Service
public class OrderService {
  @Autowired
  private OrderRepository repository;

  @Transactional
  public void saveOrders() {
    List<Order> orders = new ArrayList<>();

    Order order1 = new Order("customer1", new Date());
    order1.addOrderDetail(new OrderDetail("item1", 4));
    order1.addOrderDetail(new OrderDetail("item2", 10));
    orders.add(order1);

    Order order2 = new Order("customer2", new Date());
    order2.addOrderDetail(new OrderDetail("item2", 7));
    order2.addOrderDetail(new OrderDetail("item3", 2));
    orders.add(order2);

    repository.saveAll(orders);
  }
}

まとめ

以上、駆け足となりましたが、これで検証は終わりとなります。
Oracle Database の IDENTITY 列は、従来利用されてきた SEQUENCE と比べて、自動採番をシンプルに実現できる機能だとお分かり頂けたのではないでしょうか。今後、IDENTITY 列を利用する際に、この記事が少しでも参考になれば幸いです。

さて、ラクーン技術戦略部では一緒に働く仲間を募集しています！採用情報はこちらです。