JDBC Statement.SetFetchsize exaclty的工作原理 [英] How JDBC Statement.SetFetchsize exaclty works

问题描述

我想实施此方法功能。我应该使用哪种方法来设置setMaxResults？

I want to implement this methods functionality in my custom JDBC driver. Which method from JDBC Should I use to setMaxResults?

如果我的查询结果是1000行，并且我设置了setFetchSize = 100的值;
那么结果会是什么？
它将只返回100行或者在返回100之后它将从db获得下一个100直到1000行完成。

If my query results is 1000 rows and I have set the value for setFetchSize=100; then what will be the results? It will return only 100 row or after returning first 100 it will get next 100 from db until 1000 rows completed.

推荐答案

来自 Statement.setFetchSize ：

为JDBC驱动程序提供行数提示当这个语句生成的 ResultSet 对象需要更多行时，应该从数据库中获取该数据。如果指定的值为零，则忽略提示。默认值为零。

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

因此，如果您总共有1000行，并且获取大小为100（假设驱动程序没有忽略提示或取一个近似的nr），这意味着驱动程序将获取10批100行中的所有行。当本地没有可用的行来完成对 next（）的调用时，将获取一批行，这将完成，直到获取所有行或 maxRows 已经到达。

So if you have a total of 1000 rows and the fetch size is 100 (and assuming the driver doesn't ignore the hint or fetches an approximate nr), then this means that the driver will fetch all rows in 10 batches of 100 rows each. A batch of rows is fetched when there is no row available locally to fulfill a call to next(), this is done until all rows have been fetched or maxRows has been reached.

这导致类似于：

Statement stmt = ...;
stmt.setFetchSize(100);
ResultSet rs = ...;
rs.next(); // Triggers fetch of 100 rows, positions on row 1 / 100
rs.next(); // Positions on row 2 / 100
// 3-99 rs.next();
rs.next(); // Positions on row 100 / 100
rs.next(); // Triggers fetch of 100 rows, positions on row 101 / 200
// etc

理想情况下，这是从JDBC规范的角度来看。有些驱动程序会忽略提示（例如，如果将MySQL设置为 Integer.MIN_VALUE 并忽略所有其他值，则MySQL会逐行删除它，并且某些数据库将采用提示，但随后返回的行数少于（有时更多）。

This is speaking ideally from the perspective of the JDBC specification. There are drivers that ignore the hint (eg MySQL abuses it to fetch row by row if you set it to Integer.MIN_VALUE and ignores all other values), and some databases will take the hint, but then return less (sometimes more) rows than requested.

我找到了 com.google.api.services.bigquery的文档.model.QueryRequest.setMaxResults 你链接不完全清楚，但似乎做同样的事情。

I find the documentation of com.google.api.services.bigquery.model.QueryRequest.setMaxResults you link not entirely clear, but it seems to do the same thing.

这篇关于JDBC Statement.SetFetchsize exaclty的工作原理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！