【MySQL+Java】採番プロシージャ

はじめに

MySQL + Java な環境で採番の仕組みを用意する必要があったので、
下記の記事を参考に作ってみました。

MySQL で採番テーブル
MySQL で シーケンス 機能実現
LAST_INSERT_IDを使って採番テーブルを扱う

OracleのシーケンスがMySQLにもあればなー。

環境

MySQL 5.7
Java 8

やったこと

• 採番用のテーブルを用意する。×２
• 採番プロシージャを用意する。×２
• Javaで検証する。

採番用テーブル

テーブル１

CREATE TABLE `seq1` (
  `prefix` varchar(8) NOT NULL,
  `id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

テーブル２

CREATE TABLE `seq2` (
  `prefix` varchar(8) NOT NULL,
  `id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

それぞれ１レコードずつ登録しておく。

採番プロシージャ

プロシージャ１

CREATE DEFINER=`trial`@`localhost` PROCEDURE `nextval_seq1`(out seqnum varchar(12))
BEGIN
    START TRANSACTION;
    update seq1 set id = LAST_INSERT_ID(id + 1);
    select CONCAT(prefix, LPAD(LAST_INSERT_ID(id), 10, '0')) into seqnum from seq1;
    COMMIT;
END

プロシージャ２

CREATE DEFINER=`trial`@`localhost` PROCEDURE `nextval_seq2`(out seqnum varchar(12))
BEGIN
    START TRANSACTION;
    update seq2 set id = LAST_INSERT_ID(id + 1);
    select CONCAT(prefix, LPAD(LAST_INSERT_ID(id), 10, '0')) into seqnum from seq2;
    COMMIT;
END

検証Javaコード

以下の観点で検証コードを実装しました。

• マルチスレッドでも問題ない事
• プロシージャを利用してもコネクションが死なない事
• 複数の採番用テーブル、採番プロシージャでちゃんと？採番される事

    private void execute(String[] args) {
        ExecutorService pool = Executors.newFixedThreadPool(100);
        try {
            List<Callable<Boolean>> taskList = new ArrayList<>();
            for (int i = 0; i < 50; i++) {
                taskList.add(() -> callProcedureAndUpdatePrefix1());
                taskList.add(() -> callProcedureAndUpdatePrefix2());
            }
            pool.invokeAll(taskList);
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            pool.shutdown();
        }
    }

    private boolean callProcedureAndUpdatePrefix1() {
        try (Connection conn = ConnectionManager.getConnection()) {
            conn.setAutoCommit(false);
            callProcedure1(conn);
            updateSeq1(conn);
            conn.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private boolean callProcedureAndUpdatePrefix2() {
        try (Connection conn = ConnectionManager.getConnection()) {
            conn.setAutoCommit(false);
            callProcedure2(conn);
            updateSeq2(conn);
            conn.commit();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private void callProcedure1(Connection con) throws SQLException {
        try (CallableStatement cs = con.prepareCall("call nextval_seq1(?)")) {
            cs.executeQuery();
            System.out.println(cs.getString(1));
        }
    }

    private void callProcedure2(Connection con) throws SQLException {
        try (CallableStatement cs = con.prepareCall("call nextval_seq2(?)")) {
            cs.executeQuery();
            System.out.println(cs.getString(1));
        }
    }

    private void updateSeq1(Connection con) throws SQLException {
        boolean b = (new Random()).nextInt(99) % 2 == 0 ? true : false;
        String sql = "update seq1 set prefix = " + (b ? "'XA'" : "'XB'");
        try (Statement st = con.createStatement()) {
            st.execute(sql);
        }
    }

    private void updateSeq2(Connection con) throws SQLException {
        boolean b = (new Random()).nextInt(99) % 2 == 0 ? true : false;
        String sql = "update seq2 set prefix = " + (b ? "'YA'" : "'YB'");
        try (Statement st = con.createStatement()) {
            st.execute(sql);
        }
    }

検証結果

XA0000000001
YA0000000006
YA0000000004
XA0000000006
XA0000000005
XA0000000002
XA0000000004
XA0000000007
YA0000000003
YA0000000005
XA0000000019
XA0000000018
YA0000000012
XA0000000013
YA0000000008
YA0000000011
YA0000000007
YA0000000015
YA0000000013
XA0000000003
XA0000000012
XA0000000016
XA0000000015
YA0000000014
XA0000000014
XA0000000017
YA0000000009
YA0000000010
YB0000000022
XB0000000023
XB0000000022
YB0000000021
YB0000000019
YA0000000018
YB0000000020
YA0000000016
YB0000000023
XA0000000020
YA0000000017
XA0000000021
XA0000000025
XB0000000026
XA0000000024
XA0000000011
XA0000000008
YA0000000001
XA0000000010
XA0000000009
YB0000000029
YB0000000026
YB0000000028
YB0000000030
YA0000000002
YB0000000024
YB0000000025
YB0000000027
YA0000000034
YA0000000037
YA0000000031
YA0000000032
YA0000000033
YA0000000035
YA0000000038
YA0000000039
YA0000000036
YA0000000043
XA0000000027
XA0000000029
XA0000000035
YA0000000041
XA0000000034
YA0000000042
YA0000000040
XA0000000030
XA0000000028
XA0000000036
XA0000000033
XA0000000032
XA0000000031
XB0000000045
XB0000000046
XB0000000041
XB0000000047
XB0000000048
XB0000000038
YB0000000050
XB0000000037
XB0000000050
YA0000000045
YB0000000047
YA0000000044
XB0000000042
XB0000000043
XB0000000044
XB0000000049
XB0000000039
XB0000000040
YB0000000048
YB0000000046
YB0000000049

以上