SpringBoot how to set connection fetch size for Hikari Pool and jdbcTemplate

Question

I'm looking for a way to

• configure Hikari Connection Pool for Spring DataSource
• explicitly set fetchSize for resultSet.

Here is my application.properties

# Datasource
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:postgresql://mydb
spring.datasource.username=user
spring.datasource.password=pass
spring.datasource.driverClassName=org.postgresql.Driver
#Hikari
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.auto-commit=true

here is my service code

@Service
@RequiredArgsConstructor
public class PerfService {

    private final JdbcTemplate template;

    public RowCountResponse getData(String param) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = Objects.requireNonNull(template.getDataSource()).getConnection();
          
            preparedStatement = connection.prepareStatement("SELECT whatever");
            preparedStatement.setFetchSize(30000); // !!!
            preparedStatement.setString(1, param);
          
            ResultSet resultSet = preparedStatement.executeQuery();
            int rowCount = 0;
            while (resultSet.next()) {
                rowCount++;
            }

            resultSet.close();
            return new RowCountResponse()
                    .setRowCount(rowCount);
        } catch (Exception e) {
            throw new RuntimeException("Error while fetching rows", e);
        } finally {
            JdbcUtils.closeStatement(preparedStatement);
            DataSourceUtils.releaseConnection(connection, template.getDataSource());
        }
    }
}

• Seems like my pool is leaking. I don't return connection properly to the pool.
• Is there more elegant way to use jdbcTemplate and preparedStatement.setFetchSize(30000);

Answer 1

Sorry for being late, But may help others.

There is an issue with postgresql when setting fetchSize - https://github.com/spring-projects/spring-framework/issues/24848

In order to work you also have to set autoCommit=false for your connection so in your example it would be:

spring.datasource.hikari.auto-commit=false

But setting auto-commit to false for your connection pool could be inconvenient so one workaround is to have two connection pools - one readonly with autoCommit=false and second(ordinary) with autoCommit=true.

Also JdbcTemplate have setFetchSize method so you could simply do

template.setFetchSize(30000);