如何在 Java 中执行 SQL 脚本文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2071682/
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
How to Execute SQL Script File in Java?
提问by Vladimir
I want to execute an SQL script file in Java without reading the entire file content into a big query and executing it.
我想在 Java 中执行一个 SQL 脚本文件，而不是将整个文件内容读入一个大查询并执行它。
Is there any other standard way?
有没有其他标准方法？
采纳答案by Pablo Santa Cruz
There is no portable way of doing that. You can execute a native client as an external program to do that though:
没有便携式方法可以做到这一点。您可以将本机客户端作为外部程序执行来执行此操作：
import java.io.*;
public class CmdExec {
public static void main(String argv[]) {
try {
String line;
Process p = Runtime.getRuntime().exec
("psql -U username -d dbname -h serverhost -f scripfile.sql");
BufferedReader input =
new BufferedReader
(new InputStreamReader(p.getInputStream()));
while ((line = input.readLine()) != null) {
System.out.println(line);
}
input.close();
}
catch (Exception err) {
err.printStackTrace();
}
}
}
- Code sample was extracted from hereand modified to answer question assuming that the user wants to execute a PostgreSQL script file.
- 代码示例从这里提取并修改为回答问题，假设用户想要执行 PostgreSQL 脚本文件。
回答by Aaron Digulla
No, you must read the file, split it into separate queries and then execute them individually (or using the batch API of JDBC).
不，您必须读取文件，将其拆分为单独的查询，然后单独执行（或使用 JDBC 的批处理 API）。
One of the reasons is that every database defines their own way to separate SQL statements (some use ;, others /, some allow both or even to define your own separator).
原因之一是每个数据库都定义了自己的分隔 SQL 语句的方式（有些使用;，有些使用/，有些允许两者甚至定义自己的分隔符）。
回答by Itay Maman
JDBC does not support this option (although a specific DB driver may offer this). Anyway, there should not be a problem with loading all file contents into memory.
JDBC 不支持此选项（尽管特定的 DB 驱动程序可能会提供此选项）。无论如何，将所有文件内容加载到内存中应该没有问题。
回答by Srinivas M.V.
You cannot do using JDBC as it does not support . Work around would be including iBatis iBATIS is a persistence framework and call the Scriptrunnerconstructor as shown in iBatisdocumentation .
您不能使用 JDBC，因为它不支持 . 解决方法是包括 iBatis iBATIS 是一个持久性框架并调用Scriptrunner构造函数，如iBatis文档中所示。
Its not good to include a heavy weight persistence framework like ibatis in order to run a simple sql scripts any ways which you can do using command line
包含像 ibatis 这样的重量级持久性框架以运行简单的 sql 脚本并不好，您可以使用命令行以任何方式执行此操作
$ mysql -u root -p db_name < test.sql
回答by Ruslans Uralovs
There is great way of executing SQL scripts from Java without reading them yourself as long as you don't mind having a dependency on Ant. In my opinion such a dependency is very well justified in your case. Here is sample code, where SQLExec class lives in ant.jar:
只要您不介意依赖 Ant，就可以从 Java 执行 SQL 脚本，而无需自己阅读它们。在我看来，这种依赖在你的情况下是非常合理的。这是示例代码，其中 SQLExec 类位于 ant.jar 中：
private void executeSql(String sqlFilePath) {
final class SqlExecuter extends SQLExec {
public SqlExecuter() {
Project project = new Project();
project.init();
setProject(project);
setTaskType("sql");
setTaskName("sql");
}
}
SqlExecuter executer = new SqlExecuter();
executer.setSrc(new File(sqlFilePath));
executer.setDriver(args.getDriver());
executer.setPassword(args.getPwd());
executer.setUserid(args.getUser());
executer.setUrl(args.getUrl());
executer.execute();
}
回答by nabil
Try this code:
试试这个代码：
String strProc =
"DECLARE \n" +
" sys_date DATE;"+
"" +
"BEGIN\n" +
"" +
" SELECT SYSDATE INTO sys_date FROM dual;\n" +
"" +
"END;\n";
try{
DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver () );
Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@your_db_IP:1521:your_db_SID","user","password");
PreparedStatement psProcToexecute = connection.prepareStatement(strProc);
psProcToexecute.execute();
}catch (Exception e) {
System.out.println(e.toString());
}
回答by Raposo
Since JDBC doesn't support this option the best way to solve this question is executing command lines via the Java Program. Bellow is an example to postgresql:
由于 JDBC 不支持此选项，因此解决此问题的最佳方法是通过 Java 程序执行命令行。Bellow 是 postgresql 的一个例子：
private void executeSqlFile() {
try {
Runtime rt = Runtime.getRuntime();
String executeSqlCommand = "psql -U (user) -h (domain) -f (script_name) (dbName)";
Process pr = rt.exec();
int exitVal = pr.waitFor();
System.out.println("Exited with error code " + exitVal);
} catch (Exception e) {
System.out.println(e.toString());
}
}
回答by Stephen
Flyway library is really good for this:
Flyway 库对此非常有用：
Flyway flyway = new Flyway();
flyway.setDataSource(dbConfig.getUrl(), dbConfig.getUsername(), dbConfig.getPassword());
flyway.setLocations("classpath:db/scripts");
flyway.clean();
flyway.migrate();
This scans the locations for scripts and runs them in order. Scripts can be versioned with V01__name.sql so if just the migrate is called then only those not already run will be run. Uses a table called 'schema_version' to keep track of things. But can do other things too, see the docs: flyway.
这会扫描脚本的位置并按顺序运行它们。脚本可以使用 V01__name.sql 进行版本控制，因此如果只调用 migrate，那么只会运行那些尚未运行的脚本。使用名为“schema_version”的表来跟踪事物。但也可以做其他事情，请参阅文档：flyway。
The clean call isn't required, but useful to start from a clean DB. Also, be aware of the location (default is "classpath:db/migration"), there is no space after the ':', that one caught me out.
clean 调用不是必需的，但对于从干净的数据库开始很有用。另外，请注意位置（默认为“classpath:db/migration”），':' 后面没有空格，这让我很困惑。
回答by raisercostin
The simplest external tool that I found that is also portable is jisql - https://www.xigole.com/software/jisql/jisql.jsp. You would run it as:
我发现也是可移植的最简单的外部工具是 jisql - https://www.xigole.com/software/jisql/jisql.jsp。你会运行它：
java -classpath lib/jisql.jar:\
lib/jopt-simple-3.2.jar:\
lib/javacsv.jar:\
/home/scott/postgresql/postgresql-8.4-701.jdbc4.jar
com.xigole.util.sql.Jisql -user scott -password blah \
-driver postgresql \
-cstring jdbc:postgresql://localhost:5432/scott -c \; \
-query "select * from test;"
回答by Emerica
If you use Springyou can use DataSourceInitializer:
如果您使用Spring，您可以使用DataSourceInitializer：
@Bean
public DataSourceInitializer dataSourceInitializer(@Qualifier("dataSource") final DataSource dataSource) {
ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));
DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
dataSourceInitializer.setDataSource(dataSource);
dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
return dataSourceInitializer;
}
Used to set up a database during initialization and clean up a database during destruction.
用于在初始化期间建立数据库并在销毁期间清理数据库。
