package sequence; 

import java.util.HashMap; 
import java.util.Map; 
import java.sql.SQLException; 

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-4-2 15:21:30<br> 
* <b>Note</b>: Java实现的Sequence工具 
*/ 
public  class SequenceUtils { 
     private  static SequenceUtils _instance =  new SequenceUtils(); 
     private Map<String, KeyInfo> keyMap =  new HashMap<String, KeyInfo>(20);  //Sequence载体容器 
     private  static  final  int POOL_SIZE = 10;       //Sequence值缓存大小 

     /** 
     * 禁止外部实例化 
     */ 
     private SequenceUtils() { 
    } 

     /** 
     * 获取SequenceUtils的单例对象 
     * @return SequenceUtils的单例对象 
     */ 
     public  static SequenceUtils getInstance() { 
         return _instance; 
    } 

     /** 
     * 获取下一个Sequence键值 
     * @param keyName Sequence名称 
     * @return 下一个Sequence键值 
     */ 
     public  synchronized  long getNextKeyValue(String keyName) { 
        KeyInfo keyInfo =  null; 
        Long keyObject =  null; 
         try { 
             if (keyMap.containsKey(keyName)) { 
                keyInfo = keyMap.get(keyName); 
            }  else { 
                keyInfo =  new KeyInfo(keyName, POOL_SIZE); 
                keyMap.put(keyName, keyInfo); 
            } 
            keyObject = keyInfo.getNextKey(); 
        }  catch (SQLException e) { 
            e.printStackTrace(); 
        } 
         return keyObject; 
    } 
}

package sequence; 

import java.sql.*; 

/** 
* Created by IntelliJ IDEA.<br> 
* <b>User</b>: leizhimin<br> 
* <b>Date</b>: 2008-4-2 15:24:52<br> 
* <b>Note</b>: Sequence载体 
*/ 
public  class KeyInfo { 
     private  long maxKey;         //当前Sequence载体的最大值 
     private  long minKey;         //当前Sequence载体的最小值 
     private  long nextKey;        //下一个Sequence值 
     private  int poolSize;        //Sequence值缓存大小 
     private String keyName;      //Sequence的名称 
     private  static  final String sql_update =  "UPDATE KEYTABLE SET KEYVALUE = KEYVALUE + ? WHERE KEYNAME = ?"; 
     private  static  final String sql_query =  "SELECT KEYVALUE FROM KEYTABLE WHERE KEYNAME = ?"; 

     public KeyInfo(String keyName,  int poolSize)  throws SQLException { 
         this.poolSize = poolSize; 
         this.keyName = keyName; 
        retrieveFromDB(); 
    } 

     public String getKeyName() { 
         return keyName; 
    } 

     public  long getMaxKey() { 
         return maxKey; 
    } 

     public  long getMinKey() { 
         return minKey; 
    } 

     public  int getPoolSize() { 
         return poolSize; 
    } 

     /** 
     * 获取下一个Sequence值 
     * 
     * @return 下一个Sequence值 
     * @throws SQLException 
     */ 
     public  synchronized  long getNextKey()  throws SQLException { 
         if (nextKey > maxKey) { 
            retrieveFromDB(); 
        } 
         return nextKey++; 
    } 

