/** * Execute the given SQL script using default settings for statement * separators, comment delimiters, and exception handling flags. * <p>Statement separators and comments will be removed before executing * individual statements within the supplied script. * <p><strong>Warning</strong>: this method does <em>not</em> release the * provided {@link Connection}. * @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 * @throws ScriptException if an error occurred while executing the SQL script * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) * @see #DEFAULT_STATEMENT_SEPARATOR * @see #DEFAULT_COMMENT_PREFIX * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection */ public static void executeSqlScript(Connection connection, EncodedResource resource) throws ScriptException { executeSqlScript(connection, resource, false, false, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR, DEFAULT_BLOCK_COMMENT_START_DELIMITER, DEFAULT_BLOCK_COMMENT_END_DELIMITER); }
script = readScript(resource, commentPrefix, separator); separator = DEFAULT_STATEMENT_SEPARATOR; if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) { separator = FALLBACK_STATEMENT_SEPARATOR; splitSqlScript(resource, script, separator, commentPrefix, blockCommentStartDelimiter, blockCommentEndDelimiter, statements);
appendSeparatorToScriptIfNecessary(scriptBuilder, separator); return scriptBuilder.toString();
/** * 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); }
/** * Read a script from the given resource, using "{@code --}" as the comment prefix * and "{@code ;}" as the statement separator, and build a String containing the lines. * @param resource the {@code EncodedResource} to be read * @return {@code String} containing the script lines * @throws IOException in case of I/O errors */ static String readScript(EncodedResource resource) throws IOException { return readScript(resource, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR); }
@Test public void containsDelimiters() { assertFalse(containsSqlScriptDelimiters("select 1\n select ';'", ";")); assertTrue(containsSqlScriptDelimiters("select 1; select 2", ";")); assertFalse(containsSqlScriptDelimiters("select 1; select '\\n\n';", "\n")); assertTrue(containsSqlScriptDelimiters("select 1\n select 2", "\n")); assertFalse(containsSqlScriptDelimiters("select 1\n select 2", "\n\n")); assertTrue(containsSqlScriptDelimiters("select 1\n\n select 2", "\n\n")); // MySQL style escapes '\\' assertFalse(containsSqlScriptDelimiters("insert into users(first_name, last_name)\nvalues('a\\\\', 'b;')", ";")); assertTrue(containsSqlScriptDelimiters("insert into users(first_name, last_name)\nvalues('Charles', 'd\\'Artagnan'); select 1;", ";")); }
/** * 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); }
/** * Read a script from the provided resource, using the supplied comment prefix * and statement separator, and build a {@code String} containing the lines. * <p>Lines <em>beginning</em> with the comment prefix are excluded from the * results; however, line comments anywhere else — for example, within * a statement — will be included in the results. * @param resource the {@code EncodedResource} containing the script * to be processed * @param commentPrefix the prefix that identifies comments in the SQL script — * typically "--" * @param separator the statement separator in the SQL script — typically ";" * @return a {@code String} containing the script lines * @throws IOException in case of I/O errors */ private static String readScript(EncodedResource resource, @Nullable String commentPrefix, @Nullable String separator) throws IOException { LineNumberReader lnr = new LineNumberReader(resource.getReader()); try { return readScript(lnr, commentPrefix, separator); } finally { lnr.close(); } }
/** * Determine if the provided SQL script contains the specified delimiter. * @param script the SQL script * @param delim character delimiting each statement — typically a ';' character * @return {@code true} if the script contains the delimiter; {@code false} otherwise * @deprecated as of Spring 4.0.3, in favor of using * {@link org.springframework.jdbc.datasource.init.ScriptUtils#containsSqlScriptDelimiters} */ @Deprecated public static boolean containsSqlScriptDelimiters(String script, char delim) { return ScriptUtils.containsSqlScriptDelimiters(script, String.valueOf(delim)); }
/** * {@inheritDoc} * @see #execute(DataSource) */ @Override public void populate(Connection connection) throws ScriptException { Assert.notNull(connection, "Connection must not be null"); for (Resource script : this.scripts) { EncodedResource encodedScript = new EncodedResource(script, this.sqlScriptEncoding); ScriptUtils.executeSqlScript(connection, encodedScript, this.continueOnError, this.ignoreFailedDrops, this.commentPrefix, this.separator, this.blockCommentStartDelimiter, this.blockCommentEndDelimiter); } }
@Test public void splitSqlScriptDelimitedWithNewLineButDefaultDelimiterSpecified() { String statement1 = "do something"; String statement2 = "do something else"; char delim = '\n'; String script = statement1 + delim + statement2 + delim; List<String> statements = new ArrayList<>(); splitSqlScript(script, DEFAULT_STATEMENT_SEPARATOR, statements); assertEquals("wrong number of statements", 1, statements.size()); assertEquals("script should have been 'stripped' but not actually 'split'", script.replace('\n', ' '), statements.get(0)); }
private String readScript(String path) throws Exception { EncodedResource resource = new EncodedResource(new ClassPathResource(path, getClass())); return ScriptUtils.readScript(resource); }
separator = DEFAULT_STATEMENT_SEPARATOR; if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) { separator = FALLBACK_STATEMENT_SEPARATOR;
appendSeparatorToScriptIfNecessary(scriptBuilder, separator); return scriptBuilder.toString();
/** * Execute the given SQL script using default settings for statement * separators, comment delimiters, and exception handling flags. * <p>Statement separators and comments will be removed before executing * individual statements within the supplied script. * <p><strong>Warning</strong>: this method does <em>not</em> release the * provided {@link Connection}. * @param connection the JDBC connection to use to execute the script; already * configured and ready to use * @param resource the resource to load the SQL script from; encoded with the * current platform's default encoding * @throws ScriptException if an error occurred while executing the SQL script * @see #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String) * @see #DEFAULT_STATEMENT_SEPARATOR * @see #DEFAULT_COMMENT_PREFIX * @see #DEFAULT_BLOCK_COMMENT_START_DELIMITER * @see #DEFAULT_BLOCK_COMMENT_END_DELIMITER * @see org.springframework.jdbc.datasource.DataSourceUtils#getConnection * @see org.springframework.jdbc.datasource.DataSourceUtils#releaseConnection */ public static void executeSqlScript(Connection connection, Resource resource) throws ScriptException { executeSqlScript(connection, new EncodedResource(resource)); }
@Test // SPR-13218 public void splitScriptWithSingleQuotesNestedInsideDoubleQuotes() throws Exception { String statement1 = "select '1' as \"Dogbert's owner's\" from dual"; String statement2 = "select '2' as \"Dilbert's\" from dual"; char delim = ';'; String script = statement1 + delim + statement2 + delim; List<String> statements = new ArrayList<>(); splitSqlScript(script, ';', statements); assertEquals("wrong number of statements", 2, statements.size()); assertEquals("statement 1 not split correctly", statement1, statements.get(0)); assertEquals("statement 2 not split correctly", statement2, statements.get(1)); }
/** * Read a script from the given resource, using "{@code --}" as the comment prefix * and "{@code ;}" as the statement separator, and build a String containing the lines. * @param resource the {@code EncodedResource} to be read * @return {@code String} containing the script lines * @throws IOException in case of I/O errors */ static String readScript(EncodedResource resource) throws IOException { return readScript(resource, DEFAULT_COMMENT_PREFIX, DEFAULT_STATEMENT_SEPARATOR); }
@Before public void setUpSchema() throws SQLException { executeSqlScript(db.getConnection(), usersSchema()); }
@Test // SPR-9531 public void readAndSplitScriptContainingMultiLineComments() throws Exception { String script = readScript("test-data-with-multi-line-comments.sql"); List<String> statements = new ArrayList<>(); splitSqlScript(script, ';', statements); String statement1 = "INSERT INTO users(first_name, last_name) VALUES('Juergen', 'Hoeller')"; String statement2 = "INSERT INTO users(first_name, last_name) VALUES( 'Sam' , 'Brannen' )"; assertEquals("wrong number of statements", 2, statements.size()); assertEquals("statement 1 not split correctly", statement1, statements.get(0)); assertEquals("statement 2 not split correctly", statement2, statements.get(1)); }
/** * Read a script from the provided {@code LineNumberReader}, using the supplied * comment prefix, and build a {@code String} containing the lines. * <p>Lines <em>beginning</em> with the comment prefix are excluded from the * results; however, line comments anywhere else — for example, within * a statement — will be included in the results. * @param lineNumberReader the {@code LineNumberReader} containing the script * to be processed * @param commentPrefix the prefix that identifies comments in the SQL script — typically "--" * @return a {@code String} containing the script lines * @deprecated as of Spring 4.0.3, in favor of using * {@link org.springframework.jdbc.datasource.init.ScriptUtils#readScript(LineNumberReader, String, String)} */ @Deprecated public static String readScript(LineNumberReader lineNumberReader, String commentPrefix) throws IOException { return ScriptUtils.readScript(lineNumberReader, commentPrefix, ScriptUtils.DEFAULT_STATEMENT_SEPARATOR); }