com.alibaba.druid.sql.SQLUtils Java Examples

public String getExecutableSql() {
    if (parametersSize < 1) {
        return sql;
    }

    List<Object> parameters = new ArrayList<>(parametersSize);
    JdbcParameter jdbcParam;
    for (int i = 0; i < parametersSize; ++i) {
        jdbcParam = this.parameters[i];
        parameters.add(jdbcParam != null ? jdbcParam.getValue() : null);
    }

    try {
        SQLStatementParser parser = ESActionFactory.createSqlStatementParser(sql);
        List<SQLStatement> statementList = parser.parseStatementList();
        return SQLUtils.toSQLString(statementList, JdbcConstants.MYSQL, parameters, sqlFormatOption);
    } catch (ClassCastException | ParserException ex) {
        LOG.warn("format error", ex);
        return sql;
    }
}
 

protected void setLimitIFChange(SQLStatement stmt, RouteResultset rrs, SchemaConfig schema, SQLBinaryOpExpr one, int firstrownum, int lastrownum)
{
	rrs.setLimitStart(firstrownum);
	rrs.setLimitSize(lastrownum - firstrownum);
	LayerCachePool tableId2DataNodeCache = (LayerCachePool) MycatServer.getInstance().getCacheService().getCachePool("TableID2DataNodeCache");
	try
	{
		tryRoute(schema, rrs, tableId2DataNodeCache);
	} catch (SQLNonTransientException e)
	{
		throw new RuntimeException(e);
	}
	if (isNeedChangeLimit(rrs))
	{
		one.setRight(new SQLIntegerExpr(0));
		String curentDbType ="db2".equalsIgnoreCase(this.getCurentDbType())?"oracle":getCurentDbType();
		String sql =   SQLUtils.toSQLString(stmt, curentDbType);;
		rrs.changeNodeSqlAfterAddLimit(schema,getCurentDbType(), sql,0,lastrownum, false);
		//设置改写后的sql
		getCtx().setSql(sql);
	}
}
 

/**
 * 解析DDL
 *
 * @param dto
 * @return
 */
public List<SQLStatement> parse(ReverseEngineeringDTO dto) {
    List<SQLStatement> sqlStatements;
    try {
        sqlStatements = SQLUtils.parseStatements(dto.getDdl(), dto.getDbType());
    } catch (ParserException e) {
        LOGGER.warn("反向工程校验失败：{}", e);
        throw new BusinessException(ErrorCode.BAD_PARAMETER, "DDL解析失败:" + e.getMessage());
    }
    if (CollectionUtils.isEmpty(sqlStatements)) {
        throw new BusinessException(ErrorCode.BAD_PARAMETER, "未找到有效DDL语句");
    }
    for (SQLStatement sqlStatement : sqlStatements) {
        if (!(sqlStatement instanceof SQLCreateTableStatement)) {
            throw new BusinessException(ErrorCode.BAD_PARAMETER, "只支持create table语句，请删除多余的sql");
        }
    }
    return sqlStatements;
}
 

private String buildSqlString() {
    if (MediaSourceType.MYSQL.equals(mediaSourceType) || MediaSourceType.SDDL.equals(mediaSourceType)) {
        return SQLUtils.toMySqlString(sqlStatement).toLowerCase();
    } else if (MediaSourceType.SQLSERVER.equals(mediaSourceType)) {
        return SQLUtils.toSQLServerString(sqlStatement).toLowerCase();
    } else if (MediaSourceType.POSTGRESQL.equals(mediaSourceType)) {
        return SQLUtils.toPGString(sqlStatement).toLowerCase();
    }
    return "";
}
 

public static String toSQLString(SQLObject sqlObject) {
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = new DbleOutputVisitor(out);
    SQLUtils.FormatOption option = new SQLUtils.FormatOption(true, true);
    visitor.setUppCase(option.isUppCase());
    visitor.setPrettyFormat(option.isPrettyFormat());
    visitor.setParameterized(option.isParameterized());
    sqlObject.accept(visitor);
    String sql = out.toString();
    return sql;
}
 

