javajdbcscrollableresults

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.

Solution

  • 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);