Mule Sql查询-将参数传递给IN运算符 [英] Mule Sql Query - passing parameters to the IN operator

问题描述

我正在尝试创建一个执行许多DB(Microsoft SQL Server 2008 DB)操作的m子流.

I'm trying to create a mule flow that does a lot of DB (Microsoft SQL Server 2008 DB) operations.

我执行的第一个数据库查询返回ID列表

The first DB query I do returns a list of IDs

select id from first_table;

我通过构建ArrayList的Java转换器运行有效负载: [1,2,3,4,5] 然后将其另存为变量索引

I run the payload through a Java transformer that builds an ArrayList: [1,2,3,4,5] and then save it as a variable indices

然后我尝试在另一个查询中使用此数组

Then I try to use this Array in another query

select * from another_table where first_table_fk in (#[flowVars.indices]);

但是无论我做什么我都会不断得到错误

But no matter what I do I keep getting the error

不支持从UNKNOWN到UNKNOWN的转换.

The conversion from UNKNOWN to UNKNOWN is unsupported.

使用IN运算符时，Mule无法处理java.util.ArrayList(或常规数组int []).

Mule can't handle the java.util.ArrayList (or a regular array, int[]) when using the IN operator.

然后我尝试将整个查询创建为字符串变量(然后在中引用它).

I then tried creating the whole query as a string variable (and then referencing it in the ).

以下结果除外: java.lang.IllegalArgumentException:找不到SQL语句的SQL策略:#[flowVars.queryString]

This results with an exception: java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement: #[flowVars.queryString]

能否解决我的问题(在Mule sql查询中使用IN运算符)?

Can you please help me with my problem (using the IN operator in Mule sql queries) ?

我正在使用Mule studio 3.4.0 CE.

I am using Mule studio 3.4.0 CE.

谢谢！

推荐答案

Mule使用语句(来自java.sql)，因此不支持以这种方式使用IN.每次出现的MEL表达式(#[])都将替换为问号(?)，并且实际值将作为参数传递.我建议您通过动态构建查询并将其分配给JDBC连接器并使用通用端点执行它来解决此问题.

Mule uses statements (from java.sql) and thus doesn't support using IN in that way. Every occurance of MEL-expression (#[]) will be replaced by a question mark (?) and the actual values will be passed as parameters. I suggest you solve this by building the query dynamically and assign it to the JDBC-connector, and the execute it using a generic endpoint.

1. 创建这样的Java类:

1. Create a Java class like this:

public class CustomQueryBuilder implements Callable {
    @Override
    public Object onCall(MuleEventContext eventContext) throws Exception {
        JdbcConnector c = (JdbcConnector) eventContext.getMuleContext().getRegistry().lookupConnector("JDBC_connector");

        StringBuilder query = new StringBuilder();
        String queryBase = "select * from another_table where first_table_fk in (";
        query.append(queryBase);

        int numIndices = ((ArrayList<Integer>)eventContext.getMessage().getInvocationProperty("indices")).size();
        ArrayList<String> indices = new ArrayList<String>();
        for(int i=0; i<numIndices; i++) {
                indices.add("#[flowVars.indices[" + i + "]");
        }
        query.append(StringUtils.join(indices, ", "));
        query.append(")");

        String finalQuery = query.toString();

        MessageDigest md = MessageDigest.getInstance("MD5");
        String queryDigest = String.format("%1$032X",new BigInteger(1, md.digest(finalQuery.getBytes("UTF-8"))));

        if (!c.getQueries().containsKey(queryDigest)) {
                c.getQueries().put(queryDigest, finalQuery);
        }

        eventContext.getMessage().setInvocationProperty("generatedQueryKey", queryDigest);

        return eventContext.getMessage();
    }
}

在设置索引"流变量后在流中使用它.

Use it in your flow after you set the "indices" flow var.

<component class="CustomQueryBuilder"/>

然后插入通用出站终结点而不是jdbc出站终结点，并引用创建的查询.

Then insert an generic outbound endpoint instead of your jdbc outbound endpoint and reference the created query.

<outbound-endpoint exchange-pattern="request-response" address="jdbc://#[flowVars.generatedQueryKey]" />

这篇关于Mule Sql查询-将参数传递给IN运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！