001/* 002 * Copyright 2002-2018 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.LinkedList; 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.util.Assert; 034import org.springframework.util.StringUtils; 035 036/** 037 * Generic utility methods for working with SQL scripts. 038 * 039 * <p>Mainly for internal use within the framework. 040 * 041 * @author Thomas Risberg 042 * @author Sam Brannen 043 * @author Juergen Hoeller 044 * @author Keith Donald 045 * @author Dave Syer 046 * @author Chris Beams 047 * @author Oliver Gierke 048 * @author Chris Baldwin 049 * @author Nicolas Debeissat 050 * @since 4.0.3 051 */ 052public abstract class ScriptUtils { 053 054 /** 055 * Default statement separator within SQL scripts: {@code ";"}. 056 */ 057 public static final String DEFAULT_STATEMENT_SEPARATOR = ";"; 058 059 /** 060 * Fallback statement separator within SQL scripts: {@code "\n"}. 061 * <p>Used if neither a custom separator nor the 062 * {@link #DEFAULT_STATEMENT_SEPARATOR} is present in a given script. 063 */ 064 public static final String FALLBACK_STATEMENT_SEPARATOR = "\n"; 065 066 /** 067 * End of file (EOF) SQL statement separator: {@code "^^^ END OF SCRIPT ^^^"}. 068 * <p>This value may be supplied as the {@code separator} to {@link 069 * #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String)} 070 * to denote that an SQL script contains a single statement (potentially 071 * spanning multiple lines) with no explicit statement separator. Note that 072 * such a script should not actually contain this value; it is merely a 073 * <em>virtual</em> statement separator. 074 */ 075 public static final String EOF_STATEMENT_SEPARATOR = "^^^ END OF SCRIPT ^^^"; 076 077 /** 078 * Default prefix for single-line comments within SQL scripts: {@code "--"}. 079 */ 080 public static final String DEFAULT_COMMENT_PREFIX = "--"; 081 082 /** 083 * Default start delimiter for block comments within SQL scripts: {@code "/*"}. 084 */ 085 public static final String DEFAULT_BLOCK_COMMENT_START_DELIMITER = "/*"; 086 087 /** 088 * Default end delimiter for block comments within SQL scripts: <code>"*/"</code>. 089 */ 090 public static final String DEFAULT_BLOCK_COMMENT_END_DELIMITER = "*/"; 091 092 093 private static final Log logger = LogFactory.getLog(ScriptUtils.class); 094 095 096 /** 097 * Split an SQL script into separate statements delimited by the provided 098 * separator character. Each individual statement will be added to the 099 * provided {@code List}. 100 * <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the 101 * comment prefix; any text beginning with the comment prefix and extending to 102 * the end of the line will be omitted from the output. Similarly, 103 * {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and 104 * {@value #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the 105 * <em>start</em> and <em>end</em> block comment delimiters: any text enclosed 106 * in a block comment will be omitted from the output. In addition, multiple 107 * adjacent whitespace characters will be collapsed into a single space. 108 * @param script the SQL script 109 * @param separator character separating each statement — typically a ';' 110 * @param statements the list that will contain the individual statements 111 * @throws ScriptException if an error occurred while splitting the SQL script 112 * @see #splitSqlScript(String, String, List) 113 * @see #splitSqlScript(EncodedResource, String, String, String, String, String, List) 114 */ 115 public static void splitSqlScript(String script, char separator, List<String> statements) throws ScriptException { 116 splitSqlScript(script, String.valueOf(separator), statements); 117 } 118 119 /** 120 * Split an SQL script into separate statements delimited by the provided 121 * separator string. Each individual statement will be added to the 122 * provided {@code List}. 123 * <p>Within the script, {@value #DEFAULT_COMMENT_PREFIX} will be used as the 124 * comment prefix; any text beginning with the comment prefix and extending to 125 * the end of the line will be omitted from the output. Similarly, 126 * {@value #DEFAULT_BLOCK_COMMENT_START_DELIMITER} and 127 * {@value #DEFAULT_BLOCK_COMMENT_END_DELIMITER} will be used as the 128 * <em>start</em> and <em>end</em> block comment delimiters: any text enclosed 129 * in a block comment will be omitted from the output. In addition, multiple 130 * adjacent whitespace characters will be collapsed into a single space. 131 * @param script the SQL script 132 * @param separator text separating each statement — typically a ';' or newline character 133 * @param statements the list that will contain the individual statements 134 * @throws ScriptException if an error occurred while splitting the SQL script 135 * @see #splitSqlScript(String, char, List) 136 * @see #splitSqlScript(EncodedResource, String, String, String, String, String, List) 137 */ 138 public static void splitSqlScript(String script, String separator, List<String> statements) throws ScriptException { 139 splitSqlScript(null, script, separator, DEFAULT_COMMENT_PREFIX, DEFAULT_BLOCK_COMMENT_START_DELIMITER, 140 DEFAULT_BLOCK_COMMENT_END_DELIMITER, statements); 141 } 142 143 /** 144 * Split an SQL script into separate statements delimited by the provided 145 * separator string. Each individual statement will be added to the provided 146 * {@code List}. 147 * <p>Within the script, the provided {@code commentPrefix} will be honored: 148 * any text beginning with the comment prefix and extending to the end of the 149 * line will be omitted from the output. Similarly, the provided 150 * {@code blockCommentStartDelimiter} and {@code blockCommentEndDelimiter} 151 * delimiters will be honored: any text enclosed in a block comment will be 152 * omitted from the output. In addition, multiple adjacent whitespace characters 153 * will be collapsed into a single space. 154 * @param resource the resource from which the script was read 155 * @param script the SQL script; never {@code null} or empty 156 * @param separator text separating each statement — typically a ';' or 157 * newline character; never {@code null} 158 * @param commentPrefix the prefix that identifies SQL line comments — 159 * typically "--"; never {@code null} or empty 160 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; 161 * never {@code null} or empty 162 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; 163 * never {@code null} or empty 164 * @param statements the list that will contain the individual statements 165 * @throws ScriptException if an error occurred while splitting the SQL script 166 */ 167 public static void splitSqlScript(EncodedResource resource, String script, String separator, String commentPrefix, 168 String blockCommentStartDelimiter, String blockCommentEndDelimiter, List<String> statements) 169 throws ScriptException { 170 171 Assert.hasText(script, "'script' must not be null or empty"); 172 Assert.notNull(separator, "'separator' must not be null"); 173 Assert.hasText(commentPrefix, "'commentPrefix' must not be null or empty"); 174 Assert.hasText(blockCommentStartDelimiter, "'blockCommentStartDelimiter' must not be null or empty"); 175 Assert.hasText(blockCommentEndDelimiter, "'blockCommentEndDelimiter' must not be null or empty"); 176 177 StringBuilder sb = new StringBuilder(); 178 boolean inSingleQuote = false; 179 boolean inDoubleQuote = false; 180 boolean inEscape = false; 181 182 for (int i = 0; i < script.length(); i++) { 183 char c = script.charAt(i); 184 if (inEscape) { 185 inEscape = false; 186 sb.append(c); 187 continue; 188 } 189 // MySQL style escapes 190 if (c == '\\') { 191 inEscape = true; 192 sb.append(c); 193 continue; 194 } 195 if (!inDoubleQuote && (c == '\'')) { 196 inSingleQuote = !inSingleQuote; 197 } 198 else if (!inSingleQuote && (c == '"')) { 199 inDoubleQuote = !inDoubleQuote; 200 } 201 if (!inSingleQuote && !inDoubleQuote) { 202 if (script.startsWith(separator, i)) { 203 // We've reached the end of the current statement 204 if (sb.length() > 0) { 205 statements.add(sb.toString()); 206 sb = new StringBuilder(); 207 } 208 i += separator.length() - 1; 209 continue; 210 } 211 else if (script.startsWith(commentPrefix, i)) { 212 // Skip over any content from the start of the comment to the EOL 213 int indexOfNextNewline = script.indexOf('\n', i); 214 if (indexOfNextNewline > i) { 215 i = indexOfNextNewline; 216 continue; 217 } 218 else { 219 // If there's no EOL, we must be at the end of the script, so stop here. 220 break; 221 } 222 } 223 else if (script.startsWith(blockCommentStartDelimiter, i)) { 224 // Skip over any block comments 225 int indexOfCommentEnd = script.indexOf(blockCommentEndDelimiter, i); 226 if (indexOfCommentEnd > i) { 227 i = indexOfCommentEnd + blockCommentEndDelimiter.length() - 1; 228 continue; 229 } 230 else { 231 throw new ScriptParseException( 232 "Missing block comment end delimiter: " + blockCommentEndDelimiter, resource); 233 } 234 } 235 else if (c == ' ' || c == '\r' || c == '\n' || c == '\t') { 236 // Avoid multiple adjacent whitespace characters 237 if (sb.length() > 0 && sb.charAt(sb.length() - 1) != ' ') { 238 c = ' '; 239 } 240 else { 241 continue; 242 } 243 } 244 } 245 sb.append(c); 246 } 247 248 if (StringUtils.hasText(sb)) { 249 statements.add(sb.toString()); 250 } 251 } 252 253 /** 254 * Read a script from the given resource, using "{@code --}" as the comment prefix 255 * and "{@code ;}" as the statement separator, and build a String containing the lines. 256 * @param resource the {@code EncodedResource} to be read 257 * @return {@code String} containing the script lines 258 * @throws IOException in case of I/O errors 259 */ 260 static String readScript(EncodedResource resource) throws IOException { 261 return readScript(resource, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR); 262 } 263 264 /** 265 * Read a script from the provided resource, using the supplied comment prefix 266 * and statement separator, and build a {@code String} containing the lines. 267 * <p>Lines <em>beginning</em> with the comment prefix are excluded from the 268 * results; however, line comments anywhere else — for example, within 269 * a statement — will be included in the results. 270 * @param resource the {@code EncodedResource} containing the script 271 * to be processed 272 * @param commentPrefix the prefix that identifies comments in the SQL script — 273 * typically "--" 274 * @param separator the statement separator in the SQL script — typically ";" 275 * @return a {@code String} containing the script lines 276 * @throws IOException in case of I/O errors 277 */ 278 private static String readScript(EncodedResource resource, String commentPrefix, String separator) 279 throws IOException { 280 281 LineNumberReader lnr = new LineNumberReader(resource.getReader()); 282 try { 283 return readScript(lnr, commentPrefix, separator); 284 } 285 finally { 286 lnr.close(); 287 } 288 } 289 290 /** 291 * Read a script from the provided {@code LineNumberReader}, using the supplied 292 * comment prefix and statement separator, and build a {@code String} containing 293 * the lines. 294 * <p>Lines <em>beginning</em> with the comment prefix are excluded from the 295 * results; however, line comments anywhere else — for example, within 296 * a statement — will be included in the results. 297 * @param lineNumberReader the {@code LineNumberReader} containing the script 298 * to be processed 299 * @param commentPrefix the prefix that identifies comments in the SQL script — 300 * typically "--" 301 * @param separator the statement separator in the SQL script — typically ";" 302 * @return a {@code String} containing the script lines 303 * @throws IOException in case of I/O errors 304 */ 305 public static String readScript(LineNumberReader lineNumberReader, String commentPrefix, String separator) 306 throws IOException { 307 308 String currentStatement = lineNumberReader.readLine(); 309 StringBuilder scriptBuilder = new StringBuilder(); 310 while (currentStatement != null) { 311 if (commentPrefix != null && !currentStatement.startsWith(commentPrefix)) { 312 if (scriptBuilder.length() > 0) { 313 scriptBuilder.append('\n'); 314 } 315 scriptBuilder.append(currentStatement); 316 } 317 currentStatement = lineNumberReader.readLine(); 318 } 319 appendSeparatorToScriptIfNecessary(scriptBuilder, separator); 320 return scriptBuilder.toString(); 321 } 322 323 private static void appendSeparatorToScriptIfNecessary(StringBuilder scriptBuilder, String separator) { 324 if (separator == null) { 325 return; 326 } 327 String trimmed = separator.trim(); 328 if (trimmed.length() == separator.length()) { 329 return; 330 } 331 // separator ends in whitespace, so we might want to see if the script is trying 332 // to end the same way 333 if (scriptBuilder.lastIndexOf(trimmed) == scriptBuilder.length() - trimmed.length()) { 334 scriptBuilder.append(separator.substring(trimmed.length())); 335 } 336 } 337 338 /** 339 * Does the provided SQL script contain the specified delimiter? 340 * @param script the SQL script 341 * @param delim String delimiting each statement - typically a ';' character 342 */ 343 public static boolean containsSqlScriptDelimiters(String script, String delim) { 344 boolean inLiteral = false; 345 for (int i = 0; i < script.length(); i++) { 346 if (script.charAt(i) == '\'') { 347 inLiteral = !inLiteral; 348 } 349 if (!inLiteral && script.startsWith(delim, i)) { 350 return true; 351 } 352 } 353 return false; 354 } 355 356 /** 357 * Execute the given SQL script using default settings for statement 358 * separators, comment delimiters, and exception handling flags. 359 * <p>Statement separators and comments will be removed before executing 360 * individual statements within the supplied script. 361 * <p><strong>Warning</strong>: this method does <em>not</em> release the 362 * provided {@link Connection}. 363 * @param connection the JDBC connection to use to execute the script; already 364 * configured and ready to use 365 * @param resource the resource to load the SQL script from; encoded with the 366 * current platform's default encoding 367 * @throws ScriptException if an error occurred while executing the SQL script 368 * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) 369 * @see #DEFAULT_STATEMENT_SEPARATOR 370 * @see #DEFAULT_COMMENT_PREFIX 371 * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER 372 * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER 373 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 374 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 375 */ 376 public static void executeSqlScript(Connection connection, Resource resource) throws ScriptException { 377 executeSqlScript(connection, new EncodedResource(resource)); 378 } 379 380 /** 381 * Execute the given SQL script using default settings for statement 382 * separators, comment delimiters, and exception handling flags. 383 * <p>Statement separators and comments will be removed before executing 384 * individual statements within the supplied script. 385 * <p><strong>Warning</strong>: this method does <em>not</em> release the 386 * provided {@link Connection}. 387 * @param connection the JDBC connection to use to execute the script; already 388 * configured and ready to use 389 * @param resource the resource (potentially associated with a specific encoding) 390 * to load the SQL script from 391 * @throws ScriptException if an error occurred while executing the SQL script 392 * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) 393 * @see #DEFAULT_STATEMENT_SEPARATOR 394 * @see #DEFAULT_COMMENT_PREFIX 395 * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER 396 * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER 397 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 398 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 399 */ 400 public static void executeSqlScript(Connection connection, EncodedResource resource) throws ScriptException { 401 executeSqlScript(connection, resource, false, false, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR, 402 DEFAULT_BLOCK_COMMENT_START_DELIMITER, DEFAULT_BLOCK_COMMENT_END_DELIMITER); 403 } 404 405 /** 406 * Execute the given SQL script. 407 * <p>Statement separators and comments will be removed before executing 408 * individual statements within the supplied script. 409 * <p><strong>Warning</strong>: this method does <em>not</em> release the 410 * provided {@link Connection}. 411 * @param connection the JDBC connection to use to execute the script; already 412 * configured and ready to use 413 * @param resource the resource (potentially associated with a specific encoding) 414 * to load the SQL script from 415 * @param continueOnError whether or not to continue without throwing an exception 416 * in the event of an error 417 * @param ignoreFailedDrops whether or not to continue in the event of specifically 418 * an error on a {@code DROP} statement 419 * @param commentPrefix the prefix that identifies single-line comments in the 420 * SQL script — typically "--" 421 * @param separator the script statement separator; defaults to 422 * {@value #DEFAULT_STATEMENT_SEPARATOR} if not specified and falls back to 423 * {@value #FALLBACK_STATEMENT_SEPARATOR} as a last resort; may be set to 424 * {@value #EOF_STATEMENT_SEPARATOR} to signal that the script contains a 425 * single statement without a separator 426 * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; never 427 * {@code null} or empty 428 * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; never 429 * {@code null} or empty 430 * @throws ScriptException if an error occurred while executing the SQL script 431 * @see #DEFAULT_STATEMENT_SEPARATOR 432 * @see #FALLBACK_STATEMENT_SEPARATOR 433 * @see #EOF_STATEMENT_SEPARATOR 434 * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection 435 * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection 436 */ 437 public static void executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, 438 boolean ignoreFailedDrops, String commentPrefix, String separator, String blockCommentStartDelimiter, 439 String blockCommentEndDelimiter) throws ScriptException { 440 441 try { 442 if (logger.isInfoEnabled()) { 443 logger.info("Executing SQL script from " + resource); 444 } 445 long startTime = System.currentTimeMillis(); 446 447 String script; 448 try { 449 script = readScript(resource, commentPrefix, separator); 450 } 451 catch (IOException ex) { 452 throw new CannotReadScriptException(resource, ex); 453 } 454 455 if (separator == null) { 456 separator = DEFAULT_STATEMENT_SEPARATOR; 457 } 458 if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) { 459 separator = FALLBACK_STATEMENT_SEPARATOR; 460 } 461 462 List<String> statements = new LinkedList<String>(); 463 splitSqlScript(resource, script, separator, commentPrefix, blockCommentStartDelimiter, 464 blockCommentEndDelimiter, statements); 465 466 int stmtNumber = 0; 467 Statement stmt = connection.createStatement(); 468 try { 469 for (String statement : statements) { 470 stmtNumber++; 471 try { 472 stmt.execute(statement); 473 int rowsAffected = stmt.getUpdateCount(); 474 if (logger.isDebugEnabled()) { 475 logger.debug(rowsAffected + " returned as update count for SQL: " + statement); 476 SQLWarning warningToLog = stmt.getWarnings(); 477 while (warningToLog != null) { 478 logger.debug("SQLWarning ignored: SQL state '" + warningToLog.getSQLState() + 479 "', error code '" + warningToLog.getErrorCode() + 480 "', message [" + warningToLog.getMessage() + "]"); 481 warningToLog = warningToLog.getNextWarning(); 482 } 483 } 484 } 485 catch (SQLException ex) { 486 boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop"); 487 if (continueOnError || (dropStatement && ignoreFailedDrops)) { 488 if (logger.isDebugEnabled()) { 489 logger.debug(ScriptStatementFailedException.buildErrorMessage(statement, stmtNumber, resource), ex); 490 } 491 } 492 else { 493 throw new ScriptStatementFailedException(statement, stmtNumber, resource, ex); 494 } 495 } 496 } 497 } 498 finally { 499 try { 500 stmt.close(); 501 } 502 catch (Throwable ex) { 503 logger.debug("Could not close JDBC Statement", ex); 504 } 505 } 506 507 long elapsedTime = System.currentTimeMillis() - startTime; 508 if (logger.isInfoEnabled()) { 509 logger.info("Executed SQL script from " + resource + " in " + elapsedTime + " ms."); 510 } 511 } 512 catch (Exception ex) { 513 if (ex instanceof ScriptException) { 514 throw (ScriptException) ex; 515 } 516 throw new UncategorizedScriptException( 517 "Failed to execute database script from resource [" + resource + "]", ex); 518 } 519 } 520 521}