Oracle data source data pool join not working with Spring and JDBCTemplate

Question

Question: There are many active closed physical connections with the database, even with the connection pool. Can someone tell me why this is so?

I configured connection pool settings using oracle.jdbc.pool.OracleDataSource. However, it seems that physical connections do not close after use. I thought: since this is pooling, the connections will be reused from the pool, so so many physical connections will not be made, but this is not what is happening now!

There are more than 100 active physical connections in the database generating the application (not from plsql developers or any such client tools), which is why it triggers a TNS error when trying to write to the database, where, since the read operations are accurate even with a large number of active compounds.

Here is the Spring configuration,

<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
                                p:URL="${url}"
                                p:user="${username}"
                                p:password="${password}"
                                p:connectionCachingEnabled="true">
                                <property name="connectionProperties">
                                   <props merge="default">
                                      <prop key="AutoCommit">false</prop>
                                   </props>
                                </property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
                                p:dataSource-ref="oracleDataSource" />

<bean id="transactionManager"
                                class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
                                p:dataSource-ref="oracleDataSource">
</bean>

SQL that returns 100+ active connections,

select username, terminal,schemaname, osuser,program from v$session where username = 'grduser'

Answer 1

You must configure the connection cache, the default value for maximum connections for the implicit connection cache is the maximum number of database sessions configured for the database.

Answer 2

.

:

• ConnectionCache , . , ,

`

<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
                                        p:URL="${url}"
                                        p:user="${username}"
                                        p:password="${password}"
                                        p:connectionCachingEnabled="true">
                                        <property name="connectionProperties">
                                           <props merge="default">
                                              <prop key="AutoCommit">false</prop>
                                           </props>
                                        </property>
         <property name="connectionCacheProperties">
                    <props>
                        <prop key="MinLimit">5</prop>
                        <prop key="MaxLimit">10</prop>
                        <prop key="InactivityTimeout">2</prop>
                    </props>
                </property>
     </bean>

`

, , , , , 10 . .

2. connectionCache, , ,

Connection connection = getJdbcTemplate().getDataSource().getConnection();

, JDBCTemplate . , , . , , , , maxLimit.

, - , , ArrayDescriptor [ PLSQL, IN, , Varchar RAW]. ArrayDescriptor,

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
                           "SOME_TYPE_NAME", connection );
ARRAY SQLArray= new ARRAY(arrayDescriptor, connection , arrayString);

, a connection.close().

:

Connection connection = getJdbcTemplate().getDataSource().getConnection()

• , DataSource.

- , . , . !.So maxLimit 10, 10 , , , [ ].

, , 10 db, maxLimit 10.

, , , JDBCTemplate [ 10 ]

, getJdbcTemplate().getDataSource().getConnection() , .

- , . .. connection.close() , Spring, Spring . Oracle Data Source JDBCTemplate, [ ] Spring.