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.

Answer 1

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}