哪些JDBC驱动程序正确支持滚动敏感/不敏感滚动? [英] Which JDBC drivers support scroll sensitive/insensitive properly?

问题描述

我刚刚发现Postgres Java JDBC驱动程序并不真正使用流支持SCROLL_SENSITIVE/SCROLL_INSENSITIVE模式，而是通过一次将全部结果集全部加载到客户端内存中来模拟那些模式.对于具有较大结果集的查询，这可能导致意外的巨大内存使用，尤其是在Java之类的语言中，对不带框的值的支持很少.使用FORWARD_ONLY模式时，驱动程序将按预期方式传输结果.

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.

(详细信息；根据我的理解，这是Postgres有线协议的局限性，尽管驱动程序理论上可以通过将查询转换为显式游标来解决.)

(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.)

为防止将来出现此类意外，我想知道这种行为的传播范围以及哪些其他常见的JDBC驱动程序未在预期的位置实现真正的滚动，因此我知道在发生这种情况时要小心使用这些驱动程序之一.哪些其他常见的JDBC驱动程序不以流方式支持所有滚动模式?

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?

推荐答案

MySQL/MariaDB比Postgres稍差.与Postgres一样，默认情况下它将缓冲整个查询结果.与Postgres一样，它只能进行前向滚动而没有缓冲，但是与Postgres不同，数据库连接被占用，而查询结果尚未耗尽，并且不能同时用于其他查询.在结果集用完之前，还将保留所有锁和其他资源，因此不建议长时间打开结果集. 参考

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流结果，但是服务器或网络协议不支持向后滚动.如果设置了SCROLL_INSENSITIVE或SCROLL_SENSITIVE，则JDBC驱动程序通过缓存结果来模拟滚动.因此，除了默认值以外，它类似于Postgres. 参考

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

这篇关于哪些JDBC驱动程序正确支持滚动敏感/不敏感滚动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！