- 读取sql脚本文件时,需要通过ClassPathResource 来获取(其他方式服务器发布后可能会出现无法获取脚本的情况)
- 根据上步获取的inputStream 流创建InputStreamReader
- 根据Connection信息初始化创建ScriptRunner ,并执行
private void execute(String url, String rootName, String rootPwd, String dbName, String userName, String password, String port) {
String connectUrl = "jdbc:mysql://" + url + ":" + port + "/?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true";
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(connectUrl, rootName, rootPwd);
initTable(connection);
} catch (SQLException | ClassNotFoundException | IOException e) {
// 异常处理
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// 异常处理
}
}
}
}
private void initTable(Connection connection) throws IOException {
// sql 脚本
ClassPathResource classPathResource = new ClassPathResource("sql/initTable.sql");
InputStream inputStream = classPathResource.getInputStream();
InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
ScriptRunner scriptRunner = new ScriptRunner(connection);
// 必须设置该参数为true,否则无法执行begin……end;
scriptRunner.setSendFullScript(true);
scriptRunner.runScript(inputStreamReader);
}
注意:当所要执行的sql脚本中包含begin……end;类的sql语句是,需要设置sendFullScript为true,否则会出现以下错误
Error executing: CREATE PROCEDURE `optimizet_procedure`()
begin
optimize table task
. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Error executing: end
. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1