camel-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean Francois LE BESCONT <jflebesc...@gmail.com>
Subject Re: Enrich with data from SQL
Date Thu, 07 Mar 2013 08:54:07 GMT
Thanks for all these explictions ! Hope I will give back my knowledge when
I will have enough camel knowledge :)

With filter, I goto my enrich only for the first line of my file.
 .filter(simple("${property.CamelSplitIndex} == 0"))
      .enrich("direct:datasource", new AggregationEnrichissementZone())

I gonna also try your method Raul.

Thanks





2013/3/7 Raul Kripalani <raul@evosent.com>

> If you want to query the DB once, you should definitely restructure the
> route a bit.
>
> Here's the simplest option IMO. The goal is to query once and make the
> query result available to all split tasks:
>
> - Run bindy before the split, which will generate a List of AED objects.
> - Create a small bean that constructs the SQL query from the List of AED.
> - Do the JDBC enrichment before the split.
> - In your aggregation strategy, inject the result of the DB query in an
> exchange property.
> - Then run the split without specifying any split criteria (no tokenize).
> We split lists into its constituents out of the box.
> - Inside the split body, enrich your AED object with the DB results stored
> in the above exchange property.
>
> Hope this helps!
> Raúl.
>
> Sent while on the move.
> On 7 Mar 2013 08:25, "Jean Francois LE BESCONT" <jflebescont@gmail.com>
> wrote:
>
> > Sorry if my explanation are not good ... :)
> >
> > I have a csv file. I would like  to process this file to enrich with data
> > in sql table (then write enriched line in output).
> >
> > I  process file with a binding in business class :
> >
> > .from("file://mycsv.csv")
> > .split()
> > .tokenize("\n")
> > .streaming()
> > .unmarshal()
> > .bindy(BindyType.Csv, AED.class)
> >
> > Then I would like to enrich the line converted in AED to add element
> from a
> > DB table :
> > .enrich("direct:datasource", new AggregationEnrichissementZone())
> >
> >
> > The AggregationEnrichissementZone do my business transformation with both
> > the current line and the content of the database which is in
> > direct:datasource. It works no problem ! The
> > direct:datasource is called correctly and retrieve the data from sql
> table.
> > Initially I wrote it like this :
> >
> > from("direct:datasource")
> > .setBody(constant("SELECT * FROM TEST"))
> > .to("jdbc:dataSource")
> > .to("direct:ds")
> > .end();
> >
> > But direct:datasource is called for each line which cost to much time. I
> > would like to make the database query only once for my csv file.
> >
> > Thanks
> >
> > JF
> >
> >
> >
> >
> > 2013/3/7 Claus Ibsen <claus.ibsen@gmail.com>
> >
> > > On Thu, Mar 7, 2013 at 8:03 AM, Jean Francois LE BESCONT
> > > <jflebescont@gmail.com> wrote:
> > > > it  is a really  simple  studies case no ? I can't be the first guy
> who
> > > > don't want to reload the datasource on each line isn't it ?
> > > >
> > >
> > > I dont think people can understand what you try to do and pasting a
> > > lot of code and with little detail to go along.
> > > I suggest to debug your application and see what goes on and figure
> > > out your solution.
> > >
> > >
> > >
> > > >
> > > > 2013/3/6 Jean Francois LE BESCONT <jflebescont@gmail.com>
> > > >
> > > >> I made a mistake in my test. It doesn't do what expected ...
> > > >>
> > > >> Still the same example :
> > > >>
> > > >> .from(...)
> > > >>  .split()
> > > >> .tokenize("\n")
> > > >>  .streaming()
> > > >> .unmarshal()
> > > >> .bindy(BindyType.Csv, AED.class)
> > > >>  .enrich("direct:refreshReferentielDynamic", new
> > > >> AggregationEnrichissementZone())
> > > >>
> > > >> And a :
> > > >>
> > > >> from("direct:refreshReferentielDynamic")
> > > >>  .processRef("validatingProcessor")
> > > >>
> > > >> .end();
> > > >>
> > > >>
> > > >> And
> > > >>
> > > >>
> > > >> public class ValidatingProcessor implements Processor {
> > > >>
> > > >> @Autowired
> > > >>  DriverManagerDataSource dataSource;
> > > >>  public void process(Exchange exchange) throws Exception {
> > > >>
> > > >>
> > > >> Integer idxParse = (Integer)
> > exchange.getProperty(Exchange.SPLIT_INDEX);
> > > >>  if (idxParse != null & idxParse == 0) {
> > > >> JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
> > > >> List<Map<String, Object>> ref = jdbcTemplate
> > > >>  .queryForList("SELECT ID , CODE , LIBELLE FROM  ZONE_T ");
> > > >> Map<String, Zone> newRef = new HashMap();
> > > >>  for (Map<String, Object> map : ref) {
> > > >>  int id=(Integer) map.get("ID");
> > > >>  String code =(String) map.get("CODE");
> > > >>  String libelle =(String)map.get("LIBELLE");
> > > >>  newRef.put(code, new Zone(id,code,libelle));
> > > >> }
> > > >>  exchange.getOut().setBody(newRef) ;
> > > >>
> > > >>
> > > >> }
> > > >>         }
> > > >>
> > > >>
> > > >> The problem is that in my AggregationStrategy zones which is
> populate
> > > >> before has data only for the first line ( the first spit index).
> > > >>
> > > >>
> > > >> public class AggregationEnrichissementZone implements
> > > AggregationStrategy {
> > > >>
> > > >> Logger LOG =
> > > LoggerFactory.getLogger(AggregationEnrichissementZone.class);
> > > >>
> > > >> @Override
> > > >> public Exchange aggregate(Exchange original, Exchange resource) {
> > > >>
> > > >>  Map<String, Zone> zones = (Map<String, Zone>)
> > > resource.getIn().getBody();
> > > >>
> > > >>
> > > >> Any idea ?
> > > >>
> > > >>
> > > >> Thanks !
> > > >>
> > > >>
> > > >>
> > > >>
> > > >> 2013/3/6 Jean Francois LE BESCONT <jflebescont@gmail.com>
> > > >>
> > > >>> Thanks Claus !
> > > >>>
> > > >>> Tricky but it works :)
> > > >>>
> > > >>>
> > > >>> 2013/3/6 Claus Ibsen <claus.ibsen@gmail.com>
> > > >>>
> > > >>>> On Wed, Mar 6, 2013 at 1:05 PM, Jean Francois LE BESCONT
> > > >>>> <jflebescont@gmail.com> wrote:
> > > >>>> > When we do an enrich, the *resourceUri ( in my example
> > > >>>> *.enrich("direct:ds",
> > > >>>> > new AggregationStrategyRessource()))  is called every
time.
> > > >>>> >
> > > >>>> > When I parse a file, big query with all the datasource
while be
> > > called
> > > >>>> > every, is there a trick to tell the route to execute
only one ?
> I
> > > have
> > > >>>> > watch camel cache but I have understand that it is used
for
> > > something
> > > >>>> else.
> > > >>>> >
> > > >>>> > Thanks !
> > > >>>> >
> > > >>>>
> > > >>>> You can use a filter / content based route and only use the
enrich
> > the
> > > >>>> 1st time.
> > > >>>> When you use the splitter the exchange has a property with
the
> split
> > > >>>> index, so the 1st time the index is zero.
> > > >>>>
> > > >>>> The key for that property is Exchange.SPLIT_INDEX
> > > >>>>
> > > >>>> >
> > > >>>> >
> > > >>>> >
> > > >>>> > 2013/3/6 Jean Francois LE BESCONT <jflebescont@gmail.com>
> > > >>>> >
> > > >>>> >> Thanks Claus for the link ( have you an HashMap off
the FAQ in
> > the
> > > >>>> head ?
> > > >>>> >>  :)
> > > >>>> >>
> > > >>>> >> Don't forget to add :
> > > >>>> >>
> > > >>>> >>
> > > >>>> >>    original.getOut().setHeaders(original.getIn().getHeaders());
> > > >>>> >>
> > > >>>> >>
> > > >>>> >> When enrich with strategy !
> > > >>>> >>
> > > >>>> >>
> > > >>>> >> 2013/3/6 Claus Ibsen <claus.ibsen@gmail.com>
> > > >>>> >>
> > > >>>> >>> On Wed, Mar 6, 2013 at 10:26 AM, Jean Francois
LE BESCONT
> > > >>>> >>> <jflebescont@gmail.com> wrote:
> > > >>>> >>> > My mistake ...I used   resource.getOut().getBody();
instead
> > > >>>> >>> > od  resource.getIn().getBody();
> > > >>>> >>> >
> > > >>>> >>>
> > > >>>> >>> Yeah see this FAQ
> > > >>>> >>>
> > > >>>>
> > > http://camel.apache.org/using-getin-or-getout-methods-on-exchange.html
> > > >>>> >>>
> > > >>>> >>> >
> > > >>>> >>> >   public Exchange aggregate(Exchange original,
Exchange
> > > resource) {
> > > >>>> >>> >
> > > >>>> >>> > AED originalBody = (AED)original.getIn().getBody();
> > > >>>> >>> > Object resourceResponse = (Object)
> resource.getIn().getBody();
> > > >>>> >>> >
> > > >>>> >>> >         LOG.info("originalBody " + originalBody
) ;
> > > >>>> >>> >         LOG.info("resourceResponse " + resourceResponse
) ;
> > > >>>> >>> >         // do stuff with
> > > >>>> >>> >
> > > >>>> >>> >         return resource;
> > > >>>> >>> >     }
> > > >>>> >>> >
> > > >>>> >>> > Thanks raul for the help ! :)
> > > >>>> >>> >
> > > >>>> >>> >
> > > >>>> >>> > 2013/3/6 Jean Francois LE BESCONT <jflebescont@gmail.com>
> > > >>>> >>> >
> > > >>>> >>> >> Thanks Raul !
> > > >>>> >>> >>
> > > >>>> >>> >> I haven't found simple example  enrich
here (
> > > >>>> >>> >> http://camel.apache.org/content-enricher.html,
> > > direct:resource in
> > > >>>> >>> never
> > > >>>> >>> >> explains ). Perharps that I haven't
understand fondamental
> > but
> > > >>>> it's a
> > > >>>> >>> >> huge framework and learning needs time
:)
> > > >>>> >>> >>
> > > >>>> >>> >> So I have a querie :
> > > >>>> >>> >>
> > > >>>> >>> >> from("direct:refreshReferentielDynamic")
> > > >>>> >>> >> .setBody(constant("SELECT * FROM TEST"))
> > > >>>> >>> >>  .to("jdbc:dataSource")
> > > >>>> >>> >> .to("direct:ds")
> > > >>>> >>> >> .end();
> > > >>>> >>> >>
> > > >>>> >>> >> Plus :
> > > >>>> >>> >>
> > > >>>> >>> >> from("direct:ds").to("log:dans.directds").end();
> > > >>>> >>> >>
> > > >>>> >>> >> Otherwise it doesn't works.
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >> And an " main route " :
> > > >>>> >>> >>
> > > >>>> >>> >> from("file://....")
> > > >>>> >>> >> .split()
> > > >>>> >>> >> .tokenize("\n")
> > > >>>> >>> >> .enrich("direct:ds", new AggregationStrategyRessource())
> > > >>>> >>> >>
> > > >>>> >>> >> With :
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >> public class AggregationStrategyRessource
implements
> > > >>>> >>> AggregationStrategy {
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>     public Exchange aggregate(Exchange
original, Exchange
> > > >>>> resource) {
> > > >>>> >>> >>
> > > >>>> >>> >>         return resource; // for the
moment
> > > >>>> >>> >>     }
> > > >>>> >>> >>
> > > >>>> >>> >> }
> > > >>>> >>> >>
> > > >>>> >>> >> But original is always null ...
> > > >>>> >>> >>
> > > >>>> >>> >> Do you see with ?
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >>
> > > >>>> >>> >> 2013/3/6 Jean Francois LE BESCONT <jflebescont@gmail.com>
> > > >>>> >>> >>
> > > >>>> >>> >>> Hey !
> > > >>>> >>> >>>
> > > >>>> >>> >>> A picture is sometime better than
explication :) I try to
> > > make a
> > > >>>> >>> really
> > > >>>> >>> >>> simple test ( equivalent of MAP
with lookup in ETL) :
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> -------------------------
> > > >>>> >>> >>>                                
          |  TABLE TEST  |
