what's the default size of hibernate.jdbc.fetch_size?

Question

We know by default, most of jdbc drivers' fetch sizes are 10.

does anyone know what the default fetch size in hibernate, namely hibernate.jdbc.fetch_size is?

Answer 1

In JDBC parlance the fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next(). With hibernate.jdbc.fetch_size a non-zero value determines the JDBC fetch size, which in turn calls java.sql.Statement.setFetchSize().

The JDBC fetch size is an optimization hint for the database driver; it may not result in any performance improvement if the driver you're using doesn't implement this functionality. If it does,it can improve the communication between the JDBC client and your database.

See http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#setFetchSize%28int%29 for more info.

Answer 2

The default is null, i.e. no setting. The base implementation of the Query interface, AbstractQueryImpl internally uses a RowSelection to keep track of first row, fetch size and timeout. If these values are null then the values are not set on the underlying JDBC connection.

Answer 3

Basically, all the configuration settings are used to build a o.h.c.Settings instance. This is done by the o.h.c.SettingsFactory#buildSettings() method which contains the following lines:

Integer statementFetchSize = PropertiesHelper.getInteger(Environment.STATEMENT_FETCH_SIZE, properties);
if (statementFetchSize!=null) log.info("JDBC result set fetch size: " + statementFetchSize);
settings.setJdbcFetchSize(statementFetchSize);

So, a null value is allowed, it won't be translated and will be "propagated" as such.

This setting is then used in o.h.j.AbstractBatcher.java#setStatementFetchSize:

private void setStatementFetchSize(PreparedStatement statement) throws SQLException {
    Integer statementFetchSize = factory.getSettings().getJdbcFetchSize();
    if ( statementFetchSize!=null ) {
        statement.setFetchSize( statementFetchSize.intValue() );
    }
}

This private method is called when preparing a PreparedStatement or a CallableStatement. See prepareQueryStatement and prepareCallableQueryStatement in AbstractBatcher.

So, the default value is null and results in Hibernate not calling Statement#setFetchSize().

This is actually summarized in the Reference Documentation:

3.4. Optional configuration properties

...

hibernate.jdbc.fetch_size: A non-zero value determines the JDBC fetch size (calls Statement.setFetchSize())

And here is what the javadoc has to say about Statement#setFetchSize():

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.

In other words, when using a null fetch_size, the JDBC driver will use the default value which is zero.