Which JDBC drivers support scroll sensitive/insens

问题:

I just found out that the Postgres Java JDBC driver does not really support the SCROLL_SENSITIVE/SCROLL_INSENSITIVE modes using streaming, but instead simulates those modes by loading the full result set into client memory all at once. For queries with a big result set, that can lead to an unexpectedly huge memory usage, especially in a language like Java with little support for unboxed values. When using FORWARD_ONLY mode, the driver streams the results as expected.

(details; From my understanding this is a limitation of the Postgres wire protocol, though the driver could maybe in theory work around that by converting queries into explicit cursors.)

To prevent such surprises in the future, I'm wondering how wide spread such behavior is and which other common JDBC drivers do not implement real scrolling where that would be expected, so I know to watch out for that if I happen to use one of those drivers. Which other common JDBC drivers do not support all of the scrolling modes in a streaming fashion?

回答1:

MySQL/MariaDB is slightly worse than Postgres. Like Postgres it will buffer the entire query result by default. Also like Postgres it can only do forward scrolling without buffering, but unlike Postgres the database connection is occupied while a query result has not been exhausted and cannot be used for other queries at the same time. Any locks and other resources are also held until the result set has been exhausted, so leaving a result set open for a long time is not recommended. ref

Oracle streams results by default, but the server or network protocol does not support scrolling backwards. The JDBC driver emulates scrolling by caching the results if SCROLL_INSENSITIVE or SCROLL_SENSITIVE is set. So other than the default this is similar to Postgres. ref