aspose file tools*
The moose likes JDBC and the fly likes Show retrived rows while fetching from database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Show retrived rows while fetching from database" Watch "Show retrived rows while fetching from database" New topic
Author

Show retrived rows while fetching from database

Nitin Katdare
Greenhorn

Joined: Oct 18, 2011
Posts: 6
Suppose we have 50000 records in a table, can we start showing the rows as they are returned from database, i.e. during the data is getting retrieve start showing the records and at the same time retrieve the data from the table as well. How to achieve this?
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 475
    
    3
If your users really want that, fetch records in batches of a small size and send them back via listener interfaces from the data tier, instead of fetching all 50000 records in one go.

But does any user really want to be bombarded with 50000 records? I feel it's bad usability. It's better to implement pagination or continuous scrolling with a "More" button, and make both user and database happy.
Nitin Katdare
Greenhorn

Joined: Oct 18, 2011
Posts: 6
Sorry I am newbee.
Can you explain which "listener interfaces" you are telling to use.
Plsease give any example OR reference to check.
Thanks
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 475
    
    3
The listener (or callback) interface is something which is application specific and you have to implement. I couldn't find any example that doesn't confuse further, so I'll explain below.

The usual flow for fetching data is
1. Logic layer examines a user request and decides what data it requires to process that request. It requests that data from the data layer.
2. Data layer classes are the ones that talk to DB. They run SQL queries, get data and return them to logic layer.

If logic requests a large number of records in this kind of pull model, a user has to wait for the DB operation to complete, wait some more for its processing to complete, and wait some more while so much information is being rendered to screen in one go.

So you have 2 alternatives:
1. Make logic layer request small number of records, display them and request some more only as and when required.
This is still a pull model, where usually the user decides when more data is required, for example, by clicking on "next page" or a "more" button or scrolling down towards bottom of screen (as in facebook, twitter...). This model is suitable for web apps.
It's also possible for the logic layer itself to request data in small batches like this, have them displayed, then request some more, have them displayed... without any user actions. This is suitable only for desktop apps.

2. The other option is logic requests all records, but instructs the data layer classes to *call back * with small batches of data regularly via a listener/callback interface implemented by logic layer.
In this case, it's the data layer classes that *pushes* data, and logic layer just processes the data as and when it receives. It'll look something like this:


This kind of push model is convenient only for desktop apps where data access layer executes in the same process.

A solution depends on the kind of app you are implementing - desktop or webapp - and how to make the user experience convenient. It's highly unusual to request 50000 records and display them in one go while keeping user waiting.
Nitin Katdare
Greenhorn

Joined: Oct 18, 2011
Posts: 6
Thank for your prompt reply.
Just one more query.
In my case how to get specific no of rows from database
like
10 - 20 rows
21 - 30 rows
etc...
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 475
    
    3
Depends on which database you're using.
Most DBs implement LIMIT and OFFSET clauses (refer this)
In MySql, for example
SELECT * FROM mytable LIMIT 10 OFFSET 20
will fetch 10 rows starting from row#20
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Show retrived rows while fetching from database
 
Similar Threads
Statement and it's Fetch Size
Groovy Question
How to use streaming when query the database opposed to retrieve all data at once in resultset.
Can you stop a method request by recalling the method?
Resultset with million rows