如何通过java代码执行Oracle sql脚本 [英] How to execute Oracle sql script through java code

问题描述

我是Oracle DB的新手，并且正在使用Oracle 11g，我想通过java代码执行Oracle sql脚本。我的SQL脚本可能包含SQL语句（DDL或DML）或PL / SQL块，所以我不想在我的java代码中解析脚本，而是希望一次性执行完整的脚本。希望我的问题很清楚，如果不让我知道，我可以澄清。

I am new to Oracle DB and am using Oracle 11g, I want to execute Oracle sql script through java code. my SQL script may contain SQL statements(DDL or DML) or PL/SQL blocks, so I don't want to parse the script in my java code, but would prefer executing the complete script in one go. Hope my question is clear enough, if not let me know and I can clarify.

iBatis ScriptRunner 在我的场景中有效吗？

Would iBatis ScriptRunner work in my scenario?

推荐答案

一个选项是包含SQLcl并从java中使用它

One option is to include SQLcl and use it from java

步骤1.下载并解压缩SQLcl> http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index。 html

Step 1. Download and unzip SQLcl > http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

步骤2.在类路径中添加所有内容

Step 2. Added everything to the classpath

# all files in sqlcl/lib/*
# adjust to your install ( unzip ) of sqlcl
LIB=sqlcl/lib/
CP=
for  f in $(ls $LIB/*.jar); do
 echo $f
 CP=$CP:$f
done
echo --- $CP ---

步骤3.写一些java

Step 3. Write some java

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","klrice","klrice");

//#get a DBUtil but won't actually use it in this example
DBUtil util = DBUtil.getInstance(conn);

//#create sqlcl
ScriptExecutor sqlcl = new ScriptExecutor(conn);

// Capture the results without this it goes to STDOUT
ByteArrayOutputStream bout = new ByteArrayOutputStream();
BufferedOutputStream buf = new BufferedOutputStream(bout);
sqlcl.setOut(buf);

//#setup the context
ScriptRunnerContext ctx = new ScriptRunnerContext();

//#set the context
sqlcl.setScriptRunnerContext(ctx);
ctx.setBaseConnection(conn);

//# run a whole file
sqlcl.setStmt("@myfile.sql");
sqlcl.run();

//#run 1 statement
sqlcl.setStmt("select * from user_objects");
sqlcl.run();

String results = bout.toString("UTF8");
System.out.println(results);

在这里更新了GitHub回购：
https://github.com/oracle/oracle-db-tools/tree/master/sqlcl / java

Updated a GitHub repo of doing exactly this here: https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/java

这篇关于如何通过java代码执行Oracle sql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！