Feature: Load data in slices(pagination under hood)

[CyborTronik]

Considering the fact this library has to be used in Rx architecture, there comes the need to load and process data in slices (pagination).

Lets say you have to process a million of records so you cannot handle all this stuff just using one 'select' operation. You have to load data in bits and process just few rows at time and then load next few rows and process ...

[thomasnield]

It sounds like there are many ways to achieve this already using existing RxJava operators. buffer(), flatMap(), etc. Even backpressure natively will prevent all data from being pushed at once. It's not clear what you want RxJava-JDBC to do that Rx doesn't do already.

[CyborTronik]

if you run a sql query it does request all the data at once and then transform to rx.
But what I mean is to have a way to request data in parts via JDBC and process part by part.
for example what happens when you run a select * from a_table and there you have few millions of rows? do you have enough resources to load this data in memory? There is the deal to load and process data using pagination or some cursor for that, so would be nice to have a built in approach for that.

[davidmoten]

Sounds like you are talking about fetchSize. This is supported now, see

https://github.com/davidmoten/rxjava-jdbc#fetch-size.

If you don't set fetchSize then your jdbc driver will use its default (which can be quite small; for Oracle is 10!) so you are unlikely to run out of memory but if you are fetching a lot of records you will make fewer calls to the db by setting fetchSize to a larger value.

[stanisfun]

Cool. Somehow I've missed that from documentation.
Thanks

[litalk]

@CyborTronik @stanisfun pay attention that currently fetchsize cannot be set to Integer.MIN_VALUE, which is critical for getting query result as a stream, at least on MySQL.
#78

[jad7]

I assume that @CyborTronik means an alternative of JdbcCursorItemReader (from spring batch). This really makes sense, because consumer can work significantly slowly, then producer. And pauses between fetching new data can be more than connection timeout, and you will get an exception. Cursor reader will fix this problem, but it's not easy to implement, because of different DBs has a different syntax (approach) for pagination.