While you can easily set the fetch size with JdbcTemplate.setFetchSize(1000);, on Postgres one of the requirements for it to actually take effect is for the connection to be in autocommit=false mode.
Even if you're aware of that fact, it can be convoluted to achieve that by the time you're using the template. Resorting to changing the default autocommit mode, wrapping the pool or something else.
I realize this is more complicated than for example MySQL's streaming issue. Having JdbcTemplate itself change the autocommit mode automatically when fetchsize is issued for Postgres would be too database specific, and not really its job. Maybe a method for overriding autocommit mode when JdbcTemplate fetches a connection? I understand this is also a driver issue, and workarounds are available. However it can be confusing when the fetch size is ignored completely and it can force you to resort to the clumsier JDBC API.
Comment From: snicoll
Unfortunately, I don't see how that's a problem with JdbcTemplate. As you've pointed out yourself, we cannot add database-specific handling in JdbcTemplate. If use a transaction manager, you can flag your transaction read-only which will flag the actual Connection read-only as well.