How to increase default fetchsize for queries (Oracle DB)

Question

How can I set the fetchsize for an Oracle database? For example, when starting SQL*Plus, I can set arraysize 250 and increase the performance of my queries drastically. How can I achieve the same with ORMLite?

The only thing I found to do this programmatically is the method void setDefaultRowPrefetch(int var1) throws SQLException; in oracle.jbdc.OracleConnection, but this has nothing to do ORMLite. So, how can I access the underlying connection and set database specific parameters, such as arraysize for Oracle DB's?

Answer 1

The only thing I found to do this programmatically is the method void setDefaultRowPrefetch(int var1) throws SQLException; in oracle.jbdc.OracleConnection, but this has nothing to do ORMLite. So, how can I access the underlying connection and set database specific parameters, such as arraysize for Oracle DB's?

Unfortunately, right now this is a bit of a hack.

When running with JDBC, the DatabaseConnection that you get from the JdbcConnectionSource is actually JdbcDatabaseConnection. This connection has a public getInternalConnection() method which returns the underlying java.sql.Connection (see the source). That should be (hopefully) a oracle.jbdc.OracleConnection.

I've made the change to the source to official expose that method (renamed to getUnderlyingConnection()) which will be in version 5.3 of ORMLite. See the pull-request.