Difference between hibernate.jdbc.fetch_size and batch fetch strategy @BatchSize

Question

For the N+1 queries issue in Hibernate, I add @BatchSize(size=20) on collection and class level. And it works very well as it fetches 20 records each time. Now I found that there is a hibernate.jdbc.fetch_size configuration option which I think it is the same as @BatchSize annotation. However, it has no effect to the N+1 queries issue when I configured it in my project.

Am I wrong with hibernate.jdbc.fetch_size ? What is hibernate.jdbc.fetch_size for?

Answer 1

The hibernate.jdbc.fetch_size is a JDBC driver configuration, and it determines:

the number of rows fetched when there is more than a one row result on select statements

Hibernate uses this configuration to set the PreparedStatement.fetchSize and according to JDBC documentation:

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 genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

So this setting is only a hint and it allows the JDBC Driver to retrieve the selected rows in batches, to minimize the number of database network round-trips.

The @BatchSize is a Hibernate fetching optimization and it controls the number of SELECT statements being generated.