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()
- Of these two, what should I specify?
- 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"?)
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.
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
