camel-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <>
Subject RE: Processing large jdbc result set
Date Wed, 14 Nov 2012 15:38:32 GMT
You mention mybatis - I guess you mean ibatis. I have never worked on it but worked on hibernate
which supports batching.

19.1.5. Using batch fetching

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed.
Batch fetching is an optimization of the lazy select fetching strategy. There are two ways
you can configure batch fetching: on the class level and the collection level.

Batch fetching for classes/entities is easier to understand. Consider the following example:
at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to
its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate
through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT
statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size
in the mapping of Person:

<class name="Person" batch-size="10">...</class>
Hibernate will now execute only three queries: the pattern is 10, 10, 5.

You can also enable batch fetching of collections. For example, if each Person has a lazy
collection of Cats, and 10 persons are currently loaded in the Session, iterating through
all persons will generate 10 SELECTs, one for every call to getCats(). If you enable batch
fetching for the cats collection in the mapping of Person, Hibernate can pre-fetch collections:

<class name="Person">
    <set name="cats" batch-size="3">
With a batch-size of 3, Hibernate will load 3, 3, 3, 1 collections in four SELECTs. Again,
the value of the attribute depends on the expected number of uninitialized collections in
a particular Session.

Batch fetching of collections is particularly useful if you have a nested tree of items, i.e.
the typical bill-of-materials pattern. However, a nested set or a materialized path might
be a better option for read-mostly trees.

-----Original Message-----
From: Zhemzhitsky Sergey []
Sent: Wednesday, November 14, 2012 8:42 PM
Subject: Processing large jdbc result set

Hello camel gurus,

Could you suggest a best way to process a large jdbc result set that contains, let's say,
more than 10000 rows.

What I need is to fetch all the new rows from a given table when the timer event fires.
Currently jdbc components (jdbc, sql, jpa, mybatis, etc.) do not support batching, so what
I'd like to implement is something like this

    .repeat(new HasMoreRowsPredicate())
        .to("sql:select top 10 * from MyTable")

Loop EIP seems very similar to what I need, but it must be initialized with a predefined number
of repetitions.

Best Regards,

CONFIDENTIALITY NOTICE: This email and any files attached to it may be confidential. If you
are not the intended recipient you are notified that using, copying, distributing or taking
any action in reliance on the contents of this information is strictly prohibited. If you
have received this email in error please notify the sender and delete this email.

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s)
and may contain confidential and privileged information. If you are not the intended recipient(s),
please reply to the sender and destroy all copies of the original message. Any unauthorized
review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or
any action taken in reliance on the contents of this e-mail is strictly prohibited and may
be unlawful.

View raw message