SqlSplitter { public List<String> splitSql(String text) { text = text.trim(); List<String> queries = new ArrayList<>(); StringBuilder query = new StringBuilder(); char character; boolean multiLineComment = false; boolean singleLineComment = false; boolean singleQuoteString = false; boolean doubleQuoteString = false; for (int index = 0; index < text.length(); index++) { character = text.charAt(index); if (singleLineComment && (character == '\n')) { singleLineComment = false; if (query.toString().trim().isEmpty()) { continue; } } if (multiLineComment && (index - 1) >= 0 && text.charAt(index - 1) == '/' && (index - 2) >= 0 && text.charAt(index - 2) == '*') { multiLineComment = false; } if (character == '\'') { if (singleQuoteString) { singleQuoteString = false; } else if (!doubleQuoteString) { singleQuoteString = true; } } if (character == '"') { if (doubleQuoteString && index > 0) { doubleQuoteString = false; } else if (!singleQuoteString) { doubleQuoteString = true; } } if (!singleQuoteString && !doubleQuoteString && !multiLineComment && !singleLineComment && text.length() > (index + 1)) { if (isSingleLineComment(text.charAt(index), text.charAt(index + 1))) { singleLineComment = true; } else if (text.charAt(index) == '/' && text.length() > (index + 2) && text.charAt(index + 1) == '*' && text.charAt(index + 2) != '+') { multiLineComment = true; } } if (character == ';' && !singleQuoteString && !doubleQuoteString && !multiLineComment && !singleLineComment) { if (!query.toString().trim().isEmpty()) { queries.add(query.toString().trim()); query = new StringBuilder(); } } else if (index == (text.length() - 1)) { if (!singleLineComment && !multiLineComment) { query.append(character); } if (!query.toString().trim().isEmpty()) { queries.add(query.toString().trim()); query = new StringBuilder(); } } else if (!singleLineComment && !multiLineComment) { query.append(character); } } return queries; } SqlSplitter();  SqlSplitter(String... additionalSingleCommentPrefixList); List<String> splitSql(String text);  }
@Test public void testNormalSql() { SqlSplitter sqlSplitter = new SqlSplitter(); List<String> sqls = sqlSplitter.splitSql("show tables"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\nselect * from table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("select * from table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("show\ntables;\nselect * \nfrom table_1"); assertEquals(2, sqls.size()); assertEquals("show\ntables", sqls.get(0)); assertEquals("select * \nfrom table_1", sqls.get(1)); }
@Test public void testSingleLineComment() { SqlSplitter sqlSplitter = new SqlSplitter(); List<String> sqls = sqlSplitter.splitSql("show tables;\n--comment_1"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n--comment_1"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n--comment_1;"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n--comment_1;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("--comment_1;\nshow tables"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("--comment_1;\nshow tables;\n--comment_2"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("show tables;\n--comment_1;\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("select a\nfrom table_1;\ndescribe table_1;--comment_1"); assertEquals(2, sqls.size()); assertEquals("select a\nfrom table_1", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("--comment_1;\n--comment_2\n"); assertEquals(0, sqls.size()); sqls = sqlSplitter.splitSql("select a -- comment\n from table_1"); assertEquals(1, sqls.size()); assertEquals("select a \n from table_1", sqls.get(0)); sqls = sqlSplitter.splitSql("--comment 1\nselect a from table_1\n--comment 2"); assertEquals(1, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); sqls = sqlSplitter.splitSql("--comment 1\nselect a from table_1;\n--comment 2\nselect b from table_1"); assertEquals(2, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); assertEquals("select b from table_1", sqls.get(1)); }
@Test public void testMultipleLineComment() { SqlSplitter sqlSplitter = new SqlSplitter(); List<String> sqls = sqlSplitter.splitSql("show tables;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("\nshow tables"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("\nshow tables;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("select a\nfrom table_1;\ndescribe table_1;"); assertEquals(2, sqls.size()); assertEquals("select a\nfrom table_1", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("\n\n"); assertEquals(0, sqls.size()); sqls = sqlSplitter.splitSql("select a from table_1"); assertEquals(1, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); sqls = sqlSplitter.splitSql("\nselect a from table_1\n"); assertEquals(1, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); sqls = sqlSplitter.splitSql("\nselect a from table_1;\nselect b from table_1"); assertEquals(2, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); assertEquals("select b from table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("\nselect a from table_1;\nselect b from table_1"); assertEquals(2, sqls.size()); assertEquals("select a from table_1", sqls.get(0)); assertEquals("select b from table_1", sqls.get(1)); }
@Test public void testInvalidSql() { SqlSplitter sqlSplitter = new SqlSplitter(); List<String> sqls = sqlSplitter.splitSql("select a from table_1 where a=' and b=1"); assertEquals(1, sqls.size()); assertEquals("select a from table_1 where a=' and b=1", sqls.get(0)); sqls = sqlSplitter.splitSql("--comment_1;\nselect a from table_1 where a=' and b=1"); assertEquals(1, sqls.size()); assertEquals("select a from table_1 where a=' and b=1", sqls.get(0)); sqls = sqlSplitter.splitSql("select a from table_1 where a=' and b=1;\n--comment_1"); assertEquals(1, sqls.size()); assertEquals("select a from table_1 where a=' and b=1;\n--comment_1", sqls.get(0)); }
@Test public void testComplexSql() { SqlSplitter sqlSplitter = new SqlSplitter(); String text = "\n" + "-- \n" + "\n" + "-- a ; b\n" + "select * from test_table WHERE ID = ';--';\n" + "select * from test_table WHERE ID = '/*'; -- test"; List<String> sqls = sqlSplitter.splitSql(text); assertEquals(3, sqls.size()); assertEquals("select * from test_table", sqls.get(0)); assertEquals("select * from test_table WHERE ID = ';--'", sqls.get(1)); assertEquals("select * from test_table WHERE ID = '/*'", sqls.get(2)); }
@Test public void testCustomSplitter_1() { SqlSplitter sqlSplitter = new SqlSplitter(" List<String> sqls = sqlSplitter.splitSql("show tables;\n assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql(" assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql(" assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("show tables;\n assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("select a\nfrom table_1;\ndescribe table_1; assertEquals(2, sqls.size()); assertEquals("select a\nfrom table_1", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql(" assertEquals(0, sqls.size()); sqls = sqlSplitter.splitSql("select a assertEquals(1, sqls.size()); assertEquals("select a \n from table_1", sqls.get(0)); }
@Test public void testCustomSplitter_2() { SqlSplitter sqlSplitter = new SqlSplitter("#"); List<String> sqls = sqlSplitter.splitSql("show tables;\n#comment_1"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n#comment_1"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n#comment_1;"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\n#comment_1;\n"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("#comment_1;\nshow tables"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("#comment_1;\nshow tables;\n#comment_2"); assertEquals(1, sqls.size()); assertEquals("show tables", sqls.get(0)); sqls = sqlSplitter.splitSql("show tables;\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("show tables;\n#comment_1;\ndescribe table_1"); assertEquals(2, sqls.size()); assertEquals("show tables", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("select a\nfrom table_1;\ndescribe table_1;#comment_1"); assertEquals(2, sqls.size()); assertEquals("select a\nfrom table_1", sqls.get(0)); assertEquals("describe table_1", sqls.get(1)); sqls = sqlSplitter.splitSql("#comment_1;\n#comment_2\n"); assertEquals(0, sqls.size()); sqls = sqlSplitter.splitSql("select a # comment\n from table_1"); assertEquals(1, sqls.size()); assertEquals("select a \n from table_1", sqls.get(0)); }