Java 使用 MySQL 和 JDBC 运行 .sql 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1044194/
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
Running a .sql script using MySQL with JDBC
提问by Macarse
I am starting to use MySQL with JDBC.
我开始在 JDBC 中使用 MySQL。
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///x", "x", "x");
stmt = conn.createStatement();
stmt.execute( "CREATE TABLE amigos" +
"("+
"id int AUTO_INCREMENT not null,"+
"nombre char(20) not null,"+
"primary key(id)" +
")");
I have 3-4 tables to create and this doesn't look good.
我要创建 3-4 个表，这看起来不太好。
Is there a way to run a .sql script from MySQL JDBC?
有没有办法从 MySQL JDBC 运行 .sql 脚本？
采纳答案by jitter
Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info.
好的。你可以在你的项目中在这里使用这个类（由于文件长度在 pastebin 上发布）。但请记住保留 apache 许可证信息。
It's ripoff of the iBatis ScriptRunner with dependencies removed.
它是删除了依赖项的 iBatis ScriptRunner 的翻版。
You can use it like this
你可以像这样使用它
Connection con = ....
ScriptRunner runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror]);
runner.runScript(new BufferedReader(new FileReader("test.sql")));
That's it!
就是这样！
回答by matt b
Write code to:
写代码到：
- Read in a file containing a number of SQL statements.
- Run each SQL statement.
- 读入包含许多 SQL 语句的文件。
- 运行每个 SQL 语句。
回答by jitter
There isn't really a way to do this.
真的没有办法做到这一点。
You could either run the mysql command line client via Runtime.exec(String[]) and read this articlewhen you decide for this option
您可以通过 Runtime.exec(String[]) 运行 mysql 命令行客户端，并在决定使用此选项时阅读本文
Or try using the ScriptRunner (com.ibatis.common.jdbc.ScriptRunner) from ibatis. But it's a bit stupid to include a whole library just to run a script.
或者尝试使用ibatis的 ScriptRunner (com.ibatis.common.jdbc.ScriptRunner) 。但是为了运行脚本而包含整个库有点愚蠢。
回答by Gregor
For Oracle PL/SQL, the Oracle JDBC-driver indeed supports executing entire SQL-scripts including stored procedures and anonymous blocks (PL/SQL specific notation), see
对于 Oracle PL/SQL，Oracle JDBC 驱动程序确实支持执行整个 SQL 脚本，包括存储过程和匿名块（PL/SQL 特定符号），请参阅
Can the JDBC Drivers access PL/SQL Stored Procedures?
The Oracle JDBC driver FAQhas more info:
在Oracle JDBC驱动程序常见问题有更多的信息：
Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:
Oracle JDBC 驱动程序支持执行 PL/SQL 存储过程和匿名块。它们支持 SQL92 转义语法和 Oracle PL/SQL 块语法。以下 PL/SQL 调用适用于任何 Oracle JDBC 驱动程序：
// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // stored func
It should be possible to read in a file and feed the content to the prepareCall()-method.
应该可以读入文件并将内容提供给 prepareCall() 方法。
回答by Pantelis Sopasakis
Regarding SQL script runner (which I'm also using), I noticed the following piece of code:
关于 SQL 脚本运行程序（我也在使用），我注意到以下代码：
for (int i = 0; i < cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
However, in the API documentation for the method getString(int) it's mentioned that indexes start with 1, so this should become:
但是，在方法 getString(int) 的 API 文档中提到索引以 1 开头，所以这应该变成：
for (int i = 1; i <= cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
Second, this implementation of ScriptRunner does not provide support for DELIMITERstatements in the SQL script which are important if you need to compile TRIGGERS or PROCEDURES. So I have created this modified version of ScriptRunner: http://pastebin.com/ZrUcDjSxwhich I hope you'll find useful.
其次，ScriptRunner 的这种实现不支持SQL 脚本中的DELIMITER语句，如果您需要编译 TRIGGERS 或 PROCEDURES，这很重要。所以我创建了这个 ScriptRunner 的修改版本：http://pastebin.com/ZrUcDjSx ，我希望你会觉得有用。
回答by joe776
@Pantelis Sopasakis
@Pantelis Sopasakis
Slightly modified version on GitHub: https://gist.github.com/831762/
GitHub 上稍微修改的版本：https: //gist.github.com/831762/
Its easier to track modifications there.
在那里跟踪修改更容易。
回答by Amir Raminfar
I did a lot of research on this and found a good util from spring. I think using SimpleJdbcTestUtils.executeSqlScript(...)is actually the best solution, as it is more maintained and tested.
我对此进行了大量研究，并从 spring 中找到了一个很好的实用程序。我认为使用SimpleJdbcTestUtils.executeSqlScript(...)实际上是最好的解决方案，因为它得到了更多的维护和测试。
Edit: SimpleJdbcTestUtilsis deprecated. You should use JdbcTestUtils. Updated the link.
编辑：SimpleJdbcTestUtils已弃用。你应该使用JdbcTestUtils. 更新了链接。
回答by Abdull
Spring Framework's ResourceDatabasePopulatormay help. As you said you're using MySQL and JDBC, let's assume you have a MySQL-backed DataSourceinstance ready. Further, let's assume your MySQL script files are classpath-locatable. Let's assume you are using WAR layout and the script files are located in a directory src/main/webapp/resources/mysql-scripts/...or src/test/resources/mysql-scripts/.... Then you can use ResourceDatabasePopulatorto execute SQL scripts like this:
Spring Framework 的ResourceDatabasePopulator可能会有所帮助。正如您所说，您正在使用 MySQL 和 JDBC，让我们假设您已经DataSource准备好一个 MySQL 支持的实例。此外，让我们假设您的 MySQL 脚本文件是类路径可定位的。假设您使用 WAR 布局并且脚本文件位于目录src/main/webapp/resources/mysql-scripts/...或src/test/resources/mysql-scripts/.... 然后你可以使用ResourceDatabasePopulator这样的 SQL 脚本来执行：
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import javax.sql.DataSource;
DataSource dataSource = getYourMySQLDriverBackedDataSource();
ResourceDatabasePopulator rdp = new ResourceDatabasePopulator();
rdp.addScript(new ClassPathResource(
"mysql-scripts/firstScript.sql"));
rdp.addScript(new ClassPathResource(
"mysql-scripts/secondScript.sql"));
try {
Connection connection = dataSource.getConnection();
rdp.populate(connection); // this starts the script execution, in the order as added
} catch (SQLException e) {
e.printStackTrace();
}
回答by kapil das
Maven SQL PluginUse this plugin to execute SQL statements a file or list of files through
Maven SQL Plugin使用这个插件来执行一个文件或文件列表的 SQL 语句
- sqlCommand
- srcFiles 3.fileset configurations
- sql命令
- srcFiles 3.fileset 配置
回答by lunicon
For simple sql script splitted by ';' you can use this simple function. It remove comments and run statements one by one
对于由 ';' 分割的简单 sql 脚本 你可以使用这个简单的功能。它删除注释并一一运行语句
static void executeScript(Connection conn, InputStream in)
throws SQLException
{
Scanner s = new Scanner(in);
s.useDelimiter("/\*[\s\S]*?\*/|--[^\r\n]*|;");
Statement st = null;
try
{
st = conn.createStatement();
while (s.hasNext())
{
String line = s.next().trim();
if (!line.isEmpty())
st.execute(line);
}
}
finally
{
if (st != null)
st.close();
}
}
