/**
* Split an SQL script into separate statements delimited by the provided delimiter character.
* Each individual statement will be added to the provided {@code List}.
*
* <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the comment prefix; any
* text beginning with the comment prefix and extending to the end of the line will be omitted
* from the output. Similarly, {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and {@value
* #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the <em>start</em> and <em>end</em> block
* comment delimiters: any text enclosed in a block comment will be omitted from the output. In
* addition, multiple adjacent whitespace characters will be collapsed into a single space.
*
* @param script the SQL script
* @param delimiter character delimiting each statement — typically a ';' character
* @param statements the list that will contain the individual statements
* @see #splitSqlScript(EncodedResource, String, String, String, String, String, List)
*/
public static void splitSqlScript(String script, char delimiter, List<String> statements)
throws ScriptException {
splitSqlScript(
null,
script,
String.valueOf(delimiter),
DEFAULT_COMMENT_PREFIX,
DEFAULT_BLOCK_COMMENT_START_DELIMITER,
DEFAULT_BLOCK_COMMENT_END_DELIMITER,
statements);
}
/**
* Split an SQL script into separate statements delimited by the provided separator string. Each
* individual statement will be added to the provided {@code List}.
*
* <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the comment prefix; any
* text beginning with the comment prefix and extending to the end of the line will be omitted
* from the output. Similarly, {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and {@value
* #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the <em>start</em> and <em>end</em> block
* comment delimiters: any text enclosed in a block comment will be omitted from the output. In
* addition, multiple adjacent whitespace characters will be collapsed into a single space.
*
* @param script the SQL script
* @param separator text separating each statement — typically a ';' or newline character
* @param statements the list that will contain the individual statements
* @throws ScriptException if an error occurred while splitting the SQL script
* @see #splitSqlScript(String, char, List)
* @see #splitSqlScript(EncodedResource, String, String, String, String, String, List)
*/
public static void splitSqlScript(String script, String separator, List<String> statements)
throws ScriptException {
splitSqlScript(
null,
script,
separator,
DEFAULT_COMMENT_PREFIX,
DEFAULT_BLOCK_COMMENT_START_DELIMITER,
DEFAULT_BLOCK_COMMENT_END_DELIMITER,
statements);
}
/**
* Execute the given SQL script.
*
* <p>Statement separators and comments will be removed before executing individual statements
* within the supplied script.
*
* <p><b>Do not use this method to execute DDL if you expect rollback.</b>
*
* @param connection the JDBC connection to use to execute the script; already configured and
* ready to use
* @param resource the resource (potentially associated with a specific encoding) to load the SQL
* script from
* @param continueOnError whether or not to continue without throwing an exception in the event of
* an error
* @param ignoreFailedDrops whether or not to continue in the event of specifically an error on a
* {@code DROP} statement
* @param commentPrefix the prefix that identifies comments in the SQL script — typically
* "--"
* @param separator the script statement separator; defaults to {@value
* #DEFAULT_STATEMENT_SEPARATOR} if not specified
* @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; never {@code
* null} or empty
* @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; never {@code null} or
* empty
*/
public static void executeSqlScript(
Connection connection,
EncodedResource resource,
boolean continueOnError,
boolean ignoreFailedDrops,
String commentPrefix,
String separator,
String blockCommentStartDelimiter,
String blockCommentEndDelimiter)
throws SQLException, ScriptException {
if (logger.isInfoEnabled()) {
logger.info("Executing SQL script from " + resource);
}
long startTime = System.currentTimeMillis();
List<String> statements = new LinkedList<String>();
String script;
try {
script = readScript(resource, commentPrefix, separator);
} catch (IOException ex) {
throw new CannotReadScriptException(resource, ex);
}
if (separator == null) {
separator = DEFAULT_STATEMENT_SEPARATOR;
if (!containsSqlScriptDelimiters(script, separator)) {
separator = "\n";
}
}
splitSqlScript(
resource,
script,
separator,
commentPrefix,
blockCommentStartDelimiter,
blockCommentEndDelimiter,
statements);
int lineNumber = 0;
Statement stmt = connection.createStatement();
try {
for (String statement : statements) {
lineNumber++;
try {
stmt.execute(statement);
int rowsAffected = stmt.getUpdateCount();
if (logger.isDebugEnabled()) {
logger.debug(rowsAffected + " returned as updateCount for SQL: " + statement);
}
} catch (SQLException ex) {
boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop");
if (continueOnError || (dropStatement && ignoreFailedDrops)) {
if (logger.isDebugEnabled()) {
logger.debug(
"Failed to execute SQL script statement at line "
+ lineNumber
+ " of resource "
+ resource
+ ": "
+ statement,
ex);
}
} else {
throw new ScriptStatementFailedException(statement, lineNumber, resource, ex);
}
}
}
} finally {
try {
stmt.close();
} catch (Throwable ex) {
logger.debug("Could not close JDBC Statement", ex);
}
}
long elapsedTime = System.currentTimeMillis() - startTime;
if (logger.isInfoEnabled()) {
logger.info("Executed SQL script from " + resource + " in " + elapsedTime + " ms.");
}
}
/**
* Split an SQL script into separate statements delimited by the provided separator character.
* Each individual statement will be added to the provided {@code List}.
*
* <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the comment prefix; any
* text beginning with the comment prefix and extending to the end of the line will be omitted
* from the output. Similarly, {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and {@value
* #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the <em>start</em> and <em>end</em> block
* comment delimiters: any text enclosed in a block comment will be omitted from the output. In
* addition, multiple adjacent whitespace characters will be collapsed into a single space.
*
* @param script the SQL script
* @param separator character separating each statement — typically a ';'
* @param statements the list that will contain the individual statements
* @throws ScriptException if an error occurred while splitting the SQL script
* @see #splitSqlScript(String, String, List)
* @see #splitSqlScript(EncodedResource, String, String, String, String, String, List)
*/
public static void splitSqlScript(String script, char separator, List<String> statements)
throws ScriptException {
splitSqlScript(script, String.valueOf(separator), statements);
}