How to get all remaining rows from ResultSet java sql package

Question

I used to use com.datastax.driver.core.ResultSet. I could take all remaining rows and put it into an Array of my model class and then add it to List of Arrays.

Below is my previous code with ResultSet from datastax package.

List<ReportDescription[]> result = new ArrayList<>();
ReportDescription[] csvReportDescriptions;
csvReportDescriptions = resultSet.all().stream()
                    .map(row -> new ReportDescription(row.getObject("value"))).toArray(ReportDescription[]::new);
result.add(csvReportDescriptions);

Nowadays I change database so now I need to swithch ResultSet to java.sql.ResultSet package. Is it any possibility to get all rows, create new instance of my models and put it into List of Arrays as I did it before?

I make it by my own like that

try {
ResultSet rS = dataSource.getConnection().createStatement().executeQuery(query.toString());
    while(rS.next()){
        descriptions.add(new ReportDescription(rS.getObject("VALUE")));
    }
    } catch (SQLException e) {
                dataSource.logError("Can't execute statement :" + query.toString());
    }               

    csvReportDescriptions = descriptions.toArray(new ReportDescription[descriptions.size()]);
    result.add(csvReportDescriptions);

This seems to have something similar, https://stackoverflow.com/questions/32209248/java-util-stream-with-resultset — Antho Christen, Apr 30 '18 at 11:27
You could convert it to an Iterator (using https://commons.apache.org/proper/commons-dbutils/apidocs/org/apache/commons/dbutils/ResultSetIterator.html#ResultSetIterator-java.sql.ResultSet-)and then stream the iterator. Or you could simply do what piotr has done — Antho Christen, Apr 30 '18 at 11:44

UsamaAmjad · Answer 1 · 2018-05-04T22:24:10.530

You are trying to retrieve data from ResultSet in a Java8 way using Streams, So there are multiple ways to do it.

How we write SQL in Java 7 using JDBC

List<Schema> result = new ArrayList<>();
try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    try (PreparedStatement stmt = c.prepareStatement(sql);
         ResultSet rs = stmt.executeQuery()) {

        while (rs.next()) {
            System.out.println(
                new Schema(rs.getString("SCHEMA_NAME"),
                           rs.getBoolean("IS_DEFAULT"))
            );
        }
    }
}

How we write SQL in Java 8 using jOOλ

try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    try (PreparedStatement stmt = c.prepareStatement(sql) {

        // We can wrap a Statement or a ResultSet in a
        // Java 8 ResultSet Stream
        SQL.stream(stmt, Unchecked.function(rs ->
            new Schema(
                rs.getString("SCHEMA_NAME"),
                rs.getBoolean("IS_DEFAULT")
            )
        ))
        .forEach(System.out::println);
    }
}

How we write SQL in Java 8 using jOOQ

try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    DSL.using(c)
       .fetch(sql)

       // We can use lambda expressions to map jOOQ Records
       .map(rs -> new Schema(
           rs.getValue("SCHEMA_NAME", String.class),
           rs.getValue("IS_DEFAULT", boolean.class)
       ))

       // ... and then profit from the new Collection methods
       .forEach(System.out::println);
}

How we write SQL in Java 8 using Spring JDBC

try (Connection c = getConnection()) {
    String sql = "select schema_name, is_default " +
                 "from information_schema.schemata " +
                 "order by schema_name";

    new JdbcTemplate(
            new SingleConnectionDataSource(c, true))

        // We can use lambda expressions as RowMappers
        .query(sql, (rs, rowNum) ->
            new Schema(
                rs.getString("SCHEMA_NAME"),
                rs.getBoolean("IS_DEFAULT")
            ))

        // ... and then profit from the new Collection methods
        .forEach(System.out::println);
}

Source: JOOQ

score 1 · Answer 2 · answered Apr 30 '18 at 11:39

1

Short answer: you cannot. This is because ResultSet was originally created with support for databases that have cursor support - all of your results might not even be in the database server's memory, nor can it be viable to actually download all of them.

What you can do is simply traverse the result set adding all the results to a list with the help of some boilerplate code:

List<ReportDescription> descs = new ArrayList<>();
while (resultSet.next()) {
    descs.add(new ReportDescription(resultSet.getObject("value")));
}

EDIT: To expand on the answer - a ResultSet actually always holds a cursor to a row (or a special quasi-row: before first or after last). Therefore, any getters you call on a ResultSet actually fetch the respective data from the row the cursor points to (or throw an exception if it's not on any row).

answered Apr 30 '18 at 11:39

Piotr Wilkin

3,446
10
18

It is not a problem to make a List of ReportDescription however I need an array of ReportDesciption. – Tom Apr 30 '18 at 11:50
You can transform a list to an array using the `toArray()` method. – Piotr Wilkin Apr 30 '18 at 11:51
Could you please help me? `List descriptions = new ArrayList<>();` `descriptions.add(new ReportDescription(rS.getObject("VALUE")));``result.add(descriptions.stream().toArray(ReportDescription[]::new));` I tried like that but it is still bad. I would like to have all ReportDescribtions in 1st index of list – Tom Apr 30 '18 at 12:02

How to get all remaining rows from ResultSet java sql package

2 Answers2