/** * 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); }
/** * 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); }
@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)); }
@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)); }
splitSqlScript(resource, script, separator, commentPrefix, blockCommentStartDelimiter, blockCommentEndDelimiter, statements);
@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)); }
@Test // SPR-11560 public void readAndSplitScriptWithMultipleNewlinesAsSeparator() throws Exception { String script = readScript("db-test-data-multi-newline.sql"); List<String> statements = new ArrayList<>(); splitSqlScript(script, "\n\n", statements); String statement1 = "insert into T_TEST (NAME) values ('Keith')"; String statement2 = "insert into T_TEST (NAME) values ('Dave')"; 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)); }
@Test public void splitSqlScriptDelimitedWithNewLine() { String statement1 = "insert into customer (id, name) values (1, 'Rod ; Johnson'), (2, 'Adrian \n Collier')"; String statement2 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; String statement3 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; char delim = '\n'; String script = statement1 + delim + statement2 + delim + statement3 + delim; List<String> statements = new ArrayList<>(); splitSqlScript(script, delim, statements); assertEquals("wrong number of statements", 3, statements.size()); assertEquals("statement 1 not split correctly", statement1, statements.get(0)); assertEquals("statement 2 not split correctly", statement2, statements.get(1)); assertEquals("statement 3 not split correctly", statement3, statements.get(2)); }
@Test // SPR-10330 public void readAndSplitScriptContainingCommentsWithLeadingTabs() throws Exception { String script = readScript("test-data-with-comments-and-leading-tabs.sql"); List<String> statements = new ArrayList<>(); splitSqlScript(script, ';', statements); String statement1 = "insert into customer (id, name) values (1, 'Sam Brannen')"; String statement2 = "insert into orders(id, order_date, customer_id) values (1, '2013-06-08', 1)"; String statement3 = "insert into orders(id, order_date, customer_id) values (2, '2013-06-08', 1)"; assertEquals("wrong number of statements", 3, statements.size()); assertEquals("statement 1 not split correctly", statement1, statements.get(0)); assertEquals("statement 2 not split correctly", statement2, statements.get(1)); assertEquals("statement 3 not split correctly", statement3, statements.get(2)); }
@Test public void splitSqlScriptDelimitedWithSemicolon() { String rawStatement1 = "insert into customer (id, name)\nvalues (1, 'Rod ; Johnson'), (2, 'Adrian \n Collier')"; String cleanedStatement1 = "insert into customer (id, name) values (1, 'Rod ; Johnson'), (2, 'Adrian \n Collier')"; String rawStatement2 = "insert into orders(id, order_date, customer_id)\nvalues (1, '2008-01-02', 2)"; String cleanedStatement2 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; String rawStatement3 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; String cleanedStatement3 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; char delim = ';'; String script = rawStatement1 + delim + rawStatement2 + delim + rawStatement3 + delim; List<String> statements = new ArrayList<>(); splitSqlScript(script, delim, statements); assertEquals("wrong number of statements", 3, statements.size()); assertEquals("statement 1 not split correctly", cleanedStatement1, statements.get(0)); assertEquals("statement 2 not split correctly", cleanedStatement2, statements.get(1)); assertEquals("statement 3 not split correctly", cleanedStatement3, statements.get(2)); }
@Test public void readAndSplitScriptContainingComments() throws Exception { String script = readScript("test-data-with-comments.sql"); List<String> statements = new ArrayList<>(); splitSqlScript(script, ';', statements); String statement1 = "insert into customer (id, name) values (1, 'Rod; Johnson'), (2, 'Adrian Collier')"; String statement2 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; String statement3 = "insert into orders(id, order_date, customer_id) values (1, '2008-01-02', 2)"; // Statement 4 addresses the error described in SPR-9982. String statement4 = "INSERT INTO persons( person_id , name) VALUES( 1 , 'Name' )"; assertEquals("wrong number of statements", 4, statements.size()); assertEquals("statement 1 not split correctly", statement1, statements.get(0)); assertEquals("statement 2 not split correctly", statement2, statements.get(1)); assertEquals("statement 3 not split correctly", statement3, statements.get(2)); assertEquals("statement 4 not split correctly", statement4, statements.get(3)); }
/** * 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); }
/** * 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); }
/** * 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 a statement, "{@code --}" 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 statement. In addition, multiple adjacent * whitespace characters will be collapsed into a single space. * @param script the SQL script * @param delim character delimiting each statement — typically a ';' character * @param statements the list that will contain the individual statements * @deprecated as of Spring 4.0.3, in favor of using * {@link org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript(String, char, List)} */ @Deprecated public static void splitSqlScript(String script, char delim, List<String> statements) { ScriptUtils.splitSqlScript(script, delim, statements); }