java oracle 执行语句,如何通过java代码执行Oracle sql脚本

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.

Would iBatis ScriptRunner work in my scenario?

解决方案

One option is to include SQLcl and use it from java

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 ---

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);