Exemple #1
0
  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());
  }
Exemple #7
0
  /** @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);
  }