数据库的ID自增字段代码生成器——解决不同数据库自增字段的差异机制
问题:
在设计数据库字段时,有时需要一个int型的id主键,让它能自动递增,每次插入一条数据,它都能够自动增1或者规定的自增数n。对于特定的数据库,要实现这个很简单,比如mysql是用auto_increment,Sql Server是用identity。但是如果在代码中使用和特定数据库有关的特性,那么代码就不能移植,比如把数据库从mysql换成sql server,那么代码就要更改,就不能实现移植扩展。
解决思路:
既然要实现代码的移植扩展,就不能使用和数据库相关的特性,那该怎么办呢?这就是本篇要介绍的ID生成器。
思路是这样的:在数据库中另外建立一张表ID_Main,其包含两个字段:一个字段是要实现ID自增的所属表名(table_name),另一个字段是该表中自增字段对应的现在最大的记录值(id_max)。假设现在要在数据表(user_info)中插入一条数据时,先使表ID_Main中对应字段table_name为的user_info的id_max的值加1,然后再取出该值,就是自动增1后的值。以后每次要插入一条记录,都会先使ID_Main表中对应的id值加1,再返回,其也就是保持了id的自增。
表ID_Main的设计如下:
/*==============================================================*/
/* Table: ID_Main */
/*==============================================================*/
create table ID_Main (
TABLE_NAME VARCHAR2(20) not null,
ID_MAX NUMBER(10) not null,
constraint PK_ ID_Main primary key (TABLE_NAME)
);
comment on table ID_Main is '用于维护各个表中的记录条数';
以下就是代码层,以Java分析为例:
import java.sql.ResultSet; import java.sql.SQLException; import kane.DbUtility; //ID生成器实体类 public class IDGenerator { // 使用单例模式 private static IDGenerator instance = null; private IDGenerator() { } public static synchronized IDGenerator getInstance() { if (instance == null) { instance = new IDGenerator(); } return instance; } // 通过主键维护表ID_Main来获取表中的记录数 public int getTotal(Connection conn, String tableName) throws SQLException { int i = 0; PreparedStatement pstmt = null; ResultSet rs = null; try { String sql = "select ID_MAX from ID_Main where table_name = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, tableName); rs = pstmt.executeQuery(); if (rs.next()) { i = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); throw e; } finally { DbUtility.closeResultSet(rs); DbUtility.closePreparedStatement(pstmt); } return i; } // 将主键维护表ID_Main中的记录数加1,然后再返回该值 public int getNextID(String tableName) { int total = 0; Connection conn = null; PreparedStatement pstmt = null; try { conn = DbUtility.getConnection(); String sql = "update ID_Main set ID_MAX = ID_MAX +1 where table_name=?"; // 开启事务 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql); pstmt.setString(1, tableName); pstmt.executeUpdate(); total = getTotal(conn, tableName); // 执行事务 conn.commit(); } catch (SQLException e) { e.printStackTrace(); // 事务回滚 try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } finally { DbUtility.closePreparedStatement(pstmt); DbUtility.closeConnection(conn); } return total; } }
其中的DbUtility类是连接数据库操作的封装类,其代码详细参见》。代码使用了connection的事务操作,保证事务的一致性,也就是当ID_MAX的值自增1时,应该在相应的表中插入一条记录,以保证自增值一致。如果出错,这应该回滚事务。
通过以上分析与代码示例,就简单的实现了一个IDGenerator即ID生成器,它与数据库的特性隔离,实现了代码层的移植性。