Search in sources :

Example 6 with SQLStatementParser

use of com.alibaba.druid.sql.parser.SQLStatementParser in project druid by alibaba.

the class Demo1 method test_0.

public void test_0() throws Exception {
    String sql = "select * from user where uid = ? and uname = ?";
    List<Object> parameters = new ArrayList<Object>();
    parameters.add(1);
    parameters.add("wenshao");
    SQLStatementParser parser = new OracleStatementParser(sql);
    //
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement first = (SQLStatement) stmtList.get(0);
    GetVariantVisitor variantVisitor = new GetVariantVisitor();
    first.accept(variantVisitor);
    SQLVariantRefExpr firstVar = variantVisitor.getVariantList().get(0);
    int varIndex = (Integer) firstVar.getAttribute("varIndex");
    Integer param = (Integer) parameters.get(varIndex);
    String tableName;
    if (param.intValue() == 1) {
        tableName = "user_1";
    } else {
        tableName = "user_x";
    }
    MyOracleVisitor visitor = new MyOracleVisitor(tableName);
    first.accept(visitor);
    String realSql = SQLUtils.toOracleString(first);
    System.out.println(realSql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser) SQLVariantRefExpr(com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr)

Example 7 with SQLStatementParser

use of com.alibaba.druid.sql.parser.SQLStatementParser in project druid by alibaba.

the class Demo2 method test_0.

public void test_0() throws Exception {
    String sql = "select * from user where uid = 2 and uname = ?";
    List<Object> parameters = new ArrayList<Object>();
    parameters.add(1);
    parameters.add("wenshao");
    SQLStatementParser parser = new MySqlStatementParser(sql);
    //
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement first = (SQLStatement) stmtList.get(0);
    MyVisitor visitor = new MyVisitor();
    first.accept(visitor);
    SQLExpr firstVar = visitor.getVariantList().get(0);
    int userId;
    if (firstVar instanceof SQLVariantRefExpr) {
        int varIndex = (Integer) firstVar.getAttribute("varIndex");
        userId = (Integer) parameters.get(varIndex);
    } else {
        userId = ((SQLNumericLiteralExpr) firstVar).getNumber().intValue();
    }
    String tableName;
    if (userId == 1) {
        tableName = "user_1";
    } else {
        tableName = "user_x";
    }
    for (SQLExprTableSource tableSource : visitor.getTableSourceList()) {
        SQLExpr expr = tableSource.getExpr();
        if (expr instanceof SQLIdentifierExpr) {
            SQLIdentifierExpr identExpr = (SQLIdentifierExpr) expr;
            String ident = identExpr.getName();
            if (ident.equals("user")) {
                identExpr.setName(tableName);
            }
        } else if (expr instanceof SQLPropertyExpr) {
            SQLPropertyExpr proExpr = (SQLPropertyExpr) expr;
            String ident = proExpr.getName();
            if (ident.equals("user")) {
                proExpr.setName(tableName);
            }
        }
    }
    String realSql = SQLUtils.toOracleString(first);
    System.out.println(realSql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) SQLIdentifierExpr(com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr) SQLPropertyExpr(com.alibaba.druid.sql.ast.expr.SQLPropertyExpr) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) SQLNumericLiteralExpr(com.alibaba.druid.sql.ast.expr.SQLNumericLiteralExpr) SQLVariantRefExpr(com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr) SQLExprTableSource(com.alibaba.druid.sql.ast.statement.SQLExprTableSource) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)

Example 8 with SQLStatementParser

use of com.alibaba.druid.sql.parser.SQLStatementParser in project druid by alibaba.

the class OdpsSelectTest6 method test_distribute_by.

public void test_distribute_by() throws Exception {
    File file = new File("/Users/wenshao/Downloads/datasafe_base_dev.udf_test.txt");
    String sql = FileUtils.readFileToString(file, "UTF-8");
    SQLStatementParser parser = new OdpsStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
//        Assert.assertEquals("SELECT *"
//                + "\nFROM t"
//                + "\nWHERE ds = '20160303'"
//                + "\n\tAND hour IN ('18')", SQLUtils.formatOdps(sql));
}
Also used : MySqlSchemaStatVisitor(com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) OdpsStatementParser(com.alibaba.druid.sql.dialect.odps.parser.OdpsStatementParser) File(java.io.File) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement)

