mysql fetchsize原理,是否有一个尊重fetchSize的mysql JDBC？

I am using MySQL and want to utilize the setFetchSize property. The default MySQL JDBC implementation does not really respect it. If you set fetchsize to Integer.MIN_VALUE it will fetch each row individually, but considering the reason I want to use fetchSize is that I have enough data to put my memory usage into the 2 G range having to do one query per row is going to take forever.

I would like to instead plug in a JDBC implementation that will work with MySQL and properly respects fetch size, allowing me to set a fetchsize of 10,000 or some other higher limit. Can anyone point me to a jar that may provide such an implementation? failing that is there any other resource to allow me to reasonable do a query containing tens of thousands of entries in a manner that is efficient, but in memory and number of sql queries required.

解决方案

Technically questions asking for libraries are off-topic. That said, as far as I know there is no alternative driver for MySQL. You either have the choice between getting all rows which might lead to out of memory situations, or to have the driver fetch them on demand by setting setFetchSize(Integer.MIN_VALUE).

The reason for this - as I understand from the Connector/J implementation notes - is that the MySQL protocol cannot have more than one cursor open per connection, therefor it defaults to streaming all rows to the client on execute.

The other option is that rows are retrieved one-by-one, but this comes with the problem that you cannot execute other statements on the same connection while processing the ResultSet:

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

So the MySQL only has the option to get everything or to get one at a time. This means that there is no way for a driver to respect a different fetch size. And due to the caveats when getting one-by-one they opted to use the Integer.MIN_VALUE (instead of simply 1) as a signal that you should really think before doing this.

A possible 'in-between' solution would require you to program this yourself using LIMIT and OFFSET and repeatedly executing queries.