CODEWITHSUNDEEP
mysqlmybatissequences
Lucky
Lucky

Reputation: 17365

How do I generate a unique sequential number in java?

I need to create an entity form which has unique identification column in the database and its not a primary key column and I need to display that in the form creation page. I've set this column as UNIQUE and not null. Now whenever I create a new user, employee or any entity I need to generate a sequence number like in this format and display it in the form,

ID_001, ID_002 ... ID_00N and so on.

EMP_001, EMP_002 ... EMP_00N and so on.

and when the three digit sequence number reaches the max limit of 999. The seqence number should generate the number as four digits until 9999 is reached and the employee code will be like EMP_1000. So when I get the last insert id when creating the form, it will not work if more than one user is creating simultaneously and there would be a conflict. I thought about creating a new table like sequence_generator. Where I store key-value pair of the the entity-last insert id. So whenver next insert happens I can read from this table and increment by 1 for new sequence numbers.

So How do I best implement this sequence generating which is also Unique in Java/MySql/Mybatis/Spring?

Upvotes: 0

Views: 1894

Answers (1)

Rafa
Rafa

Reputation: 2027

I would create my own sequencing implementation using triggers. I am not very familiar with mysql. So, take my examples as a pseudo-code. Your trigger would look like:

  1. Create a table with no auto-increment. Example:
CREATE TABLE EMPLOYEE (
    ID CHAR(30), NAME CHAR(30)
)
  1. Create a trigger with the logic to auto-increment your columns. Similar to:
CREATE TRIGGER EMPLOYEE_SEQUENCE BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
    SET @PREPENDED_ZEROS = '';
    SET @ID_AS_NUMBER = CAST(SUBSTRING(ID,3) AS INT) + 1;
    IF @ID_AS_NUMBER < 10 THEN
       SET @PREPENDED_ZEROS = '00';
    ELSEIF @ID_AS_NUMBER < 100 THEN
       SET @PREPENDED_ZEROS = '0';
    END IF;
    SET NEW.ID = 'EMP_' || @PREPENDED_ZEROS || @ID_AS_NUMBER;
END;

Upvotes: 1

Related Questions