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）。

    具体实现代码如下：

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

    }

}