CODEWITHSUNDEEP
javajdbcscrollableresults
Ravi Jain
Ravi Jain

Reputation: 1482

Advice on JDBC ResultSet

I have Employee table and an Entity class for it,
My task is such that i need the data of employee table within result set(of type scrollable) two times,
in such case what would be better of the following for using the data second time ->

1: create instance of entity class and store it in List while iterating through result set for first time.
OR
2: after first iteration call the first() method of result set to go back to first row and use data for second time.

Which option will consume less time and resources.
If You have better suggestions, please provide.
Thanks.

Upvotes: 1

Views: 513

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220942

Unless this is about very large resultsets, you're probably much better off consuming the whole JDBC ResultSet into memory and operating on a java.util.List rather than on a java.sql.ResultSet for these reasons:

  1. The List is more user-friendly for developers
  2. The database resource can be released immediately (which is less error-prone)
  3. You will probably not run out of memory in Java on 1000 rows.
  4. You can make many many mistakes when operating on a scrollable ResultSet, as various JDBC drivers implement this functionality just subtly differently

You can use tools for consuming JDBC result sets. For instance Apache DbUtils:

QueryRunner run = new QueryRunner(dataSource);
ResultSetHandler<List<Person>> h
  = new BeanListHandler<Person>(Person.class);
List<Person> persons = run.query("SELECT * FROM Person", h);

jOOQ (3.0 syntax):

List<Person> list =
DSL.using(connection, sqldialect)
   .fetch("SELECT * FROM Person");
   .into(Person.class);

Spring JdbcTemplate:

JdbcTemplate template = // ...
List result = template.query("SELECT * FROM Person", rowMapper);

Upvotes: 1

Roadrunner
Roadrunner

Reputation: 6801

Maybe using a Map of employees by their primary key would help?

If You'd describe why You think You need to iterate the list more than once, than we'd see if there's a better algorithm there to get rid of that second interation in the first place.

Upvotes: 0

ahanin
ahanin

Reputation: 892

Cache the data you retrieve from database. It's always better than polling it, even if driver provides caching on its own level. You can always withdraw it if it's not needed anymore.

Upvotes: 0

Related Questions