mule进阶之jdbc transport:

简单需求如下：向一个vm:queue发送map消息， mule根据map信息， 动态执行sql， 并返回数据.

select 的查询mule默认返回map数据.
配置文件：my-mule-jdbc-config.xml如下：

Xml代码  

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <mule xmlns="http://www.mulesource.org/schema/mule/core/2.1"  
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
4.     xmlns:jdbc="http://www.mulesource.com/schema/mule/jdbc/2.1"  
5.     xmlns:spring="http://www.springframework.org/schema/beans"  
6.     xmlns:vm="http://www.mulesource.org/schema/mule/vm/2.1"  
7.     xsi:schemaLocation="   
8.           http://www.mulesource.com/schema/mule/jdbc/2.1 http://www.mulesource.com/schema/mule/jdbc/2.1/mule-jdbc-ee.xsd   
9.           http://www.mulesource.org/schema/mule/core/2.1 http://www.mulesource.org/schema/mule/core/2.1/mule.xsd   
10.           http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd   
11.              http://www.mulesource.org/schema/mule/vm/2.1 http://www.mulesource.org/schema/mule/vm/2.1/mule-vm.xsd">  
12.   
13.   
14.     <spring:bean id="dataSource"  
15.         class="org.apache.commons.dbcp.BasicDataSource"  
16.         destroy-method="close">  
17.         <spring:property name="driverClassName"  
18.             value="com.mysql.jdbc.Driver" />  
19.         <spring:property name="url"  
20.             value="jdbc:mysql://192.168.10.120/sand_res" />  
21.         <spring:property name="username" value="username" />  
22.         <spring:property name="password" value="888" />  
23.         <spring:property name="maxActive" value="30" />  
24.         <spring:property name="maxIdle" value="10" />  
25.         <spring:property name="maxWait" value="1000" />  
26.         <spring:property name="defaultAutoCommit" value="true" />  
27.     </spring:bean>  
28.     <jdbc:connector name="jdbcConnector" dataSource-ref="dataSource">  
29.         <jdbc:query key="selectUser"  
30.             value="SELECT first_name,last_name FROM app_user where first_name=#[map-payload:firstName]" />  
31.   
32.         <jdbc:query key="insertUser"  
33.             value="insert into app_user   
34.             (id,first_name,last_name ) values(#[map-payload:id], #[map-payload:firstName], #[map-payload:lastName])" />  
35.     </jdbc:connector>  
36.       
37.   
38.     <!--  
39.         The Mule model initialises and manages your UMO components  
40.     -->  
41.     <model name="databaseModel">  
42.         <service name="insertUMO">  
43.             <!-- any number of endpoints can be added to an inbound router -->  
44.             <inbound>  
45.                 <vm:inbound-endpoint path="query"/>  
46.             </inbound>  
47.   
48.             <!--   
49.                 An outbound router can have one or more router configurations that can be   
50.                 invoked depending on business rules, message contents, headers or any other   
51.                 criteria. The pass-through-router is a router that automatically passes   
52.                 on every message it receives   
53.             -->  
54.             <outbound>  
55.                 <pass-through-router>  
56.                     <jdbc:outbound-endpoint queryKey="selectUser" synchronous="true"/>  
57.                 </pass-through-router>  
58.             </outbound>  
59.         </service>  
60.     </model>  
61. </mule>  

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesource.org/schema/mule/core/2.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jdbc="http://www.mulesource.com/schema/mule/jdbc/2.1"
    xmlns:spring="http://www.springframework.org/schema/beans"
    xmlns:vm="http://www.mulesource.org/schema/mule/vm/2.1"
    xsi:schemaLocation="
          http://www.mulesource.com/schema/mule/jdbc/2.1 http://www.mulesource.com/schema/mule/jdbc/2.1/mule-jdbc-ee.xsd
          http://www.mulesource.org/schema/mule/core/2.1 http://www.mulesource.org/schema/mule/core/2.1/mule.xsd
          http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
             http://www.mulesource.org/schema/mule/vm/2.1 http://www.mulesource.org/schema/mule/vm/2.1/mule-vm.xsd">


    <spring:bean id="dataSource"
        class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <spring:property name="driverClassName"
            value="com.mysql.jdbc.Driver" />
        <spring:property name="url"
            value="jdbc:mysql://192.168.10.120/sand_res" />
        <spring:property name="username" value="username" />
        <spring:property name="password" value="888" />
        <spring:property name="maxActive" value="30" />
        <spring:property name="maxIdle" value="10" />
        <spring:property name="maxWait" value="1000" />
        <spring:property name="defaultAutoCommit" value="true" />
    </spring:bean>
    <jdbc:connector name="jdbcConnector" dataSource-ref="dataSource">
        <jdbc:query key="selectUser"
            value="SELECT first_name,last_name FROM app_user where first_name=#[map-payload:firstName]" />

        <jdbc:query key="insertUser"
            value="insert into app_user
            (id,first_name,last_name ) values(#[map-payload:id], #[map-payload:firstName], #[map-payload:lastName])" />
    </jdbc:connector>
   

    <!--
        The Mule model initialises and manages your UMO components
    -->
    <model name="databaseModel">
        <service name="insertUMO">
            <!-- any number of endpoints can be added to an inbound router -->
            <inbound>
                <vm:inbound-endpoint path="query"/>
            </inbound>

            <!--
                An outbound router can have one or more router configurations that can be
                invoked depending on business rules, message contents, headers or any other
                criteria. The pass-through-router is a router that automatically passes
                on every message it receives
            -->
            <outbound>
                <pass-through-router>
                    <jdbc:outbound-endpoint queryKey="selectUser" synchronous="true"/>
                </pass-through-router>
            </outbound>
        </service>
    </model>
</mule>

 

注意： 如果mule采用2.1， jdbc transport的namespase后缀为com， 而不是org， 如果写错，IDE不会提示，程序异常也很奇怪,让我折腾了一个下午:(
测试程序：

Java代码  

1. public class MyMuleClientTest   
2. {   
3.     public static void main(String[] args) throws MuleException   
4.     {   
5.         // create mule   
6.         MuleContext muleContext;   
7.         String config = "my-mule-jdbc-config.xml";   
8.         muleContext = new DefaultMuleContextFactory().createMuleContext(config);   
9.         muleContext.start();   
10.         // creat mule client   
11.         MuleClient client = new MuleClient();   
12.         Map map = new HashMap();   
13.         map.put("firstName", "feng");   
14.         MuleMessage response = client.send("vm://query", map, null);          
15.         System.out.println("response = " + response.getPayload());   
16.     }   
17.   
18. }  

public class MyMuleClientTest
{
    public static void main(String[] args) throws MuleException
    {
        // create mule
        MuleContext muleContext;
        String config = "my-mule-jdbc-config.xml";
        muleContext = new DefaultMuleContextFactory().createMuleContext(config);
        muleContext.start();
        // creat mule client
        MuleClient client = new MuleClient();
        Map map = new HashMap();
        map.put("firstName", "feng");
        MuleMessage response = client.send("vm://query", map, null);       
        System.out.println("response = " + response.getPayload());
    }

}

 

执行的sql为：
SELECT first_name,last_name FROM app_user where first_name="feng"

insert的执行类似，只需修改如下：

Xml代码  

1. <outbound>  
2.     <pass-through-router>  
3.         <jdbc:outbound-endpoint queryKey="insertUser" synchronous="true"/>  
4.     </pass-through-router>  
5. </outbound>  

            <outbound>
                <pass-through-router>
                    <jdbc:outbound-endpoint queryKey="insertUser" synchronous="true"/>
                </pass-through-router>
            </outbound>

 

感觉mule的jdbc transtort， 就是微缩版的ibatis