001/* 002 * Copyright 2002-2020 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.jdbc.datasource.init; 018 019import java.io.IOException; 020import java.io.LineNumberReader; 021import java.sql.Connection; 022import java.sql.SQLException; 023import java.sql.SQLWarning; 024import java.sql.Statement; 025import java.util.ArrayList; 026import java.util.List; 027 028import org.apache.commons.logging.Log; 029import org.apache.commons.logging.LogFactory; 030 031import org.springframework.core.io.Resource; 032import org.springframework.core.io.support.EncodedResource; 033import org.springframework.lang.Nullable; 034import org.springframework.util.Assert; 035import org.springframework.util.StringUtils; 036 037/** 038 * Generic utility methods for working with SQL scripts. 039 * 040 * <p>Mainly for internal use within the framework. 041 * 042 * @author Thomas Risberg 043 * @author Sam Brannen 044 * @author Juergen Hoeller 045 * @author Keith Donald 046 * @author Dave Syer 047 * @author Chris Beams 048 * @author Oliver Gierke 049 * @author Chris Baldwin 050 * @author Nicolas Debeissat 051 * @author Phillip Webb 052 * @since 4.0.3 053 */ 054public abstract class ScriptUtils { 055 056 /** 057 * Default statement separator within SQL scripts: {@code ";"}. 058 */ 059 public static final String DEFAULT_STATEMENT_SEPARATOR = ";"; 060 061 /** 062 * Fallback statement separator within SQL scripts: {@code "\n"}. 063 * <p>Used if neither a custom separator nor the 064 * {@link #DEFAULT_STATEMENT_SEPARATOR} is present in a given script. 065 */ 066 public static final String FALLBACK_STATEMENT_SEPARATOR = "\n"; 067 068 /** 069 * End of file (EOF) SQL statement separator: {@code "^^^ END OF SCRIPT ^^^"}. 070 * <p>This value may be supplied as the {@code separator} to {@link 071 * #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String)} 072 * to denote that an SQL script contains a single statement (potentially 073 * spanning multiple lines) with no explicit statement separator. Note that 074 * such a script should not actually contain this value; it is merely a 075 * <em>virtual</em> statement separator. 076 */ 077 public static final String EOF_STATEMENT_SEPARATOR = "^^^ END OF SCRIPT ^^^"; 078 079 /** 080 * Default prefix for single-line comments within SQL scripts: {@code "--"}. 081 */ 082 public static final String DEFAULT_COMMENT_PREFIX = "--"; 083 084 /** 085 * Default prefixes for single-line comments within SQL scripts: {@code ["--"]}. 086 * @since 5.2 087 */ 088 public static final String[] DEFAULT_COMMENT_PREFIXES = {DEFAULT_COMMENT_PREFIX}; 089 090 /** 091 * Default start delimiter for block comments within SQL scripts: {@code "/*"}. 092 */ 093 public static final String DEFAULT_BLOCK_COMMENT_START_DELIMITER = "/*"; 094 095 /** 096 * Default end delimiter for block comments within SQL scripts: <code>"*/"</code>. 097 */ 098 public static final String DEFAULT_BLOCK_COMMENT_END_DELIMITER = "*/"; 099 100 101 private static final Log logger = LogFactory.getLog(ScriptUtils.class); 102 103 104 /** 105 * Split an SQL script into separate statements delimited by the provided 106 * separator character. Each individual statement will be added to the 107 * provided {@code List}. 108 * <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the 109 * comment prefix; any text beginning with the comment prefix and extending to 110 * the end of the line will be omitted from the output. Similarly, 111 * {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and 112 * {@value #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the 113 * <em>start</em> and <em>end</em> block comment delimiters: any text enclosed 114 * in a block comment will be omitted from the output. In addition, multiple 115 * adjacent whitespace characters will be collapsed into a single space. 116 * @param script the SQL script 117 * @param separator character separating each statement (typically a ';') 118 * @param statements the list that will contain the individual statements 119 * @throws ScriptException if an error occurred while splitting the SQL script 120 * @see #splitSqlScript(String, String, List) 121 * @see #splitSqlScript(EncodedResource, String, String, String, String, String, List) 122 */ 123 public static void splitSqlScript(String script, char separator, List<String> statements) throws ScriptException { 124 splitSqlScript(script, String.valueOf(separator), statements); 125 } 126 127 /** 128 * Split an SQL script into separate statements delimited by the provided 129 * separator string. Each individual statement will be added to the 130 * provided {@code List}. 131 * <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the 132 * comment prefix; any text beginning with the comment prefix and extending to 133 * the end of the line will be omitted from the output. Similarly, 134 * {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and 135 * {@value #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the 136 * <em>start</em> and <em>end</em> block comment delimiters: any text enclosed 137 * in a block comment will be omitted from the output. In addition, multiple 138 * adjacent whitespace characters will be collapsed into a single space. 139 * @param script the SQL script 140 * @param separator text separating each statement 141 * (typically a ';' or newline character) 142 * @param statements the list that will contain the individual statements 143 * @throws ScriptException if an error occurred while splitting the SQL script 144 * @see #splitSqlScript(String, char, List) 145 * @see #splitSqlScript(EncodedResource, String, String, String, String, String, List) 146 */ 147 public static void splitSqlScript(String script, String separator, List<String> statements) throws ScriptException { 148 splitSqlScript(null, script, separator, DEFAULT_COMMENT_PREFIX, DEFAULT_BLOCK_COMMENT_START_DELIMITER, 149 DEFAULT_BLOCK_COMMENT_END_DELIMITER, statements); 150 } 151 152 /** 153 * Split an SQL script into separate statements delimited by the provided 154 * separator string. Each individual statement will be added to the provided 155 * {@code List}. 156 * <p>Within the script, the provided {@code commentPrefix} will be honored: 157 * any text beginning with the comment prefix and extending to the end of the 158 * line will be omitted from the output. Similarly, the provided 159 * {@code blockCommentStartDelimiter} and {@code blockCommentEndDelimiter} 160 * delimiters will be honored: any text enclosed in a block comment will be 161 * omitted from the output. In addition, multiple adjacent whitespace characters 162 * will be collapsed into a single space. 163 * @param resource the resource from which the script was read 164 * @param script the SQL script 165 * @param separator text separating each statement 166 * (typically a ';' or newline character) 167 * @param commentPrefix the prefix that identifies SQL line comments 168 * (typically "--") 169 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; 170 * never {@code null} or empty 171 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; 172 * never {@code null} or empty 173 * @param statements the list that will contain the individual statements 174 * @throws ScriptException if an error occurred while splitting the SQL script 175 */ 176 public static void splitSqlScript(@Nullable EncodedResource resource, String script, 177 String separator, String commentPrefix, String blockCommentStartDelimiter, 178 String blockCommentEndDelimiter, List<String> statements) throws ScriptException { 179 180 Assert.hasText(commentPrefix, "'commentPrefix' must not be null or empty"); 181 splitSqlScript(resource, script, separator, new String[] { commentPrefix }, 182 blockCommentStartDelimiter, blockCommentEndDelimiter, statements); 183 } 184 185 /** 186 * Split an SQL script into separate statements delimited by the provided 187 * separator string. Each individual statement will be added to the provided 188 * {@code List}. 189 * <p>Within the script, the provided {@code commentPrefixes} will be honored: 190 * any text beginning with one of the comment prefixes and extending to the 191 * end of the line will be omitted from the output. Similarly, the provided 192 * {@code blockCommentStartDelimiter} and {@code blockCommentEndDelimiter} 193 * delimiters will be honored: any text enclosed in a block comment will be 194 * omitted from the output. In addition, multiple adjacent whitespace characters 195 * will be collapsed into a single space. 196 * @param resource the resource from which the script was read 197 * @param script the SQL script 198 * @param separator text separating each statement 199 * (typically a ';' or newline character) 200 * @param commentPrefixes the prefixes that identify SQL line comments 201 * (typically "--") 202 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; 203 * never {@code null} or empty 204 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; 205 * never {@code null} or empty 206 * @param statements the list that will contain the individual statements 207 * @throws ScriptException if an error occurred while splitting the SQL script 208 * @since 5.2 209 */ 210 public static void splitSqlScript(@Nullable EncodedResource resource, String script, 211 String separator, String[] commentPrefixes, String blockCommentStartDelimiter, 212 String blockCommentEndDelimiter, List<String> statements) throws ScriptException { 213 214 Assert.hasText(script, "'script' must not be null or empty"); 215 Assert.notNull(separator, "'separator' must not be null"); 216 Assert.notEmpty(commentPrefixes, "'commentPrefixes' must not be null or empty"); 217 for (String commentPrefix : commentPrefixes) { 218 Assert.hasText(commentPrefix, "'commentPrefixes' must not contain null or empty elements"); 219 } 220 Assert.hasText(blockCommentStartDelimiter, "'blockCommentStartDelimiter' must not be null or empty"); 221 Assert.hasText(blockCommentEndDelimiter, "'blockCommentEndDelimiter' must not be null or empty"); 222 223 StringBuilder sb = new StringBuilder(); 224 boolean inSingleQuote = false; 225 boolean inDoubleQuote = false; 226 boolean inEscape = false; 227 228 for (int i = 0; i < script.length(); i++) { 229 char c = script.charAt(i); 230 if (inEscape) { 231 inEscape = false; 232 sb.append(c); 233 continue; 234 } 235 // MySQL style escapes 236 if (c == '\\') { 237 inEscape = true; 238 sb.append(c); 239 continue; 240 } 241 if (!inDoubleQuote && (c == '\'')) { 242 inSingleQuote = !inSingleQuote; 243 } 244 else if (!inSingleQuote && (c == '"')) { 245 inDoubleQuote = !inDoubleQuote; 246 } 247 if (!inSingleQuote && !inDoubleQuote) { 248 if (script.startsWith(separator, i)) { 249 // We've reached the end of the current statement 250 if (sb.length() > 0) { 251 statements.add(sb.toString()); 252 sb = new StringBuilder(); 253 } 254 i += separator.length() - 1; 255 continue; 256 } 257 else if (startsWithAny(script, commentPrefixes, i)) { 258 // Skip over any content from the start of the comment to the EOL 259 int indexOfNextNewline = script.indexOf('\n', i); 260 if (indexOfNextNewline > i) { 261 i = indexOfNextNewline; 262 continue; 263 } 264 else { 265 // If there's no EOL, we must be at the end of the script, so stop here. 266 break; 267 } 268 } 269 else if (script.startsWith(blockCommentStartDelimiter, i)) { 270 // Skip over any block comments 271 int indexOfCommentEnd = script.indexOf(blockCommentEndDelimiter, i); 272 if (indexOfCommentEnd > i) { 273 i = indexOfCommentEnd + blockCommentEndDelimiter.length() - 1; 274 continue; 275 } 276 else { 277 throw new ScriptParseException( 278 "Missing block comment end delimiter: " + blockCommentEndDelimiter, resource); 279 } 280 } 281 else if (c == ' ' || c == '\r' || c == '\n' || c == '\t') { 282 // Avoid multiple adjacent whitespace characters 283 if (sb.length() > 0 && sb.charAt(sb.length() - 1) != ' ') { 284 c = ' '; 285 } 286 else { 287 continue; 288 } 289 } 290 } 291 sb.append(c); 292 } 293 294 if (StringUtils.hasText(sb)) { 295 statements.add(sb.toString()); 296 } 297 } 298 299 /** 300 * Read a script from the given resource, using "{@code --}" as the comment prefix 301 * and "{@code ;}" as the statement separator, and build a String containing the lines. 302 * @param resource the {@code EncodedResource} to be read 303 * @return {@code String} containing the script lines 304 * @throws IOException in case of I/O errors 305 */ 306 static String readScript(EncodedResource resource) throws IOException { 307 return readScript(resource, DEFAULT_COMMENT_PREFIXES, DEFAULT_STATEMENT_SEPARATOR, DEFAULT_BLOCK_COMMENT_END_DELIMITER); 308 } 309 310 /** 311 * Read a script from the provided resource, using the supplied comment prefixes 312 * and statement separator, and build a {@code String} containing the lines. 313 * <p>Lines <em>beginning</em> with one of the comment prefixes are excluded 314 * from the results; however, line comments anywhere else — for example, 315 * within a statement — will be included in the results. 316 * @param resource the {@code EncodedResource} containing the script 317 * to be processed 318 * @param commentPrefixes the prefixes that identify comments in the SQL script 319 * (typically "--") 320 * @param separator the statement separator in the SQL script (typically ";") 321 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter 322 * @return a {@code String} containing the script lines 323 * @throws IOException in case of I/O errors 324 */ 325 private static String readScript(EncodedResource resource, @Nullable String[] commentPrefixes, 326 @Nullable String separator, @Nullable String blockCommentEndDelimiter) throws IOException { 327 328 try (LineNumberReader lnr = new LineNumberReader(resource.getReader())) { 329 return readScript(lnr, commentPrefixes, separator, blockCommentEndDelimiter); 330 } 331 } 332 333 /** 334 * Read a script from the provided {@code LineNumberReader}, using the supplied 335 * comment prefix and statement separator, and build a {@code String} containing 336 * the lines. 337 * <p>Lines <em>beginning</em> with the comment prefix are excluded from the 338 * results; however, line comments anywhere else — for example, within 339 * a statement — will be included in the results. 340 * @param lineNumberReader the {@code LineNumberReader} containing the script 341 * to be processed 342 * @param lineCommentPrefix the prefix that identifies comments in the SQL script 343 * (typically "--") 344 * @param separator the statement separator in the SQL script (typically ";") 345 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter 346 * @return a {@code String} containing the script lines 347 * @throws IOException in case of I/O errors 348 */ 349 public static String readScript(LineNumberReader lineNumberReader, @Nullable String lineCommentPrefix, 350 @Nullable String separator, @Nullable String blockCommentEndDelimiter) throws IOException { 351 352 String[] lineCommentPrefixes = (lineCommentPrefix != null) ? new String[] { lineCommentPrefix } : null; 353 return readScript(lineNumberReader, lineCommentPrefixes, separator, blockCommentEndDelimiter); 354 } 355 356 /** 357 * Read a script from the provided {@code LineNumberReader}, using the supplied 358 * comment prefixes and statement separator, and build a {@code String} containing 359 * the lines. 360 * <p>Lines <em>beginning</em> with one of the comment prefixes are excluded 361 * from the results; however, line comments anywhere else — for example, 362 * within a statement — will be included in the results. 363 * @param lineNumberReader the {@code LineNumberReader} containing the script 364 * to be processed 365 * @param lineCommentPrefixes the prefixes that identify comments in the SQL script 366 * (typically "--") 367 * @param separator the statement separator in the SQL script (typically ";") 368 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter 369 * @return a {@code String} containing the script lines 370 * @throws IOException in case of I/O errors 371 * @since 5.2 372 */ 373 public static String readScript(LineNumberReader lineNumberReader, @Nullable String[] lineCommentPrefixes, 374 @Nullable String separator, @Nullable String blockCommentEndDelimiter) throws IOException { 375 376 String currentStatement = lineNumberReader.readLine(); 377 StringBuilder scriptBuilder = new StringBuilder(); 378 while (currentStatement != null) { 379 if ((blockCommentEndDelimiter != null && currentStatement.contains(blockCommentEndDelimiter)) || 380 (lineCommentPrefixes != null && !startsWithAny(currentStatement, lineCommentPrefixes, 0))) { 381 if (scriptBuilder.length() > 0) { 382 scriptBuilder.append('\n'); 383 } 384 scriptBuilder.append(currentStatement); 385 } 386 currentStatement = lineNumberReader.readLine(); 387 } 388 appendSeparatorToScriptIfNecessary(scriptBuilder, separator); 389 return scriptBuilder.toString(); 390 } 391 392 private static void appendSeparatorToScriptIfNecessary(StringBuilder scriptBuilder, @Nullable String separator) { 393 if (separator == null) { 394 return; 395 } 396 String trimmed = separator.trim(); 397 if (trimmed.length() == separator.length()) { 398 return; 399 } 400 // separator ends in whitespace, so we might want to see if the script is trying 401 // to end the same way 402 if (scriptBuilder.lastIndexOf(trimmed) == scriptBuilder.length() - trimmed.length()) { 403 scriptBuilder.append(separator.substring(trimmed.length())); 404 } 405 } 406 407 private static boolean startsWithAny(String script, String[] prefixes, int offset) { 408 for (String prefix : prefixes) { 409 if (script.startsWith(prefix, offset)) { 410 return true; 411 } 412 } 413 return false; 414 } 415 416 /** 417 * Does the provided SQL script contain the specified delimiter? 418 * @param script the SQL script 419 * @param delim the string delimiting each statement - typically a ';' character 420 */ 421 public static boolean containsSqlScriptDelimiters(String script, String delim) { 422 boolean inLiteral = false; 423 boolean inEscape = false; 424 425 for (int i = 0; i < script.length(); i++) { 426 char c = script.charAt(i); 427 if (inEscape) { 428 inEscape = false; 429 continue; 430 } 431 // MySQL style escapes 432 if (c == '\\') { 433 inEscape = true; 434 continue; 435 } 436 if (c == '\'') { 437 inLiteral = !inLiteral; 438 } 439 if (!inLiteral && script.startsWith(delim, i)) { 440 return true; 441 } 442 } 443 444 return false; 445 } 446 447 /** 448 * Execute the given SQL script using default settings for statement 449 * separators, comment delimiters, and exception handling flags. 450 * <p>Statement separators and comments will be removed before executing 451 * individual statements within the supplied script. 452 * <p><strong>Warning</strong>: this method does <em>not</em> release the 453 * provided {@link Connection}. 454 * @param connection the JDBC connection to use to execute the script; already 455 * configured and ready to use 456 * @param resource the resource to load the SQL script from; encoded with the 457 * current platform's default encoding 458 * @throws ScriptException if an error occurred while executing the SQL script 459 * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) 460 * @see #DEFAULT_STATEMENT_SEPARATOR 461 * @see #DEFAULT_COMMENT_PREFIX 462 * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER 463 * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER 464 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 465 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 466 */ 467 public static void executeSqlScript(Connection connection, Resource resource) throws ScriptException { 468 executeSqlScript(connection, new EncodedResource(resource)); 469 } 470 471 /** 472 * Execute the given SQL script using default settings for statement 473 * separators, comment delimiters, and exception handling flags. 474 * <p>Statement separators and comments will be removed before executing 475 * individual statements within the supplied script. 476 * <p><strong>Warning</strong>: this method does <em>not</em> release the 477 * provided {@link Connection}. 478 * @param connection the JDBC connection to use to execute the script; already 479 * configured and ready to use 480 * @param resource the resource (potentially associated with a specific encoding) 481 * to load the SQL script from 482 * @throws ScriptException if an error occurred while executing the SQL script 483 * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) 484 * @see #DEFAULT_STATEMENT_SEPARATOR 485 * @see #DEFAULT_COMMENT_PREFIX 486 * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER 487 * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER 488 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 489 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 490 */ 491 public static void executeSqlScript(Connection connection, EncodedResource resource) throws ScriptException { 492 executeSqlScript(connection, resource, false, false, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR, 493 DEFAULT_BLOCK_COMMENT_START_DELIMITER, DEFAULT_BLOCK_COMMENT_END_DELIMITER); 494 } 495 496 /** 497 * Execute the given SQL script. 498 * <p>Statement separators and comments will be removed before executing 499 * individual statements within the supplied script. 500 * <p><strong>Warning</strong>: this method does <em>not</em> release the 501 * provided {@link Connection}. 502 * @param connection the JDBC connection to use to execute the script; already 503 * configured and ready to use 504 * @param resource the resource (potentially associated with a specific encoding) 505 * to load the SQL script from 506 * @param continueOnError whether or not to continue without throwing an exception 507 * in the event of an error 508 * @param ignoreFailedDrops whether or not to continue in the event of specifically 509 * an error on a {@code DROP} statement 510 * @param commentPrefix the prefix that identifies single-line comments in the 511 * SQL script (typically "--") 512 * @param separator the script statement separator; defaults to 513 * {@value #DEFAULT_STATEMENT_SEPARATOR} if not specified and falls back to 514 * {@value #FALLBACK_STATEMENT_SEPARATOR} as a last resort; may be set to 515 * {@value #EOF_STATEMENT_SEPARATOR} to signal that the script contains a 516 * single statement without a separator 517 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter 518 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter 519 * @throws ScriptException if an error occurred while executing the SQL script 520 * @see #DEFAULT_STATEMENT_SEPARATOR 521 * @see #FALLBACK_STATEMENT_SEPARATOR 522 * @see #EOF_STATEMENT_SEPARATOR 523 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 524 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 525 */ 526 public static void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, 527 boolean ignoreFailedDrops, String commentPrefix, @Nullable String separator, 528 String blockCommentStartDelimiter, String blockCommentEndDelimiter) throws ScriptException { 529 530 executeSqlScript(connection, resource, continueOnError, ignoreFailedDrops, 531 new String[] { commentPrefix }, separator, blockCommentStartDelimiter, 532 blockCommentEndDelimiter); 533 } 534 535 /** 536 * Execute the given SQL script. 537 * <p>Statement separators and comments will be removed before executing 538 * individual statements within the supplied script. 539 * <p><strong>Warning</strong>: this method does <em>not</em> release the 540 * provided {@link Connection}. 541 * @param connection the JDBC connection to use to execute the script; already 542 * configured and ready to use 543 * @param resource the resource (potentially associated with a specific encoding) 544 * to load the SQL script from 545 * @param continueOnError whether or not to continue without throwing an exception 546 * in the event of an error 547 * @param ignoreFailedDrops whether or not to continue in the event of specifically 548 * an error on a {@code DROP} statement 549 * @param commentPrefixes the prefixes that identify single-line comments in the 550 * SQL script (typically "--") 551 * @param separator the script statement separator; defaults to 552 * {@value #DEFAULT_STATEMENT_SEPARATOR} if not specified and falls back to 553 * {@value #FALLBACK_STATEMENT_SEPARATOR} as a last resort; may be set to 554 * {@value #EOF_STATEMENT_SEPARATOR} to signal that the script contains a 555 * single statement without a separator 556 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter 557 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter 558 * @throws ScriptException if an error occurred while executing the SQL script 559 * @since 5.2 560 * @see #DEFAULT_STATEMENT_SEPARATOR 561 * @see #FALLBACK_STATEMENT_SEPARATOR 562 * @see #EOF_STATEMENT_SEPARATOR 563 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 564 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 565 */ 566 public static void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, 567 boolean ignoreFailedDrops, String[] commentPrefixes, @Nullable String separator, 568 String blockCommentStartDelimiter, String blockCommentEndDelimiter) throws ScriptException { 569 570 try { 571 if (logger.isDebugEnabled()) { 572 logger.debug("Executing SQL script from " + resource); 573 } 574 long startTime = System.currentTimeMillis(); 575 576 String script; 577 try { 578 script = readScript(resource, commentPrefixes, separator, blockCommentEndDelimiter); 579 } 580 catch (IOException ex) { 581 throw new CannotReadScriptException(resource, ex); 582 } 583 584 if (separator == null) { 585 separator = DEFAULT_STATEMENT_SEPARATOR; 586 } 587 if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) { 588 separator = FALLBACK_STATEMENT_SEPARATOR; 589 } 590 591 List<String> statements = new ArrayList<>(); 592 splitSqlScript(resource, script, separator, commentPrefixes, blockCommentStartDelimiter, 593 blockCommentEndDelimiter, statements); 594 595 int stmtNumber = 0; 596 Statement stmt = connection.createStatement(); 597 try { 598 for (String statement : statements) { 599 stmtNumber++; 600 try { 601 stmt.execute(statement); 602 int rowsAffected = stmt.getUpdateCount(); 603 if (logger.isDebugEnabled()) { 604 logger.debug(rowsAffected + " returned as update count for SQL: " + statement); 605 SQLWarning warningToLog = stmt.getWarnings(); 606 while (warningToLog != null) { 607 logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + 608 "', error code '" + warningToLog.getErrorCode() + 609 "', message [" + warningToLog.getMessage() + "]"); 610 warningToLog = warningToLog.getNextWarning(); 611 } 612 } 613 } 614 catch (SQLException ex) { 615 boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop"); 616 if (continueOnError || (dropStatement && ignoreFailedDrops)) { 617 if (logger.isDebugEnabled()) { 618 logger.debug(ScriptStatementFailedException.buildErrorMessage(statement, stmtNumber, resource), ex); 619 } 620 } 621 else { 622 throw new ScriptStatementFailedException(statement, stmtNumber, resource, ex); 623 } 624 } 625 } 626 } 627 finally { 628 try { 629 stmt.close(); 630 } 631 catch (Throwable ex) { 632 logger.trace("Could not close JDBC Statement", ex); 633 } 634 } 635 636 long elapsedTime = System.currentTimeMillis() - startTime; 637 if (logger.isDebugEnabled()) { 638 logger.debug("Executed SQL script from " + resource + " in " + elapsedTime + " ms."); 639 } 640 } 641 catch (Exception ex) { 642 if (ex instanceof ScriptException) { 643 throw (ScriptException) ex; 644 } 645 throw new UncategorizedScriptException( 646 "Failed to execute database script from resource [" + resource + "]", ex); 647 } 648 } 649 650}