ExecuteQuery() vs getResultSet() in Java

Question

What is the difference between statement.executeQuery and statement.getResultSet(). I believe both will return ResultSet for a select statement but are there any specific criteria when we should use which of them.

Possible duplicate of [executeQuery() and getResultSet() when retrieving data from callable statement](https://stackoverflow.com/questions/24212592/executequery-and-getresultset-when-retrieving-data-from-callable-statement) — Emre Acar, Oct 08 '18 at 10:26
@EmreAcar The possible duplicate answers the difference between execute, executeUpdate and executeQuery. I am more interested in the difference between executeQuery and getResultSet as both return the same thing (a resultSet). — Pratik Kumawat, Oct 08 '18 at 10:29

Mark Rotteveel · Accepted Answer · 2018-10-08T13:34:37.377

7

In general you should use executeQuery if you know you are executing a select statement. The getResultSet() method by itself does not execute the statement.

The getResultSet is intended to be used in combination with execute. The execute methods are intended for use with unknown statement types, or statements that can produce multiple results (that is 0 or more update counts or result sets).

So in short: you should normally use executeQuery.

A simple example when you should use execute if the code doesn't know what query it is going to execute (an update, a query, or something more complex), for example when executing user provided queries.

Another example are SQL Server stored procedures, which can return multiple update counts and result sets.

A generic way of processing a result of execute is:

boolean isResultSet = statement.execute(sql);
while (true) {
    if (isResultSet) {
        try (ResultSet rs = statement.getResultSet()) {
            // do something with result set
        }
    } else {
        int updateCount = statement.getUpdateCount();
        if (updateCount == -1) {
            // -1 signals no more results
            break;
        }
        // do something with update count
    }
    // move to next result
    isResultSet = statement.getMoreResults();
}

This ensures that all^* results get processed.

^{*: This example ignores exception results for systems (like SQL Server) that allow multiple exceptions to be interleaved with result sets and update counts, see How to get *everything* back from a stored procedure using JDBC for a more thorough example}

edited Oct 08 '18 at 13:34

answered Oct 08 '18 at 10:45

Mark Rotteveel

100,966
191
140
197

Thanks Mark. If you can add any performance related differences (if any), it would be very helpful. – Pratik Kumawat Oct 08 '18 at 10:48
@PratikKumawat There is unlikely to be a performance difference, but that would depend on the specific driver implementation, so can't be answered with 100% certainty in general. Just keep it simple and use `executeQuery()` unless your really have to do something else. – Mark Rotteveel Oct 08 '18 at 10:53
Thanks Mark. But your code confused me a bit. Are statement and stmt different? Is there any max value which is returned by getResultSet()? If not then why did you use stmt.getMoreResults(); – Pratik Kumawat Oct 08 '18 at 11:21
@PratikKumawat No, that was me unconsciously switching between variables names in my code, I have fixed this. – Mark Rotteveel Oct 08 '18 at 11:24
@PratikKumawat This has nothing to do with a 'max value' of a result set (there is no such thing). The point of this processing is that there can be an arbitrary number of update counts and result sets (in arbitrary order). If you want to process all these results, this is what you need to do. The most common example I know are SQL Server stored procedures, which can produce multiple update counts and multiple result sets from a single stored procedure call. For example a SP that does a select, an update and another select produces a result set, an update count and another result set. – Mark Rotteveel Oct 08 '18 at 11:29
1

@PratikKumawat - Related question: [How to get *everything* back from a stored procedure using JDBC](https://stackoverflow.com/q/42169951/2144390) – Gord Thompson Oct 08 '18 at 13:18

score -1 · Answer 2 · edited Oct 08 '18 at 12:23

-1

Check out the JavaDoc for these methods. getResultSet can return null but executeQuery never return null.

There are also more constraints. For example executeQuery cannot be called on a PreparedStatement or CallableStatement.

edited Oct 08 '18 at 12:23

William Wu

69
7

answered Oct 08 '18 at 10:35

Ivan Kulezic

440
4
15

Thanks Ivan. Is there any difference from performance perspective? I have gone through the JavaDoc but that didnt help much. – Pratik Kumawat Oct 08 '18 at 10:37

ExecuteQuery() vs getResultSet() in Java

2 Answers2

Linked