> > > >>>> >>> >>>
> > > >>>> -------------------------
> > > >>>> >>> >>>                                
          | COL1  | COL2
>  |
> > > >>>> >>> >>>                                
          | jack  |  2
> > >    |
> > > >>>> >>> >>>                                
          | bob  |   3
> > >   |
> > > >>>> >>> >>>
> > > >>>> ------------------------
> > > >>>> >>> >>>                                
                 |
> > > >>>> >>> >>>                                
                 |
> > > >>>> >>> >>>                                
                 |
> > > >>>> >>> >>>                                
                 |
> > > >>>> >>> >>>                                
                 |
> > > >>>> >>> >>> ----------------
> > > ----------------------
> > > >>>> >>> >>>            ------------------------
> > > >>>> >>> >>> | FILE INPUT   |               
     |     ENRICH ? |
> > > >>>> >>> >>>  | FILE OUTPUT  |
> > > >>>> >>> >>> ----------------
> > > ----------------------
> > > >>>> >>> >>>            -----------------------
> > > >>>> >>> >>> | jack  | cool    | >>>>>>>>>
 |                     |
> > > >>>>>>>>>
> > > >>>> |
> > > >>>> >>> jack
> > > >>>> >>> >>> | cool |  2   |
> > > >>>> >>> >>> | bob   | foo     |            
        |
