AUTO_INCREMENTに依存せず、欠番なくIDを作る機能を自前で実装し各種DBに対応する

はじめに

特定のDBの機能に依存した実装をしてしまうと、将来に大きな負担となる場合があります。

AUTO_INCREMENTは便利な機能ですが、機能を有していないDBも存在します。
同等の機能をアプリケーション側で実装し、
各種DBに対応できるようにするとともに、欠番が発生しないようにしてみます。

実装例

ID(シーケンス番号)を管理するためのテーブルを定義します。
カラム:NAMEには、この機能で発行するIDを使うテーブル名、
カラム:SEQUENCEには、テーブルごとのID(数値)が入ります。
桁数などは適当に調整してください。

sequence.sql

# MySQL
CREATE TABLE SEQUENCE (
  NAME VARCHAR(255) BINARY NOT NULL,
  SEQUENCE BIGINT DEFAULT 0 NOT NULL,
  PRIMARY KEY PK_SEQUENCE (NAME)
);

# Oracle
CREATE TABLE SEQUENCE (
  NAME VARCHAR2(256) NOT NULL,
  SEQUENCE NUMBER(16) DEFAULT 0 NOT NULL,
  CONSTRAINT PK_SEQUENCE PRIMARY KEY (NAME)
);

# PostgreSQL / SQL Server / DB2
CREATE TABLE SEQUENCE (
  NAME VARCHAR(256) NOT NULL,
  SEQUENCE BIGINT DEFAULT 0 NOT NULL,
  CONSTRAINT PK_SEQUENCE PRIMARY KEY (NAME)
);

次に、IDを発行するクラスを定義します。

SequenceTest.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

/**
 *
 * @author tool-taro.com
 */
public class SequenceTest {

	public static long create(Connection connection, String name) throws SQLException {
		long id = 0;

		PreparedStatement preparedStatement;
		ResultSet resultSet;
		int count;
		int update;
		Savepoint savepoint;

		connection.setAutoCommit(false);

		//初回はレコードがないのでINSERTする
		savepoint = connection.setSavepoint();
		count = 0;
		update = 0;
		preparedStatement = connection.prepareStatement("INSERT INTO SEQUENCE (NAME, SEQUENCE) VALUES (?, ?)");
		preparedStatement.setString(++count, name);
		preparedStatement.setLong(++count, 0);
		try {
			update = preparedStatement.executeUpdate();
		}
		catch (SQLException e) {
			connection.rollback(savepoint);
		}
		preparedStatement.close();

		if (update == 0) {
			//INSERTに失敗した場合はレコードがあるのでUPDATEする
			count = 0;
			preparedStatement = connection.prepareStatement("UPDATE SEQUENCE SET SEQUENCE = SEQUENCE + 1 WHERE NAME = ?");
			preparedStatement.setString(++count, name);
			update = preparedStatement.executeUpdate();
			preparedStatement.close();
			//INSERTもUPDATEも失敗したということは最初にINSERTした側のTransactionがrollbackされてレコードが存在しないということなので最初からやり直す
			if (update == 0) {
				return SequenceTest.create(connection, name);
			}

			count = 0;
			preparedStatement = connection.prepareStatement("SELECT SEQUENCE FROM SEQUENCE WHERE NAME = ?");
			preparedStatement.setString(++count, name);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				id = resultSet.getLong("SEQUENCE");
			}
			resultSet.close();
			preparedStatement.close();
		}

		return id;
	}
}

ID発行機構の準備が終わりました。
サンプルでは、動作確認しやすいようにjspで実装しています。

sequence_test.jsp

<%-- 
    Author     : tool-taro.com
--%>

<%@page import="SequenceTest"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="java.sql.Connection"%>
<%@page import="javax.naming.InitialContext"%>
<%@page contentType="text/html" pageEncoding="UTF-8" session="false" %>
<%
        //コネクションを取得するjndi
        String jndi = "java:comp/env/jdbc/MySQL";
        //String jndi = "java:comp/env/jdbc/Oracle"; //←Oracleの場合

        InitialContext context = null;
        Connection connection = null;

        //コネクション取得処理
        long id_1 = -1;
        long id_2 = -1;

        try {
                context = new InitialContext();
                DataSource dataSource = (DataSource) context.lookup(jndi);

                connection = dataSource.getConnection();
                //トランザクション分離レベルはDBによって異なるので設定を統一
                connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                connection.setAutoCommit(false);

                //IDを取得してわざとrollbackする
                SequenceTest.create(connection, "TEST_TABLE_NAME");
                connection.rollback();

                //IDを取得してcommitする(初回アクセスなら"0"を得られるはず)
                id_1 = SequenceTest.create(connection, "TEST_TABLE_NAME");
                //このタイミングで対象のテーブルにデータを挿入するなどの処理を行う
                connection.commit();

                //IDを取得してわざとrollbackする
                SequenceTest.create(connection, "TEST_TABLE_NAME");
                connection.rollback();

                //IDを取得してcommitする(初回アクセスなら"1"を得られるはず)
                id_2 = SequenceTest.create(connection, "TEST_TABLE_NAME");
                //このタイミングで対象のテーブルにデータを挿入するなどの処理を行う
                connection.commit();
        }
        finally {
                if (context != null) {
                        try {
                                context.close();
                        }
                        catch (Exception e) {
                        }
                }
                if (connection != null) {
                        try {
                                connection.close();
                        }
                        catch (Exception e) {
                        }
                }
        }
%>
<!DOCTYPE html>
<html>
    <head>
        <title>tool-taro.com</title>
    </head>
    <body>
        取得したID_1="<%= id_1%>"<br>
        取得したID_2="<%= id_2%>"<br>
    </body>
</html>

動作確認

sequence_test.jspの実行結果を見てみましょう。

取得したID_1="0"
取得したID_2="1"

想定通りの結果を得られました。
2回目のアクセスでは次のような結果となります。

取得したID_1="2"
取得したID_2="3"

環境

• 開発

  • Windows 10 Pro
  • JDK 1.8.0_112
  • NetBeans IDE 8.2

• 動作検証

  • CentOS Linux release 7.2
  • JDK 1.8.0_112

Webツールも公開しています。
Web便利ツール@ツールタロウ