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 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 testTop() throws JSQLParserException {
String statement = "SELECT TOP 3 * FROM mytable WHERE mytable.col = 9";
Select select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(3, ((PlainSelect) select.getSelectBody()).getTop().getRowCount());
statement = "select top 5 foo from bar";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(5, ((PlainSelect) select.getSelectBody()).getTop().getRowCount());
}
public static void main(String[] args) {
try {
Statement stat =
new CCJSqlParserManager()
.parse(
new StringReader(
"select xx1,xx2,xx3 from Xtable where (xx1 like 'abc%' escape '\\' or xx1 like 'abc%' or xx1 = 'abc' ) and stime between 1234 and 4567"));
Select select = (Select) stat;
Expression where = ((PlainSelect) select.getSelectBody()).getWhere();
System.out.println();
} catch (JSQLParserException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @brief Visit the statement. This is the function that should be called in the first place.
* @param statement A SQL statement.
*/
public void visit(Statement statement) throws Exception {
if (statement instanceof Select) {
Select select = (Select) statement;
if (select.getWithItemsList() != null) {
for (WithItem withItem : ((Select) statement).getWithItemsList()) {
withItem.accept(this);
}
}
if (select.getSelectBody() != null) {
select.getSelectBody().accept(this);
}
} else if (statement instanceof DescribeTable) {
((DescribeTable) statement).accept(this);
}
// TODO: Add more type support here.
}
@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 void testDouble() throws JSQLParserException {
String statement = "SELECT 1e2, * FROM mytable WHERE mytable.col = 9";
Select select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(
1e2,
((DoubleValue)
((SelectExpressionItem)
((PlainSelect) select.getSelectBody()).getSelectItems().get(0))
.getExpression())
.getValue(),
0);
statement = "SELECT * FROM mytable WHERE mytable.col = 1.e2";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(
1e2,
((DoubleValue)
((BinaryExpression) ((PlainSelect) select.getSelectBody()).getWhere())
.getRightExpression())
.getValue(),
0);
statement = "SELECT * FROM mytable WHERE mytable.col = 1.2e2";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(
1.2e2,
((DoubleValue)
((BinaryExpression) ((PlainSelect) select.getSelectBody()).getWhere())
.getRightExpression())
.getValue(),
0);
statement = "SELECT * FROM mytable WHERE mytable.col = 2e2";
select = (Select) parserManager.parse(new StringReader(statement));
assertEquals(
2e2,
((DoubleValue)
((BinaryExpression) ((PlainSelect) select.getSelectBody()).getWhere())
.getRightExpression())
.getValue(),
0);
}
@Override
public void visit(Select select) {
SelectDeParser selectDeParser = new SelectDeParser();
selectDeParser.setBuffer(buffer);
ExpressionDeParser expressionDeParser = new ExpressionDeParser(selectDeParser, buffer);
selectDeParser.setExpressionVisitor(expressionDeParser);
if (select.getWithItemsList() != null && !select.getWithItemsList().isEmpty()) {
buffer.append("WITH ");
for (Iterator<WithItem> iter = select.getWithItemsList().iterator(); iter.hasNext(); ) {
WithItem withItem = iter.next();
withItem.accept(selectDeParser);
if (iter.hasNext()) {
buffer.append(",");
}
buffer.append(" ");
}
}
select.getSelectBody().accept(selectDeParser);
}
@Override
public void visit(Select arg0) {
Logger.println("Processing a new query");
SqlSelectVisitor sVisitor = new SqlSelectVisitor();
arg0.getSelectBody().accept(sVisitor);
// Output what we have
PhysicalOperator queryOp = sVisitor.getOperator();
outputTuplesFromOperator(queryOp);
}
/**
* 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;
}
/**
* 静态调用接口，获取所有表的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 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);
}
// PlainSelect o Select
@Override
public void visit(Select select) throws Exception {
SelectTranslator selectTranslator = new SelectTranslator(connector);
SelectBody selectBody = select.getSelectBody();
// This handles both: PlainSelect and Union
selectBody.accept(selectTranslator);
// selectTranslator.setBuffer(buffer);
// ExpressionTranslator expressionTranslator = new ExpressionTranslator(selectTranslator);
// selectTranslator.setExpressionVisitor(expressionTranslator);
// if (select.getWithItemsList() != null && !select.getWithItemsList().isEmpty()) {
// buffer.append("WITH ");
// for (Iterator iter = select.getWithItemsList().iterator(); iter.hasNext();) {
// WithItem withItem = (WithItem)iter.next();
// buffer.append(withItem);
// if (iter.hasNext())
// buffer.append(",");
// buffer.append(" ");
// }
// }
// select.getSelectBody().accept(selectTranslator);
}
public void visit(Select select) {
select.getSelectBody().accept(this);
}