jdbc connection pool using ThreadpoolExecutor in spring boot

Question

I have an application that runs through multiple databases and for each database runs select query on all tables and dumps it to hadoop.

My design is to create one datasource connection at a time and use the connection pool obtained to run select queries in multiple threads. Once done for this datasource, close the connection and create new one.

Here is the Async code

@Component
public class MySampleService {

    private final static Logger LOGGER = Logger
            .getLogger(MySampleService.class);

    @Async
    public Future<String> callAsync(JdbcTemplate template, String query) throws InterruptedException {

 try {
        jdbcTemplate.query(query);
        //process the results
        return new AsyncResult<String>("success");
      }
 catch (Exception ex){
         return new AsyncResult<String>("failed");
    }    
}

Here is the caller

 public String taskExecutor() throws InterruptedException, ExecutionException {
        Future<String> asyncResult1 = mySampleService.callAsync(jdbcTemplate,query1);
        Future<String> asyncResult2 = mySampleService.callAsync(jdbcTemplate,query2);
        Future<String> asyncResult3 = mySampleService.callAsync(jdbcTemplate,query3);
        Future<String> asyncResult4 = mySampleService.callAsync(jdbcTemplate,query4);

        LOGGER.info(asyncResult1.get());
        LOGGER.info(asyncResult2.get());
        LOGGER.info(asyncResult3.get());
        LOGGER.info( asyncResult4.get());

       //now all threads finished, close the connection
       jdbcTemplate.getConnection().close();
    }

I am wondering if this is a right way to do it or do any exiting/optimized solution that out of box I am missing. I can't use spring-data-jpa since my queries are complex.

Thanks

Answer 1

Spring Boot docs:

Production database connections can also be auto-configured using a pooling DataSource. Here’s the algorithm for choosing a specific implementation:

• We prefer the Tomcat pooling DataSource for its performance and concurrency, so if that is available we always choose it.
• Otherwise, if HikariCP is available we will use it.
• If neither the Tomcat pooling datasource nor HikariCP are available and if Commons DBCP is available we will use it, but we don’t recommend it in production.
• Lastly, if Commons DBCP2 is available we will use it.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa ‘starters’ you will automatically get a dependency to tomcat-jdbc.

So you should be provided with sensible defaults.