Mule-JDBC配置实例

来源：互联网发布：全民飞机巅峰飞跃算法编辑：程序博客网时间：2024/05/01 05:13

最近一个集成项目需求如下：从Oracle临时表中轮询数据，将轮询结果插入到MSSQLSERVER数据库中，然后在确保数据插入MSSQLSERVER后，将Oracle临时表中的数据删除。由于初次接触Mule，一些概念和应用尚未搞明白，现将实现过程整理如下，仅供参考。

重点注意事项：

1.Mule版本：mule-standalone-3.3.0社区版；

2.NameSpace：比如下文，千万别迷信《Mule 3 User Guide》中关于JDBC Transport相关段落，这可是我逐个验证的结果，信不信由你；

3.chaining-router：使用chaining-router返回插入MSSQLSERVER时验证消息时，必须使用exchange-pattern="request-response"属性，否则Mule会提示“Chaining router cannot process any further targets. There was no result returned from endpoint invocation”，这个问题花费了我好长时间才解决；

4.如果无法判断sql语句是否被执行，可以故意将sql语句搞出点语法错误，这样可以在console界面中看到错误信息以及参数值，达到调试的目的；

5.建议大家认真了解一下Mule的消息机制，否则会走不少弯路

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:jms="http://www.mulesoft.org/schema/mule/jms/3.3"
xmlns:stdio="http://www.mulesoft.org/schema/mule/stdio"
xmlns:management="http://www.mulesoft.org/schema/mule/management/3.3"
xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
xmlns:http="http://www.mulesoft.org/schema/mule/http/3.3"
xmlns:axis="http://www.mulesoft.org/schema/mule/axis/3.3"
xmlns:soap="http://www.mulesoft.org/schema/mule/soap/3.3"
xsi:schemaLocation="
          http://www.mulesoft.org/schema/mule/corehttp://www.mulesoft.org/schema/mule/core/3.3/mule.xsd
          http://www.mulesoft.org/schema/mule/jdbchttp://www.mulesoft.org/schema/mule/jdbc/3.3/mule-jdbc.xsd

          http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.1.xsd
          http://www.springframework.org/schema/jeehttp://www.springframework.org/schema/jee/spring-jee-2.5.xsd
          http://www.springframework.org/schema/utilhttp://www.springframework.org/schema/util/spring-util-2.5.xsd

          http://www.mulesoft.org/schema/mule/jms/3.3http://www.mulesoft.org/schema/mule/jms/3.3/mule-jms.xsd
          http://www.mulesoft.org/schema/mule/stdiohttp://www.mulesoft.org/schema/mule/stdio/3.3/mule-stdio.xsd
          http://www.mulesoft.org/schema/mule/management/3.3http://www.mulesoft.org/schema/mule/management/3.3/mule-management.xsd
          http://www.mulesoft.org/schema/mule/vmhttp://www.mulesoft.org/schema/mule/vm/3.3/mule-vm.xsd
          http://www.mulesoft.org/schema/mule/http/3.3http://www.mulesoft.org/schema/mule/http/3.3/mule-http.xsd
          http://www.mulesoft.org/schema/mule/axis/3.3http://www.mulesoft.org/schema/mule/axis/3.3/mule-axis.xsd
          http://www.mulesoft.org/schema/mule/soap/3.3http://www.mulesoft.org/schema/mule/soap/3.3/mule-soap.xsd">



<spring:beans>
  <spring:bean id="hisjee-ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <spring:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
      <spring:property name="url" value="jdbc:oracle:thin:@192.168.1.5:1521:qzy"/>
      <spring:property name="username" value="interface"/>
      <spring:property name="password" value="combobox"/>
  </spring:bean>

<spring:bean id="founder-ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <spring:property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
       <spring:property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=his_interface"/>
       <spring:property name="username" value="sa"/>
       <spring:property name="password" value="combobox"/>
  </spring:bean>
</spring:beans>

<jdbc:connector name="hisjee-connector" dataSource-ref="hisjee-ds" pollingFrequency="100000">
  <jdbc:query key="queryHisZyxxTemp" value="select to_char(act_time,'yyyy-mm-dd hh24:mi:ss') ACT_TIME,ZYH,TIMES,NAME,SEX,AGE,BIRTHDAY,
               SOCIAL,RESPONE,RELATION,TEL,
                     DEPT,DOCTOR,OPERA_FLAG,to_char(OPERA_DATE,'yyyy-mm-dd hh24:mi:ss') OPERA_DATE,BED,NATION,FLAG
                   from interface.jk_his_zyxx_tmp t order by t.act_time,t.zyh,t.times"/>
  <jdbc:query key="queryFounderZyxx.ack" value="delete from interface.jk_his_zyxx_tmp ,
        where act_time = #[map-payload:act_time]
                    and zyh= #[map-payload:zyh] and times= #[map-payload:times]"/>
     </jdbc:connector>

<jdbc:connector name="founder-connector" dataSource-ref="founder-ds">
  <jdbc:query key="queryHisZyxxTemp.ack" value="insert into [his_interface].[dbo].[jk_his_zyxx]
   ([act_time],[zyh],[times],[name],[sex],[age],[birthday],[social],[respone],[relation],
   [tel],[dept],[doctor],[opera_flag],[opera_date],[bed],[nation],[flag])
            values(#[map-payload:act_time],#[map-payload:zyh],#[map-payload:times],#[map-payload:name],
            #[map-payload:sex],#[map-payload:age],#[map-payload:birthday],#[map-payload:social],#[map-payload:respone],#[map-payload:relation],
             #[map-payload:tel],#[map-payload:dept],#[map-payload:doctor],#[map-payload:opera_flag],#[map-payload:opera_date],
             #[map-payload:bed],#[map-payload:nation],#[map-payload:flag])"/>
  <jdbc:query key="queryFounderZyxx"
     value="SELECT zyh,times,name,sex,age,birthday,social,respone,
        relation,tel,dept,doctor,CONVERT(varchar(100),act_time,120) act_time
        bed,nation,flag,opera_flag,CONVERT(varchar(100),opera_date,120) opera_date
         FROM his_interface.dbo.jk_his_zyxx
         where act_time = #[map-payload:act_time]
                    and zyh= #[map-payload:zyh] and times= #[map-payload:times]"/>
     </jdbc:connector>