java代码批量生成 数据库 oracle 序列 和触发器，并写入文件

 创建一个 db.setting 文件，放入项目的resources 目录

# CrateSenquence 批量创建序列需要的配置
url = jdbc:oracle:thin:@127.0.0.1:1521/orcl
user = XXX
pass = XXX
## 可选配置
# 是否在日志中显示执行的SQL
showSql = true
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = true
# 打印SQL的日志等级，默认debug，可以是info、warn、error
sqlLevel = debug

使用 hutool 链接操作数据库 

import cn.hutool.core.io.file.FileWriter;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.db.sql.SqlExecutor;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @version 1.0
 * 序列
 * DROP SEQUENCE "WQJCXXPT"."SEQ_TABLE_NAME";
 * CREATE SEQUENCE "WQJCXXPT"."SEQ_TABLE_NAME" MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 CACHE 10;
 * <p>
 * 触发器
 * CREATE TRIGGER "WQJCXXPT"."SEQ_TABLE_NAME" BEFORE INSERT ON "WQJCXXPT"."TABLE_NAME" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW WHEN (new.sid is null)
 * begin
 * select SEQ_TABLE_NAME.nextval into :new.sid from dual;
 * if :new.PXBJ is null then
 * select SEQ_TABLE_NAME.CURRVAL into :new.PXBJ from dual;
 * end if;
 * end;
 * /
 * @description: 批量生产 oracle 序列 和触发器
 * @date 2021/2/7
 */
public class CrateSenquence {
    private static String DATABASE_NAME = "DATABASE_NAME";

    public static void main(String[] args) throws SQLException {
        List<Entity> query = Db.use().query("SELECT TABLE_NAME from all_tables where OWNER = '" + DATABASE_NAME + "' ");
        List<String> tables = new ArrayList<String>();
        for (Entity entity : query) {
            String tname = entity.get("TABLE_NAME").toString();
            tables.add(tname);
            System.out.println("========================================>>>" + tname);
            crateSZEQZUENCEZ(tname);
        }
        for (int i = 0; i < tables.size(); i++) {
            System.out.println(i + "--------" + tables.get(i));
            try {
                creteTRIGGER(tables.get(i));
            } catch (Exception e) {
                System.out.println(e);
            }
        }

    }

    //创建触发器
    private static void creteTRIGGER(String tname) {
// FileWriter wr= new FileWriter("sequense.sql");
        Connection conn = null;
        try {
            DataSource ds = DSFactory.get();
            conn = ds.getConnection();
            List<Entity> query2 = Db.use().query("SELECT PXBJ from " + DATABASE_NAME + "." + tname);
            String sql = "CREATE TRIGGER " + DATABASE_NAME + ".SEQ_" + tname + " BEFORE INSERT ON " + DATABASE_NAME + "." + tname + " REFERENCING OLD AS \"OLD\" NEW AS \"NEW\" FOR EACH ROW WHEN (new.sid is null) \n" +
                    "begin\n" +
                    " select SEQ_" + tname + ".nextval into :new.sid from dual ;\n" +
                    " if :new.PXBJ is null then\n" +
                    " select SEQ_" + tname + ".CURRVAL into :new.PXBJ from dual ; \n" +
                    " end if;\n" +
                    "end;\n";
// wr.append(sql+"\n");
            SqlExecutor.executeForGeneratedKey(conn, sql, " ", " ", " ");
            System.out.println("-------------->" + sql);
            Db.use().execute(sql, " ", " ", " ");
        } catch (Exception e) {
            System.out.println(e);
            try {
                String sql = "CREATE TRIGGER " + DATABASE_NAME + ".SEQ_" + tname + " BEFORE INSERT ON " + DATABASE_NAME + "." + tname + " REFERENCING OLD AS \"OLD\" NEW AS \"NEW\" FOR EACH ROW WHEN (new.sid is null) \n" +
                        "begin\n" +
                        " select SEQ_" + tname + ".nextval into :new.sid from dual ;\n" +
                        "end;\n";
                SqlExecutor.executeForGeneratedKey(conn, sql, " ", " ", " ");
                System.out.println("-------------->" + sql);
                Db.use().query(sql, "");
// wr.append(sql+"\n");
            } catch (Exception e2) {
                System.out.println(e2);
            }
        }
    }

    //创建序列
    private static void crateSZEQZUENCEZ(String tname) {
        try {
            Db.use().execute(" CREATE SEQUENCE " + DATABASE_NAME + ".\"SEQ_" + tname + "\"\n" +
                    " MINVALUE 1\n" +
                    " MAXVALUE 99999999999\n" +
                    " INCREMENT BY 1\n" +
                    " CACHE 10");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

}