Examples with SQLBinaryOpExpr com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr used on opensource projects

Example 21 with SQLBinaryOpExpr

use of com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr in project druid by alibaba.

the class MySqlSelectTest_16 method test_0.

public void test_0() throws Exception {
    String sql = "select a from t where not a>1 and not b<1";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());
    {
        SQLExpr where = queryBlock.getWhere();
        Assert.assertTrue(where instanceof SQLBinaryOpExpr);
        SQLBinaryOpExpr binaryWhere = (SQLBinaryOpExpr) where;
        Assert.assertEquals(binaryWhere.getOperator(), SQLBinaryOperator.BooleanAnd);
        Assert.assertTrue(binaryWhere.getLeft() instanceof SQLNotExpr);
        Assert.assertTrue(binaryWhere.getRight() instanceof SQLNotExpr);
    }
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t")));
    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(//
    "SELECT a" + //
    "\nFROM t" + //
    "\nWHERE NOT a > 1" + //
    "\n\tAND NOT b < 1", output);
}

Example 22 with SQLBinaryOpExpr

use of com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr in project druid by alibaba.

the class LargeOrTest method test_largeOr.

public void test_largeOr() throws Exception {
    StringBuffer buf = new StringBuffer();
    buf.append("SELECT 1 FROM T WHERE ID = ?");
    for (int i = 0; i < 10000; ++i) {
        buf.append(" OR ID = ?");
    }
    String sql = buf.toString();
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    SQLSelectQueryBlock select = (SQLSelectQueryBlock) stmt.getSelect().getQuery();
    SQLBinaryOpExpr where = (SQLBinaryOpExpr) select.getWhere();
    SQLBinaryOpExpr last = (SQLBinaryOpExpr) where.getRight();
    Assert.assertEquals(SQLBinaryOperator.Equality, last.getOperator());
}

Example 23 with SQLBinaryOpExpr

use of com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr in project druid by alibaba.

the class BoxTest method test_timestamp.

public void test_timestamp() throws Exception {
    String sql = "box '((0,0),(1,1))' + point '(2.0,0)'";
    PGExprParser parser = new PGExprParser(sql);
    SQLBinaryOpExpr binaryExpr = (SQLBinaryOpExpr) parser.expr();
    PGBoxExpr box = (PGBoxExpr) binaryExpr.getLeft();
    PGPointExpr point = (PGPointExpr) binaryExpr.getRight();
    Assert.assertEquals("BOX '((0,0),(1,1))' + POINT '(2.0,0)'", binaryExpr.toString());
}

Example 24 with SQLBinaryOpExpr

use of com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr in project druid by alibaba.

the class PagerUtils method limitDB2.

private static String limitDB2(SQLSelect select, String dbType, int offset, int count) {
    SQLSelectQuery query = select.getQuery();
    SQLBinaryOpExpr gt = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.GreaterThan, //
    new SQLNumberExpr(offset), JdbcConstants.DB2);
    SQLBinaryOpExpr lteq = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.DB2);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, JdbcConstants.DB2);
    if (query instanceof SQLSelectQueryBlock) {
        DB2SelectQueryBlock queryBlock = (DB2SelectQueryBlock) query;
        if (offset <= 0) {
            queryBlock.setFirst(new SQLNumberExpr(count));
            return SQLUtils.toSQLString(select, dbType);
        }
        SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy == null && select.getQuery() instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock selectQueryBlcok = (SQLSelectQueryBlock) select.getQuery();
            orderBy = selectQueryBlcok.getOrderBy();
            selectQueryBlcok.setOrderBy(null);
        } else {
            select.setOrderBy(null);
        }
        aggregateExpr.setOver(new SQLOver(orderBy));
        queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
        DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
        countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
        countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
        countQueryBlock.setWhere(pageCondition);
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
    SQLOrderBy orderBy = select.getOrderBy();
    aggregateExpr.setOver(new SQLOver(orderBy));
    select.setOrderBy(null);
    countQueryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}

Example 25 with SQLBinaryOpExpr

use of com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr in project druid by alibaba.

the class PagerUtils method limitOracle.

private static String limitOracle(SQLSelect select, String dbType, int offset, int count) {
    SQLSelectQuery query = select.getQuery();
    if (query instanceof SQLSelectQueryBlock) {
        OracleSelectQueryBlock queryBlock = (OracleSelectQueryBlock) query;
        if (queryBlock.getGroupBy() == null && select.getOrderBy() == null && offset <= 0) {
            SQLExpr condition = new //
            SQLBinaryOpExpr(//
            new SQLIdentifierExpr("ROWNUM"), //
            SQLBinaryOperator.LessThanOrEqual, //
            new SQLNumberExpr(count), JdbcConstants.ORACLE);
            if (queryBlock.getWhere() == null) {
                queryBlock.setWhere(condition);
            } else {
                queryBlock.setWhere(new //
                SQLBinaryOpExpr(//
                queryBlock.getWhere(), //
                SQLBinaryOperator.BooleanAnd, //
                condition, JdbcConstants.ORACLE));
            }
            return SQLUtils.toSQLString(select, dbType);
        }
    }
    OracleSelectQueryBlock countQueryBlock = new OracleSelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLIdentifierExpr("ROWNUM"), "RN"));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    countQueryBlock.setWhere(new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.ORACLE));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    OracleSelectQueryBlock offsetQueryBlock = new OracleSelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("RN"), //
    SQLBinaryOperator.GreaterThan, //
    new SQLNumberExpr(offset), JdbcConstants.ORACLE));
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}