欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mule ESB 学习笔记(13)CSV数据文件到数据库

程序员文章站 2022-07-12 19:00:38
...

  简单需求:       

             在应用中需要数据从一个数据库传输到另一个数据库的(数据库类型不一样),将数据转换为csv文件,再从csv文件insert到数据库的操作的具体实现:

 

mule-jdbc-csv-load.xml

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns:jdbc-ee="http://www.mulesoft.org/schema/mule/ee/jdbc" xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:file="http://www.mulesoft.org/schema/mule/file"
      xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
      xmlns:spring="http://www.springframework.org/schema/beans"
      xsi:schemaLocation="           http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd           http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd           http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd           http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/current/mule-vm.xsd           http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/current/mule-jdbc-ee.xsd">
    <spring:bean id="property-placeholder" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
        <spring:property name="location" value="classpath:db.properties"></spring:property>  
    </spring:bean>
    
    <spring:bean id="jdbcDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" > 
        <spring:property name="driverClassName" value="${database.driverClassName}"></spring:property>  
        <spring:property name="url" value="${database.url}"></spring:property>  
        <spring:property name="username" value="${database.username}"></spring:property>  
        <spring:property name="password" value="${database.password}"></spring:property>  
    </spring:bean>
    
    <jdbc-ee:connector name="jdbcConnector" dataSource-ref="jdbcDataSource"> 
        <jdbc-ee:query key="commitLoadedMules" value="insert into mule_source 
            (ID, MULE_NAME, RANCH, COLOR, WEIGHT, AGE) 
            values 
            (#[map-payload:ID], #[map-payload:MULE_NAME], #[map-payload:RANCH], #[map-payload:COLOR], #[map-payload:WEIGHT], #[map-payload:AGE])"></jdbc-ee:query>  
    </jdbc-ee:connector>
    <file:connector name="fileConnector" autoDelete="false" pollingFrequency="100000000"></file:connector>
    <file:endpoint  path="/E:/upload/data/"  moveToDirectory="/e:/download/data/" name="get" pollingFrequency="2000" connector-ref="fileConnector"></file:endpoint>
    <jdbc-ee:csv-to-maps-transformer name="CSV2Maps" delimiter="," mappingFile="mules-csv-format.xml" ignoreFirstRecord="true"></jdbc-ee:csv-to-maps-transformer>
    <model name="LoaderModel"> 
        <service name="CSVLoader"> 
            <inbound> 
                <file:inbound-endpoint ref="get" transformer-refs="CSV2Maps"> 
                    <file:filename-wildcard-filter pattern="*.csv"></file:filename-wildcard-filter>  
                </file:inbound-endpoint>  
            </inbound>  
            <echo-component></echo-component>  
            <outbound> 
                <pass-through-router> 
                    <jdbc-ee:outbound-endpoint queryKey="commitLoadedMules"></jdbc-ee:outbound-endpoint>  
                </pass-through-router>  
            </outbound>  
        </service>  
    </model>

    <!-- Long pollingFrequency so that this operations runs only once -->
</mule>

 

 

格式化文件:

mules-csv-format.xml

 

 

 

<?xml version='1.0'?>
<!-- DTD can be pulled from the Jar or over the web-->
<!DOCTYPE PZMAP SYSTEM  "pzfilereader.dtd" >
<!--<!DOCTYPE PZMAP SYSTEM  "http://pzfilereader.sourceforge.net/pzfilereader.dtd" >-->
<PZMAP>
    <RECORD id="header" indicator="H" elementNumber="1">
        <COLUMN name="RECORDINDICATOR" />
        <COLUMN name="HEADERDATA" />
    </RECORD>
    <COLUMN name="ID" />
    <COLUMN name="MULE_NAME" />
    <COLUMN name="RANCH" />
    <COLUMN name="COLOR" />
    <COLUMN name="WEIGHT" />
    <COLUMN name="AGE" />
    <RECORD id="trailer" indicator="T" elementNumber="1">
        <COLUMN name="RECORDINDICATOR" />
        <COLUMN name="TRAILERDATA" />
    </RECORD>
</PZMAP> 

 

测试代码:

package com.easyway;

import org.mule.api.MuleContext;
import org.mule.api.context.MuleContextFactory;
import org.mule.config.spring.SpringXmlConfigurationBuilder;
import org.mule.context.DefaultMuleContextFactory;
public class Main {
	public static void main(String[] args) {
		 try {
	            String configFile = "mule-jdbc-csv-load.xml";
	            String[] configFileArr = new String[] {configFile };
	            MuleContextFactory muleContextFactory = new DefaultMuleContextFactory();
	            MuleContext muleContext = muleContextFactory
	                    .createMuleContext(new SpringXmlConfigurationBuilder(configFileArr));
	            muleContext.start();
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	}

}

 

数据文件看附件: