Search in sources :

Example 66 with MySqlSelectQueryBlock

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

the class SqlParser method parse.

/**
 * 解析sql
 *
 * @param sql sql
 * @return 视图对象
 */
public static SchemaItem parse(String sql) {
    try {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
        MySqlSelectQueryBlock sqlSelectQueryBlock = (MySqlSelectQueryBlock) statement.getSelect().getQuery();
        SchemaItem schemaItem = new SchemaItem();
        schemaItem.setSql(SQLUtils.toMySqlString(sqlSelectQueryBlock));
        SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
        List<TableItem> tableItems = new ArrayList<>();
        SqlParser.visitSelectTable(schemaItem, sqlTableSource, tableItems, null);
        tableItems.forEach(tableItem -> schemaItem.getAliasTableItems().put(tableItem.getAlias(), tableItem));
        List<FieldItem> fieldItems = collectSelectQueryFields(sqlSelectQueryBlock);
        fieldItems.forEach(fieldItem -> schemaItem.getSelectFields().put(fieldItem.getFieldName(), fieldItem));
        schemaItem.init();
        if (schemaItem.getAliasTableItems().isEmpty() || schemaItem.getSelectFields().isEmpty()) {
            throw new ParserException("Parse sql error");
        }
        return schemaItem;
    } catch (Exception e) {
        throw new ParserException();
    }
}
Also used : ParserException(com.alibaba.druid.sql.parser.ParserException) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) TableItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem) ArrayList(java.util.ArrayList) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLTableSource(com.alibaba.druid.sql.ast.statement.SQLTableSource) ParserException(com.alibaba.druid.sql.parser.ParserException) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) FieldItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)

Example 67 with MySqlSelectQueryBlock

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

the class SqlParser method parseSQLSelectQueryBlock.

public static MySqlSelectQueryBlock parseSQLSelectQueryBlock(String sql) {
    if (sql == null || "".equals(sql)) {
        return null;
    }
    SQLStatementParser parser = new MySqlStatementParser(sql);
    SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
    return (MySqlSelectQueryBlock) statement.getSelect().getQuery();
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)

Example 68 with MySqlSelectQueryBlock

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

the class ESSyncService method subTableSimpleFieldOperation.

/**
 * 关联子查询, 主表简单字段operation
 *
 * @param config es配置
 * @param dml dml信息
 * @param data 单行dml数据
 * @param old 单行old数据
 * @param tableItem 当前表配置
 */
