Java实现的Sequence工具 - 2

Sequence是数据库系统按照一定规则自动增加的数字序列。这个序列一般作为代理主键（因为不会重复），没有其他任何意义。 

Sequence是数据库系统的特性，有的数据库实现了Sequence，有的则没有。比如Oracle、DB2、PostgreSQL数据库实现Sequence, 而MySQL、SQL Server、Sybase等数据库没有Sequence。

那么如何给一个不支持Sequence的数据库增加支持呢？

下面将给出Java版本的代码。这个代码还实现了对 Sequence ID的高效缓存，并不是每一次调用都会对数据库进行访问，在高并发环境下性能优异。

1. SequenceId.java

package uuid; public class SequenceId {    public static final long NOT_FOUND = 0;    private static final long STEP = 100;    private final SequenceIdProvider provider;    private final String name;    private final long beginValue;    private long value;     protected SequenceId(SequenceIdProvider provider, String name, long beginValue) {        this.provider = provider;        this.name = name;        this.beginValue = beginValue;        this.value = -1;         if (beginValue <= 0) {            throw new IllegalArgumentException("begin value must be great than zero.");        }    }     public String getName() {        return name;    }     public synchronized long nextVal() {        if (value < 0) {            value = provider.load(name);            if (value <= NOT_FOUND) {                value = beginValue - 1;            }            provider.store(name, value + STEP);        }         value++;         if (value % STEP == 0) {            provider.store(name, value + STEP);        }         return value;    }}

?

2. SequenceIdProvider.java

package uuid; public interface SequenceIdProvider {         public SequenceId create(String name);         public SequenceId create(String name, long begin);         public long load(String name);     public void store(String name, long value);     }

?

3. JdbcSequenceIdProvider.java

?

package uuid; import java.sql.*;import javax.sql.DataSource; public class JdbcSequenceIdProvider implements SequenceIdProvider {     private static final String TABLE_NAME = "_SEQUENCE_";    private final DataSource dataSource;     public JdbcSequenceIdProvider(DataSource dataSource) {        this.dataSource = dataSource;         confirmTableExists();    }     @Override    public SequenceId create(String name) {        return new SequenceId(this, name, 1);    }     @Override    public SequenceId create(String name, long begin) {        return new SequenceId(this, name, begin);    }     private void confirmTableExists() {        Connection conn = null;        try {            conn = dataSource.getConnection();             ResultSet rs = conn.getMetaData().getTables(null, null, TABLE_NAME, null);            boolean found = rs.next();            rs.close();             if (!found) {                Statement stmt = conn.createStatement();                String sql = "create table " + TABLE_NAME + " (name varchar(50) not null, next_val long not null, primary key(name))";                stmt.execute(sql);                stmt.close();            }        } catch (SQLException e) {            throw new RuntimeException(e);        } finally {            close(conn);        }    }     private void close(Connection conn) {        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                throw new RuntimeException(e);            }        }    }     @Override    public long load(String name) {        long value = SequenceId.NOT_FOUND;        Connection conn = null;        try {            conn = dataSource.getConnection();            String sql = "select next_val from " + TABLE_NAME + " where name=?";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, name);            ResultSet rs = ps.executeQuery();            if (rs.next()) {                value = rs.getLong(1);            }            rs.close();            ps.close();        } catch (SQLException e) {            throw new RuntimeException(e);        } finally {            close(conn);        }        return value;    }     @Override    public void store(String name, long value) {        Connection conn = null;        try {            conn = dataSource.getConnection();            String sql = "update " + TABLE_NAME + " set next_val=? where name=?";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setLong(1, value);            ps.setString(2, name);            int updated = ps.executeUpdate();            ps.close();             if (updated == 0) {                sql = "insert into " + TABLE_NAME + " (name, next_val) values (?,?)";                ps = conn.prepareStatement(sql);                ps.setString(1, name);                ps.setLong(2, value);                ps.executeUpdate();                ps.close();            }        } catch (SQLException e) {            throw new RuntimeException(e);        } finally {            close(conn);        }    }}

4. GlobalSequenceIdProvider.java

package uuid; import javax.sql.DataSource; public class GlobalSequenceIdProvider {     private static final SequenceId global;    private static final SequenceId table1;    private static final SequenceId table2;     static {        SequenceIdProvider provider = new JdbcSequenceIdProvider(getDataSource());        global = provider.create("global");        table1 = provider.create("table1");        table2 = provider.create("table2", 1000);    }     public static long nextVal() {        return global.nextVal();    }     public static long nextVal_table1() {        return table1.nextVal();    }     public static long nextVal_table2() {        return table2.nextVal();    }     private static DataSource getDataSource() {        // TODO:        return null;    } }

?