[SQL Server] 自動生成キーの使用

SQL Server では列に IDENTITY属性を付けて定義すると、レコードが INSERT されるたびに一意な ID値を自動生成してくれます。

CREATE TABLE TABLE1 
   (ID int IDENTITY, 
    NAME varchar(50), 
    VALUE varchar(200);

キーを自動生成したいときに便利な機能です。


しかし、INSERT した時にこの自動生成された ID値を取得するのは意外と面倒です。

例えば、SQL Server 2000 の頃は以下のような INSERT 用のプロシージャを作成して、これを CallableStatement を使って実行し、OUTパラメータとして自動生成された ID値を取得していました。

CREATE OR REPLACE PROCEDURE TABLE1_INSERT
(
  i_name IN VARCHAR2,
  i_value IN VARCHAR2,
  o_id OUT VARCHAR2,
  o_name OUT VARCHAR2,
  o_value OUT VARCHAR2
)
IS
BEGIN
  INSERT INTO TABLE1 (
    NAME,
    VALUE
  )
    VALUES (
      ip_name,
      ip_value
    )
    RETURNING 
      ID,
      NAME,
      VALUE
      INTO 
        o_id,
        o_name,
        o_value;
END;
/

メンテナンス性が低下するのであまりやりたくない方法です。


ところが、Microsoft SQL Server 2005 JDBC Driver では JDBC 3.0 API の自動生成キー取得機能を使用して、通常の INSERT 文の実行結果から自動生成キーを取得することが可能になっているんですね。

以下のように PreparedStatement のインスタンスを生成するときに PreparedStatement.RETURN_GENERATED_KEYS を指定し、executeUpdate() を実行した後に getGeneratedKeys() を実行すれば自動生成キーを ResultSet として取得することができます。

public static int insertTable1(Connection con, Object[] args) {
  int id = 0;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  String sql = "INSERT INTO TABLE1 (NAME, VALUE) VALUES (?,?)";
  try {
    pstmt = ((SQLServerConnection) con).prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
    for (int i = 1; i <= 2; i++) {
      pstmt.setObject(i, args[0]);
    }
    pstmt.executeUpdate();
    rs = pstmt.getGeneratedKeys();
    if (rs.next()) {
      id = rs.getInt(1);
    }
  } catch (Exception e){
    e.printStackTrace();
  } finally {
    try {
      if (rs != null){
        rs.close();
      }
      if (pstmt != null){
        pstmt.close();
      }
    } catch (Exception e){
      e.printStackTrace();
    }
  }
  return id;
}

単純に INSERT文を実行する場合と比べるとわずかに手順が増えますが、SQL の実行自体は 1回で済みますし、プロシージャを作成する手間も省けますので非常に便利かと思います。

参考：自動生成キーの使用［MSDNライブラリ］