camel-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Stark <cst...@redhat.com>
Subject Re: Need Example, use result from SQL-Query as input into Second Database/Table
Date Tue, 14 Nov 2017 11:10:09 GMT
Hello Claus,

This was exactly what I needed


Thank you so much

Christian

On Tue, Nov 14, 2017 at 12:05 PM, Claus Ibsen <claus.ibsen@gmail.com> wrote:

> On Tue, Nov 14, 2017 at 11:53 AM, Christian Stark <starki78c@gmail.com>
> wrote:
> > Thanks Claus
> >
> > I found the following example very helpful.
> > The SQL returns multiple rows and I need to map the Columns to other
> > Objects.
> >
> > http://www.javarticles.com/2015/07/apache-camel-jdbc-select-example.html
> >
> >
> > The only thing I need to find out is how to reuse the Output of my
> > Transformer Class as Input for the Insert.
> >
>
> What information from the Article java class do you want to store in
> the SQL insert?
>
> You are doing now: NSERT INTO USERS (column1) values(*${body[0]})
>
> Where body[0] means that you access a java.util.list or array and grab
> the first element.
>
> Instead you may want to call a getter/property on the Article class,
> for example the getAuthor method
>
> INSERT INTO USERS (column1) values(${body.getAuthor()})
>
> And if its a getter you can use a short-hand version
>
> INSERT INTO USERS (column1) values(${body.author})
>
> And you may need to add single quotes around the values, as if its a
> varchar/string type, eg to be SQL compliant
>
> INSERT INTO USERS (column1) values('${body.author'})
>
>
>
>
>
> >
> >
> > <camelContext id="camelContext-1b6e9b83-bc33-4311-9e9b-0f2458fcedd9"
> xmlns="
> > http://camel.apache.org/schema/spring">
> >         <route id="_route1">
> >             <from id="_from1" uri="timer://timer1?period=2s"/>
> >             <setBody id="_setBody1">
> >                 <constant>
> >                     select user_nm,email_id from drupal_users where
> user_id
> > &lt; 5000
> >                 </constant>
> >             </setBody>
> >             <to id="_to1" uri="jdbc:myDataSource"/>
> >             <split id="_split1">
> >                 <simple>${body}</simple>
> >                 <to uri="bean:rowProcessor" />
> >                 <to uri="stream:out"/>
> >                 <setBody id="_setBody2">
> >                     <simple>INSERT INTO USERS (column1)
> values(*${body[0]})*
> > </simple>
> >                 </setBody>
> >               <to id="_to3" uri="jdbc:myDataSource2"/>
> >             </split>
> >         </route>
> >   <bean class="org.mycompany.MyTransformer" id="rowProcessor"/>
> >
> >
> > import java.util.Map;
> >
> > import org.apache.camel.Exchange;
> > import org.apache.camel.Processor;
> >
> > public class MyTransformer implements Processor {
> >
> >     public void process(Exchange exchange) throws Exception {
> >         Map<String, Object> row = exchange.getIn().getBody(Map.class);
> >         System.out.println("Processing " + row);
> >         Article article = new Article();
> >
> >         article.setAuthor((String) row.get("email_id"));
> >
> >
> >         *exchange.getOut().setBody(article);*
> >
> >     }
> >
> >
> >
> >
> >
> >
> > 2017-11-13 13:48 GMT+01:00 Claus Ibsen <claus.ibsen@gmail.com>:
> >
> >> Hi
> >>
> >> If the 1st query return only 1 row, then you can set
> outputType=SelectOne
> >>
> >> Then the returned value is a Map
> >> Then you can use the column names as-is in the 2nd query
> >>
> >> See the docs at
> >> https://github.com/apache/camel/blob/master/components/
> >> camel-sql/src/main/docs/sql-component.adoc
> >>
> >> On Mon, Nov 13, 2017 at 1:41 PM, Christian Stark <starki78c@gmail.com>
> >> wrote:
> >> > Hello,
> >> >
> >> > Assuming the following example: I'd like to know how to
> >> control/manipulate
> >> > the variables to insert the input from the first query from
> >> table/database1
> >> > into the second table/database2.
> >> > What is the best option to manipulate $input1,$input2,$input3
> >> >
> >> > Do we need a transformer or a converter?
> >> >
> >> >
> >> >  <route id="demo_route">
> >> >             <from id="route-timer" uri="timer:foo?period=20000"/>
> >> >             <to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
> >> > testtable where key = ':3685127';?dataSource=myDataSource"/>
> >> >
> >> >             <to id="_to2" uri="sql:INSERT INTO USERS
> >> > (column1,column2,column3) values
> >> > ('$input1,$input2,$input3)?dataSource=myDataSource2"/>
> >> >  </route>
> >> >
> >> >
> >> >
> >> > Any input is appreciated
> >> >
> >> >
> >> > Thanks
> >> > Christian
> >>
> >>
> >>
> >> --
> >> Claus Ibsen
> >> -----------------
> >> http://davsclaus.com @davsclaus
> >> Camel in Action 2: https://www.manning.com/ibsen2
> >>
>
>
>
> --
> Claus Ibsen
> -----------------
> http://davsclaus.com @davsclaus
> Camel in Action 2: https://www.manning.com/ibsen2
>



-- 
Beste GrĂ¼sse / Bien cordialement / Kind Regards,

*Christian Stark*
Technical Account Manager
Red Hat GmbH
Werner-von-Siemens-Ring 14, 85630 Grasbrunn

Phone:  +4989205071106
Mobile: +4915161659068

Email: christian.stark@redhat.com
Video: bluejeans.com/9885521768 <https://redhat.bluejeans.com/9885521768/>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message