通过 Java 执行 .sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5801838/
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
Executing a .sql file through Java
提问by Mahesh
I have a sql script file, i need to execute commands present in that through java. I searched in internet regarding the same, i got some code defining parsers to split the SQL statements and executing that. But none of them worked for my sql script file.Because my script file contains both create statements and alter statements without semicolon at the end[Instead it has GO]Can anybody suggest a solution to execute the script file? Thanks, Mahesh
我有一个 sql 脚本文件，我需要通过 java 执行其中存在的命令。我在互联网上搜索了同样的内容，我得到了一些定义解析器的代码来拆分 SQL 语句并执行它。但是它们都不适用于我的 sql 脚本文件。因为我的脚本文件包含 create 语句和 alter 语句，末尾没有分号 [而是它有 GO] 任何人都可以提出执行脚本文件的解决方案吗？谢谢，马赫什
采纳答案by Bozhidar Batsov
For simple scripts I generally use this class from ibatis - ScriptRunner. Alternative you can spawn a new db client process from Java and feed in the script you wan't execute. This will work for all scripts, as simple solutions like ScriptRunner don't work well when the delimiters in the sql files get changed for instance.
对于简单的脚本，我通常使用 ibatis- ScriptRunner 中的这个类。或者，您可以从 Java 生成一个新的数据库客户端进程，并输入您不想执行的脚本。这将适用于所有脚本，因为例如 ScriptRunner 之类的简单解决方案在 sql 文件中的分隔符发生更改时无法正常工作。
Here's an example how to feed the sql as a string to a spawed db client process:
这是一个如何将 sql 作为字符串提供给生成的 db 客户端进程的示例：
private void runSql(String pSql) {
String tCommand = "mysql -u " + username + (password != null ? " -p" + password : "") + " " + dbName;
System.out.println(tCommand);
try {
Process tProcess = Runtime.getRuntime().exec(tCommand);
OutputStream tOutputStream = tProcess.getOutputStream();
Writer w = new OutputStreamWriter(tOutputStream);
System.out.println(pSql);
w.write(pSql);
w.flush();
Scanner in = new Scanner(tProcess.getErrorStream());
String errorMessage = "";
while (in.hasNext()) {
errorMessage += in.next() + " ";
}
if (errorMessage.length() > 0) {
System.out.println(errorMessage);
throw new ClientSqlExecutionException(errorMessage);
}
} catch (IOException e) {
e.printStackTrace();
}
}
回答by Jonas Kongslund
Use this slightly modified version of the com.ibatis.common.jdbc.ScriptRunner classwhich is fully self contained, i.e. you do not need to have any third party jar dependencies.
使用完全自包含的 com.ibatis.common.jdbc.ScriptRunner 类的这个稍微修改的版本，即您不需要任何第三方 jar 依赖项。
It is possible to change the delimiter from ;to GO. I think that should do the trick.
可以将分隔符从 更改;为GO。我认为这应该可以解决问题。
Here is an example:
下面是一个例子：
Reader reader = new BufferedReader(*** Your FileReader instance ***);
try
{
ScriptRunner runner = new ScriptRunner(connection, false, true);
runner.setDelimiter("GO", true);
runner.runScript(reader);
}
finally
{
reader.close();
}
回答by southpaw
Apache Ant SQL Task worked for me. https://ant.apache.org/manual/Tasks/sql.html
Apache Ant SQL 任务对我有用。 https://ant.apache.org/manual/Tasks/sql.html
回答by Pih
The simplest way is to simply get the statements and check if they need they semi-column at the end: (this is an example and only works if it is a statement by line:
最简单的方法是简单地获取语句并检查它们是否需要在最后半列：（这是一个示例，仅当它是逐行语句时才有效：
public void executeScript(String script) {
BufferedReader in = new BufferedReader(new FileReader(script));
while (in.read() > 0) {
String statement = in.readLine();
statement = statement.trim().toLowerCase();
String command = statement.split("[ ]+")[0]; // split the statement.
if (command.equals("insert") || command.equals("update") /* || any other */) {
statement = statement + ";";
}
// execute statement using jdbc
}
}
If you do not know how to use jdbc, just ask :-)
如果您不知道如何使用 jdbc，请询问 :-)
回答by Riccardo Cossu
Have a look at Mybatis Migrations code, it does something like the one you need:
看看 Mybatis Migrations 代码，它会做一些你需要的事情：
回答by duffymo
You need to change the parser so it produces executable statements. But I'm not sure I understand what you mean when you say "execute through Java".
您需要更改解析器，使其生成可执行语句。但是当您说“通过 Java 执行”时，我不确定我是否理解您的意思。
Java won't execute those SQL statements - the database you connect to will. Java can connect to a database using JDBC and send the SQL statements from the file.
Java 不会执行这些 SQL 语句 - 您连接到的数据库会。Java 可以使用 JDBC 连接到数据库并从文件发送 SQL 语句。
I don't see why you have to parse the SQL, unless you want Java to validate them before sending them on to the database server. The server will parse and validate them again, so it feels like you're doing extra work for nothing.
我不明白为什么必须解析 SQL，除非您希望 Java 在将它们发送到数据库服务器之前对其进行验证。服务器将再次解析和验证它们，所以感觉就像你在做额外的工作。
回答by Dennis
The simplest solution I can present to you is this, presuming I understand your question.
我可以向您提供的最简单的解决方案是这个，假设我理解您的问题。
1) Read text file into a string or array via Java IO. 2) Pass string or array to MySQL via JDBC.
1) 通过 Java IO 将文本文件读入字符串或数组。2) 通过 JDBC 将字符串或数组传递给 MySQL。
Read from file example,
从文件示例中读取，
import java.io.*;
class FileRead
{
public static void main(String args[])
{
try{
// Open the file that is the first
// command line parameter
FileInputStream fstream = new FileInputStream("textfile.txt");
// Get the object of DataInputStream
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;
//Read File Line By Line
while ((strLine = br.readLine()) != null) {
// Print the content on the console
System.out.println (strLine);
}
//Close the input stream
in.close();
}catch (Exception e){//Catch exception if any
System.err.println("Error: " + e.getMessage());
}
}
}
acquired from, http://www.roseindia.net/java/beginners/java-read-file-line-by-line.shtml
从http://www.roseindia.net/java/beginners/java-read-file-line-by-line.shtml获取
