public void testLimit() throws JSQLParserException {
String statement = "SELECT * FROM mytable WHERE mytable.col = 9 LIMIT 3, ?";
Select select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(3, ((PlainSelect) select.getSelectBody()).getLimit().getOffset());
assertTrue(((PlainSelect) select.getSelectBody()).getLimit().isRowCountJdbcParameter());
assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isOffsetJdbcParameter());
assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isLimitAll());
// toString uses standard syntax
statement = "SELECT * FROM mytable WHERE mytable.col = 9 LIMIT ? OFFSET 3";
assertEquals(statement, "" + select);
statement = "SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(0, ((PlainSelect) select.getSelectBody()).getLimit().getRowCount());
assertTrue(((PlainSelect) select.getSelectBody()).getLimit().isOffsetJdbcParameter());
assertFalse(((PlainSelect) select.getSelectBody()).getLimit().isLimitAll());
assertEquals(statement, "" + select);
statement =
"(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION "
+ "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) LIMIT 3, 4";
select = (Select) parserManager.parse(new StringReader(statement));
Union union = (Union) select.getSelectBody();
assertEquals(3, union.getLimit().getOffset());
assertEquals(4, union.getLimit().getRowCount());
// toString uses standard syntax
statement =
"(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION "
+ "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) LIMIT 4 OFFSET 3";
assertEquals(statement, "" + select);
statement =
"(SELECT * FROM mytable WHERE mytable.col = 9 OFFSET ?) UNION ALL "
+ "(SELECT * FROM mytable2 WHERE mytable2.col = 9 OFFSET ?) UNION ALL "
+ "(SELECT * FROM mytable3 WHERE mytable4.col = 9 OFFSET ?) LIMIT 4 OFFSET 3";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(statement, "" + select);
}
public void testUnion() throws JSQLParserException {
String statement =
"SELECT * FROM mytable WHERE mytable.col = 9 UNION "
+ "SELECT * FROM mytable3 WHERE mytable3.col = ? UNION "
+ "SELECT * FROM mytable2 LIMIT 3,4";
Union union =
(Union) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(3, union.getPlainSelects().size());
assertEquals(
"mytable",
((Table) ((PlainSelect) union.getPlainSelects().get(0)).getFromItem()).getName());
assertEquals(
"mytable3",
((Table) ((PlainSelect) union.getPlainSelects().get(1)).getFromItem()).getName());
assertEquals(
"mytable2",
((Table) ((PlainSelect) union.getPlainSelects().get(2)).getFromItem()).getName());
assertEquals(3, ((PlainSelect) union.getPlainSelects().get(2)).getLimit().getOffset());
// use brakets for toString
// use standard limit syntax
String statementToString =
"(SELECT * FROM mytable WHERE mytable.col = 9) UNION "
+ "(SELECT * FROM mytable3 WHERE mytable3.col = ?) UNION "
+ "(SELECT * FROM mytable2 LIMIT 4 OFFSET 3)";
assertEquals(statementToString, "" + union);
}