JdbcPagingItemReader vs JdbcCursorItemReader

Hi,

I am going through below link

http://www.mkyong.com/spring-batch/spring-batch-example-mysql-database-to-xml/


i wonder wonder when to use
org.springframework.batch.item.database.JdbcPagingItemReader vs org.springframework.batch.item.database.JdbcCursorItemReader

what are advantages, disadvanages and practial uses of each.

when i installed mysql5.5 coomand line client it does not start properly. It just comes and closes quickly. are there any detailed instructions on setting mysql and running and connecting using any good database client tool.


Please adviseAny links resources ideas highly appreciated. Thanks in advance
Please advise
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

girionisCommented:
The JdbcCursorItemReader is a cursor-based implementation and works directly with the result set. The JdbcPagingItemReader works with a  PagingQueryProvider which is responsible for providing the SQL queries to retrieve the rows. For more information have a look at the spring documentation about item readers and item writers.
0
gudii9Author Commented:
The JdbcCursorItemReader is a cursor-based implementation and works directly with the result set. The JdbcPagingItemReader works with a  PagingQueryProvider which is responsible for providing the SQL queries to retrieve the rows.


when to use which one and what are the advantages and disadvantages, pratical uses, performance considerations of each one. please advise
0
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Ok, consider this situation... You have a web page that shows data in a table form, for example. Firstly, consider the case where this data is say event logs generated by an application, and assume that there can be LOTS of data, say thousands or even millions of rows. You obviously can't display all rows on the one page, so you implement a paging mechanism (I'm sure you've seen this many times before). You wouldn't want to run an SQL query that retrieves ALL of the rows, only to then display the first 100 on the page. Because then when the user select "next page" you would have to run the same query again retrieving all the rows again to only show rows 101-200, etc. This is the use case for JdbcPagingItemReader, and the text that you quoted above says, with this you can provide different SQL queries to retrieve JUST the rows that you need to process, ie. maybe you run the first query with a "WHERE logId > 0 and logId <= 100" and then you can run the second query with different bounds, ie. "WHERE logId > 100 and logId <= 200".

Secondly, consider a slightly different situation where the table is displaying users configured in your application and the application is fairly small. You might only be talking about say 30 user records and you have a page that shows 20 at a time. In this case, it is probably not worth going to the extra effort of developing the code to generate the different SQL statements. You just use the one statement that gets all the rows and then filter that into what you want displayed on the web page. Retrieve a few extra rows from the query wont really affect your performance and you can use the more simple design of the JdbcCursorItemReader.


So in brief, use JdbcPagingItemReader where performance will benefit from performing the filtering on the Database side by using different SQL statements, otherwise use the more simple JdbcCursorItemReader.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

girionisCommented:
when to use which one and what are the advantages and disadvantages, pratical uses, performance considerations of each one. please advise

JdbcCursorItemReader you read all the records from the database, you hold them in memory and you go up and down the result set by using a cursor (a cursor points to the current row of data). When a user changes page you don't read the data from the database again.

JdbcPagingItemReader you read chunks of records from the database. But when the user changes page you issue another query and read the data again.

First approach: You consume more memory but it's faster.
Second approach: You consume less memory but it's slower.
0
gudii9Author Commented:
i am going through the link
http://docs.spring.io/spring-batch/trunk/reference/html/readersAndWriters.html

i confuse with names

'foo', 'bar', 'foobar'

Do they contain any real meaning? Or just some arbitrary names?

Sorry english is not my first language.
0
girionisCommented:
They are some arbitrary names. They are used for tutorials/examples.
0
gudii9Author Commented:
I did not understand the flow and how they got the output

ID,lastName,firstName,position,birthYear,debutYear
"AbduKa00,Abdul-Jabbar,Karim,rb,1974,1996",
"AbduRa00,Abdullah,Rabih,rb,1975,1999",
"AberWa00,Abercrombie,Walter,rb,1959,1982",
"AbraDa00,Abramowicz,Danny,wr,1945,1967",
"AdamBo00,Adams,Bob,te,1946,1969",
"AdamCh00,Adams,Charlie,wr,1979,2003"  


Please advise
0
mccarlIT Business Systems Analyst / Software DeveloperCommented:
I did not understand the flow and how they got the output
Are you talking about the snippet from 6.6.2.5. Simple Delimited File Reading Example in the link that gironis posted above? If so, then that ISN'T an output, it is an input file that is being read by a FlatFileItemReader object, which is totally different to what you are asking in this question. You should be reading a bit further down in section "6.9. Database"
0
gudii9Author Commented:
that make sense. we have about 30,000 records and project team decided to use JdbcPagingItemReader  approach(but i do not see them changing the query though for each page).

Are there are any links where i can download the sourcee code and import as eclipse project and see myself both approaches.


the big advantage of the ItemReader is that it allows items to be 'streamed'. The read method can be called once, and the item written out via an ItemWriter, and then the next item obtained via read. This allows item reading and writing to be done in 'chunks' and committed periodically, which is the essence of high performance batch processing.


IS performance is related to memory or time.

How the chunk come into picture. What is the meaning of streaming they are talking about.

please advise.

Problem i face with some of the links is they are too big and it is tough to make working example out of it (which takes days, weeks) to practically see and understand the concepts.

When I read links like this which is having many pages do you suggest to take consolidated notes on some notepad so that when i revisit that link in future i do not have to read all these pages again??

please advise
0
gudii9Author Commented:
Please advise
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.