Is JDBC ResultSet an application level cursor

Question

The definition of a database pointer resembles the JDBC ResultSet API in different ways.

• the database cursor can only be forward, 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 support for sustainability, for example ResultSet.HOLD_CURSORS_OVER_COMMIT

• and even support for positional update / deletion is replicated to JDBC ResultSet.CONCUR_UPDATABLE

But despite all this, MySQL does not support database cursors :

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

So, is the JDBC implementation a data access specification that mimics the implementation of a database cursor, even if the database does not really support such a function?

Answer 1

Of course, you can think about it. All of these concepts are inherited from ODBC, so you can thank (blame?) The story for doing just that. Cursors are not widely supported by most dbs to the full extent that functions are provided in APIs such as JDBC. In MySQL, in particular, there is a select pointer with support for MySQL 5.0, which means that the driver is not forced to read the entire result, regardless of whether it is needed or not. This means that you can abandon the set of results at an early stage without any costs. However, additional backtracking is required to periodically request block blocks. MySQL Connector / J does not apply FORWARD_ONLY semantics by default and buffers the entire result in the client, allowing for "scrollability". However, due to the implementation on the server, this does not allow one to be sensitive to changes made in other transactions. Capabilities are typically simulated / emulated to provide the convenience of an API.

Answer 2

What's in the name ...

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

A cursor is a mechanism by which table rows can act (for example, return to the host programming language) one at a time.

This is very similar to a ResultSet . Further, the SQL: 2011 standard continues and is mentioned:

The cursor declaration descriptor and result set descriptor have four properties: a sensitivity property (either SENSITIVE, INSENSIVE, or ASENSITIVE), a scroll property (either SCROLL, or NO SCROLL), a holdability property (either WITH HOLD, or WITHOUT HOLD), and a return property (either WITH RETURN, or WITH RETURN).

In other words, none of these features has been “invented” by specialized JDBC (or ODBC) teams. They exist exactly in this form in many SQL database implementations, and, as with any specification, many of the above functions are optional in SQL implementations.

You got an authoritative answer to the MySQL part already with Jess . I would like to add that JDBC, like any high-level specification, has parts that are required and parts that are optional.

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

6.3. JDBC 4.2 API Compliance

A driver complying with the JDBC specification should 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 implementing ResultSet types. Further in the specifications you will find:

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

Answer 3

Based on my understanding of the JDBC ResultSet, I will say that it does not depend on the database to which it connects, its behavior will be the same.

JDBC will always retrieve the default number of rows (not the entire result set) in your local memory. As soon as you reach the last row of the selected rows (say by doing the following () and try to access the next row), and if there are more rows as a result, then another callback request will be sent to the database to get the next packet of rows to local memory.

Even you can set the number of lines you want to retrieve in local memory than usual, you can consider CachedRowSet.

When you set fetchSize () to Statement, you only give the JDBC driver instructions how much you want to receive, but the JDBC driver can ignore your instructions. I do not know what the Oracle driver does with the fetchSize () function. In most cases, he observed that the MySQL JDBC driver will always retrieve all rows unless you set fetchSize () to Integer.MIN_VALUE.