Search in sources :

Example 11 with JSONArray

use of com.alibaba.fastjson2.JSONArray 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 = JSON.toJSONString(parameters, JSONWriter.Feature.WriteClassName);
    System.out.println(params_json);
    JSONArray jsonArray = JSON.parseArray(params_json);
    String json = JSON.toJSONString(jsonArray, JSONWriter.Feature.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.fastjson2.JSONArray) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Example 12 with JSONArray

use of com.alibaba.fastjson2.JSONArray in project druid by alibaba.

the class MySqlParameterizedOutputVisitorTest_42 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "UPDATE offline_file_user" + " SET sended_file_num = sended_file_num-1, sended_flie_total_size = sended_flie_total_size-19039064" + " WHERE login_id = ?";
    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("UPDATE offline_file_user\n" + "SET sended_file_num = sended_file_num - ?, sended_flie_total_size = sended_flie_total_size - ?\n" + "WHERE login_id = ?", psql);
    String params_json = JSON.toJSONString(parameters, JSONWriter.Feature.WriteClassName);
    System.out.println(params_json);
    JSONArray jsonArray = JSON.parseArray(params_json);
    String json = JSON.toJSONString(jsonArray, JSONWriter.Feature.WriteClassName);
    assertEquals("[1,19039064]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("UPDATE offline_file_user\n" + "SET sended_file_num = sended_file_num - 1, sended_flie_total_size = sended_flie_total_size - 19039064\n" + "WHERE login_id = ?", rsql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) JSONArray(com.alibaba.fastjson2.JSONArray) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Example 13 with JSONArray

use of com.alibaba.fastjson2.JSONArray 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.fastjson2.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 14 with JSONArray

use of com.alibaba.fastjson2.JSONArray in project druid by alibaba.

the class ParseUtil method main.

public static void main(String[] args) {
    // String sql = "alter table sql_perf add index `idx_instance_8` (`host`,`port`,`hashcode`,`item`,`time`,`value`);";
    /*  String sql = "CREATE INDEX PersonIndex\n" +
                "ON Person (LastName) ";*/
    // String sql = "CREATE TABLE t (id int );";
    // String sql = "ALTER TABLE `app_api_dup_control`\n\tDROP INDEX `idx_url_uuid`,\n\tADD UNIQUE KEY `uk_url_uuid` (uuid, url)";
    /*  String sql = "ALTER TABLE `push_seed_0000`\n" +
                "\tADD KEY `idx_betstatus_gmtcreate` (bet_status, gmt_create),\n" +
                "\tADD KEY `idx_winstatus_gmtcreate` (win_status, gmt_create)";*/
    // System.out.println(getIdxInfo(sql, "AA", null));
    // System.out.println(DateTimeUtils.toYyyyMMddhhmmss(new Date()));
    /*String ip = "xx";
        System.out.println(IP_PATTERN.matcher(ip).find());*/
    /* String sql = "\nalter table `dms_sign_info_0222` modify column `station_id` bigint comment '分拣流水中记录的分拨中心ID'";

        List<IdxFlagInfo> list = getIdxInfo(sql,"XX",null);
        System.out.println(list);*/
    // String sql = "select * from task where status = 1 and valid = 1 and type <> 100 and type <> 99 order by priority desc,gmt_create limit 0,500";
    // System.out.println(parseSQL(sql));
    /*String sql = "SELECT IFNULL(SUM(CASE WHEN `tms_waybill_detail`.`dissendout_status` = ? AND DATE_ADD(`tms_waybill_detail`.`rdc_accept_time`, INTERVAL ? HOUR) < `tms_waybill_detail`.`dissendout_time` OR `tms_waybill_detail`.`dissendout_status` = ? AND DATE_ADD(`tms_waybill_detail`.`rdc_accept_time`, INTERVAL ? HOUR) < NOW() THEN ? ELSE ? END), ?) AS `count` FROM tms_waybill_detail `tms_waybill_detail` WHERE `tms_waybill_detail`.`rdc_accept_time` >= ? AND `tms_waybill_detail`.`rdc_accept_time` < ? AND `tms_waybill_detail`.`districenter_code` = ? AND `tms_waybill_detail`.`schedule_code` = ?";

        String table = "[\"tms_waybill_detail_0014\"]";
        String params = "[1,24,0,24,1,0,0,\"2017-01-15 00:00:00\",\"2017-01-15 17:32:03.558\",686,\"10102\"]";

        System.out.println("----- : "+restore(sql,table,params));*/
    String sql = "/* 0bba613214845441110397435e/0.4.6.25// */select `f`.`id`,`f`.`biz_id`,`f`.`user_id`,`f`.`file_name`,`f`.`parent_id`,`f`.`length`,`f`.`type`,`f`.`stream_key`,`f`.`biz_status`,`f`.`mark`,`f`.`content_modified`,`f`.`status`,`f`.`gmt_create`,`f`.`gmt_modified`,`f`.`md5`,`f`.`extra_str1`,`f`.`extra_str2`,`f`.`extra_str3`,`f`.`extra_num1`,`f`.`extra_num2`,`f`.`extra_num3`,`f`.`safe`,`f`.`open_status`,`f`.`inner_mark`,`f`.`sys_extra`,`f`.`feature`,`f`.`domain_option`,`f`.`version`,`f`.`reference_type`,`f`.`dentry_type`,`f`.`space_id`,`f`.`extension`,`f`.`creator_id`,`f`.`modifier_id`,`f`.`store_type`,`f`.`link_mark`,`f`.`content_type` from  ( select `vfs_dentry_2664`.`id` from `vfs_dentry_2664` FORCE INDEX (idx_gmt) where ((`vfs_dentry_2664`.`extra_str1` = '97d45a25df387b4460e5b4151daeb452') AND (`vfs_dentry_2664`.`biz_id` = 62) AND (`vfs_dentry_2664`.`status` = 0) AND (`vfs_dentry_2664`.`user_id` = '11168360') AND (`vfs_dentry_2664`.`dentry_type` = 1)) limit 0,50 )  `t`  join `vfs_dentry_2664` `f` on `t`.`id` = `f`.`id` where ((`t`.`id` = `f`.`id`) AND (`f`.`user_id` = 11168360))";
    // SQLStatement sqlStatement = getStatement(sql);
    String sqlTempalte = parseSQL(sql);
    JSONArray array = new JSONArray();
    array.add("VFS_DENTRY_001");
    System.out.println(restore(sqlTempalte, array.toJSONString(), new JSONArray().toJSONString()));
}
Also used : JSONArray(com.alibaba.fastjson2.JSONArray)

Example 15 with JSONArray

use of com.alibaba.fastjson2.JSONArray in project druid by alibaba.

the class Oracle_param_2 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "SELECT TO_DATE('2013-02-11', 'YYYY-MM-DD') FROM dual;";
    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.ORACLE);
    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 TO_DATE(?, 'YYYY-MM-DD')\n" + "FROM dual;", psql);
    String params_json = JSON.toJSONString(parameters, JSONWriter.Feature.WriteClassName);
    System.out.println(params_json);
    JSONArray jsonArray = JSON.parseArray(params_json);
    String json = JSON.toJSONString(jsonArray, JSONWriter.Feature.WriteClassName);
    assertEquals("[\"2013-02-11\"]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("SELECT TO_DATE('2013-02-11', 'YYYY-MM-DD')\n" + "FROM dual;", rsql);
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) ArrayList(java.util.ArrayList) JSONArray(com.alibaba.fastjson2.JSONArray) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType)

