Introduction

Since it was necessary to prepare a numbering mechanism in a MySQL + Java environment, I made it with reference to the following article.

Numbering table in MySQL Sequence function realized by MySQL Handling numbering table using LAST_INSERT_ID

I wish there was an Oracle sequence in MySQL too.

environment

MySQL 5.7 Java 8

What i did

Prepare a table for numbering. × 2
Prepare a numbering procedure. × 2
Verify with Java.

Numbering table

`Table 1`



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

`Table 2`



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

Numbering procedure

`Procedure 1`



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

`Procedure 2`



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

Validation Java code

I implemented the verification code from the following viewpoints.

No problem with multithreading
The connection does not die even if you use the procedure
Multiple numbering tables, numbering procedures properly? Being numbered


    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);
        }
    }

inspection result

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

that's all

[MySQL + Java] Numbering procedure