SQL编号生成器

在编写基于数据库的应用时，有一个常见的需求：某一张表有个编码字段，需要按照一定的规则生成，例如：某订单编号的生成规则是：部门编号+yyyyMMdd+四位流水号，中间部分代表当前的年月日。难点就是如何生成流水号，并且能够保证在多用户并发的情况下，保证流水号不重复。
    得到流水号的方法比较简单：select max(theColumn) from theTable where theColumn like “BBXXXXXXX%”，即在该表中查询具有相同前缀（编码流水号之前的部分）的编码最大值，然后再将流水号部分+1就可以得到新的编码了。为了保证流水号不重复，我们需要锁定数据，但是如果锁定该表的话，开销太大，针对该表的增、删、改操作都不能进行。这里采用一个小技巧：我们单独建立一张新的表格，SQL语句如下：
create table LOCK_TABLE (
   TABLE_NAME  VARCHAR(20) not null
   constraint PK_ LOCK_TABLE  primary key  (TABLE_NAME)
)
这个表只有一个字段，数据即需要我们生成编号的表名。我们在计算某个表的当前最大流水号之前，首先锁定LOCK_TABLE表中数据为该表名的那条数据（具体方法后文有介绍），然后再执行上面的select max(**) …… 操作，得到新的编码。请注意：上面的锁定LOCK_TABLE表中一行数据；查询最大编码是在一个事务中完成的。
    锁定LOCK_TABLE表中数据为该表名的那条数据的方法：就是在普通的SQL语句中加入锁定的关键字，对于Oracle来说是： for update ；对于SQLServer来说是 with （holdlock）。

    具体实现代码如下：

Java代码

1. import java.sql.Connection;   
2. import java.sql.PreparedStatement;   
3. import java.sql.ResultSet;   
4. import java.sql.SQLException;   
5. import java.sql.Statement;   
6. import org.apache.log4j.Logger;   
7.   
8. public class KeyGenerator{   
9.   
10.     private Logger logger = Logger.getLogger(KeyGenerator.class);   
11.     private Connection conn;   
12.     private String tableName;   
13.     private String columnName;   
14.     private String head;   
15.     private int numCount;   
16.     private String lockString;   
17.   
18.     /**  
19.      * @param tableName - 表名  
20.      * @param columnName - code 对应 字段名  
21.      * @param head - 缺少最后流水号的 code 前端  
22.      * @param numCount - 最后流水号数字的个数  
23.      * @param lockString - 对应于 LOCK_TABLE表中的字符串名  
24.      */  
25.        
26.     // example used in xxxCreateBO   
27.     // KeyGenerator keyGenerator= new KeyGenerator(getSession().connection(),"TS_GXCPRD","APP_ID","2005210204A",4,"TS_GXCPRD");   
28.     // String key=keyGenerator.getKey();   
29.        
30.        
31.     public KeyGenerator(Connection conn,String tableName,String columnName,String head,int numCount,String lockString) {   
32.         this.conn=conn;   
33.         this.tableName=tableName;   
34.         this.columnName=columnName;   
35.         this.head=head;   
36.         this.numCount=numCount;   
37.         this.lockString=lockString;   
38.            
39.     }   
40.        
41.     private String computeNewCode(String maxCode,String head,int numCount){   
42.            
43.         String newCode="";   
44.         if(maxCode!=null){   
45.             int i=head.length();   
46.             int j=maxCode.length();   
47.             int k=j-i;   
48.                
49.             String numPart=maxCode.substring(i,j);   
50.             int theInt= new Integer(numPart).intValue();   
51.             theInt++;   
52.             String numString =new Integer(theInt).toString();   
53.             k=k-numString.length();   
54.             String temp0="";   
55.             for(;k>0;k--){   
56.                 temp0=temp0+"0";   
57.             }   
58.             numString=temp0+numString;   
59.             newCode=head+numString;   
60.         }   
61.         else{   
62.             String temp0="";   
63.             for(int k=numCount-1;k>0;k--){   
64.                 temp0=temp0+"0";   
65.             }   
66.             newCode=head+temp0+"1";   
67.         }   
68.         return newCode;   
69.     }   
70.   
71.   
72.     public String getKey() {   
73.   
74.         String oracleLockStr=" for update ";   
75.         String sqlServerLockStr=" with (holdlock) ";   
76.            
77.         String sql1 = " SELECT * FROM " + "LOCK_TABLE ";   
78.         // 用来锁定表中记录   
79.         // 如果是SQLServer数据库用 with (holdlock)，放在where条件前面   
80.         // SQLServer 例子：select * from LOCK_TABLE with (holdlock) where TABLE_NAME like 'aaa%';   
81.         // 如果是oracle数据库用 for update,放在where条件后面   
82.         // Oracle 例子：   select * from LOCK_TABLE where TABLE_NAME like 'aaa%' for update;   
83.         sql1 = sql1+sqlServerLockStr;   
84.         sql1 = sql1+" WHERE " + "TABLE_NAME" + " LIKE '" + lockString.trim() + "'";   
85.            
86.         String sql2 = " SELECT MAX(" + columnName+ ") AS A FROM "+ tableName ;   
87.         sql2 = sql2+" WHERE " + columnName + " LIKE '" + head.trim() + "%' ";   
88.            
89.         PreparedStatement pstm1 = null;   
90.         PreparedStatement pstm2 = null;   
91.         Statement stmt = null;   
92.         ResultSet rset1 = null;   
93.         String maxCode="";   
94.         String newCode="";   
95.            
96.         try {   
97.             pstm1 = conn.prepareStatement(sql1);   
98.             pstm1.executeQuery();   
99.             pstm2 = conn.prepareStatement(sql2);   
100.             rset1 = pstm2.executeQuery();   
101.             rset1.next();   
102.             maxCode=rset1.getString("A");   
103.                
104.             newCode=computeNewCode(maxCode,head,numCount);   
105.                
106.             logger.info("newCode:"+newCode);   
107.             System.out.println("newCode:"+newCode);   
108.             return newCode;   
109.   
110.         }  catch (Exception e) {   
111.             System.out.println(e);   
112.             e.printStackTrace();   
113.             return null;   
114.   
115.         }   
116.         finally{   
117.             try {   
118.                 if (rset1 != null)   
119.                     rset1.close();   
120.             } catch (SQLException e1) {   
121.             }   
122.   
123.             try {   
124.                 if (pstm1 != null)   
125.                     pstm1.close();   
126.                 if (pstm2 != null)   
127.                     pstm2.close();   
128.                 if (stmt != null)   
129.                     stmt.close();   
130.             } catch (SQLException e1) {   
131.             }   
132.         }   
133.   
134.     }   
135.   
136. }