JDBCtemplate not closing the inactive connection

Question

I have an API that retrieve the sysdate from oracle DB.

I have used JDBCtemplate to execute a query and it is displaying the results as expected. But when I check the JDBC session in DB, I can see that there are 10 inactive session in Oracle. This session does not create when i start the application.

While hitting the API, this idle session's are getting created in DB and remains until application shutdown. In Spring document's it is mentioned that JDBCtemplate close the connection automatically. but in my case it is always keeping the 10 inactive connection in oracle. I'm not using any pooling mechanism here.

Can anyone suggest on this?

package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.core.env.Environment;
//import org.springframework.data.repository.query.Param;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;



@RestController
public class SessionCheck {
    
    @Autowired
    public DataSource datasource;
    
    @GetMapping("/Check")     
    public Date checkdat() {
    
        
        JdbcTemplate template = new JdbcTemplate(datasource);
        Date sysdate = (Date) template.queryForObject("select sysdate from dual", Date.class);
       
      
               return sysdate;
        
                
    }

}


application.properties

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url =jdbc:oracle:thin:@(DESCRIPTION....
spring.datasource.username= xyz
spring.datasource.password=****

score 0 · Answer 1 · answered Jun 29 '21 at 07:10

I have configured the hikariCP pool in my application and idle session reduced to 1 now. Minimum idle property helped to close the idle session which is created by jdbctemplate.

 spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=1
spring.datasource.hikari.connection-timeout=20000 
spring.datasource.hikari.idle-timeout=300000 
spring.datasource.hikari.max-lifetime=1200000

JDBCtemplate not closing the inactive connection

1 Answers1