I think the only reliable way to check whether connection is valid is to execute something using the connection (so it requires roundtrip to db).
a) this execution happens only once for your transaction (which may include numbers of CRUD operations etc)
b) Validation query is chosen to be simple (like select 1 from dual for oracle), so that performance penalty is minimal.
So IMO unless you are designing some realtime application you can stop worrying about additional roundtrip stealing maybe a dozen of milliseconds...
When I was using the "validationQuery" and "testOnBorrow" properties to ensure connection works fine, did it mean two round-trip happened? Validation query and my SQL query? thus, how to reduce the db round-trip while the query became huge?
I knew iBatis to mysql would not ensure connection valid while disabling testOnBorrow, if it is the same toward oracle? is there any workaound to reduce db round-trip? any help is appreciated, thanks.