private void subTableSimpleFieldOperation(ESSyncConfig config, Dml dml, Map<String, Object> data, Map<String, Object> old, TableItem tableItem) {
    ESMapping mapping = config.getEsMapping();
    MySqlSelectQueryBlock queryBlock = SqlParser.parseSQLSelectQueryBlock(tableItem.getSubQuerySql());
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ").append(SqlParser.parse4SQLSelectItem(queryBlock)).append(" FROM ").append(SqlParser.parse4FromTableSource(queryBlock));
    String whereSql = SqlParser.parse4WhereItem(queryBlock);
    if (whereSql != null) {
        sql.append(" WHERE ").append(whereSql);
    } else {
        sql.append(" WHERE 1=1 ");
    }
    List<Object> values = new ArrayList<>();
    for (FieldItem fkFieldItem : tableItem.getRelationTableFields().keySet()) {
        String columnName = fkFieldItem.getColumn().getColumnName();
        Object value = esTemplate.getValFromData(mapping, data, fkFieldItem.getFieldName(), columnName);
        sql.append(" AND ").append(columnName).append("=? ");
        values.add(value);
    }
    String groupSql = SqlParser.parse4GroupBy(queryBlock);
    if (groupSql != null) {
        sql.append(groupSql);
    }
    DataSource ds = DatasourceConfig.DATA_SOURCES.get(config.getDataSourceKey());
    if (logger.isTraceEnabled()) {
        logger.trace("Join table update es index by query sql, destination:{}, table: {}, index: {}, sql: {}", config.getDestination(), dml.getTable(), mapping.get_index(), sql.toString().replace("\n", " "));
    }
    Util.sqlRS(ds, sql.toString(), values, rs -> {
        try {
            while (rs.next()) {
                Map<String, Object> esFieldData = new LinkedHashMap<>();
                for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                    if (old != null) {
                        out: for (FieldItem fieldItem1 : tableItem.getSubQueryFields()) {
                            for (ColumnItem columnItem0 : fieldItem.getColumnItems()) {
                                if (fieldItem1.getFieldName().equals(columnItem0.getColumnName()))
                                    for (ColumnItem columnItem : fieldItem1.getColumnItems()) {
                                        if (old.containsKey(columnItem.getColumnName())) {
                                            Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                                            esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), val);
                                            break out;
                                        }
                                    }
                            }
                        }
                    } else {
                        Object val = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                        esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), val);
                    }
                }
                Map<String, Object> paramsTmp = new LinkedHashMap<>();
                for (Map.Entry<FieldItem, List<FieldItem>> entry : tableItem.getRelationTableFields().entrySet()) {
                    for (FieldItem fieldItem : entry.getValue()) {
                        if (fieldItem.getColumnItems().size() == 1) {
                            Object value = esTemplate.getValFromRS(mapping, rs, fieldItem.getFieldName(), entry.getKey().getColumn().getColumnName());
                            String fieldName = fieldItem.getFieldName();
                            // 判断是否是主键
                            if (fieldName.equals(mapping.get_id())) {
                                fieldName = "_id";
                            }
                            paramsTmp.put(fieldName, value);
                        }
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.trace("Join table update es index by query sql, destination:{}, table: {}, index: {}", config.getDestination(), dml.getTable(), mapping.get_index());
                }
                esTemplate.updateByQuery(config, paramsTmp, esFieldData);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return 0;
    });
}
Also used : ColumnItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.ColumnItem) ArrayList(java.util.ArrayList) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) DataSource(javax.sql.DataSource) LinkedHashMap(java.util.LinkedHashMap) ESMapping(com.alibaba.otter.canal.client.adapter.es.core.config.ESSyncConfig.ESMapping) ArrayList(java.util.ArrayList) List(java.util.List) FieldItem(com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem) LinkedHashMap(java.util.LinkedHashMap) Map(java.util.Map)

Example 69 with MySqlSelectQueryBlock

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

the class MySqlSelectParser method query.