Aggregations

JSONArray (com.alibaba.fastjson2.JSONArray)16 ArrayList (java.util.ArrayList)13 DbType (com.alibaba.druid.DbType)10 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)10 SQLASTOutputVisitor (com.alibaba.druid.sql.visitor.SQLASTOutputVisitor)10 SQLStatementParser (com.alibaba.druid.sql.parser.SQLStatementParser)8 JSONObject (com.alibaba.fastjson2.JSONObject)4 Map (java.util.Map)3 ServiceNode (com.alibaba.druid.admin.model.ServiceNode)2 HashMap (java.util.HashMap)2 LinkedHashMap (java.util.LinkedHashMap)2 SqlListResult (com.alibaba.druid.admin.model.dto.SqlListResult)1 WebResult (com.alibaba.druid.admin.model.dto.WebResult)1 MySqlSchemaStatVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor)1 SchemaStatVisitor (com.alibaba.druid.sql.visitor.SchemaStatVisitor)1 TableStat (com.alibaba.druid.stat.TableStat)1 JSONException (com.alibaba.fastjson2.JSONException)1 CanalCluster (com.alibaba.otter.canal.admin.model.CanalCluster)1 NodeServer (com.alibaba.otter.canal.admin.model.NodeServer)1 ESException (com.sakura.common.es.exception.ESException)1