Search in sources :

Example 6 with JSONArray

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

the class Oracle_param_1 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "SELECT TO_CHAR(SYSDATE, '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_CHAR(SYSDATE, '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("[]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("SELECT TO_CHAR(SYSDATE, '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)

Example 7 with JSONArray

use of com.alibaba.fastjson2.JSONArray in project sakura-boot by yanjingfan.

the class ESTemplate method postObjectString.

/**
 * es的post请求
 * {
 *     "query": {
 *         "bool": {
 *             "must": [
 *                 {
 *                     "match_phrase": {
 *                         "age": 10
 *                     }
 *                 },
 *                 {
 *                     "range": {
 *                         "time.keyword": {
 *                             "gte": "2022-01-01 11:20:32",
 *                             "lte": "2022-12-24 11:20:32"
 *                         }
 *                     }
 *                 }
 *             ]
 *         }
 *     },
 *     "sort": [
 *         {
 *             "time.keyword": {
 *                 "order": "asc"
 *             }
 *         }
 *     ]
 * }
 * @param filterInfo
 * @return
 */
public String postObjectString(FilterInfo filterInfo) {
    String indexName = filterInfo.getIndexName();
    String end = filterInfo.getEnd();
    Integer page = filterInfo.getPageNum();
    String start = filterInfo.getStart();
    Integer pageSize = filterInfo.getPageSize();
    List<QueryField> queryFields = filterInfo.getQueryFields();
    List<String> selectFields = filterInfo.getSelectFields();
    String sortFiled = filterInfo.getSortFiled();
    Boolean sortIsStr = filterInfo.getSortIsStr();
    String rangeFiled = filterInfo.getRangeFiled();
    Boolean rangeIsStr = filterInfo.getRangeIsStr();
    String sortMethod = filterInfo.getSortMethod();
    String scrollId = filterInfo.getScrollId();
    Boolean openScroll = filterInfo.getScroll();
    RestTemplate restTemplate = new RestTemplate();
    StringBuilder sb = new StringBuilder("http://");
    sb.append(ESurl).append("/");
    if (!StringUtils.isEmpty(scrollId) && openScroll) {
        sb.append("/_search/scroll?scroll_id=");
        sb.append(scrollId).append("&scroll=1m");
        try {
            return restTemplate.getForObject(sb.toString(), String.class);
        } catch (Exception e) {
            throw new ESException("ES深分页查询出错!");
        }
    } else {
        sb.append(indexName);
        sb.append("/_search?");
        if (!CollectionUtils.isEmpty(selectFields)) {
            sb.append("_source=");
            StringBuilder sBuilder = new StringBuilder();
            selectFields.forEach(field -> sBuilder.append(field).append(","));
            String substring = sBuilder.substring(0, sBuilder.length() - 1);
            sb.append(substring);
        }
        JSONObject queryObject = new JSONObject();
        // 搜索
        JSONObject boolObject = new JSONObject();
        JSONObject mustObject = new JSONObject();
        JSONArray must = new JSONArray();
        if (!CollectionUtils.isEmpty(queryFields)) {
            queryFields.stream().forEach(item -> {
                JSONObject matchPhrase = new JSONObject();
                JSONObject match = new JSONObject();
                match.put(item.getKey(), item.getValue());
                matchPhrase.put("match_phrase", match);
                must.add(matchPhrase);
            });
        }
        // 默认降序排序,目前测试的时候,只有数值类的字段允许排序
        if (!StringUtils.isEmpty(sortFiled)) {
            JSONObject sortFiledObject = new JSONObject();
            JSONObject orderObject = new JSONObject();
            orderObject.put("order", sortMethod);
            // 排序字段为字符串时,后缀添加.keyword
            if (sortIsStr) {
                sortFiled = sortFiled + ".keyword";
            }
            sortFiledObject.put(sortFiled, orderObject);
            queryObject.put("sort", sortFiledObject);
        }
        // 范围查询
        if (!StringUtils.isEmpty(rangeFiled)) {
            JSONObject rangeObject = new JSONObject();
            JSONObject rangeFiledObject = new JSONObject();
            JSONObject range = new JSONObject();
            range.put("gte", start);
            range.put("lte", end);
            // 范围查询字段为字符串时,后缀添加.keyword
            if (rangeIsStr) {
                rangeFiled = rangeFiled + ".keyword";
            }
            rangeFiledObject.put(rangeFiled, range);
            rangeObject.put("range", rangeFiledObject);
            must.add(rangeObject);
        }
        // 分页
        if (page != null && page > 0) {
            if (page * pageSize > 10000) {
                page--;
                sb.append("&from=").append(10000 - pageSize);
            } else {
                page--;
                sb.append("&from=").append(page * pageSize);
            }
        }
        if (pageSize != null) {
            sb.append("&size=").append(pageSize);
        }
        mustObject.put("must", must);
        boolObject.put("bool", mustObject);
        queryObject.put("query", boolObject);
        return sendPostRequest(sb.toString(), queryObject);
    }
}
Also used : QueryField(com.sakura.common.es.item.QueryField) ESException(com.sakura.common.es.exception.ESException) JSONObject(com.alibaba.fastjson2.JSONObject) RestTemplate(org.springframework.web.client.RestTemplate) JSONArray(com.alibaba.fastjson2.JSONArray) JSONException(com.alibaba.fastjson2.JSONException) ESException(com.sakura.common.es.exception.ESException)

Example 8 with JSONArray

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

the class MySqlParameterizedOutputVisitorTest_38_1 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "SELECT lower(hex(file_md5)) as file_md5,\n" + "        lower(hex(thumb)) as thumb,st\n" + "        FROM t_f_p_thumb\n" + "        WHERE file_md5 = x'84C1F969587F5FD1942148EE9D36A0FB'";
    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);
    String params_json = JSON.toJSONString(parameters, JSONWriter.Feature.WriteClassName);
    System.out.println(params_json);
    JSONArray jsonArray = JSON.parseArray(params_json);
    System.out.println(JSON.toJSONString(jsonArray, JSONWriter.Feature.WriteClassName));
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    System.out.println(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 9 with JSONArray

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

the class MySqlParameterizedOutputVisitorTest_39 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "select * from t where 1 = 1 or id = 3";
    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 t\n" + "WHERE 1 = 1\n" + "\tOR 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("[3]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("SELECT *\n" + "FROM t\n" + "WHERE 1 = 1\n" + "\tOR id = 3", 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 10 with JSONArray

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

the class MySqlParameterizedOutputVisitorTest_40 method test_for_parameterize.

public void test_for_parameterize() throws Exception {
    final DbType dbType = JdbcConstants.MYSQL;
    String sql = "select * from t where 1 <> 1 or id = 3";
    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 t\n" + "WHERE 1 <> 1\n" + "\tOR 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("[3]", json);
    String rsql = SQLUtils.toSQLString(SQLUtils.parseStatements(psql, dbType), dbType, jsonArray);
    assertEquals("SELECT *\n" + "FROM t\n" + "WHERE 1 <> 1\n" + "\tOR id = 3", 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