• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 6109
6
Android IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic