Oracle修改有规则的序列

需求/介绍

这是一个简单的依据使用序列的表的行数修改序列的值使其不会在业务发生时首先冲突，通常在对表进行批量操作后需要此功能。

约束

1. 序列名称是有规则的，其中包含有使用此序列的表名称。

2. 序列的值不会影响与业务逻辑的关联关系。

实现机制

代码

package org.ybygjy.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.ybygjy.util.DBUtils;

/**
 * 依据表的行数更新序列，有如下步骤：
 * <p>1、查数据库中的序列
 * <p>2、分析序列名称提取表名称
 * <p>3、查询表行数
 * <p>4、更新序列值
 * <h4>有如下约束：</h4>
 * <p>1、要求序列名称是有规则的
 * @author WangYanCheng
 * @version 2012-5-31
 */
public class AutoIncrementSEQ2 {
    /** 与数据库的连接 */
    private Connection conn;
    /** 前缀 */
    private String[] seqREGPrefix = { "^S_", "^SEQ_" };
    /** 后缀 */
    private String[] seqREGSuffix = { "_SEQ$" };

    /**
     * Constructor
     * @param conn 与数据库的连接
     */
    public AutoIncrementSEQ2(Connection conn) {
        this.conn = conn;
    }

    /**
     * 查表行数
     * @param tableName 表名称
     * @return rtnNums：表的行数/-1：表不存在或其它情况
     */
    public int qryTableNums(String tableName) {
        String tmplSQL = "SELECT COUNT(1) CC FROM ".concat(tableName);
        int rtnNums = -1;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(tmplSQL);
            if (rs.next()) {
                rtnNums = rs.getInt(1);
            }
        } catch (Exception e) {
            rtnNums = -1;
        } finally {
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return rtnNums;
    }

    /**
     * 查序列
     * @return rtnSEQArr/null
     */
    public String[] qrySEQ() {
        String qrySEQ = "SELECT SEQUENCE_NAME,INCREMENT_BY FROM USER_SEQUENCES";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String[] rtnArray = null;
        try {
            pstmt = conn.prepareStatement(qrySEQ);
            rs = pstmt.executeQuery();
            List<String> tmpList = new ArrayList<String>();
            while (rs.next()) {
                tmpList.add(rs.getString(1));
                tmpList.add(rs.getString(2));
            }
            rtnArray = tmpList.toArray(new String[1]);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != pstmt) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return rtnArray;
    }

    /**
     * 分析提取表名称
     * @param seqArray 序列集
     * @return tableNameArray 表名称集合
     */
    public String[] analyseTableName(String[] seqArray) {
        String[] tableNameArray = new String[seqArray.length];
        for (int index = tableNameArray.length; index >= 0; index--) {
            String seqName = seqArray[index];
            tableNameArray[index] = analyseTableName(seqName);
            System.out.println("表：".concat(tableNameArray[index]).concat(":").concat(seqName));
        }
        return tableNameArray;
    }

    /**
     * 重置序列
     * @param seqName 序列名称
     * @param newSeqValue 新序列值
     * @param oldSeqValue <strong>原始序列自增值</strong>
     */
    public void resetSEQ(String seqName, int newSeqValue, int oldSeqValue) {
        String tmpSql1 = "ALTER SEQUENCE @SEQ INCREMENT BY @V";
        String tmpSql2 = "SELECT @SEQ.nextval from dual";
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute(tmpSql1.replaceFirst("@SEQ", seqName).replaceFirst("@V",
                String.valueOf(newSeqValue - 1)));
            stmt.execute(tmpSql2.replaceFirst("@SEQ", seqName));
            stmt.execute(tmpSql1.replaceFirst("@SEQ", seqName).replaceFirst("@V",
                String.valueOf(oldSeqValue)));
        } catch (Exception e) {
            System.err.println("序列重置失败：".concat(seqName).concat("：").concat(String.valueOf(newSeqValue))
                .concat("：").concat(String.valueOf(oldSeqValue)));
        } finally {
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 提取表名称
     * @param seqName 序列名称
     * @return tableName 表名称
     */
    private String analyseTableName(String seqName) {
        String tableName = null;
        for (String tmpStr : seqREGPrefix) {
            tableName = tableName == null ? seqName.replaceFirst(tmpStr, "") : tableName.replaceFirst(
                tmpStr, "");
        }
        for (String tmpStr : seqREGSuffix) {
            tableName = tableName == null ? seqName.replaceFirst(tmpStr, "") : tableName.replaceFirst(
                tmpStr, "");
        }
        return tableName;
    }

    /**
     * 程序调用入口
     */
    public void doWork() {
        String[] seqArray = qrySEQ();
        int index = seqArray.length - 1;
        for (; index >= 0; index--) {
            if (index % 2 == 0) {
                String seqName = seqArray[index];
                String tableName = analyseTableName(seqName);
                int tableNums = qryTableNums(tableName);
                tableNums = tableNums <= 0 ? 1 : tableNums;
                resetSEQ(seqName, tableNums * 2, Integer.parseInt(seqArray[index + 1]));
            }
        }
    }

    /**
     * 测试入口
     * @param args 参数列表
     */
    public static void main(String[] args) {
        String connURL = "jdbc:oracle:thin:NSTCSA4002/6316380@192.168.3.232:1521/NSDEV";
        Connection conn = DBUtils.createConn4Oracle(connURL);
        try {
            AutoIncrementSEQ2 ais2 = new AutoIncrementSEQ2(conn);
            ais2.doWork();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}