private void processIncrementColumn(DumpFileContext context, List<SQLExpr> values) throws SQLNonTransientException {
    int incrementIndex = context.getIncrementColumnIndex();
    if (incrementIndex == -1) {
        return;
    }

    String tableKey = StringUtil.getFullName(context.getSchema(), context.getTable());
    long val = SequenceManager.getHandler().nextId(tableKey);
    SQLExpr value = values.get(incrementIndex);
    if (!StringUtil.isEmpty(SQLUtils.toMySqlString(value)) && !context.isNeedSkipError()) {
        context.addError("For table using global sequence, dble has set increment column values for you.");
        context.setNeedSkipError(true);
    }
    values.set(incrementIndex, new SQLIntegerExpr(val));
}
 

@Test
public void format() {
    String sql = "SELECT id,task_id,task_source, housedel_code, del_type, office_address, company_code, brand, " +
            "class1_code, class2_code, class2_name, status, create_time, end_time, creator_ucid, creator_name, " +
            "org_code, prove_id, prove_time, audit_ucid, audit_name, audit_reject_reason, audit_content, " +
            "audit_time, pass_mode, sms_content, sms_time, lianjia_app_content, lianjia_app_time \r\n FROM " +
            "sh_true_house_task \r\n  WHERE office_address = 0 AND status = 0 ORDER     BY id DESC";
    System.out.println(sql);
    System.out.println(SQLUtils.formatMySql(sql));
    System.out.println(SQLUtils.formatMySql(sql, new FormatOption(VisitorFeature.OutputUCase)));
}
 

@Override
public TableDataInfo getPresentValue(List<Object> sqlParamsList, MySqlInsertStatement parseSqlStatement,
    StatementAdapter statementAdapter, TableMetaInfo tableMetaInfo) throws SQLException {
    TableDataInfo txcTable = new TableDataInfo();
    txcTable.setTableName(parseSqlStatement.getTableName().getSimpleName());
    List<TxcLine> line = txcTable.getLine();
    List<SQLInsertStatement.ValuesClause> valuesList = parseSqlStatement.getValuesList();
    List<SQLExpr> columns = parseSqlStatement.getColumns();
    for (SQLInsertStatement.ValuesClause valuesClause : valuesList) {
        List<SQLExpr> values = valuesClause.getValues();
        TxcLine txcLine = new TxcLine();
        for (int i = 0; i < columns.size(); i++) {
            TxcField txcField = new TxcField();
            String columnName = SQLUtils.toSQLString(columns.get(i)).replace("\'", "").replace("`", "").trim();
            txcField.setName(columnName);
            if (sqlParamsList != null && !sqlParamsList.isEmpty()) {
                if (columnName.equalsIgnoreCase(tableMetaInfo.getAutoIncrementPrimaryKey())) {
                    sqlParamsList.add(i, getAutoIncrementPrimaryKeyValue(statementAdapter.getStatement()));
                }
                txcField.setValue(sqlParamsList.get(i));
            } else {
                txcField.setValue(SQLUtils.toSQLString(values.get(i)));
            }
            txcField.setJdkValue(SerializeUtils.serialize(txcField.getValue()));
            txcLine.getFields().add(txcField);
        }
        line.add(txcLine);
    }
    return txcTable;
}
 

@Override
protected String selectSql(SQLUpdateStatement mySqlUpdateStatement, Set<String> primaryKeyNameSet) {
    StringBuffer stringBuffer = new StringBuffer();
    stringBuffer.append("SELECT ");
    List<SQLUpdateSetItem> items = mySqlUpdateStatement.getItems();
    for (SQLUpdateSetItem sqlUpdateSetItem : items) {
        stringBuffer.append(SQLUtils.toSQLString(sqlUpdateSetItem.getColumn())).append(",");
    }
    stringBuffer.append(String.join(",", primaryKeyNameSet));
    stringBuffer.append(" from ").append(mySqlUpdateStatement.getTableName().getSimpleName()).append(" where ");
    stringBuffer.append(SQLUtils.toSQLString(mySqlUpdateStatement.getWhere()));
    return stringBuffer.toString();
}
 

