How to disable jdbc prepared statement cache

When you are using prepared statement to insert a record to db where one column is blob with complete xls file.

Each time a report is created, X MB (higher than the size Y of report ) of heap is used. These results in server crash with out of memory exception after many reports are created.

We can avoid this situation by setting statementcachesize to 0 to prevent caching.

In the datasource label below config can make the cache size to 0 from default 10.

<jdbc-connection-pool-params>
...
<statement-cache-size>0</statement-cache-size>
...
</jdbc-connection-pool-params>

Please be aware that

1. default value is 10

2. you can have as many cached statements as 10(or whatever value you have)*(#number of connections in pool)

Also, if you could, by chance “reserve” one connection for that use case, e.g. by either having a pool of, say, 1 conn specific for that use case or by getting a connection and not giving it back to the pool (non invoking close) and reusing it in your use case, since Prep Stmts are linked to the connection you might significantly reduce memory usage.

We can do the same in code label too:

To disable only for this datasource:

import oracle.jdbc.OracleConnection;

((OracleConnection)dbc).setStatementCacheSize(0);

((OracleConnection)dbc).setImplicitCachingEnabled(false);

and for statement :

import oracle.jdbc.OraclePreparedStatement;

((OraclePreparedStatement)pstmt).setDisableStmtCaching(true);

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.