Prepared statement with result set of type TYPE_SCROLL_INSENSITIVE don't use a database cursor (at least under PostgreSQL).
This means that all results are returned at the JDBC level even if we gets only few of them.
When returning thousands of results, this impacts the traffic and memory footprint.
Forcing a cursor usage (TYPE_FORWARD_ONLY + setFetchSize) is not a good option because:
- PostgreSQL will use named statement that may have less optimized query plan and use more
- Using a limit can give a better query plan
The drawback is that we don't have anymore a total count if the number of results is greater than the limit.
This may be acceptable or not, this improvement should be optional.