@Override
public TableDataInfo getPresentValue(List<Object> sqlParamsList, SQLUpdateStatement parseSqlStatement,
    StatementAdapter statementAdapter, TableMetaInfo tableMetaInfo) throws SQLException {
    TableDataInfo txcTable = new TableDataInfo();
    txcTable.setTableName(parseSqlStatement.getTableName().getSimpleName());
    TxcLine txcLine = new TxcLine();
    List<SQLUpdateSetItem> items = parseSqlStatement.getItems();
    int variantExpr = 0;
    for (int i = 0; i < items.size(); i++) {
        SQLUpdateSetItem sqlUpdateSetItem = items.get(i);
        TxcField txcField = new TxcField();
        String cloumnName =
            SQLUtils.toSQLString(sqlUpdateSetItem.getColumn()).replace("\'", "").replace("`", "").trim();
        txcField.setName(cloumnName);
        if (sqlUpdateSetItem.getValue() instanceof SQLVariantRefExpr) {
            txcField.setValue(sqlParamsList.get(variantExpr++));
        } else if (sqlUpdateSetItem.getValue() instanceof SQLValuableExpr) {
            txcField.setValue(SQLUtils.toSQLString(items.get(i).getValue()));
        } else {
            throw new UnsupportedOperationException(
                String.format("Do not support complex sql,%s", sqlUpdateSetItem.getClass().toString()));
        }
        txcField.setJdkValue(SerializeUtils.serialize(txcField.getValue()));
        txcLine.getFields().add(txcField);
    }
    txcTable.getLine().add(txcLine);
    return txcTable;
}
 

@Override
protected String selectSql(MySqlDeleteStatement mySqlUpdateStatement, Set<String> primaryKeyNameSet) {
    StringBuffer stringBuffer = new StringBuffer();
    stringBuffer.append("SELECT * ");
    stringBuffer.append(" from ").append(mySqlUpdateStatement.getTableName().getSimpleName()).append(" where ");
    stringBuffer.append(SQLUtils.toSQLString(mySqlUpdateStatement.getWhere()));
    return stringBuffer.toString();
}
 

@Override
protected String getWhere(SQLSelectStatement parseSqlStatement) {
    SQLSelectQueryBlock selectQueryBlock = parseSqlStatement.getSelect().getQueryBlock();
    SQLExpr where = selectQueryBlock.getWhere();
    if (where == null) {
        return "";
    }
    return SQLUtils.toSQLString(where);

}
 

public void addSqlMap(String sqlStr, Long hour, Double costTime) {

        if (sqlStr == null || sqlStr.trim().length() == 0) {
            return;
        }

        //clean up the parameter of sql
        String sqlStrPure = SQLUtils.format(sqlStr, JdbcConstants.MYSQL);
        sqlStrPure = ParameterizedOutputVisitorUtils.parameterize(sqlStrPure, JdbcConstants.MYSQL);
        sqlStrPure = sqlStrPure.replaceAll("\\s+", " ");

        SimHash hash1;
        try {
            hash1 = new SimHash(sqlStrPure, 64);
        } catch (Exception e) {
            log.debug(e.getMessage());
            return;
        }

        boolean issame = false;
        for (Map.Entry<SQLKey, Map<Long, SqlStatisticStruct>> entry : _sqlMap.entrySet()) {

            Integer similityThreadHold;
            if (sqlStrPure.length() < 100) {
                similityThreadHold = 8;
            } else if (sqlStrPure.length() < 500) {
                similityThreadHold = 5;
            } else {
                similityThreadHold = 3;
            }

            if (entry.getKey().getHash().hammingDistance(hash1) <= similityThreadHold) {
                addHourMap(entry.getValue(), entry.getKey().getSql(), hour, costTime);
                issame = true;
                break;
            }
        }
        if (!issame) {
            Map<Long, SqlStatisticStruct> hourMap = new HashMap<Long, SqlStatisticStruct>();
            addHourMap(hourMap, sqlStr, hour, costTime);
            SQLKey sqlKey = new SQLKey();
            sqlKey.setHash(hash1);
            sqlKey.setPureSql(sqlStrPure);
            sqlKey.setSql(sqlStr);
            _sqlMap.put(sqlKey, hourMap);
        }
    }
 

