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

oracle table caching in java Map

 
karthik sai
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have to cache the oracle table in java, the table has information such as user name,first name, last name and the size of the table is 1,50000 rows. We need to use JDBC to query the table for 500 records and subsequently firing next 500 record for the same. We are planning to use java Hashmap , where the key would be user name and the value would be the Value Object say UserInfoVO. The UserInfoVO will have all the user information. It would be like Map<String, UserInfoVO>. Please suggest us the pseudo code for fetching 500 records and next 500 record until the entire table is cached in the HashMap.

 
Jesper de Jong
Java Cowboy
Saloon Keeper
Posts: 15642
47
Android IntelliJ IDE Java Scala Spring
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

Databases are very good at managing and finding data (it's their primary goal in life...) so when you would implement this, you might find that it doesn't help a lot to solve a (performance?) problem you might have. For example, Oracle itself already does a lot of caching (if the database server has enough RAM) so implementing a cache yourself on top of what the database already does is often not necessary - or at least it is not really going to help much.

I'll move your question to our JDBC forum, where the experts on JDBC can probably give a good answer.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd second what Jesper has said. The code is easy enough to implement, though it is database specific. In Oracle you use the rownum property to track which rows you are currently selecting and only select those rows between known rownum values.

However, caching 150000 rows this way kind of renders the database redundant when it comes to managing this particular data. I'm assuming this is read only data? If it is not, you probably need to consider using a more complex caching mechanism (such as an ORM with a second level cache). What problem are you trying to solve by caching this data? It might be that some query tuning is a better way to go about this.
 
karthik sai
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply, instead of putting into map , will put it in the coherence data grid, but the problem is fetching the number of record from the database using JDBC, since number of rows is 1,50000, need to fetch 500 records in order to avoid oracle connection time out issue
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle connection timeout issues are easy enough to fix - just adjust the timeout for the profile of the user you are doing this with.

The usual logic for paging results in Oracle is to do something like this:


moving the low and high values accordingly. But if you know you must have all the results in the table cached, then I'd just increase the timeout for the connection.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic