public static void main(String[] args) throws IOException {
DatabaseCatalog tableMap = DatabaseCatalog.getInstance();
tableMap.autogetTableInfo();
System.out.println(tableMap.getTableLocated("Sailors"));
Schema schema = Schema.getInstance();
schema.getSchemaInfor("/Users/hanwenwang/Desktop/samples/input/db/schema.txt");
try {
CCJSqlParser parser =
new CCJSqlParser(new FileReader("/Users/hanwenwang/Desktop/queries 2.sql"));
Statement statement;
while ((statement = parser.Statement()) != null) {
Select select = (Select) statement;
System.out.println("Select body is " + select.getSelectBody());
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
System.out.println(plainSelect.getSelectItems().toString());
ScanOperator scanOperator = new ScanOperator(plainSelect.getFromItem().toString());
SelectOperator selectOperator = new SelectOperator(scanOperator, plainSelect.getWhere());
ProjectOperator projectOperator =
new ProjectOperator(selectOperator, plainSelect.getSelectItems());
projectOperator.dump();
}
} catch (Exception e) {
System.err.println("Exception occurred during parsing");
e.printStackTrace();
}
}
@Override
public String toString() {
StringBuilder b = new StringBuilder();
b.append("ALTER TABLE ").append(table.getFullyQualifiedName()).append(" ADD ");
if (columnName != null) {
b.append("COLUMN ").append(columnName).append(" ").append(dataType.toString());
} else if (pkColumns != null) {
b.append("PRIMARY KEY (").append(PlainSelect.getStringList(pkColumns)).append(")");
} else if (ukColumns != null) {
b.append("UNIQUE KEY ")
.append(ukName)
.append(" (")
.append(PlainSelect.getStringList(ukColumns))
.append(")");
} else if (fkColumns != null) {
b.append("FOREIGN KEY (")
.append(PlainSelect.getStringList(fkColumns))
.append(") REFERENCES ")
.append(fkSourceTable)
.append(" (")
.append(PlainSelect.getStringList(fkSourceColumns))
.append(")");
if (isOnDeleteCascade()) {
b.append(" ON DELETE CASCADE");
} else if (isOnDeleteRestrict()) {
b.append(" ON DELETE RESTRICT");
} else if (isOnDeleteSetNull()) {
b.append(" ON DELETE SET NULL");
}
} else if (fkIndex != null) {
b.append(fkIndex);
}
return b.toString();
}
@Override
public String toString() {
String sql = "";
sql = "CREATE " + (unlogged ? "UNLOGGED " : "") + "TABLE " + table;
if (select != null) {
sql += " AS " + select.toString();
} else {
sql += " (";
sql += PlainSelect.getStringList(columnDefinitions, true, false);
if (indexes != null && indexes.size() != 0) {
sql += ", ";
sql += PlainSelect.getStringList(indexes);
}
sql += ")";
String options = PlainSelect.getStringList(tableOptionsStrings, false, false);
if (options != null && options.length() > 0) {
sql += " " + options;
}
}
return sql;
}
public static void main(String[] args) throws IOException {
// get the information of table address
DatabaseCatalog databaseCatalog = DatabaseCatalog.getInstance();
databaseCatalog.autogetTableInfo();
Schema schema = Schema.getInstance();
schema.getSchemaInfor("samples/input/db/schema.txt");
Aliases aliases = Aliases.getInstance();
// aliases.addaliase("S", "Sailors");
// ScanOperator scanOperator = new ScanOperator("S");
// scanOperator.dump();
try {
CCJSqlParser parser =
new CCJSqlParser(new FileReader("/Users/hanwenwang/Desktop/queries.sql"));
Statement statement;
while ((statement = parser.Statement()) != null) {
Select select = (Select) statement;
System.out.println("Select body is " + select.getSelectBody());
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
String aliasName = ((Table) plainSelect.getFromItem()).getAlias();
String tableName = ((Table) plainSelect.getFromItem()).getName();
aliases.addaliase(aliasName, tableName);
ScanOperator scanOperator1 = new ScanOperator(aliasName);
// scanOperator1.dump();
ProjectOperator projectOperator =
new ProjectOperator(scanOperator1, plainSelect.getSelectItems());
projectOperator.dump();
}
} catch (Exception e) {
System.err.println("Exception occurred during parsing");
e.printStackTrace();
}
}
public void testTime() throws JSQLParserException {
String statement = "SELECT * FROM tab1 WHERE a > {t '04:05:34'}";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(
"04:05:34",
(((TimeValue) ((GreaterThan) plainSelect.getWhere()).getRightExpression()).getValue())
.toString());
assertEquals(statement, "" + plainSelect);
}
@SuppressWarnings("unchecked")
@Override
public void visit(PlainSelect select) {
if (!CollectionUtils.isEmpty(select.getGroupByColumnReferences())
|| !CollectionUtils.isEmpty(select.getJoins())) {
isSimple = false;
} else {
select.getFromItem().accept(this);
for (SelectItem item : (List<SelectItem>) select.getSelectItems()) {
item.accept(this);
}
}
}
@Override
public void visit(PlainSelect plainSelect) {
firstRun = true;
counter = 0;
aliases.clear();
for (SelectItem item : plainSelect.getSelectItems()) {
item.accept(this);
}
firstRun = false;
for (SelectItem item : plainSelect.getSelectItems()) {
item.accept(this);
}
}
public void testHaving() throws JSQLParserException {
String statement =
"SELECT MAX(tab1.b) FROM tab1 WHERE a > 34 GROUP BY tab1.b HAVING MAX(tab1.b) > 56";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertTrue(plainSelect.getHaving() instanceof GreaterThan);
assertEquals(statement, "" + plainSelect);
statement = "SELECT MAX(tab1.b) FROM tab1 WHERE a > 34 HAVING MAX(tab1.b) IN (56, 32, 3, ?)";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertTrue(plainSelect.getHaving() instanceof InExpression);
assertEquals(statement, "" + plainSelect);
}
public void deParse(Update update) {
buffer
.append("UPDATE ")
.append(PlainSelect.getStringList(update.getTables(), true, false))
.append(" SET ");
for (int i = 0; i < update.getColumns().size(); i++) {
Column column = update.getColumns().get(i);
buffer.append(column.getFullyQualifiedName()).append(" = ");
Expression expression = update.getExpressions().get(i);
expression.accept(expressionVisitor);
if (i < update.getColumns().size() - 1) {
buffer.append(", ");
}
}
if (update.getFromItem() != null) {
buffer.append(" FROM ").append(update.getFromItem());
if (update.getJoins() != null) {
for (Join join : update.getJoins()) {
if (join.isSimple()) {
buffer.append(", ").append(join);
} else {
buffer.append(" ").append(join);
}
}
}
}
if (update.getWhere() != null) {
buffer.append(" WHERE ");
update.getWhere().accept(expressionVisitor);
}
}
@Override
public String toString() {
return type
+ " "
+ PlainSelect.getStringList(columnsNames, true, true)
+ (name != null ? " " + name : "");
}
public void testDistinct() throws JSQLParserException {
String statement = "SELECT DISTINCT ON (myid) myid, mycol FROM mytable WHERE mytable.col = 9";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(
"myid",
((Column)
((SelectExpressionItem) plainSelect.getDistinct().getOnSelectItems().get(0))
.getExpression())
.getColumnName());
assertEquals(
"mycol",
((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
.getColumnName());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
}
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 testGroupBy() throws JSQLParserException {
String statement = "SELECT * FROM tab1 WHERE a > 34 GROUP BY tab1.b";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(1, plainSelect.getGroupByColumnReferences().size());
assertEquals(
"tab1.b", ((Column) plainSelect.getGroupByColumnReferences().get(0)).getWholeColumnName());
assertEquals(statement, "" + plainSelect);
statement = "SELECT * FROM tab1 WHERE a > 34 GROUP BY 2, 3";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(2, plainSelect.getGroupByColumnReferences().size());
assertEquals(2, ((ColumnIndex) plainSelect.getGroupByColumnReferences().get(0)).getIndex());
assertEquals(3, ((ColumnIndex) plainSelect.getGroupByColumnReferences().get(1)).getIndex());
assertEquals(statement, "" + plainSelect);
}
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 testLike() throws JSQLParserException {
String statement = "SELECT * FROM tab1 WHERE a LIKE 'test'";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(
"test",
(((StringValue) ((LikeExpression) plainSelect.getWhere()).getRightExpression()).getValue())
.toString());
assertEquals(statement, "" + plainSelect);
statement = "SELECT * FROM tab1 WHERE a LIKE 'test' ESCAPE 'test2'";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(
"test",
(((StringValue) ((LikeExpression) plainSelect.getWhere()).getRightExpression()).getValue())
.toString());
assertEquals("test2", (((LikeExpression) plainSelect.getWhere()).getEscape()));
assertEquals(statement, "" + plainSelect);
}
@Override
public void visit(PlainSelect plainSelect) {
log.debug("visit(PlainSelect) " + plainSelect);
enterPlainSelect(plainSelect);
this.visitingPart = VisitingPart.SELECT_ITEM;
List<SelectItem> selectItems = plainSelect.getSelectItems();
if (selectItems != null) for (SelectItem s : selectItems) s.accept(this.expressionNavigator);
log.debug("visit(PlainSelect) done");
leavePlainSelect();
}
/**
* extra order by and set default orderby to null
*
* @param selectBody
*/
public static List<OrderByElement> extraOrderBy(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
List<OrderByElement> orderByElements = ((PlainSelect) selectBody).getOrderByElements();
((PlainSelect) selectBody).setOrderByElements(null);
return orderByElements;
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
if (withItem.getSelectBody() != null) {
return extraOrderBy(withItem.getSelectBody());
}
} else {
SetOperationList operationList = (SetOperationList) selectBody;
if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
List<SelectBody> plainSelects = operationList.getSelects();
return extraOrderBy(plainSelects.get(plainSelects.size() - 1));
}
}
return null;
}
/**
* convert to order by sql
*
* @param sql
* @param orderBy
* @return
*/
public static String converToOrderBySql(String sql, String orderBy) {
// 解析SQL
Statement stmt = null;
try {
stmt = CCJSqlParserUtil.parse(sql);
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
// 处理body-去最外层order by
List<OrderByElement> orderByElements = extraOrderBy(selectBody);
String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
if (defaultOrderBy.indexOf('?') != -1) {
throw new RuntimeException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
}
// 新的sql
sql = select.toString();
} catch (Throwable e) {
e.printStackTrace();
}
return sql + " order by " + orderBy;
}
public void testFrom() throws JSQLParserException {
String statement =
"SELECT * FROM mytable as mytable0, mytable1 alias_tab1, mytable2 as alias_tab2, (SELECT * FROM mytable3) AS mytable4 WHERE mytable.col = 9";
String statementToString =
"SELECT * FROM mytable as mytable0, mytable1 as alias_tab1, mytable2 as alias_tab2, (SELECT * FROM mytable3) AS mytable4 WHERE mytable.col = 9";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(3, plainSelect.getJoins().size());
assertEquals("mytable0", ((Table) plainSelect.getFromItem()).getAlias());
assertEquals("alias_tab1", ((Join) plainSelect.getJoins().get(0)).getRightItem().getAlias());
assertEquals("alias_tab2", ((Join) plainSelect.getJoins().get(1)).getRightItem().getAlias());
assertEquals("mytable4", ((Join) plainSelect.getJoins().get(2)).getRightItem().getAlias());
assertEquals(statementToString.toUpperCase(), plainSelect.toString().toUpperCase());
}
public String toString() {
String selects = "";
String allDistinct = "";
if (isAll()) {
allDistinct = "ALL ";
} else if (isDistinct()) {
allDistinct = "DISTINCT ";
}
for (int i = 0; i < plainSelects.size(); i++) {
selects +=
"("
+ plainSelects.get(i)
+ ((i < plainSelects.size() - 1) ? ") UNION " + allDistinct : ")");
}
return selects
+ ((orderByElements != null) ? PlainSelect.orderByToString(orderByElements) : "")
+ ((limit != null) ? limit + "" : "");
}
/**
* 静态调用接口，获取所有表的ID
*
* @param sql 传入的sql语句
* @return 返回-1表示当前语句不是select语句，返回0表示当前SQL有问题，返回>0的数字表示查找出的表数目
*/
public static int getOriginalTableName(String sql) {
int num_Table = 0;
Statement statement = null;
try {
statement = CCJSqlParserUtil.parse(sql);
if (statement instanceof Select) {
Select selectStatement = (Select) statement;
System.err.println(sql);
PlainSelect plainSelect = null;
plainSelect = (PlainSelect) selectStatement.getSelectBody();
if (plainSelect != null) {
System.out.println("\n-------------------------------------------");
if (plainSelect.getFromItem() != null) {
plainSelect.getFromItem().toString();
num_Table++;
}
System.out.print(plainSelect.getFromItem().toString() + "\t");
if (plainSelect.getJoins() != null) {
for (Join join2 : plainSelect.getJoins()) {
System.out.print(join2.toString() + "\t");
num_Table++;
}
}
System.out.println("\n-------------------------------------------");
} else {
num_Table = -1;
return num_Table;
}
}
} catch (JSQLParserException ex) {
ex.printStackTrace();
num_Table = 0;
LogWriter.println(num_Table);
return num_Table;
}
LogWriter.println("共统计出表个数=>" + num_Table);
return num_Table;
}
public static SeQueryInfo parse(ISession session, PlainSelect select)
throws SeException, IOException {
String[] columns = null;
String[] tables = null;
String where = null;
String orderAndOrGroupByClause = null;
if (LOGGER.isLoggable(Level.FINER)) {
LOGGER.finer("building SeQueryInfo to reflect " + select);
}
// obtain needed SeQueryInfo components
columns = getColumns(session, select.getSelectItems());
tables = getTables(select.getFromItems());
Expression whereClause = select.getWhere();
if (whereClause != null) {
where = whereClause.toString();
}
if (select.getGroupByColumnReferences() != null
&& select.getGroupByColumnReferences().size() > 0) {
String gb = PlainSelect.getFormatedList(select.getGroupByColumnReferences(), " GROUP BY ");
orderAndOrGroupByClause = gb;
}
if (select.getOrderByElements() != null && select.getOrderByElements().size() > 0) {
String ob = PlainSelect.orderByToString(select.getOrderByElements());
if (orderAndOrGroupByClause == null) {
orderAndOrGroupByClause = "";
}
orderAndOrGroupByClause += " " + ob;
}
// build SeQueryInfo
SeQueryInfo qinfo = new SeQueryInfo();
qinfo.setColumns(columns);
SeSqlConstruct sqlConstruct = new SeSqlConstruct();
sqlConstruct.setTables(tables);
if (where != null) {
sqlConstruct.setWhere(where);
}
qinfo.setConstruct(sqlConstruct);
if (orderAndOrGroupByClause != null) {
qinfo.setByClause(orderAndOrGroupByClause);
}
return qinfo;
}
public void testJoin() throws JSQLParserException {
String statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(1, plainSelect.getJoins().size());
assertEquals(
"tab2",
((Table) ((Join) plainSelect.getJoins().get(0)).getRightItem()).getWholeTableName());
assertEquals(
"tab1.id",
((Column)
((EqualsTo) ((Join) plainSelect.getJoins().get(0)).getOnExpression())
.getLeftExpression())
.getWholeColumnName());
assertTrue(((Join) plainSelect.getJoins().get(0)).isOuter());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(2, plainSelect.getJoins().size());
assertEquals(
"tab3",
((Table) ((Join) plainSelect.getJoins().get(1)).getRightItem()).getWholeTableName());
assertFalse(((Join) plainSelect.getJoins().get(1)).isOuter());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id JOIN tab3";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(2, plainSelect.getJoins().size());
assertEquals(
"tab3",
((Table) ((Join) plainSelect.getJoins().get(1)).getRightItem()).getWholeTableName());
assertFalse(((Join) plainSelect.getJoins().get(1)).isOuter());
// implicit INNER
statement = "SELECT * FROM tab1 LEFT outer JOIN tab2 ON tab1.id = tab2.id INNER JOIN tab3";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement =
"SELECT * FROM TA2 LEFT outer JOIN O USING (col1, col2) where D.OasSD = 'asdf' And (kj >= 4 OR l < 'sdf')";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT * FROM tab1 INNER JOIN tab2 USING (id, id2)";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(1, plainSelect.getJoins().size());
assertEquals(
"tab2",
((Table) ((Join) plainSelect.getJoins().get(0)).getRightItem()).getWholeTableName());
assertFalse(((Join) plainSelect.getJoins().get(0)).isOuter());
assertEquals(2, ((Join) plainSelect.getJoins().get(0)).getUsingColumns().size());
assertEquals(
"id2",
((Column) ((Join) plainSelect.getJoins().get(0)).getUsingColumns().get(1))
.getWholeColumnName());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT * FROM tab1 RIGHT OUTER JOIN tab2 USING (id, id2)";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement =
"select * from foo as f LEFT INNER JOIN (bar as b RIGHT OUTER JOIN baz as z ON f.id = z.id) ON f.id = b.id";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
}
/** @brief The following functions override functions of the interfaces. */
public void visit(PlainSelect plainSelect) throws Exception {
if (plainSelect.getFromItem() != null) {
if (plainSelect.getFromItem().getAlias() != null) {
this.aliasTableNameList.add(plainSelect.getFromItem().getAlias().getName());
}
plainSelect.getFromItem().accept(this);
}
if (plainSelect.getJoins() != null) {
for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext(); ) {
Join join = (Join) joinsIt.next();
if (join.getRightItem().getAlias() != null) {
this.aliasTableNameList.add(join.getRightItem().getAlias().getName());
}
if (join.getOnExpression() != null) {
join.getOnExpression().accept(this);
}
join.getRightItem().accept(this);
}
}
// Select selectItem From fromItem, joinItem Where whereClause.
if (plainSelect.getSelectItems() != null) {
for (SelectItem selectItem : plainSelect.getSelectItems()) {
selectItem.accept(this);
}
}
if (plainSelect.getWhere() != null) {
plainSelect.getWhere().accept(this);
}
if (plainSelect.getGroupByColumnReferences() != null) {
for (Iterator groupByIt = plainSelect.getGroupByColumnReferences().iterator();
groupByIt.hasNext(); ) {
Expression groupBy = (Expression) groupByIt.next();
groupBy.accept(this);
}
}
if (plainSelect.getClusterByElements() != null) {
for (Iterator clusterByit = plainSelect.getClusterByElements().iterator();
clusterByit.hasNext(); ) {
ClusterByElement clusterByElement = (ClusterByElement) clusterByit.next();
visit(clusterByElement);
}
}
if (plainSelect.getDistributeByElements() != null) {
for (Iterator distributeByIt = plainSelect.getDistributeByElements().iterator();
distributeByIt.hasNext(); ) {
DistributeByElement distributeByElement = (DistributeByElement) distributeByIt.next();
visit(distributeByElement);
}
}
if (plainSelect.getOrderByElements() != null) {
for (Iterator orderByIt = plainSelect.getOrderByElements().iterator();
orderByIt.hasNext(); ) {
OrderByElement orderByElement = (OrderByElement) orderByIt.next();
orderByElement.accept(this);
}
}
if (plainSelect.getSortByElements() != null) {
for (Iterator sortByIt = plainSelect.getSortByElements().iterator(); sortByIt.hasNext(); ) {
SortByElement sortByElement = (SortByElement) sortByIt.next();
visit(sortByElement);
}
}
if (plainSelect.getHaving() != null) {
plainSelect.getHaving().accept(this);
}
}
public void testFunctions() throws JSQLParserException {
String statement = "SELECT MAX(id) as max FROM mytable WHERE mytable.col = 9";
PlainSelect select =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals("max", ((SelectExpressionItem) select.getSelectItems().get(0)).getAlias());
assertEquals(statement.toUpperCase(), select.toString().toUpperCase());
statement = "SELECT MAX(id), AVG(pro) as myavg FROM mytable WHERE mytable.col = 9 GROUP BY pro";
select =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals("myavg", ((SelectExpressionItem) select.getSelectItems().get(1)).getAlias());
assertEquals(statement.toUpperCase(), select.toString().toUpperCase());
statement = "SELECT MAX(a, b, c), COUNT(*), D FROM tab1 GROUP BY D";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
Function fun =
(Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
assertEquals("MAX", fun.getName());
assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
assertTrue(
((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
.isAllColumns());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT {fn MAX(a, b, c)}, COUNT(*), D FROM tab1 GROUP BY D";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
assertTrue(fun.isEscaped());
assertEquals("MAX", fun.getName());
assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
assertTrue(
((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
.isAllColumns());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
statement = "SELECT ab.MAX(a, b, c), cd.COUNT(*), D FROM tab1 GROUP BY D";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
assertEquals("ab.MAX", fun.getName());
assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression();
assertEquals("cd.COUNT", fun.getName());
assertTrue(fun.isAllColumns());
assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
}
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);
}
public void testSelectItems() throws JSQLParserException {
String statement =
"SELECT myid AS MYID, mycol, tab.*, schema.tab.*, mytab.mycol2, myschema.mytab.mycol, myschema.mytab.* FROM mytable WHERE mytable.col = 9";
PlainSelect plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals("MYID", ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getAlias());
assertEquals(
"mycol",
((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
.getColumnName());
assertEquals(
"tab", ((AllTableColumns) plainSelect.getSelectItems().get(2)).getTable().getName());
assertEquals(
"schema",
((AllTableColumns) plainSelect.getSelectItems().get(3)).getTable().getSchemaName());
assertEquals(
"schema.tab",
((AllTableColumns) plainSelect.getSelectItems().get(3)).getTable().getWholeTableName());
assertEquals(
"mytab.mycol2",
((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(4)).getExpression())
.getWholeColumnName());
assertEquals(
"myschema.mytab.mycol",
((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(5)).getExpression())
.getWholeColumnName());
assertEquals(
"myschema.mytab",
((AllTableColumns) plainSelect.getSelectItems().get(6)).getTable().getWholeTableName());
assertEquals(statement, "" + plainSelect);
statement =
"SELECT myid AS MYID, (SELECT MAX(ID) AS myid2 FROM mytable2) AS myalias FROM mytable WHERE mytable.col = 9";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals(
"myalias", ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getAlias());
assertEquals(statement, "" + plainSelect);
statement = "SELECT (myid + myid2) AS MYID FROM mytable WHERE mytable.col = 9";
plainSelect =
(PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
assertEquals("MYID", ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getAlias());
assertEquals(statement, "" + plainSelect);
}