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:25:28 GMT
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