My Java application has a ResultSet, generated as an outcome of the SQL-query execution. Now, in order to work (analyze specific column content, calculate specific parameter value based on the data, to represent the data in UI) efficiently (both: memory and performance) with this data I have to store it in some data collection.
My questions are:
Is it a good idea to store the rows of the ResultSet in ArrayList, or there is more suitable data collection for the needs, described above?
Should I convert each ResultSet row to the object or to store it as a plain structure? Suppose, the size of the ResultSet matrix is something about 5000 x 10, I'm not sure, that it is a good idea to begin to generate in a loop 5000 Java objects.
As far as I understand, object creation is a quite «heavy» task, hence, if it is possible to avoid this operation, it should be done. But, from the other hand, LINQ by default returns set of objects and not a plain structure, thus, may be it still worths to convert the ResultSet to the collection of the objects and not of the structures?
Is there any Java built-in way to convert the ResultSet row to the object or I have to write it manually? I know, it's not so hard work but may there is some ready solution.
No, since the number of result records can be quite large, and you may not be interested in that level of granularity, I recommend streaming through the results (e.g. forward-only cursor) and store the aggregated data (e.g. in a number of lists/maps). Also, you may want to consider to use the SQL engine to aggreate and compute values, rather than coding it in Java.
I would recommend to store the results in a number of (aggregated) maps, using objects instead of scalars. Google Guava has a few collection classes that might be useful.
Object creation is not heavy, however you should consider memory footprint since the number of result records can be potentially large. I recommend to aggregate values in the loop, populating one or more maps with the aggregated values. Do you really need the granularity on the record level? Probably not.
There are many ORM (Object Relational Mapping) tools available, e.g. Hibernate. Also, Java has a 'RowSet' class for storing a complete ResultSet locally, but use that with caution (memory footprint)