Java 从 Spring JDBC 模板执行 SQL 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30732314/
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
Execute SQL file from Spring JDBC Template
提问by Ondrej Skalicka
I'm trying to write a bit of code that reads a SQL file (multiple CREATE TABLEstatements separated by ;) and executes all the statements.
我正在尝试编写一些代码来读取 SQL 文件（CREATE TABLE由 分隔的多个语句;）并执行所有语句。
In pure JDBC, I could write:
在纯 JDBC 中，我可以这样写：
String sqlQuery = "CREATE TABLE A (...); CREATE TABLE B (...);"
java.sql.Connection connection = ...;
Statement statement = connection.createStatement();
statement.executeUpdate(sqlQuery);
statement.close();
and both (all) the statements got executed. When I tried to do the same in spring JdbcTemplate, only the first statement is executed though!
并且两个（所有）语句都被执行了。当我尝试在 spring JdbcTemplate 中做同样的事情时，虽然只执行了第一条语句！
String sqlQuery = "CREATE TABLE A (...); CREATE TABLE B (...);"
org.springframework.jdbc.core.JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute(sqlQuery);
Is there a way to execute multiple statements? While googling I found only solutions like "split the sqlQuery by ;manually" which of course is useless (it'd require much more parsing).
有没有办法执行多条语句？在谷歌搜索时，我只找到了“;手动拆分 sqlQuery”之类的解决方案，这当然是无用的（它需要更多的解析）。
采纳答案by siphiuel
Maybe Spring's ScriptUtilswill be useful in your case. Especially executeSqlScriptmethods.
也许 Spring 的ScriptUtils在你的情况下会很有用。尤其是executeSqlScript方法。
Note that DEFAULT_STATEMENT_SEPARATORhas a default value of ';'(see Constant Field Values)
请注意，DEFAULT_STATEMENT_SEPARATOR默认值为';'（请参阅常量字段值）
回答by Anatolii Stepaniuk
I've solved the issue this way:
我已经通过这种方式解决了这个问题：
public void createDefaultDB(DataSource dataSource) {
Resource resource = new ClassPathResource("CreateDefaultDB.sql");
ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
databasePopulator.execute(dataSource);
}
You can inject DataSourceas usual:
您可以DataSource像往常一样注入：
import javax.sql.DataSource;
//...
@Autowired
private DataSource dataSource;
回答by chetan singhal
We can also achive through SQLExec. Below code is working for me.
我们也可以通过 SQLExec 来实现。下面的代码对我有用。
import java.io.File;
导入 java.io.File;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;
public class Test {
public static void main(String[] args) {
Test t = new Test();
t.executeSql("");
}
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("test1.sql"));
executer.setDriver("org.postgresql.Driver");
executer.setPassword("postgres");
executer.setUserid("postgres");
executer.setUrl("jdbc:postgresql://localhost/test");
executer.execute();
}
}
回答by CloudCode
try it
尝试一下
public void executeSqlScript(Connection connection,StringBuffer sql)throws SQLException{
try {
connection.setAutoCommit(false);//设置为手工提交模式
ScriptUtils.executeSqlScript(connection, new ByteArrayResource(sql.toString().getBytes()));
connection.commit();//提交事务
} catch (SQLException e) {
connection.rollback();
}finally{
connection.close();
}
}
