mysql - Fuse ide how to define database table end point -

i have heard alot of success integration story when comes apache camel fuse. hence. here im starting explore fuse ide, simple task on top of head, achieve:

1. read fix length file
2. parse fix length file
3. persist mysql database table

i able far as:

1. read fix length file (with endpoint "file:src/data/japan?noop=true")
2. define marshal bindy , define pojo package model @fixedlengthrecord annotation
3. then stuck... how persist pojo mysql database table? can see jdbc, ibatis , jpa end point, how accomplish in fuse ide?

my pojo package: package com.mbww.model;  import org.apache.camel.dataformat.bindy.annotation.datafield; import org.apache.camel.dataformat.bindy.annotation.fixedlengthrecord;  @fixedlengthrecord(length=91) public class japan {  @datafield(pos=1, length=10) private string tnr;   @datafield(pos=11, length=10) private string atr;  @datafield(pos=21, length=70) private string str;  } 

well can use of following components read , write database:

1. jdbc
2. ibatis
3. mybatis
4. spring-jdbc
5. sql
6. custom processor

i going show how use custom processor insert rows table. main reason work messages , exchange , give more of insight camel. of other components can used following documentation on camel site.

so lets review have. reading file , converting body bindy object. each line in text file camel send bindy object of class com.mbww.model.japan next end point. next end point needs talk database. there 1 problem can spot using marshal should using unmarshal.

the documentation states: if receive message 1 of camel components such file, http or jms want unmarshal payload bean can process using bean integration or perform predicate evaluation , forth. use unmarshal word in dsl in java or xml configuration.

your bindy class looks missing getters , setters modify class this:

package com.mbww.model; import org.apache.camel.dataformat.bindy.annotation.datafield; import org.apache.camel.dataformat.bindy.annotation.fixedlengthrecord;  @fixedlengthrecord(length=91) public class japan {    @datafield(pos=1, length=10)   private string tnr;     @datafield(pos=11, length=10)   private string atr;    @datafield(pos=21, length=70)   private string str;    public string gettnr() {     return tnr;   }    public void settnr(string tnr) {     tnr = tnr;   }    public string getatr() {     return atr;   }    public void setatr(string atr) {     atr = atr;   }    public string getstr() {     return str;   }    public void setstr(string str) {     str = str;   }  } 

first need create data source database in route. first thing add mysql driver jar maven dependencies open pom.xml file , add following dependency it.

<dependency>     <groupid>mysql</groupid>     <artifactid>mysql-connector-java</artifactid>     <!-- use version of driver or later version of driver -->     <version>5.1.25</version> </dependency> 

right need declare custom processor use in route use driver , insert received body table.

so lets create new class in fuse ide called persisttodatabase code below:

package com.mbww.japandata; import java.sql.drivermanager; import java.sql.connection; import java.sql.preparedstatement; import java.sql.sqlexception; import java.util.map;  import org.apache.camel.body; import org.apache.camel.exchange; import org.apache.camel.handler; import org.apache.camel.headers;  import com.mbww.model.japan; import com.mysql.jdbc.statement;   public class persisttodatabase { @handler public void persistrecord (         @body japan msgbody         , @headers map hdr         , exchange exch ) throws exception {     try {         class.forname("com.mysql.jdbc.driver");     } catch (classnotfoundexception e) {         system.out.println("where mysql jdbc driver?");         e.printstacktrace();         return;     }      system.out.println("mysql jdbc driver registered!");     connection connection = null;      try {         connection = drivermanager.getconnection("jdbc:mysql://localhost:3306/databasename","root", "password");      } catch (sqlexception e) {         system.out.println("connection failed! check output console");         e.printstacktrace();         return;     }      if (connection != null) {         system.out.println("you made it, take control database now!");     } else {         system.out.println("failed make connection!");     }     try {         preparedstatement stmt=connection.preparestatement("insert japandate(tnr,atr,str) values(?,?,?)");         stmt.setstring(1, msgbody.gettnr());         stmt.setstring(2, msgbody.getatr());         stmt.setstring(1, msgbody.getstr());         int rows = stmt.executeupdate();         system.out.println("number of rows inserted: "+integer.tostring(rows));     }     catch(exception e){         system.out.println("error in executing sql statement: "+e.getmessage() );         throw new exception(e.getmessage());     }  } } 

this class pojo nothing fancy except @handler annotation on persistrecord. annotation tells camel persistrecord method/procedure handle message exchange. notice method persistrecord has parameter of type japan. mentioned earlier when call conversion bean in camel route translates each line japan object , passes along route.

the rest of code how handle jdbc connection , calling insert statement.

we done 1 last thing do. need declare class in our camel route xml. file typically called camel-route.xml or blueprint.xml depending on arch type. open source tab , add following line <bean id="japanpersist" class="com.mbww.japandata.persisttodatabase"/> before <camelcontext> tag.

this declares new spring bean called japanpersist based on class added camel route. can reference bean inside camel route.

thus final route xml file should this:

<?xml version="1.0" encoding="utf-8"?> <blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"    xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"    xmlns:camel="http://camel.apache.org/schema/blueprint"    xsi:schemalocation="    http://www.osgi.org/xmlns/blueprint/v1.0.0 http://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd    http://camel.apache.org/schema/blueprint http://camel.apache.org/schema/blueprint/camel-blueprint.xsd">  <bean id="japanpersist" class="com.mbww.japandata.persisttodatabase"/>    <camelcontext trace="false" id="blueprintcontext" xmlns="http://camel.apache.org/schema/blueprint">     <route id="japandatafromfiletodb">         <from uri="file:src/data/japan"/>         <unmarshal ref="japan"/>         <bean ref="japanpersist"/>             </route> </camelcontext>  </blueprint> 

or see screen shot below:

once understand technique can start scaling solution using splitter, connection pooling , threading massive amount of concurrent inserts etc.

using technique above learned how inject own beans camel route give ability work messages directly in code.

i have not tested code there bug or 2 idea should clear.