JDBC执行Oracle的Sql脚本注意细节

 
-- Oracle脚本格式      '/' 与sqlserver中的 go 意义一样
 
declare
    tablecount int;
begin
    select count(1) into tablecount from user_tables where table_name = upper('DWD_AA_TABLE');
    if tablecount = 1 then
        execute immediate 'drop table DWD_AA_TABLE';
    end if;
end;
/
create table DWD_AA_TABLE (
	fk_voucher    varchar2(64) null,
	fk_date    date null,
	datasource    varchar2(200) null
	--iid    int null,
);
/
create index DWD_AA_TABLE_datasource on DWD_AA_TABLE (datasource asc);
/
create index DWD_AA_TABLE_fk_org on DWD_AA_TABLE (fk_org asc);
/
create index gl_accyearbegin_fk_accounttime on DWD_AA_TABLE (fk_accounttime asc);

下面是代码

private void oraclJDBC() {
 
 
    String url = "jdbc:oracle:thin:@10.16.1.23:1521:orcl";
    String user = "aaa";
    String pas = "";
 
    File file_ = null;
    try (Connection conn = DriverManager.getConnection(url, user, passwd);) {
        conn.setAutoCommit(false);
        Statement statement = null;
        statement = conn.createStatement();
 
        String sql_txt = "";//从sql脚本中读取到了oracle脚本
        List sqls = getSqls(sql_txt);//重点这里***
        for (String s : sqls) {
            statement.executeUpdate(s);
        }
 
        conn.commit();
    } catch (Exception ex) {
        //conn.rollback();
        ex.printStackTrace();
        throw new BDException("执行异常文件:" + file_.getName() + " 异常信息:" + ex.getMessage());
    }
 
}
 
private List getSqls(String str) {
    List sqls = new ArrayList<>();
    //oracle脚本中要以 '/' 分隔
    String[] sql_s = str.split("/");
    for (String sql_ : sql_s) {
        String sql = sql_.trim();
        if (!sql.endsWith("end;")) {//end结尾的必须有;  而非end结尾的不能有;
            sql = sql.substring(0, sql.length() - 1);
        }
        sqls.add(sql);
    }
    if (CollectionUtils.isEmpty(sqls)) {
        throw new BDException("当前脚本无sql");
    }
    return sqls;
}