13

I see a lot of "best practices" guides for JDBC/MySQL that tells me to specify setFetchSize().

However, I have no idea when to specify, and what(statement, result set) to specify.

Statement.setFetchSize() or PreparedStatement.setFetchSize() 
ResultSet.setFetchSize()
  1. Of these two, what should I specify?
  2. From javadoc and oracle documentation, this is where I get confused about "when"

Javadoc

The default value is set by the Statement object that created the result set. The fetch size may be changed at any time.

Oracle Doc

Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.

Please correct me if I am wrong. Does this mean that setFetchSize is only Affective before a query is executed?(Therefore, setFetchSize on a ResultSet is useless? But happens to "The fetch size may be changed at any time"?)

Jee Seok Yoon
  • 4,716
  • 9
  • 32
  • 47
  • 3
    The second link is for the Oracle JDBC driver (i.e. for the Oracle *database*), not a "general" JDBC documentation - especially *not* for MySQL. You should check the MySQL JDBC manual for details on that implementation. –  Jan 03 '14 at 09:07
  • @a_horse_with_no_name Thanks for pointing that out. It was a obvious thing I should have checked first... – Jee Seok Yoon Jan 03 '14 at 09:27
  • https://docs.oracle.com/cd/A87860_01/doc/java.817/a83724/resltse5.htm – Kanagavelu Sugumar Nov 19 '15 at 09:54

2 Answers2

7

You should read this page from the official docs on result sets. It says

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                            java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

In effect, setting only fetchSize have no effect on the connector-j implementation.

Patrick Herrera
  • 3,120
  • 26
  • 21
Andreas Wederbrand
  • 38,065
  • 11
  • 68
  • 78
  • 1
    This is only for MySQL, most other databases and JDBC drivers behave better than this. – Mark Rotteveel Jan 03 '14 at 10:30
  • True, however, the question was about, at least in part, MySQL. – Andreas Wederbrand Jan 03 '14 at 14:18
  • As the question also referred to Oracle documentation I took it as a general JDBC question; this is one of my pet peeves about the MySQL Connector/J implementation... :) – Mark Rotteveel Jan 03 '14 at 14:58
  • 2
    After wasting some time trying alternatives, I can confirm that Connector/J really does only support either returning the entire result set, or streaming them one at a time. I was hoping to return batches of 10,000 but the source code specifically checks for Integer.MIN_VALUE and returns everything if any other value specified. For anyone using JDBI (http://jdbi.org/), adding the fetch size to your query is sufficient as the other flags are already at the correct values: handle.createQuery(sql) .setFetchSize(Integer.MIN_VALUE) .map()/fold() etc – Patrick Herrera Jan 23 '15 at 04:19
  • 3
    This is (no longer) true; when setting the JDBC option `useServerFetch`, MySQL actually supports any positive fetch size. However, there are some caveats; see my response to this similar question: http://stackoverflow.com/a/36986532/1016939 – lxgr May 02 '16 at 15:56
  • @lxgr did you mean to write `useCursorFetch` instead of `useServerFetch`? – Matt Ball Apr 05 '17 at 20:05
  • @MattBall You're right, that's what it is actually called. – lxgr Apr 05 '17 at 20:31
0

Found a good article with examples to your question:

https://www.baeldung.com/jdbc-resultset

7.1. Using Fetch Size on Statement 7.2. Using Fetch Size on ResultSet

akasha
  • 494
  • 6
  • 4