@Override
public SQLSelectQuery query(SQLObject parent, boolean acceptUnion) {
    List<SQLCommentHint> hints = null;
    if (lexer.token() == Token.HINT) {
        hints = this.exprParser.parseHints();
    }
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        select.setParenthesized(true);
        accept(Token.RPAREN);
        return queryRest(select, acceptUnion);
    }
    if (lexer.token() == Token.VALUES) {
        return valuesQuery(acceptUnion);
    }
    MySqlSelectQueryBlock queryBlock = new MySqlSelectQueryBlock();
    queryBlock.setParent(parent);
    class QueryHintHandler implements Lexer.CommentHandler {

        private MySqlSelectQueryBlock queryBlock;

        private Lexer lexer;

        QueryHintHandler(MySqlSelectQueryBlock queryBlock, Lexer lexer) {
            this.queryBlock = queryBlock;
            this.lexer = lexer;
        }

        @Override
        public boolean handle(Token lastToken, String comment) {
            if (lexer.isEnabled(SQLParserFeature.TDDLHint) && (comment.startsWith("+ TDDL") || comment.startsWith("+TDDL") || comment.startsWith("!TDDL") || comment.startsWith("TDDL"))) {
                SQLCommentHint hint = new TDDLHint(comment);
                if (lexer.getCommentCount() > 0) {
                    hint.addBeforeComment(lexer.getComments());
                }
                queryBlock.getHints().add(hint);
                lexer.nextToken();
            }
            return false;
        }
    }
    this.lexer.setCommentHandler(new QueryHintHandler(queryBlock, this.lexer));
    if (lexer.hasComment() && lexer.isKeepComments()) {
        queryBlock.addBeforeComment(lexer.readAndResetComments());
    }
    if (lexer.token() == Token.SELECT) {
        if (selectListCache != null) {
            selectListCache.match(lexer, queryBlock);
        }
    }
    if (lexer.token() == Token.SELECT) {
        lexer.nextTokenValue();
        for (; ; ) {
            if (lexer.token() == Token.HINT) {
                this.exprParser.parseHints(queryBlock.getHints());
            } else {
                break;
            }
        }
        while (true) {
            Token token = lexer.token();
            if (token == (Token.DISTINCT)) {
                queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.DISTINCTROW)) {
                queryBlock.setDistionOption(SQLSetQuantifier.DISTINCTROW);
                lexer.nextToken();
            } else if (token == (Token.ALL)) {
                queryBlock.setDistionOption(SQLSetQuantifier.ALL);
                lexer.nextToken();
            } else if (token == (Token.UNIQUE)) {
                queryBlock.setDistionOption(SQLSetQuantifier.UNIQUE);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.HIGH_PRIORITY)) {
                queryBlock.setHignPriority(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.STRAIGHT_JOIN)) {
                queryBlock.setStraightJoin(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_SMALL_RESULT)) {
                queryBlock.setSmallResult(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_BIG_RESULT)) {
                queryBlock.setBigResult(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_BUFFER_RESULT)) {
                queryBlock.setBufferResult(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_CACHE)) {
                queryBlock.setCache(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_NO_CACHE)) {
                queryBlock.setCache(false);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.SQL_CALC_FOUND_ROWS)) {
                queryBlock.setCalcFoundRows(true);
                lexer.nextToken();
            } else if (lexer.identifierEquals(FnvHash.Constants.TOP)) {
                Lexer.SavePoint mark = lexer.mark();
                lexer.nextToken();
                if (lexer.token() == Token.LITERAL_INT) {
                    SQLLimit limit = new SQLLimit(lexer.integerValue().intValue());
                    queryBlock.setLimit(limit);
                    lexer.nextToken();
                } else if (lexer.token() == Token.DOT) {
                    lexer.reset(mark);
                    break;
                }
            } else {
                break;
            }
        }
        parseSelectList(queryBlock);
        if (lexer.identifierEquals(FnvHash.Constants.FORCE)) {
            lexer.nextToken();
            accept(Token.PARTITION);
            SQLName partition = this.exprParser.name();
            queryBlock.setForcePartition(partition);
        }
        parseInto(queryBlock);
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseHierachical(queryBlock);
    if (lexer.token() == Token.GROUP || lexer.token() == Token.HAVING) {
        parseGroupBy(queryBlock);
    }
    if (lexer.identifierEquals(FnvHash.Constants.WINDOW)) {
        parseWindow(queryBlock);
    }
    if (lexer.token() == Token.ORDER) {
        queryBlock.setOrderBy(this.exprParser.parseOrderBy());
    }
    if (lexer.token() == Token.LIMIT) {
        queryBlock.setLimit(this.exprParser.parseLimit());
    }
    if (lexer.token() == Token.FETCH) {
        final Lexer.SavePoint mark = lexer.mark();
        lexer.nextToken();
        if (lexer.identifierEquals(FnvHash.Constants.NEXT)) {
            lexer.nextToken();
            SQLExpr rows = this.exprParser.primary();
            queryBlock.setLimit(new SQLLimit(rows));
            acceptIdentifier("ROWS");
            acceptIdentifier("ONLY");
        } else {
            lexer.reset(mark);
        }
    }
    if (lexer.token() == Token.PROCEDURE) {
        lexer.nextToken();
        throw new ParserException("TODO. " + lexer.info());
    }
    if (lexer.token() == Token.INTO) {
        parseInto(queryBlock);
    }
    if (lexer.token() == Token.FOR) {
        lexer.nextToken();
        if (lexer.token() == Token.UPDATE) {
            lexer.nextToken();
            queryBlock.setForUpdate(true);
            if (lexer.identifierEquals(FnvHash.Constants.NO_WAIT) || lexer.identifierEquals(FnvHash.Constants.NOWAIT)) {
                lexer.nextToken();
                queryBlock.setNoWait(true);
            } else if (lexer.identifierEquals(FnvHash.Constants.WAIT)) {
                lexer.nextToken();
                SQLExpr waitTime = this.exprParser.primary();
                queryBlock.setWaitTime(waitTime);
            }
            if (lexer.identifierEquals(FnvHash.Constants.SKIP)) {
                lexer.nextToken();
                acceptIdentifier("LOCKED");
                queryBlock.setSkipLocked(true);
            }
        } else {
            acceptIdentifier("SHARE");
            queryBlock.setForShare(true);
        }
    }
    if (lexer.token() == Token.LOCK) {
        lexer.nextToken();
        accept(Token.IN);
        acceptIdentifier("SHARE");
        acceptIdentifier("MODE");
        queryBlock.setLockInShareMode(true);
    }
    if (hints != null) {
        queryBlock.setHints(hints);
    }
    return queryRest(queryBlock, acceptUnion);
}
Also used : MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)

