From: http://xm-koma.iteye.com/blog/1961793

In the project application, there was the following scenario:

An int-type pipeline number is required to be sent in the interface. Because of the multithreaded mode, there may be duplication if timestamp is used (of course, the probability is very small).

So I think of using an independent self-increasing sequence to solve this problem.

The current database is: mysql

Because mysql and oracle are different and do not support direct sequence, we need to create a table to simulate the function of sequence for the following reasons:

Step 1: Create -- Sequence Management Table

1. DROP TABLE IF EXISTS sequence;  
2. CREATE TABLE sequence (  
3.          name VARCHAR(50) NOT NULL,  
4.          current_value INT NOT NULL,  
5.          increment INT NOT NULL DEFAULT 1,  
6.          PRIMARY KEY (name)  
7. ) ENGINE=InnoDB;  

Step 2: Create a function that takes the current value

1. DROP FUNCTION IF EXISTS currval;  
2. DELIMITER $  
3. CREATE FUNCTION currval (seq_name VARCHAR(50))  
4.          RETURNS INTEGER  
5.          LANGUAGE SQL  
6.          DETERMINISTIC  
7.          CONTAINS SQL  
8.          SQL SECURITY DEFINER  
9.          COMMENT ''  
10. BEGIN  
11.          DECLARE value INTEGER;  
12.          SET value = 0;  
13.          SELECT current_value INTO value  
14.                    FROM sequence  
15.                    WHERE name = seq_name;  
16.          RETURN value;  
17. END  
18. $  
19. DELIMITER ;  

Step 3: Create a function that takes the next value

1. DROP FUNCTION IF EXISTS nextval;  
2. DELIMITER $  
3. CREATE FUNCTION nextval (seq_name VARCHAR(50))  
4.          RETURNS INTEGER  
5.          LANGUAGE SQL  
6.          DETERMINISTIC  
7.          CONTAINS SQL  
8.          SQL SECURITY DEFINER  
9.          COMMENT ''  
10. BEGIN  
11.          UPDATE sequence  
12.                    SET current_value = current_value + increment  
13.                    WHERE name = seq_name;  
14.          RETURN currval(seq_name);  
15. END  
16. $  
17. DELIMITER ;  

Step 4: Create -- update the function of the current value

1. DROP FUNCTION IF EXISTS setval;  
2. DELIMITER $  
3. CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)  
4.          RETURNS INTEGER  
5.          LANGUAGE SQL  
6.          DETERMINISTIC  
7.          CONTAINS SQL  
8.          SQL SECURITY DEFINER  
9.          COMMENT ''  
10. BEGIN  
11.          UPDATE sequence  
12.                    SET current_value = value  
13.                    WHERE name = seq_name;  
14.          RETURN currval(seq_name);  
15. END  
16. $  
17. DELIMITER ;  

Step 5: Testing function functions

When the above four steps are completed, you can use the following data to set the sequence name to be created, as well as to set the initial value and get the current value and the next value.

INSERT INTO sequence VALUES ('TestSeq', 0, 1); - - Add a sequence name and initial value, and self-increment
SELECT SETVAL('TestSeq', 10); - - Sets the initial value of the specified sequence
SELECT CURRVAL('TestSeq'); queries the current value of the specified sequence
SELECT NEXTVAL('TestSeq'); -- Query for the next value of the specified sequence

In java code, sql statements can be created directly to query the next value, which solves the unique problem of pipeline number.

Post out some code (tested)

1. public void testGetSequence() {  
2.     Connection conn = JDBCUtils.getConnection(url, userName, password);  
3.     String sql = "SELECT CURRVAL('TestSeq');";  
4.     PreparedStatement ptmt = null;  
5.     ResultSet rs = null;  
6.     try {  
7.         ptmt = conn.prepareStatement(sql);  
8.         rs = ptmt.executeQuery();  
9.         int count = 0;  
10.         while (rs.next()) {  
11.             count = rs.getInt(1);  
12.         }  
13.         System.out.println(count);  
14.     } catch (SQLException e) {  
15.         e.printStackTrace();  
16.     } finally {  
17.         JDBCUtils.close(rs, ptmt, conn);  
18.     }  
19. }  

ps: In the application, there is also a way to simulate self-increasing sequence with Java code. The idea is to create a table to store sequence, and then use java to call sql statements to query and modify the value of the specified sequence name in the table. This way, Please add synchronized. Specific code is not uploaded here, because the implementation has not been tested.