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("/Users/hanwenwang/Desktop/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 5.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);
// ProjectOperator projectOperator = new
// ProjectOperator(scanOperator1,plainSelect.getSelectItems());
System.out.println(plainSelect.getWhere().toString());
SelectOperator selectOperator = new SelectOperator(scanOperator1, plainSelect.getWhere());
selectOperator.dump();
}
} catch (Exception e) {
System.err.println("Exception occurred during parsing");
e.printStackTrace();
}
}
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();
}
}
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);
}
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);
}
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 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());
}
/** @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 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);
}