Example 70 with MySqlSelectQueryBlock

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

the class MySqlSelectIntoParser method query.

@Override
public SQLSelectQuery query(SQLObject parent, boolean acceptUnion) {
    if (lexer.token() == (Token.LPAREN)) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        accept(Token.RPAREN);
        return queryRest(select, acceptUnion);
    }
    MySqlSelectQueryBlock queryBlock = new MySqlSelectQueryBlock();
    if (lexer.token() == Token.SELECT) {
        lexer.nextToken();
        if (lexer.token() == Token.HINT) {
            this.exprParser.parseHints(queryBlock.getHints());
        }
        if (lexer.token() == Token.COMMENT) {
            lexer.nextToken();
        }
        if (lexer.token() == (Token.DISTINCT)) {
            queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
            lexer.nextToken();
        } else if (lexer.identifierEquals("DISTINCTROW")) {
            queryBlock.setDistionOption(SQLSetQuantifier.DISTINCTROW);
            lexer.nextToken();
        } else if (lexer.token() == (Token.ALL)) {
            queryBlock.setDistionOption(SQLSetQuantifier.ALL);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("HIGH_PRIORITY")) {
            queryBlock.setHignPriority(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("STRAIGHT_JOIN")) {
            queryBlock.setStraightJoin(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_SMALL_RESULT")) {
            queryBlock.setSmallResult(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_BIG_RESULT")) {
            queryBlock.setBigResult(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_BUFFER_RESULT")) {
            queryBlock.setBufferResult(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_CACHE")) {
            queryBlock.setCache(true);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_NO_CACHE")) {
            queryBlock.setCache(false);
            lexer.nextToken();
        }
        if (lexer.identifierEquals("SQL_CALC_FOUND_ROWS")) {
            queryBlock.setCalcFoundRows(true);
            lexer.nextToken();
        }
        parseSelectList(queryBlock);
        argsList = parseIntoArgs();
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseGroupBy(queryBlock);
    queryBlock.setOrderBy(this.exprParser.parseOrderBy());
    if (lexer.token() == Token.LIMIT) {
        queryBlock.setLimit(this.exprParser.parseLimit());
    }
    if (lexer.token() == Token.PROCEDURE) {
        lexer.nextToken();
        throw new ParserException("TODO. " + lexer.info());
    }
    parseInto(queryBlock);
    if (lexer.token() == Token.FOR) {
        lexer.nextToken();
        accept(Token.UPDATE);
        queryBlock.setForUpdate(true);
    }
    if (lexer.token() == Token.LOCK) {
        lexer.nextToken();
        accept(Token.IN);
        acceptIdentifier("SHARE");
        acceptIdentifier("MODE");
        queryBlock.setLockInShareMode(true);
    }
    return queryRest(queryBlock, acceptUnion);
}
Also used : ParserException(com.alibaba.druid.sql.parser.ParserException) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)

Aggregations

MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)82 SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)41 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)34 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)31 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)28 SQLSelect (com.alibaba.druid.sql.ast.statement.SQLSelect)25 MySqlSchemaStatVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor)23 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)19 SQLBinaryOpExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr)13 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)11 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)10 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)9 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)8 SQLQueryExpr (com.alibaba.druid.sql.ast.expr.SQLQueryExpr)8 HashMap (java.util.HashMap)8 Item (com.actiontech.dble.plan.common.item.Item)7 SQLNonTransientException (java.sql.SQLNonTransientException)7 Test (org.junit.Test)7 SQLInSubQueryExpr (com.alibaba.druid.sql.ast.expr.SQLInSubQueryExpr)6 SQLNullExpr (com.alibaba.druid.sql.ast.expr.SQLNullExpr)6