Search in sources :

Example 56 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_41 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "select * from db_00.t_00_00 where id > 1";
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement statement = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    /*visitor.setPrettyFormat(false);*/
    statement.accept(visitor);
    /* JSONArray array = new JSONArray();
        for(String table : visitor.getTables()){
            array.add(table.replaceAll("`",""));
        }*/
    String psql = out.toString();
    assertEquals("SELECT *\n" + "FROM db.t\n" + "WHERE id > ?", psql);
    String params_json = JSONArray.toJSONString(parameters, SerializerFeature.WriteClassName);
    System.out.println(params_json);
    JSONArray jsonArray = JSON.parseArray(params_json);
    String json = JSONArray.toJSONString(jsonArray, SerializerFeature.WriteClassName);
    assertEquals("[1]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("SELECT *\n" + "FROM db.t\n" + "WHERE id > 1", rsql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) JSONArray(com.alibaba.fastjson.JSONArray) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Example 57 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class ParseUtil method restore.

public static String restore(String sql, String table, String params) /*JSONArray paramsArray, JSONArray destArray*/
{
    JSONArray destArray = null;
    if (table != null) {
        destArray = JSON.parseArray(table.replaceAll("''", "'"));
    }
    JSONArray paramsArray = JSON.parseArray(params.replaceAll("''", "'"));
    DbType dbType = JdbcConstants.MYSQL;
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    SQLStatement stmt = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, dbType);
    List<Object> paramsList = new ArrayList(paramsArray);
    visitor.setParameters(paramsList);
    JSONArray srcArray = getSrcArray(sql);
    if (destArray != null) {
        for (int i = 0; i < srcArray.size(); i++) {
            visitor.addTableMapping(srcArray.getString(i), destArray.getString(i));
        }
    }
    stmt.accept(visitor);
    return out.toString();
}
Also used : JSONArray(com.alibaba.fastjson.JSONArray) ArrayList(java.util.ArrayList) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Example 58 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class ParseUtil method getSqlHash.

public static String getSqlHash(SQLStatement statement) {
    try {
        StringBuilder out = new StringBuilder();
        List<Object> parameters = new ArrayList<Object>();
        SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
        visitor.setParameterized(true);
        visitor.setParameterizedMergeInList(true);
        visitor.setParameters(parameters);
        // visitor.setExportTables(true);
        visitor.setPrettyFormat(false);
        statement.accept(visitor);
        String sqlTemplate = out.toString();
        return DigestUtils.md5Hex(sqlTemplate);
    } catch (Exception ex) {
        logger.error("parseSql error ", ex);
    }
    return null;
}
Also used : ArrayList(java.util.ArrayList) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor)

Example 59 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class ParseUtil method getSrcArray.

private static JSONArray getSrcArray(String sql) {
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
    SQLStatement stmt = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    visitor.setExportTables(true);
    stmt.accept(visitor);
    String srcStr = JSONArray.toJSONString(visitor.getTables());
    return JSONArray.parseArray(srcStr);
}
Also used : ArrayList(java.util.ArrayList) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement)

Example 60 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_45 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "(SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count` , `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time` FROM `t_like_count0062` `t_like_count` WHERE `t_like_count`.`target_type` = ? AND `t_like_count`.`target_id` IN (?)) UNION ALL (SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count` , `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time` FROM `t_like_count0057` `t_like_count` WHERE `t_like_count`.`target_type` = ? AND `t_like_count`.`target_id` IN (?)) UNION ALL (SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count` , `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time` FROM `t_like_count0050` `t_like_count` WHERE `t_like_count`.`target_type` = ? AND `t_like_count`.`target_id` IN (?)) UNION ALL (SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count` , `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time` FROM `t_like_count0048` `t_like_count` WHERE `t_like_count`.`target_type` = ? AND `t_like_count`.`target_id` IN (?))";
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement statement = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.MYSQL);
    List<Object> parameters = new ArrayList<Object>();
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    /*visitor.setPrettyFormat(false);*/
    statement.accept(visitor);
    /* JSONArray array = new JSONArray();
        for(String table : visitor.getTables()){
            array.add(table.replaceAll("`",""));
        }*/
    String psql = out.toString();
    System.out.println(psql);
    assertEquals("(SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count`\n" + "\t, `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time`\n" + "FROM t_like_count `t_like_count`\n" + "WHERE `t_like_count`.`target_type` = ?\n" + "\tAND `t_like_count`.`target_id` IN (?))\n" + "UNION ALL\n" + "(SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count`\n" + "\t, `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time`\n" + "FROM t_like_count `t_like_count`\n" + "WHERE `t_like_count`.`target_type` = ?\n" + "\tAND `t_like_count`.`target_id` IN (?))\n" + "UNION ALL\n" + "(SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count`\n" + "\t, `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time`\n" + "FROM t_like_count `t_like_count`\n" + "WHERE `t_like_count`.`target_type` = ?\n" + "\tAND `t_like_count`.`target_id` IN (?))\n" + "UNION ALL\n" + "(SELECT `t_like_count`.`id`, `t_like_count`.`target_id`, `t_like_count`.`target_type`, `t_like_count`.`like_type`, `t_like_count`.`like_count`\n" + "\t, `t_like_count`.`like_optimalize_count`, `t_like_count`.`create_time`, `t_like_count`.`update_time`\n" + "FROM t_like_count `t_like_count`\n" + "WHERE `t_like_count`.`target_type` = ?\n" + "\tAND `t_like_count`.`target_id` IN (?))", psql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Aggregations

SQLASTOutputVisitor (com.alibaba.druid.sql.visitor.SQLASTOutputVisitor)67 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)56 ArrayList (java.util.ArrayList)48 DbType (com.alibaba.druid.DbType)45 SQLStatementParser (com.alibaba.druid.sql.parser.SQLStatementParser)44 JSONArray (com.alibaba.fastjson.JSONArray)10 MySqlOutputVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor)3 OracleOutputVisitor (com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor)3 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)2 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)2 SQLAllColumnExpr (com.alibaba.druid.sql.ast.expr.SQLAllColumnExpr)2 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)2 SQLSelectGroupByClause (com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause)2 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)2 DB2SelectQueryBlock (com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock)2 DB2OutputVisitor (com.alibaba.druid.sql.dialect.db2.visitor.DB2OutputVisitor)2 MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)2 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)2 OracleSelectQueryBlock (com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock)2 PGSelectQueryBlock (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock)2