Example 9 with SQLStatementParser

use of com.alibaba.druid.sql.parser.SQLStatementParser in project druid by alibaba.

the class WallVisitorUtils method check.

public static void check(WallVisitor visitor, SQLCommentHint x) {
    if (!visitor.getConfig().isHintAllow()) {
        addViolation(visitor, ErrorCode.EVIL_HINTS, "hint not allow", x);
        return;
    }
    String text = x.getText();
    text = text.trim();
    if (text.startsWith("!")) {
        text = text.substring(1);
    }
    if (text.length() == 0) {
        return;
    }
    int pos = 0;
    for (; pos < text.length(); pos++) {
        char ch = text.charAt(pos);
        if (ch >= '0' && ch <= '9') {
            continue;
        } else {
            break;
        }
    }
    if (pos == 5) {
        text = text.substring(5);
        text = text.trim();
    }
    text = text.toUpperCase();
    boolean isWhite = false;
    for (String hint : whiteHints) {
        if (text.equals(hint)) {
            isWhite = true;
            break;
        }
    }
    if (!isWhite) {
        if (text.startsWith("FORCE INDEX") || text.startsWith("IGNORE INDEX")) {
            isWhite = true;
        }
    }
    if (!isWhite) {
        if (text.startsWith("SET")) {
            SQLStatementParser parser = new MySqlStatementParser(text);
            List<SQLStatement> statementList = parser.parseStatementList();
            if (statementList != null && statementList.size() > 0) {
                SQLStatement statement = statementList.get(0);
                if (statement instanceof SQLSetStatement || statement instanceof MySqlSetCharSetStatement || statement instanceof MySqlSetNamesStatement) {
                    isWhite = true;
                }
            }
        }
    }
    if (!isWhite) {
        addViolation(visitor, ErrorCode.EVIL_HINTS, "hint not allow", x);
    }
}
Also used : MySqlSetNamesStatement(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSetNamesStatement) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) MySqlSetCharSetStatement(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSetCharSetStatement) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SQLCommentHint(com.alibaba.druid.sql.ast.SQLCommentHint)

Example 10 with SQLStatementParser

use of com.alibaba.druid.sql.parser.SQLStatementParser in project druid by alibaba.

the class OverTest method test_over.

public void test_over() throws Exception {
    String sql = //
    "SELECT SalesOrderID, ProductID, OrderQty" + //
    "    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'" + //
    "    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'" + //
    "    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'" + //
    "    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'" + //
    "    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max' " + //
    "FROM Sales.SalesOrderDetail " + "WHERE SalesOrderID IN(43659,43664);";
    SQLStatementParser parser = new SQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement statemen = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    SchemaStatVisitor visitor = new SchemaStatVisitor();
    statemen.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getTables().size());
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor)

Aggregations

SQLStatementParser (com.alibaba.druid.sql.parser.SQLStatementParser)436 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)431 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)382 ArrayList (java.util.ArrayList)21 SQLASTOutputVisitor (com.alibaba.druid.sql.visitor.SQLASTOutputVisitor)19 SchemaStatVisitor (com.alibaba.druid.sql.visitor.SchemaStatVisitor)14 MycatSchemaStatVisitor (io.mycat.route.parser.druid.MycatSchemaStatVisitor)6 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)5 MySqlExportParameterVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlExportParameterVisitor)5 ExportParameterVisitor (com.alibaba.druid.sql.visitor.ExportParameterVisitor)5 TableStat (com.alibaba.druid.stat.TableStat)5 MycatStatementParser (io.mycat.route.parser.druid.MycatStatementParser)5 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)4 SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)4 OracleStatementParser (com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)4 SQLVariantRefExpr (com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr)3 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)3 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)3 MySqlInsertStatement (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement)3 RouteResultset (io.mycat.route.RouteResultset)3