从java运行oracle sql脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/200513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
run oracle sql script from java
提问by Andreas Petersson
i have a bunch of sql scripts that should upgrade the database when the java web application starts up.
我有一堆 sql 脚本，它们应该在 java web 应用程序启动时升级数据库。
i tried using the ibatis scriptrunner, but it fails gloriously when defining triggers, where the ";" character does not mark an end of statement.
我尝试使用 ibatis scriptrunner，但它在定义触发器时失败了，其中“;” 字符不标记语句的结束。
now i have written my own version of a script runner, which basically does the job, but destroys possible formatting and comments, especially in "create or replace view".
现在我已经编写了自己的脚本运行器版本，它基本上可以完成工作，但会破坏可能的格式和注释，尤其是在“创建或替换视图”中。
public class ScriptRunner {
private final DataSource ds;
public ScriptRunner(DataSource ds) {
this.ds = ds;
}
public void run(InputStream sqlStream) throws SQLException, IOException {
sqlStream.reset();
final Statement statement = ds.getConnection().createStatement();
List<String> sqlFragments = createSqlfragments(sqlStream);
for (String toRun : sqlFragments) {
if (toRun.length() > 0) {
statement.execute(toRun);
}
}
}
private static List<String> createSqlfragments(InputStream sqlStream) throws IOException {
BufferedReader br = new BufferedReader(new InputStreamReader(sqlStream));
List<String> ret = new ArrayList<String>();
String line;
StringBuilder script = new StringBuilder();
while ((line = br.readLine()) != null) {
if (line.equals("/")) {
ret.add(removeMultilineComments(script));
script = new StringBuilder();
} else {
//strip comments
final int indexComment = line.indexOf("--");
String lineWithoutComments = (indexComment != -1) ? line.substring(0, indexComment) : line;
script.append(lineWithoutComments).append(" ");
}
}
if (script.length() > 0) {
ret.add(removeMultilineComments(script));
}
return ret;
}
private static String removeMultilineComments(StringBuilder script) {
return script.toString().replaceAll("/\*(.*?)\*/", "").trim();
}
is there a clean way to acieve this? is there something in hibernate i have not seen? or can i pass an inputstream to sqlplus somehow? besides my worries about the formatting, i doubt that this code is error-free, since i have limited knowledge about the pl/sql syntax.
有没有一种干净的方法来实现这一点？有什么我没见过的休眠状态吗？或者我可以以某种方式将输入流传递给 sqlplus 吗？除了我对格式的担忧之外，我怀疑这段代码没有错误，因为我对 pl/sql 语法的了解有限。
回答by anjanb
sqlplus : yes you can. I run sqlplus from within Xemacs(editor) all the time. So, you can run sqlplus in an interpreted mode and then provide it commands and read the output as well.
sqlplus ：是的，你可以。我一直在 Xemacs（编辑器）中运行 sqlplus。因此，您可以在解释模式下运行 sqlplus，然后为其提供命令并读取输出。
Another ways is to download the free java based SQL developer tool from oracle (http://www.oracle.com/technology/software/products/sql/index.html). it comes with a sqlcli.bat utility which is a wrapper over a java program. You might want to use this command line utility to do your work.
另一种方法是从 oracle ( http://www.oracle.com/technology/software/products/sql/index.html)下载免费的基于 Java 的 SQL 开发人员工具。它带有一个 sqlcli.bat 实用程序，它是一个 Java 程序的包装器。您可能希望使用此命令行实用程序来完成您的工作。
summary, I would try running sqlplus in the background and provide it's input and reading its output (like emacs does).
总结，我会尝试在后台运行 sqlplus 并提供它的输入并读取它的输出（就像 emacs 一样）。
回答by tunaranch
If you want to write your own script runner, you can use Spring JDBC's SimpleJdbcTemplate (http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html).
如果要编写自己的脚本运行程序，可以使用 Spring JDBC 的 SimpleJdbcTemplate ( http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html)。
You can, of course, load the scripts as you would any resource in Spring as well.
当然，您也可以像加载 Spring 中的任何资源一样加载脚本。
回答by Dilshod Tadjibaev
You can see other people's implementations. See this resource: "Open Source SQL Clients in Java" http://java-source.net/open-source/sql-clients
你可以看到其他人的实现。请参阅此资源：“Java 中的开源 SQL 客户端” http://java-source.net/open-source/sql-clients
回答by Dilshod Tadjibaev
The iBATIS ScriptRunner has a setDelimiter(String, boolean)method. This allows you to have a string other than ";" to be the separator between SQL statements.
iBATIS ScriptRunner 有一个setDelimiter(String, boolean)方法。这允许您拥有除“;”以外的字符串 作为 SQL 语句之间的分隔符。
In your Oracle SQL script, separate the statements with a "/" (slash).
在您的 Oracle SQL 脚本中，用“/”（斜线）分隔语句。
In your Java code, before calling the runScriptdo a setDelimter("/", false)which will instruct the ScriptRunner to recognize "/" as statement separator.
在您的 Java 代码中，在调用runScriptdo a之前setDelimter("/", false)，它将指示 ScriptRunner 将“/”识别为语句分隔符。
回答by Nitin
Use below solution for your reference , i have tried and tested and running successfully.
使用以下解决方案供您参考，我已经尝试并测试并成功运行。
private static String script_location = "";
private static String file_extension = ".sql";
private static ProcessBuilder processBuilder =null;
public static void main(String[] args) {
try {
File file = new File("C:/Script_folder");
File [] list_files= file.listFiles(new FileFilter() {
public boolean accept(File f) {
if (f.getName().toLowerCase().endsWith(file_extension))
return true;
return false;
}
});
for (int i = 0; i<list_files.length;i++){
script_location = "@" + list_files[i].getAbsolutePath();//ORACLE
processBuilder = new ProcessBuilder("sqlplus", "UserName/Password@database_name", script_location); //ORACLE
//script_location = "-i" + list_files[i].getAbsolutePath();
// processBuilder = new ProcessBuilder("sqlplus", "-Udeep-Pdumbhead-Spc-de-deep\sqlexpress-de_com",script_location);
processBuilder.redirectErrorStream(true);
Process process = processBuilder.start();
BufferedReader in = new BufferedReader(new InputStreamReader(process.getInputStream()));
String currentLine = null;
while ((currentLine = in.readLine()) != null) {
System.out.println(" " + currentLine);
}
}
} catch (IOException e) {
e.printStackTrace();
}catch(Exception ex){
ex.printStackTrace();
}
}
Use this snippet code and try and run.
使用此片段代码并尝试运行。
Thanx to user mentioned the solution in the below link:
感谢用户在以下链接中提到了解决方案：
http://forums.sun.com/thread.jspa?threadID=5413026
http://forums.sun.com/thread.jspa?threadID=5413026
Regards | Nitin
问候 | 尼丁
回答by alf
Had the same problem not so long ago, bumped into your question several times while googling for a solution, so I think I owe you—here are my findings so far:
不久前遇到同样的问题，在谷歌搜索解决方案时多次遇到您的问题，所以我想我欠您 - 这是我迄今为止的发现：
In brief, there are no ready solutions for that: if you open Antor Mavensources, you'll see they are using a simple regexp-based script splitter which is fine for simple scripts, but usually fails on e.g. stored procedures. Same story with iBATIS, c5 db migrations, etc.
简而言之，没有现成的解决方案：如果您打开Ant或Maven源代码，您会看到它们正在使用一个简单的基于 regexp 的脚本拆分器，这对于简单的脚本来说很好，但在例如存储过程中通常会失败。与 iBATIS、c5 数据库迁移等相同的故事。
The problem is, there's more than one language involved: in order to run "SQL Scripts" one must be able to handle (1) SQL, (2) PL/SQL, and (3) sqlplus commands.
问题是，涉及的语言不止一种：为了运行“SQL 脚本”，必须能够处理 (1) SQL、(2) PL/SQL 和 (3) sqlplus 命令。
Running sqlplusitself is the way indeed, but it creates configuration mess, so we tried to avoid this option.
运行sqlplus本身确实是这样，但它会造成配置混乱，所以我们试图避免这个选项。
There are ANTLR parsers for PL/SQL, such as Alexandre Porcelli's one—those are very close, but no one prepared a complete drop-in solution based on those so far.
有用于 PL/SQL 的 ANTLR 解析器，例如Alexandre Porcelli 的解析器——它们非常接近，但到目前为止还没有人准备基于这些解析器的完整解决方案。
We ended up writing yet another ad hoc splitterwhich is aware of some sqlplus commands like /and EXIT— it's still ugly, but works for most of our scripts. (Note though some scripts, e.g., with trailing --comments, won't work—it's still a kludge, not a solution.)
我们最终编写了另一个特别的拆分器，它可以识别一些 sqlplus 命令，例如/和EXIT——它仍然很丑，但适用于我们的大多数脚本。（请注意，尽管有些脚本，例如带有尾随--注释的脚本，将无法工作——它仍然是杂七杂八的，而不是解决方案。）
