Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is there a "best" way to get part of a resultSet

 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is something of an unusual situation. I have a query that will return 22 records. Depending on the parameter passed, I need to return either the first 11 records, or the last 11 records.

I see a couple of possibilities but I'm not sure which is preferred.

First option, instead of using "while (rset.next())" I could use a "for" statement to just get the number of records required. Second option, I could go ahead and get all the records and then after the beans are loaded in the Collection, I can work my way through the collection removing the records I don't want.

I'm leaning toward the second option just because I think the code will be easier to read and follow. However, the first option is almost certainly more efficient. But I'm not convinced that either option is what would be considered "best practice". Maybe there is another option that I'm not seeing?

btw, changing the query to only return the desired records is not an option. It's a stored procedure written by an outside contractor.
 
Jeff Verdegan
Bartender
Posts: 6109
6
Android IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
J. Kevin Robbins wrote:
First option, instead of using "while (rset.next())" I could use a "for" statement to just get the number of records required.


What do you mean? How would for() be any different than while()?

Second option, I could go ahead and get all the records and then after the beans are loaded in the Collection, I can work my way through the collection removing the records I don't want.


I'm leaning toward the second option just because I think the code will be easier to read and follow.

Since I don't really understand what you mean by the first option, I can't compare them, but thinking in terms of ease of readability is definitely the way to go.

However, the first option is almost certainly more efficient.


For 11 or even 111 or probably 1,111 records, you won't notice the difference.

Maybe there is another option that I'm not seeing?


You could try something like this:


But not all JDBC drivers support this"
javadocs wrote:throws SQLFeatureNotSupportedException - if the JDBC driver does not support this method or this method is not supported for the specified result set type and result set concurrency.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Option 3: only grab what you need. Each SQL dialect allows you to specify a max results and offset, though the syntax differs across dialects.

Using a JPA implementation abstracts those differences.

[Edit: I also seem to recall that modern JDBC drivers may let you specify these in a dialect-agnostic way, but it's been so long since I've used raw JDBC that I may just be hallucinating.]
 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Jeff - using for() I could do something like "for (int i = 11; i < 22; i++)" to get the second set of records. You probably see what I mean now about the readability being confusing. The more I think about it, the more I don't like this option. I'll look at the absolute() method. I've never used that. It would be a bit cleaner.

@Bear - I wish I could change the query, but like I said it's a stored procedure. I'd write my own SQL but it's an extremely complex stored procedure that takes 30-40 seconds to run on an iSeries. It runs multiple queries and calls multiple programs.

I guess if absolute() won't do it, I'll process the collection of beans to remove the unneeded records.

Why do I always get the weird ones?

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic