通用流水号

在编写基于数据库的应用时，有一个常见的需求：某一张表有个编码字段，需要按照一定的规则生成，例如：某订单编号的生成规则是：部门编号+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代码 import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class KeyGenerator{ private Logger logger = Logger.getLogger(KeyGenerator.class); private Connection conn; private String tableName; private String columnName; private String head; private int numCount; private String lockString; /** * @param tableName - 表名 * @param columnName - code 对应 字段名 * @param head - 缺少最后流水号的 code 前端 * @param numCount - 最后流水号数字的个数 * @param lockString - 对应于 LOCK_TABLE表中的字符串名 */ // example used in xxxCreateBO // KeyGenerator keyGenerator= new KeyGenerator(getSession().connection(),"TS_GXCPRD","APP_ID","2005210204A",4,"TS_GXCPRD"); // String key=keyGenerator.getKey(); public KeyGenerator(Connection conn,String tableName,String columnName,String head,int numCount,String lockString) { this.conn=conn; this.tableName=tableName; this.columnName=columnName; this.head=head; this.numCount=numCount; this.lockString=lockString; } private String computeNewCode(String maxCode,String head,int numCount){ String newCode=""; if(maxCode!=null){ int i=head.length(); int j=maxCode.length(); int k=j-i; String numPart=maxCode.substring(i,j); int theInt= new Integer(numPart).intValue(); theInt++; String numString =new Integer(theInt).toString(); k=k-numString.length(); String temp0=""; for(;k>0;k--){ temp0=temp0+"0"; } numString=temp0+numString; newCode=head+numString; } else{ String temp0=""; for(int k=numCount-1;k>0;k--){ temp0=temp0+"0"; } newCode=head+temp0+"1"; } return newCode; } public String getKey() { String oracleLockStr=" for update "; String sqlServerLockStr=" with (holdlock) "; String sql1 = " SELECT * FROM " + "LOCK_TABLE "; // 用来锁定表中记录 // 如果是SQLServer数据库用 with (holdlock)，放在where条件前面 // SQLServer 例子：select * from LOCK_TABLE with (holdlock) where TABLE_NAME like 'aaa%'; // 如果是oracle数据库用 for update,放在where条件后面 // Oracle 例子： select * from LOCK_TABLE where TABLE_NAME like 'aaa%' for update; sql1 = sql1+sqlServerLockStr; sql1 = sql1+" WHERE " + "TABLE_NAME" + " LIKE '" + lockString.trim() + "'"; String sql2 = " SELECT MAX(" + columnName+ ") AS A FROM "+ tableName ; sql2 = sql2+" WHERE " + columnName + " LIKE '" + head.trim() + "%' "; PreparedStatement pstm1 = null; PreparedStatement pstm2 = null; Statement stmt = null; ResultSet rset1 = null; String maxCode=""; String newCode=""; try { pstm1 = conn.prepareStatement(sql1); pstm1.executeQuery(); pstm2 = conn.prepareStatement(sql2); rset1 = pstm2.executeQuery(); rset1.next(); maxCode=rset1.getString("A"); newCode=computeNewCode(maxCode,head,numCount); logger.info("newCode:"+newCode); System.out.println("newCode:"+newCode); return newCode; } catch (Exception e) { System.out.println(e); e.printStackTrace(); return null; } finally{ try { if (rset1 != null) rset1.close(); } catch (SQLException e1) { } try { if (pstm1 != null) pstm1.close(); if (pstm2 != null) pstm2.close(); if (stmt != null) stmt.close(); } catch (SQLException e1) { } } } } import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class KeyGenerator{ private Logger logger = Logger.getLogger(KeyGenerator.class); private Connection conn; private String tableName; private String columnName; private String head; private int numCount; private String lockString; /** * @param tableName - 表名 * @param columnName - code 对应 字段名 * @param head - 缺少最后流水号的 code 前端 * @param numCount - 最后流水号数字的个数 * @param lockString - 对应于 LOCK_TABLE表中的字符串名 */ // example used in xxxCreateBO // KeyGenerator keyGenerator= new KeyGenerator(getSession().connection(),"TS_GXCPRD","APP_ID","2005210204A",4,"TS_GXCPRD"); // String key=keyGenerator.getKey(); public KeyGenerator(Connection conn,String tableName,String columnName,String head,int numCount,String lockString) { this.conn=conn; this.tableName=tableName; this.columnName=columnName; this.head=head; this.numCount=numCount; this.lockString=lockString; } private String computeNewCode(String maxCode,String head,int numCount){ String newCode=""; if(maxCode!=null){ int i=head.length(); int j=maxCode.length(); int k=j-i; String numPart=maxCode.substring(i,j); int theInt= new Integer(numPart).intValue(); theInt++; String numString =new Integer(theInt).toString(); k=k-numString.length(); String temp0=""; for(;k>0;k--){ temp0=temp0+"0"; } numString=temp0+numString; newCode=head+numString; } else{ String temp0=""; for(int k=numCount-1;k>0;k--){ temp0=temp0+"0"; } newCode=head+temp0+"1"; } return newCode; } public String getKey() { String oracleLockStr=" for update "; String sqlServerLockStr=" with (holdlock) "; String sql1 = " SELECT * FROM " + "LOCK_TABLE "; // 用来锁定表中记录 // 如果是SQLServer数据库用 with (holdlock)，放在where条件前面 // SQLServer 例子：select * from LOCK_TABLE with (holdlock) where TABLE_NAME like 'aaa%'; // 如果是oracle数据库用 for update,放在where条件后面 // Oracle 例子： select * from LOCK_TABLE where TABLE_NAME like 'aaa%' for update; sql1 = sql1+sqlServerLockStr; sql1 = sql1+" WHERE " + "TABLE_NAME" + " LIKE '" + lockString.trim() + "'"; String sql2 = " SELECT MAX(" + columnName+ ") AS A FROM "+ tableName ; sql2 = sql2+" WHERE " + columnName + " LIKE '" + head.trim() + "%' "; PreparedStatement pstm1 = null; PreparedStatement pstm2 = null; Statement stmt = null; ResultSet rset1 = null; String maxCode=""; String newCode=""; try { pstm1 = conn.prepareStatement(sql1); pstm1.executeQuery(); pstm2 = conn.prepareStatement(sql2); rset1 = pstm2.executeQuery(); rset1.next(); maxCode=rset1.getString("A"); newCode=computeNewCode(maxCode,head,numCount); logger.info("newCode:"+newCode); System.out.println("newCode:"+newCode); return newCode; } catch (Exception e) { System.out.println(e); e.printStackTrace(); return null; } finally{ try { if (rset1 != null) rset1.close(); } catch (SQLException e1) { } try { if (pstm1 != null) pstm1.close(); if (pstm2 != null) pstm2.close(); if (stmt != null) stmt.close(); } catch (SQLException e1) { } } } }