How can I close a physical database connection from a JDBC client?

Traditional Java applications use JDBC connection pools to establish an oracle database connection to execute SQL or to call Stored Procedures and functions. Some applications call PL/SQL functions quite often, which involves frequent JDBC calls (Popularly spring-boot apache tomcat pool / HikariCP data source) to Oracle DB servers with the creation of java.sql.Struct. This needs the unwrapped connection oracle.jdbc.driver.OracleConnection from the pooled connection java.sql.Connection.

A classic example of creating a STRUCT from JDBC Template in Spring Boot :

Connection connection = null;
CallableStatement cs = null;
try {
// TODO Assumption is the data connection is oracle always
connection = jdbcTemplate.getDataSource().getConnection();
if (connection.isWrapperFor(OracleConnection.class))
connection = connection.unwrap(OracleConnection.class);
connection.setAutoCommit(false);
List<STRUCT> customStructList = new ArrayList<STRUCT>();
StructDescriptor objectDescriptor = StructDescriptor.createDescriptor(SQL.TYPE_REQUESTOBJ,
connection);
ArrayDescriptor tableDescriptor = ArrayDescriptor.createDescriptor(SQL.TYPE_REQUESTLIST,
connection);

What is logical connection ? How it differs from physical one ?

Logical or Pooled database connections are those connection objects which are created and maintained by the Connection Pool manager. To interact with the database, the application requests the Pool manager to provide a connection object. The Pool manager either finds a free connection object or creates a new one and registers it if all are exhausted (of course, if the maximum limit of connection objects is not reached yet; otherwise, the request is queued) and returns it back app.

When unwrapping the pooled connection, the control over the unwrapped connection object will not apply. i.e connection.close will not get affect.

How can I change this behavior ?

The following properties can be used to handle this kind of scenario. The abandoning feature is disabled by default and can be configured using the following properties:

• spring.datasource.tomcat.remove-abandoned- true or false: whether to remove abandoned connections from the pool. Default: false
• spring.datasource.tomcat.remove-abandoned-timeout- The number of seconds after which a borrowed connection is assumed to be abandoned. Default: 300
• spring.datasource.tomcat.log-abandoned- true or false: whether to log stack traces for application code which abandoned a statement or connection. This adds serious overhead. Default: false

In popular Application servers like WebLogic, this is addressed by the below advanced parameter,

Remove Infected Connections Enabled : Applies only to physical database connections.

When set to true, the physical connection is not returned to the connection pool after the application closes the logical connection.
Instead, the physical connection is closed and recreated.