Java实现的Sequence工具 - 1

Java实现的Sequence工具

 

Sequence是数据库应用中很重要的一个特性，可是有些数据库没有这个特性，比如很流行的开源数据库MySQL就没有Sequence，利用自增主键感觉很不爽，每个表的ID都要设置，通用性也不好。因此考虑为这些不支持Sequence的数据做一个Sequence工具。自动为整个系统提供主键生成策略。

 

下面是一个Sequence实现，以数据库MySQL为平台。源代码如下：

 

一、Sequence工具类

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; 
    } 
}

 

二、Sequence载体

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

 

五、Sequence表的代码（for MySQL5）

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