CODEWITHSUNDEEP
javajdbcarraylistcollectionsresultset
Girdhar Singh Rathore
Girdhar Singh Rathore

Reputation: 5585

How to convert the ResultSet object into ArrayList

List patternList=new ArrayList<String>();
ResultSet rs=stat.executeQuery("select file_pattern from Pattern_table");
while(rs.next()){
    sourcePattern=rs.getString(1);
    patternList.add(sourcePattern);
}

Here I'm going through each record and add to a list. It's taking too much time to traverse through the ResultSet and adding the records in a list.

Is there another way to add all the records of ResultSet in list at once?

Or any alternate approach I could follow to achieve better performance?

Upvotes: 0

Views: 9588

Answers (5)

Gherbi Hicham
Gherbi Hicham

Reputation: 2574

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

So in short, even if you find a way to get the list directly from the result set it will always use an iteration to go through it to convert it to List.

Upvotes: 1

11thdimension
11thdimension

Reputation: 10633

Before trying to improve performance of Java code first try to run the same query from some SQL client. Check if the time taken from the SQL client is equivalent to Java.

SQL Clients have a default limit to the number of rows that they fetch at a time so you have to make sure that you have fetched all the rows that query returns to measure the time spent on running it.

If you find that SQL client is running significantly faster than the Java call, they try setting the fetchSize() on the Statement or the Resultset.

https://docs.oracle.com/cd/A87860_01/doc/java.817/a83724/resltse5.htm

A ResultSet is not the entire data from the query but a just a fraction of it. As you go on running the resultSet.next() it will keep on fetching rows once it runs out of the previously fetched rows.

For example if query returns 5000 rows and fetchSize is 100 then result.next() will fetch 100 rows at a time. Only after these 100 rows have been read next call to database will be made thus JDBC will make 5000/100=50 network calls to database.

Upvotes: 1

Deepak Vishwakarma
Deepak Vishwakarma

Reputation: 1

You can get the column values from resultSet using method getArray(String columnLabel) and store it in array. Than convert it to the arrayList as per your requirement. Example:

List patternList=new ArrayList<String>();
ResultSet rs=stat.executeQuery("select file_pattern from Pattern_table");
String[] tempArr = rs.getArray(file_pattern);
patternList = Arrays.asList(tempArr);

Upvotes: 0

Manuel Spigolon
Manuel Spigolon

Reputation: 12870

There are many utils for archive this, but the big cost in performance is all on the database connection and in the SQL statement you have executed so you need to optimize that.

Upvotes: 0

Deepanjan
Deepanjan

Reputation: 649

You could always use Commons DbUtils and the MapListHandler. From the doc:

ResultSetHandler implementation that converts a ResultSet into a List of Maps so it'll take a lot of boilerplate code out of your hands. And rest of it depends on the database transaction time.

Upvotes: 1

Related Questions