> > > |
> > > >>>> >>> >>>          | bob    | foo  |  3  
|
> > > >>>> >>> >>> ----------------
> > > >>>> -----------------------
> > > >>>> >>> >>>              ------------------------
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>>
> > > >>>> >>> >>> 2013/3/6 Jean Francois LE BESCONT
<jflebescont@gmail.com>
> > > >>>> >>> >>>
> > > >>>> >>> >>>> If i do:
> > > >>>> >>> >>>> public class AggregationStrategyRessource
implements
> > > >>>> >>> AggregationStrategy
> > > >>>> >>> >>>> {
> > > >>>> >>> >>>>
> > > >>>> >>> >>>> @Override
> > > >>>> >>> >>>>     public Exchange aggregate(Exchange
original, Exchange
> > > >>>> resource) {
> > > >>>> >>> >>>>         int i = 1 / 0;
> > > >>>> >>> >>>> return original;
> > > >>>> >>> >>>>     }
> > > >>>> >>> >>>> }
> > > >>>> >>> >>>>
> > > >>>> >>> >>>>  no exception are thows.
> > > >>>> >>> >>>>
> > > >>>> >>> >>>>
> > > >>>> >>> >>>>
> > > >>>> >>> >>>> 2013/3/6 Jean Francois LE BESCONT
<jflebescont@gmail.com
> >
> > > >>>> >>> >>>>
> > > >>>> >>> >>>>> thks Raul
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> For the moment it's a simple
:
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> public class AggregationStrategyRessource
implements
> > > >>>> >>> >>>>> AggregationStrategy {
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> @Override
> > > >>>> >>> >>>>>     public Exchange aggregate(Exchange
original,
> Exchange
> > > >>>> resource)
> > > >>>> >>> {
> > > >>>> >>> >>>>>         return original;
> > > >>>> >>> >>>>>     }
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> }
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> If I put a debug point on
it, it is never fired ....
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>> 2013/3/6 Raúl Kripalani
<raul@evosent.com>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>>> Can we see the code
of AggregationStrategyRessource?
> > > >>>> >>> >>>>>>
> > > >>>> >>> >>>>>> Regards,
> > > >>>> >>> >>>>>> Raúl.
> > > >>>> >>> >>>>>>
> > > >>>> >>> >>>>>> On Mar 5, 2013, at 23:30,
Jean Francois LE BESCONT
> wrote:
> > > >>>> >>> >>>>>>
> > > >>>> >>> >>>>>> > Hey,
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > I have this >
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>>
> > > >>>> >>>
> > > >>>>
> > >
> >
> http://camel.465427.n5.nabble.com/Enrich-message-with-data-from-dynamic-sql-query-td5329427.html
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > But I haven't found
a solution to my question.
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > How can enrich
my stream with data from a queries ?
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > I have a a route
called from the start :
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > from("direct:refreshReferentielDynamic")
> > > >>>> >>> >>>>>> > //
> > > >>>> >>> >>>>>> > .setBody(constant("SELECT
* FROM TEST"))
> > > >>>> >>> >>>>>> > //
> > > >>>> >>> >>>>>> > .to("jdbc:dataSource")
> > > >>>> >>> >>>>>> > //
> > > >>>> >>> >>>>>> > .to("direct:ds")
> > > >>>> >>> >>>>>> > //
> > > >>>> >>> >>>>>> > .end();
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > End I would like
to acces data in memory in direct:ds
> > > from
> > > >>>> an
> > > >>>> >>> other
> > > >>>> >>> >>>>>> route.
> > > >>>> >>> >>>>>> > I have try this
:
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > from("foo").
> > > >>>> >>> >>>>>> > [...]
> > > >>>> >>> >>>>>> > .enrich("direct:ds",
new
> > AggregationStrategyRessource())
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > But code in AggregationStrategyRessource
is not
> called
> > > ...
> > > >>>> and
> > > >>>> >>> body
> > > >>>> >>> >>>>>> is
> > > >>>> >>> >>>>>> > replace by the
content of the query.
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > Thks !
> > > >>>> >>> >>>>>> >
> > > >>>> >>> >>>>>> > Thks !
> > > >>>> >>> >>>>>>
> > > >>>> >>> >>>>>>
> > > >>>> >>> >>>>>
> > > >>>> >>> >>>>
> > > >>>> >>> >>>
> > > >>>> >>> >>
> > > >>>> >>>
> > > >>>> >>>
> > > >>>> >>>
> > > >>>> >>> --
> > > >>>> >>> Claus Ibsen
> > > >>>> >>> -----------------
> > > >>>> >>> Red Hat, Inc.
> > > >>>> >>> FuseSource is now part of Red Hat
> > > >>>> >>> Email: cibsen@redhat.com
> > > >>>> >>> Web: http://fusesource.com
> > > >>>> >>> Twitter: davsclaus
> > > >>>> >>> Blog: http://davsclaus.com
> > > >>>> >>> Author of Camel in Action: http://www.manning.com/ibsen
> > > >>>> >>>
> > > >>>> >>
> > > >>>> >>
> > > >>>>
> > > >>>>
> > > >>>>
> > > >>>> --
> > > >>>> Claus Ibsen
> > > >>>> -----------------
> > > >>>> Red Hat, Inc.
> > > >>>> FuseSource is now part of Red Hat
> > > >>>> Email: cibsen@redhat.com
> > > >>>> Web: http://fusesource.com
> > > >>>> Twitter: davsclaus
> > > >>>> Blog: http://davsclaus.com
> > > >>>> Author of Camel in Action: http://www.manning.com/ibsen
> > > >>>>
> > > >>>
> > > >>>
> > > >>
> > >
> > >
> > >
> > > --
> > > Claus Ibsen
> > > -----------------
> > > Red Hat, Inc.
> > > FuseSource is now part of Red Hat
> > > Email: cibsen@redhat.com
> > > Web: http://fusesource.com
> > > Twitter: davsclaus
> > > Blog: http://davsclaus.com
> > > Author of Camel in Action: http://www.manning.com/ibsen
> > >
> >
>

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