What would be the most efficient read-only options for Connection and PreparedStatement? DB changes won't happen during those SELECTs. DB pools are not used but connections and statements are re-used many times, closed only at the end. I did some research and came up with this... get-connection opts:

{:read-only   true
 :auto-commit false}

{:concurrency :read-only
 :cursors     :close
 :result-type :forward-only}

How :fetch-size (or, in fact, any of those options) interact with performance and/or streaming is very DB-specific.

I probably ought to update the docs about fetch size in particular since for PostgreSQL at least you need fetch size set to something positive when you're streaming result sets.

I need most universal read-only opts as defaults since it will be used for different rdbms... Guess I should avoid setting the :fetch-size.

It seems that MySQL uses streaming only https://stackoverflow.com/a/20900045

Yeah, it's really hard to configure things in a way that will work the same universally 😞

Ah, I see if you set useCursorFetch as a connection option on MySQL you can use fetch size just like other DBs. Oh great. And then of course if you want multi-row inserts to work as single batch operations, you need a custom option ... which is different for PostgreSQL and for MySQL 🙂

(argh! That option enables server-side cursors in a temporary table 👀 Good grief... whoever thought that was a good idea?)

I'm looking for a read-only minimum 🙂 Users will be able to change it... But not all users know / understand these JDBC options (me neither). Will test performance on most popular RDBMs to find out.