How to close the connection in Spring Boot after jdbcTemplate executes the query?

Question

In Spring Boot, does jdbcTemplate not close the connection automatically once after the it executes the query? In this case, I am executing a query using jdbcTemplate(where it connects to teradata) but the session is not closing after the query is executed. How can I close the session?

This is my dao file -

@Component
public class DDLReviewDao { 

    @Autowired
    @Qualifier("devbJdbc")
    private JdbcTemplate jdbcTemplate;

    public static final Logger logger = LogManager.getLogger(DDLReviewDao.class);

    public List<DDLObject> getDDLReviewData(DDLQuery ddlQuery) {

        String selectSql = MacroGenerator.generateMacro(ddlQuery);                  
        List<DDLObject> ddlObject = jdbcTemplate.query(selectSql, new DDLMapper());                 
        logger.info(ddlObject);
        return ddlObject;
    }

}

Solution 1

JdbcTemplate gets its connections from javax.sql.DataSource implementation - which is passed to its constructor link.

The DataSources can be basic (creates Connection object for each request) or pooling (has pool of connections and just 'borrows' one for given request's use).

So, it appears that the connection is not closing because you have passed some pooling datasource to JdbcTemplate named devbJdbc. If you realy want to close every connection opened to do the JdbcTemplate job, you can use a basic DataSource implementation: org.springframework.jdbc.datasource.SimpleDriverDataSource just like that:

@Configuration
class DevbConfig {

    @Bean(name = "devbDataSource")
    DataSource devbDataSource() {
        try {
            return new SimpleDriverDataSource(DriverManager.getDriver("jdbc:..."), "jdbc:...", "username", "password");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "devbJdbc")
    JdbcTemplate devbJdbc(@Qualifier("devbDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

Solution 2

According to the spring boot docs you can allocate a maximum number of connections to a spring pool like so:

spring.datasource.tomcat.max-active=50

This will obviously only work with the embedded webserver. If you are deploying it to something like a Jboss you'll have to configure that property in the Server config file.