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

?