Is cursor data remain in java heap memory or in database for StoredProcedureItemReader and JdbcCursorItemReader

Question

In Spring-batch, normally when we used any itemreader it will fetch each row with every call of read method and once its equal to commit interval , it sends data to writer.

I have read that StoredProcedureItemReader and JdbcCursorItemReader is cursor based reader , once queries are executed data is in curser and read method gets on row in each call.

However, my question is where this cursor resides in java memory or database as :- 1. If its fetching all the data in java memory in single go, then what is advantage of having batch, as application will have chances of getting slow or get out of memory. 2. If it is somewhere in database, then Storeprocedor or JDBC connection itself will time, till the all records are fetched in process.

I tried to find answer but didn't find anywhere in documentation and also don't know to test this to know for sure by myself. I seems important to me, as if some as to use these readers, they will need to increase the timeout for connection or they need have more heap memory.

Answer 1

Yes, with a StoredProcedureItemReader, the whole batch is fetched. This is because many drivers (including some of Oracle's) do not support batching CallableStatements. Some will fail, and some will silently ignore you. Thus to be consistent, Spring Batch allows no batching for stored procedures at all.

Some database drivers, like the IBM Informix driver, allow automatic batching based on parameters in the connection string; in other words, it will batch if possible without having to explicitly control it, but there are caveats to that. If in your use case, you absolutely NEED to batch stored procedures and you don't want to write a custom ItemReader or ItemWriter, then this might be an option for you.