Is the JDBC ResultSet an application-level query cursor

Question

The database cursor definition is strikingly resembling with the JDBC ResultSet API.

• the database cursor can be forward-only just like ResultSet.TYPE_FORWARD_ONLY.

• the database cursor can be scrollable and even have a sensitivity setting just like ResultSet.TYPE_SCROLL_SENSITIVE.

• there is also support for holdability like ResultSet.HOLD_CURSORS_OVER_COMMIT

• and even the support for positional update/delete is being replicated into JDBC ResultSet.CONCUR_UPDATABLE

But in spite of all these resembling, MySQL doesn't support database cursors:

MySQL does not support SQL cursors, and the JDBC driver doesn't emulate them, so setCursorName() has no effect.

So, is the JDBC implementation a data access specification that mimics a database cursor implementation, even if the database doesn't really support such a feature?

Answer 1

What's in a name...

Indeed, a ResultSet and a database cursor are semantically similar. The SQL:2011 standard specifies:

A cursor is a mechanism by which the rows of a table may be acted on (e.g., returned to a host programming language) one at a time.

That does sound a lot like a ResultSet. Further down, the SQL:2011 standard goes on and mentions:

A cursor declaration descriptor and a result set descriptor have four properties: the sensitivity property (either SENSITIVE, INSENSITIVE, or ASENSITIVE), the scrollability property (either SCROLL or NO SCROLL), the holdability property (either WITH HOLD or WITHOUT HOLD), and the returnability property (either WITH RETURN or WITHOUT RETURN).

In other words, none of these features were "invented" by the JDBC (or ODBC) spec teams. They do exist exactly in this form in many SQL database implementations, and as with any specs, many of the above features are optional in SQL implementations as well.

You've gotten an authoritative response on the MySQL part already by Jess. I'd like to add that JDBC, like any specification on a high level, has parts that are required and parts that are optional.

Looking at the JDBC Spec, I can see the following relevant parts.

6.3 JDBC 4.2 API Compliance

A driver that is compliant with the JDBC specification must do the following:

[...]

It must implement the Statement interface with the exception of the following optional methods:

• [...]
• setCursorName
• [...]

It must implement the ResultSet interface with the exception of the following optional methods:

• [...]
• getCursorName
• [...]

The same is true for the implementation of ResultSet types. Further down in the specs, you will find:

The method DatabaseMetaData.supportsResultSetType returns true if the specified type is supported by the driver and false otherwise.