How to change Oracle JDBC connection properties if you don’t have access to the source code

Sometimes people configure their software in ways that are appropriate for the dev environment but suboptimal for production use. For example, default fetch size. A default value of 10 will cause additional roundtrips between the database client and the database if the query returns more than ten rows. Instead of setting the fetch size to something bigger for every Statement object, I could use the magic property defaultRowPrefetch, which has roughly the same effect. How do I set it without going to the source?

With the Oracle JDBC driver, there are three non-invasive ways to set connection properties. In order of precedence (highest first):

1. Extra parameters in JDBC URL. As an example, jdbc:oracle:thin:@localhost:1521/xepdb1?defaultRowPrefetch=200
2. System properties. For my test app it would look like this: java -DdefaultRowPrefetch=200 -Dspring.profiles.active=ucp,query -jar target/connection-tester-0.0.1.jar
3. ojdbc.properties file. It is supposed to be in TNS_ADMIN directory, which can be set as a part of JDBC URL as well: jdbc:oracle:thin:@localhost:1521/xepdb1?TNS_ADMIN=/home/pripii/tnsadmin
   The file name comes from the property oracle.jdbc.config.file. Beware of the typos: it does not produce any errors if the file is not found.

Which properties can be set?

These properties change the default properties. You can set a password for the database user in JDBC URL, but it won’t have much effect if Connection.setPassword() method is called.

From the laundry list of the connection properties the most interesting seem to be networking timeouts and keepalives, and TLS properties. And it is still possible to set v$session.process, terminal, machine, osuser and program.