     /** 
     * 执行Sequence表信息初始化和更新工作 
     * 
     * @throws SQLException 
     */ 
     private  void retrieveFromDB()  throws SQLException { 
        System.out.println(""); 
        Connection conn = DBUtils.makeConnection(); 
         //查询数据库 
        PreparedStatement pstmt_query = conn.prepareStatement(sql_query); 
        pstmt_query.setString(1, keyName); 
        ResultSet rs = pstmt_query.executeQuery(); 
         if (rs.next()) { 
            maxKey = rs.getLong(1) + poolSize; 
            minKey = maxKey - poolSize + 1; 
            nextKey = minKey; 
            rs.close(); 
            pstmt_query.close(); 
        }  else { 
            System.out.println( "执行Sequence数据库初始化工作！"); 
            String init_sql =  "INSERT INTO KEYTABLE(KEYNAME,KEYVALUE) VALUES('" + keyName +  "',10000 + " + poolSize +  ")"; 
            Statement stmt = conn.createStatement(); 
            stmt.executeUpdate(init_sql); 
            maxKey = 10000 + poolSize; 
            minKey = maxKey - poolSize + 1; 
            nextKey = minKey; 
            stmt.close(); 
             return; 
        } 

         //更新数据库 
        conn.setAutoCommit( false); 
        System.out.println( "更新Sequence最大值！"); 
        PreparedStatement pstmt_up = conn.prepareStatement(sql_update); 
        pstmt_up.setLong(1, poolSize); 
        pstmt_up.setString(2, keyName); 
        pstmt_up.executeUpdate(); 
        pstmt_up.close(); 
        conn.commit(); 

        rs.close(); 
        pstmt_query.close(); 
        conn.close(); 
    } 
}

package sequence; 

import java.sql.*; 

/** 
* 简单的数据连接工具 
* File: DBUtils.java 
* User: leizhimin 
* Date: 2008-3-18 15:19:12 
*/ 
public  class DBUtils { 
     public  static  final String url =  "jdbc:mysql://127.0.0.1:3306/testdb"; 
    public static final String username = "root"; 
    public static final String password = "leizhimin"; 
    public static final String driverClassName = "com.mysql.jdbc.Driver"; 

    /** 
     * 获取数据库连接Connection 
     * 
     * @return 数据库连接Connection 
     */ 
    public static Connection makeConnection() { 
        Connection conn = null; 
        try { 
            Class.forName(driverClassName); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
        try { 
            conn = DriverManager.getConnection(url, username, password); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return conn; 
    } 

    public static void main(String args[]) { 
        testConnection(); 
    } 

    /** 
     * 测试连接方法 
     */ 
    public static void testConnection() { 
        Connection conn = makeConnection(); 
        try { 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT * FROM mysql.user"); 
            while (rs.next()) { 
                String s1 = rs.getString(1); 
                String s2 = rs.getString(2); 
                System.out.println(s1 + "\t" + s2); 
            } 
            rs.close(); 
            stmt.close(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
}

package sequence; 

/** 
* Created by IntelliJ IDEA. 
* User: leizhimin 
* Date: 2008-4-2 15:31:30 
* Company: LavaSoft([url]http://lavasoft.blog.51cto.com[/url]) 
* Sequence测试(客户端) 
*/ 
public  class TestSequence { 
     /** 
     * 测试入口 
     * @param args 
     */ 
     public  static  void main(String args[]) { 
        test(); 
    } 

     /** 
     * 测试Sequence方法 
     */ 
     public  static  void test() { 
        System.out.println( "----------test()----------"); 
         for ( int i = 0; i < 20; i++) { 
             long x = SequenceUtils.getInstance().getNextKeyValue( "sdaf"); 
            System.out.println(x); 
        } 
    } 
}

-- SQL for MySQL5 
-- [url]http://lavasoft.blog.51cto.com[/url] 
-- 2008年4月3日 

-- 创建数据库testdb 
create database if not exists testdb character set gbk collate gbk_chinese_ci; 

-- 创建Sequence表 
DROP TABLE IF EXISTS keytable; 
CREATE TABLE keytable ( 
  KEYNAME varchar(24) NOT NULL COMMENT 'Sequence名称', 
  KEYVALUE bigint(20) DEFAULT '10000' COMMENT 'Sequence最大值', 
  PRIMARY KEY (KEYNAME) 
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

----------test()---------- 

更新Sequence最大值！ 
10061 
10062 
10063 
10064 
10065 
10066 
10067 
10068 
10069 
10070 

更新Sequence最大值！ 
10071 
10072 
10073 
10074 
10075 
10076 
10077 
10078 
10079 
10080 

Process finished with exit code 0