Examples with MySqlSelectQueryBlock com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock used on opensource projects

Example 11 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project druid by alibaba.

the class MySqlSelectTest_POSITION method test_0.

public void test_0() throws Exception {
    String sql = //
    "select a.rule_id, " + //
    "b.object_id, " + //
    "a.user_nick, " + //
    "SUBSTRING(b.content, POSITION('bizOrderId' IN b.content), 30) as dingdanhao, " + //
    "SUBSTRING(b.content, POSITION('loginIn7Days' IN b.content), 88) as shengdajieguo, " + //
    "a.action_time " + //
    "from ctu_scheme_0025 a, " + "ctu_scheme_detail_0025 b";
    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());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(5, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ctu_scheme_0025")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ctu_scheme_detail_0025")));
}

Example 12 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project druid by alibaba.

the class MySqlSelectTest_10 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT * FROM t_department  WHERE name IN ('0000','4444') ORDER BY name ASC";
    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.assertNotNull(queryBlock.getOrderBy());
    //        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(1, visitor.getConditions().size());
    Assert.assertEquals(1, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t_department")));
}

Example 13 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project druid by alibaba.

the class MySqlSelectTest_14 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);";
    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());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(//
    "SELECT *" + //
    "\nFROM t1" + //
    "\nWHERE column1 = (" + //
    "\n\tSELECT column1" + //
    "\n\tFROM t2" + //
    "\n\t)", output);
    String output_lcase = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
    Assert.assertEquals(//
    "select *" + //
    "\nfrom t1" + //
    "\nwhere column1 = (" + //
    "\n\tselect column1" + //
    "\n\tfrom t2" + //
    "\n\t)", output_lcase);
}

Example 14 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project druid by alibaba.

the class MySqlSelectTest_group_concat method test_0.

public void test_0() throws Exception {
    String sql = //
    "SELECT GROUP_CONCAT(ext_customer_id Separator '.') " + //
    "from ad_texts " + //
    "where customer_id=13001 " + //
    "and description1 like '%爱丽%' " + "order by id asc;";
    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.assertNotNull(queryBlock.getOrderBy());
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertEquals(1, visitor.getOrderByColumns().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ad_texts")));
    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(//
    "SELECT GROUP_CONCAT(ext_customer_id SEPARATOR '.')" + //
    "\nFROM ad_texts" + //
    "\nWHERE customer_id = 13001" + //
    "\n\tAND description1 LIKE '%爱丽%'" + //
    "\nORDER BY id ASC", output);
}

Example 15 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class MongoSQLParser method query.

public MongoData query() throws MongoSQLException {
    if (!(statement instanceof SQLSelectStatement)) {
        //return null;
        throw new IllegalArgumentException("not a query sql statement");
    }
    MongoData mongo = new MongoData();
    DBCursor c = null;
    SQLSelectStatement selectStmt = (SQLSelectStatement) statement;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    int icount = 0;
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        BasicDBObject fields = new BasicDBObject();
        //显示的字段
        for (SQLSelectItem item : mysqlSelectQuery.getSelectList()) {
            //System.out.println(item.toString());
            if (!(item.getExpr() instanceof SQLAllColumnExpr)) {
                if (item.getExpr() instanceof SQLAggregateExpr) {
                    SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
                    if (expr.getMethodName().equals("COUNT")) {
                        icount = 1;
                        mongo.setField(getExprFieldName(expr), Types.BIGINT);
                    }
                    fields.put(getExprFieldName(expr), Integer.valueOf(1));
                } else {
                    fields.put(getFieldName(item), Integer.valueOf(1));
                }
            }
        }
        //表名
        SQLTableSource table = mysqlSelectQuery.getFrom();
        DBCollection coll = this._db.getCollection(table.toString());
        mongo.setTable(table.toString());
        SQLExpr expr = mysqlSelectQuery.getWhere();
        DBObject query = parserWhere(expr);
        //System.out.println(query);
        SQLSelectGroupByClause groupby = mysqlSelectQuery.getGroupBy();
        BasicDBObject gbkey = new BasicDBObject();
        if (groupby != null) {
            for (SQLExpr gbexpr : groupby.getItems()) {
                if (gbexpr instanceof SQLIdentifierExpr) {
                    String name = ((SQLIdentifierExpr) gbexpr).getName();
                    gbkey.put(name, Integer.valueOf(1));
                }
            }
            icount = 2;
        }
        int limitoff = 0;
        int limitnum = 0;
        if (mysqlSelectQuery.getLimit() != null) {
            limitoff = getSQLExprToInt(mysqlSelectQuery.getLimit().getOffset());
            limitnum = getSQLExprToInt(mysqlSelectQuery.getLimit().getRowCount());
        }
        if (icount == 1) {
            mongo.setCount(coll.count(query));
        } else if (icount == 2) {
            BasicDBObject initial = new BasicDBObject();
            initial.put("num", 0);
            String reduce = "function (obj, prev) { " + "  prev.num++}";
            mongo.setGrouyBy(coll.group(gbkey, query, initial, reduce));
        } else {
            if ((limitoff > 0) || (limitnum > 0)) {
                c = coll.find(query, fields).skip(limitoff).limit(limitnum);
            } else {
                c = coll.find(query, fields);
            }
            SQLOrderBy orderby = mysqlSelectQuery.getOrderBy();
            if (orderby != null) {
                BasicDBObject order = new BasicDBObject();
                for (int i = 0; i < orderby.getItems().size(); i++) {
                    SQLSelectOrderByItem orderitem = orderby.getItems().get(i);
                    order.put(orderitem.getExpr().toString(), Integer.valueOf(getSQLExprToAsc(orderitem.getType())));
                }
                c.sort(order);
            // System.out.println(order);
            }
        }
        mongo.setCursor(c);
    }
    return mongo;
}