Excessive Oracle Database Sessions Created by Spring's JdbcClient

Question

I'm encountering an issue with Spring's JdbcClient where it seems to be creating an excessive number of sessions in my Oracle database. This behavior is leading to unexpected resource consumption and potential performance problems. I'm seeking assistance in understanding and resolving this issue.

Problem Description:

1. Framework/Tools Used: I'm using Spring Boot with the JdbcClient for database interactions.
2. Database: Oracle Database
3. Observation: The application appears to be creating a large number of database sessions reach 4k session, which is impacting performance, for each execution of JdbcClient create database session.

Here's a snippet of my application

@Autowired
 private JdbcClient jdbcClient;

 jdbcClient.sql("query")
            .param("id", id)
             .query(String.class)
             .stream().findFirst().orElseGet(() -> "")

while I'm using DriverManagerDataSource to connect to oracle database

Environment Information:

Spring Boot Version: 3.2.1 Oracle Database Version: 19c

when I'm using DriverManagerDataSource JdbcClient for each time called by client it will create session when i try to change data source from DriverManagerDataSource to HikariCP and setting connection pool that open sessions in database when application start but when i call end point that use JdbcClient first request get succeeded but any other request getting error and doesn't open too many sessions

error

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30006ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:160)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:118)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
    ... 109 more

what i see on database

sessions info

i think the database are waiting from JdbcClient to end connection but there is problem with it

more details..
main class

    @EnableConfigurationProperties(RsaKeyProperties.class)
    @SpringBootApplication
    @EnableAutoConfiguration
    @EnableEncryptableProperties
    @EnableAsync
    @EnableScheduling
    @EnableCaching
    @PropertySource(name = "EncryptedProperties", value = "classpath:database.properties")
    public class Application 

service class

@Service
public class InfoService {
 @Autowired
 private JdbcClient jdbcClient;
public SponsorInformation getInformation(String token, PhoneType phoneType) {
String id = tokenService.decodeToken(token.substring(7)).getSubject();
return jdbcClient.sql("query")
            .param("id", id)
             .query(String.class)
             .stream().findFirst().orElseGet(() -> "");
}

app config class ( with hikari also try with DriverManagerDataSource )

@Configuration
@PropertySource("classpath:database.properties")
public class AppConfig {

    @Autowired
    Environment environment;

    @Value("${url}")
    private String URL;

    @Value("${dbuser}")
    private String USER;

    @Value("${driver}")
    private String DRIVER;

    @Value("${dbpassword}")
    private String PASSWORD;

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.setDriverClassName(DRIVER);

        return new HikariDataSource(config);
    }
}

application.properties

spring.datasource.hikari.connectionTimeout=60000
spring.datasource.hikari.idleTimeout=-1
spring.datasource.hikari.maxLifetime=-1

spring.datasource.hibernate.connection.characterEncoding=UTF-8
spring.mvc.charset=UTF-8
spring.datasource.oracleucp.min-pool-size=10
spring.datasource.oracleucp.max-pool-size=405
spring.datasource.hikari.maximum-pool-size =105
spring.datasource.hikari.minimum-idle =16
jasypt.encryptor.pool-size=20

database.properties

driver=oracle.jdbc.OracleDriver
url= jdbc:oracle:thin:@ip:port/sid?useUnicode=yes&characterEncoding=UTF-8 
dbuser= [database_user]
dbpassword=[encrypted_database_password]

Answer 1

You are using the stream() method from the JdbcClient. Now if you read the documentation that method comes with one caveat:

the result Stream, containing mapped objects, needing to be closed once fully processed (e.g. through a try-with-resources clause)

If you don't close it it will keep the underlying ResultSet and with that the Statement and Connection open. Modify your code to something like this.

public SponsorInformation getInformation(String token, PhoneType phoneType) {
  String id = tokenService.decodeToken(token.substring(7)).getSubject();
  try (Stream<String> stream =  jdbcClient.sql("query")
            .param("id", id)
             .query(String.class)
             .stream()) {
    return.findFirst().orElseGet(() -> "");
  }
}

This will close the Stream after use and free the blocked resources.