public void testExists() throws JSQLParserException {
String statement = "SELECT * FROM tab1 WHERE";
String where = " EXISTS (SELECT * FROM tab2)";
statement += where;
Statement parsed = parserManager.parse(new StringReader(statement));
assertEquals(statement, "" + parsed);
PlainSelect plainSelect = (PlainSelect) ((Select) parsed).getSelectBody();
ExpressionDeParser expressionDeParser = new ExpressionDeParser();
StringBuffer stringBuffer = new StringBuffer();
expressionDeParser.setBuffer(stringBuffer);
SelectDeParser deParser = new SelectDeParser(expressionDeParser, stringBuffer);
expressionDeParser.setSelectVisitor(deParser);
plainSelect.getWhere().accept(expressionDeParser);
assertEquals(where, stringBuffer.toString());
}
public void testOrderBy() throws JSQLParserException {
// TODO: should there be a DESC marker in the OrderByElement class?
String statement =
"SELECT * FROM tab1 WHERE a > 34 GROUP BY tab1.b ORDER BY tab1.a DESC, tab1.b ASC";
String statementToString =
"SELECT * FROM tab1 WHERE a > 34 GROUP BY tab1.b ORDER BY tab1.a DESC, tab1.b";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(2, plainSelect.getOrderByElements().size());
assertEquals(
"tab1.a",
((Column) ((OrderByElement) plainSelect.getOrderByElements().get(0)).getColumnReference())
.getWholeColumnName());
assertEquals(
"b",
((Column) ((OrderByElement) plainSelect.getOrderByElements().get(1)).getColumnReference())
.getColumnName());
assertTrue(((OrderByElement) plainSelect.getOrderByElements().get(1)).isAsc());
assertFalse(((OrderByElement) plainSelect.getOrderByElements().get(0)).isAsc());
assertEquals(statementToString, "" + plainSelect);
ExpressionDeParser expressionDeParser = new ExpressionDeParser();
StringBuffer stringBuffer = new StringBuffer();
SelectDeParser deParser = new SelectDeParser(expressionDeParser, stringBuffer);
expressionDeParser.setSelectVisitor(deParser);
expressionDeParser.setBuffer(stringBuffer);
plainSelect.accept(deParser);
assertEquals(statement, stringBuffer.toString());
statement = "SELECT * FROM tab1 WHERE a > 34 GROUP BY tab1.b ORDER BY tab1.a, 2";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(2, plainSelect.getOrderByElements().size());
assertEquals(
"a",
((Column) ((OrderByElement) plainSelect.getOrderByElements().get(0)).getColumnReference())
.getColumnName());
assertEquals(
2,
((ColumnIndex)
((OrderByElement) plainSelect.getOrderByElements().get(1)).getColumnReference())
.getIndex());
assertEquals(statement, "" + plainSelect);
}
public void testWhere() throws JSQLParserException {
String statement = "SELECT * FROM tab1 WHERE ";
String whereToString = "(a + b + c / d + e * f) * (a / b * (a + b)) > ?";
PlainSelect plainSelect =
(PlainSelect)
((Select) parserManager.parse(new StringReader(statement + whereToString)))
.getSelectBody();
assertTrue(plainSelect.getWhere() instanceof GreaterThan);
assertTrue(
((GreaterThan) plainSelect.getWhere()).getLeftExpression() instanceof Multiplication);
assertEquals(statement + whereToString, "" + plainSelect);
ExpressionDeParser expressionDeParser = new ExpressionDeParser();
StringBuffer stringBuffer = new StringBuffer();
expressionDeParser.setBuffer(stringBuffer);
plainSelect.getWhere().accept(expressionDeParser);
assertEquals(whereToString, stringBuffer.toString());
whereToString = "(7 * s + 9 / 3) NOT BETWEEN 3 AND ?";
plainSelect =
(PlainSelect)
((Select) parserManager.parse(new StringReader(statement + whereToString)))
.getSelectBody();
stringBuffer = new StringBuffer();
expressionDeParser.setBuffer(stringBuffer);
plainSelect.getWhere().accept(expressionDeParser);
assertEquals(whereToString, stringBuffer.toString());
assertEquals(statement + whereToString, "" + plainSelect);
whereToString = "a / b NOT IN (?, 's''adf', 234.2)";
plainSelect =
(PlainSelect)
((Select) parserManager.parse(new StringReader(statement + whereToString)))
.getSelectBody();
stringBuffer = new StringBuffer();
expressionDeParser.setBuffer(stringBuffer);
plainSelect.getWhere().accept(expressionDeParser);
assertEquals(whereToString, stringBuffer.toString());
assertEquals(statement + whereToString, "" + plainSelect);
whereToString = "NOT 0 = 0";
plainSelect =
(PlainSelect)
((Select) parserManager.parse(new StringReader(statement + whereToString)))
.getSelectBody();
String where = " NOT (0 = 0)";
whereToString = "NOT (0 = 0)";
plainSelect =
(PlainSelect)
((Select) parserManager.parse(new StringReader(statement + whereToString)))
.getSelectBody();
stringBuffer = new StringBuffer();
expressionDeParser.setBuffer(stringBuffer);
plainSelect.getWhere().accept(expressionDeParser);
assertEquals(where, stringBuffer.toString());
assertEquals(statement + whereToString, "" + plainSelect);
}