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); }