0

I got this doubt when I was modifying a code for doing batch update for MySQL retrieval using Java. My understanding is that fetch size is the maximum number of rows in a ResultSet object and Batch Limit is the number of select/insert/update queries that can be added to a batch, for batch execution. Can anyone correct me if I am wrong here.

Thanks.

pnv
  • 1,437
  • 3
  • 23
  • 52
  • Reading this link also helped me: http://dev.mysql.com/doc/refman/5.0/es/connector-j-reference-implementation-notes.html. Please consider this post [link] (http://stackoverflow.com/questions/24098247/jdbc-select-batching-fetch-size-with-mysql) – pnv Sep 11 '14 at 10:41

1 Answers1

1

You are almost correct. However to add to it javadoc of Statement#setFetchSize()

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database

Whereas the batch limit is something which is related to how many rows you can insert or update something related to max_allowed_packet

On a side note:

You may also check the JDBC API implementation notes as a good read

ResultSet

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • I thought we can use fetchSize with MySQL tables in Java as mentioned in here. [link](http://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv). Could you please clarify. – pnv Sep 11 '14 at 10:14
  • @user1930402:- Indeed we can use it but that is just to give a hint as the doc specifies. – Rahul Tripathi Sep 11 '14 at 10:21
  • Thank you. Let me try this. – pnv Sep 11 '14 at 10:22
  • @user1930402:- You are welcome. You can use it this way like: `PreparedStatement stmt = connection.prepareStatement(qry); stmt.setFetchSize(1000); stmt.executeQuery();` – Rahul Tripathi Sep 11 '14 at 10:23