ORACLE下使用java代码自动生成 序列及触发器

       使用oracle的触发器当作自增主键时，当遇到数据库迁移，常常遇到序列/触发器迁移不完整，序列重置导致主键冲突。或者新创表时，经常性的需要手动添加序列/触发器又显得格外麻烦。所以用java写了个简单的方法，来校验生成 序列及触发器。

        我这里使用的是利用触发器重新生成序列，也可以读取表，生成序列和触发器(最好添加配置表/或者其它方式控制哪些表需要生成)。

1.需要使用的SQL

把user_* 换成 all_* 则是查询全部的数据，添加所有人的条件就可以操作其它用户的表

1.1 查询当前用户所拥有的表 

SELECT * FROM user_tables;

1.2 查询当前用户所拥有的触发器

把 all_triggers 替换成user_triggers 则是查询当前连接下的用户

SELECT *  FROM user_triggers  WHERE STATUS='ENABLED' and TRIGGERING_EVENT='INSERT'

1.3 查询表的主键

SELECT TABLE_NAME,COLUMN_NAME
FROM USER_cons_columns cons
 WHERE  POSITION = '1';
 -- 或者
 SELECT t1.TABLE_NAME,t2.COLUMN_NAME
FROM USER_constraints t1
INNER JOIN USER_cons_columns t2 on t1.TABLE_NAME = t2.TABLE_NAME and t1.owner = t2.owner
--主键约束和第一个位置的
where t1.constraint_type ='P' AND T2.POSITION = '1';

1.4 新增/删除序列

-- 删除序列schemaName 模式名(用户)--可不要 sequence 序列名
  DROP SEQUENCE ${schemaName}.${sequence} 
-- 新增序列 当前序列开始的数字
CREATE SEQUENCE ${schemaName}.${sequence} MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH ${max} NOCACHE NOORDER NOCYCLE

1.5 新增/删除触发器

-- 删除触发器
DROP TRIGGER trigger_name;
-- 添加触发器
create or replace trigger SYS_ICON_TRG  before insert on SYS_ICON for each row 
begin 
	select SYS_ICON_SEQ.nextval into :new.ID from dual; 
end;

1.6 获取表内最大的ID--重建 序列使用

select
 nvl(max(TO_NUMBER(${id})),0)+1 as max
from ${schemaName}.${tableName}

2. java基本逻辑

这里只利用了触发器重新生成序列，其它功能大多都可以通过上述sql实现

service层

@Override
    public void authSequence(String schemaName) {
        //查询全部触发器
        List<Map<String,String>> listMap = authSequenceMapper.getAllTrigger(schemaName);
        for(Map<String,String> m:listMap){
            //截取出表名 和 序列
            //TRIGGER_BODY 内容 begin select syslog_seq.nextval into :new.ID from dual; end;
            //DESCRIPTION 内容 "ZJRMYY"."SYSLOG_TRI" BEFORE INSERT ON "ZJRMYY"."SYS_LOG" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
            String table =  m.get("DESCRIPTION").toUpperCase(Locale.ROOT).replace("\""+schemaName+"\".","").replace("\"","");
            int referencing = table.indexOf("REFERENCING") ;
            if(referencing<0){
                referencing = table.indexOf("FOR EACH ROW ") ;
            }
            //取出表名
            table=table.substring(table.indexOf(" ON ")+4,referencing-1);
            String sequenceSql = m.get("TRIGGER_BODY").toUpperCase(Locale.ROOT).replace("\"","");
            String sequence =sequenceSql.substring(sequenceSql.indexOf("SELECT")+7,sequenceSql.indexOf(".NEXTVAL"));
            //截取出ID
            String id =sequenceSql.substring(sequenceSql.indexOf(":NEW.") + 5, sequenceSql.indexOf("FROM") - 1);
            try {
                Long max = authSequenceMapper.getTableNumMax(schemaName,table,id);
                try {
                    authSequenceMapper.deleteSequence(schemaName,sequence);
                }catch (Exception ignored){
                }
                authSequenceMapper.authSequence(schemaName,sequence,max);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

mapper层--接口层没放

    <insert id="authSequence">
        CREATE SEQUENCE ${schemaName}.${sequence} MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH ${max} NOCACHE NOORDER NOCYCLE
    </insert>

    <delete id="deleteSequence">
        DROP SEQUENCE ${schemaName}.${sequence}
    </delete>

    <select id="getTableNumMax" resultType="java.lang.Long">
        select
            nvl(max(TO_NUMBER(${id})),0)+1 as max
        from ${schemaName}.${tableName}
    </select>

    <select id="getAllTrigger" resultType="java.util.Map">
        SELECT *  FROM all_triggers  WHERE STATUS='ENABLED' and TRIGGERING_EVENT='INSERT' and TABLE_OWNER= #{schemaName}
    </select>