@Override
protected String getWhere(SQLUpdateStatement parseSqlStatement) {
    return SQLUtils.toSQLString(parseSqlStatement.getWhere());
}
 

@Override
public void handle(DumpFileContext context, SQLStatement sqlStatement) throws InterruptedException {
    for (String dataNode : context.getTableConfig().getDataNodes()) {
        context.getWriter().write(dataNode, SQLUtils.toMySqlString(sqlStatement), true, true);
    }
}
 

private static boolean handleSingleVariable(String stmt, SQLAssignItem assignItem, ServerConnection c, List<Pair<KeyType, Pair<String, String>>> contextTask) {
    String key = handleSetKey(assignItem, c);
    if (key == null) return false;
    SQLExpr valueExpr = assignItem.getValue();
    KeyType keyType = parseKeyType(key, true, KeyType.SYSTEM_VARIABLES);
    if (!checkValue(valueExpr, keyType)) {
        c.writeErrMessage(ErrorCode.ERR_NOT_SUPPORTED, "setting target is not supported for '" + SQLUtils.toMySqlString(assignItem.getValue()) + "'");
        return false;
    }
    switch (keyType) {
        case NAMES:
            return handleSingleSetNames(stmt, c, valueExpr);
        case CHARSET:
            return handleSingleSetCharset(stmt, c, valueExpr);
        case XA:
            return SetInnerHandler.handleSingleXA(c, valueExpr);
        case TRACE:
            return SetInnerHandler.handleSingleTrace(c, valueExpr);
        case AUTOCOMMIT:
            return SetInnerHandler.handleSingleAutocommit(stmt, c, valueExpr);
        case CHARACTER_SET_CLIENT:
            return handleSingleCharsetClient(c, valueExpr);
        case CHARACTER_SET_CONNECTION:
            return handleSingleCharsetConnection(c, valueExpr);
        case CHARACTER_SET_RESULTS:
            return handleSingleCharsetResults(c, valueExpr);
        case COLLATION_CONNECTION:
            return handleCollationConnection(c, valueExpr);
        case TX_READ_ONLY:
            if (!stmt.toLowerCase().contains("session")) {
                c.writeErrMessage(ErrorCode.ERR_NOT_SUPPORTED, "setting transaction without any SESSION or GLOBAL keyword is not supported now");
                return false;
            }
            return handleTxReadOnly(c, valueExpr);
        case TX_ISOLATION:
            if (!stmt.toLowerCase().contains("session")) {
                c.writeErrMessage(ErrorCode.ERR_NOT_SUPPORTED, "setting transaction without any SESSION or GLOBAL keyword is not supported now");
                return false;
            }
            return handleTxIsolation(c, valueExpr);
        case SYSTEM_VARIABLES:
            if (key.startsWith("@@")) {
                key = key.substring(2);
            }
            if (DbleServer.getInstance().getSystemVariables().getDefaultValue(key) == null) {
                c.writeErrMessage(ErrorCode.ERR_NOT_SUPPORTED, "system variable " + key + " is not supported");
                return false;
            }
            contextTask.add(new Pair<>(KeyType.SYSTEM_VARIABLES, new Pair<>(key, parseVariablesValue(valueExpr))));
            return true;
        case USER_VARIABLES:
            contextTask.add(new Pair<>(KeyType.USER_VARIABLES, new Pair<>(key.toUpperCase(), parseVariablesValue(valueExpr))));
            return true;
        default:
            c.writeErrMessage(ErrorCode.ERR_NOT_SUPPORTED, stmt + " is not supported");
            return false;
    }
}
 

@Override
protected String getWhere(MySqlDeleteStatement parseSqlStatement) {
    return SQLUtils.toSQLString(parseSqlStatement.getWhere());
}