Creating custom connection pool in Spring Boot application

Question

I'm writing a Spring Boot application which connects with Snowflake Data Warehouse and execute SQL queries on it. I have written a Configuration class for configuring Datasource for connecting to Snowflake Data Warehouse as follows:

@Configuration
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
public class DBConfig {

    Logger logger = LoggerFactory.getLogger(DBConfig.class);
    @Bean
    JdbcTemplate jdbcTemplate() throws IllegalAccessException, InvocationTargetException, InstantiationException {
        logger.info("-----Configuring JDBCTemplate------");
        SnowflakeBasicDataSource dataSource = new SnowflakeBasicDataSource();
        dataSource.setServerName("<myserver>.snowflakecomputing.com");
        dataSource.setUser("<my_username>");
        dataSource.setPassword("<my_password>");
        dataSource.setWarehouse("DEMO_WH");
        dataSource.setDatabaseName("DEMO_DB");
        dataSource.setSchema("PUBLIC");
        return new JdbcTemplate(dataSource);
    }
}

My pom.xml looks like as follows:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.vaibhav</groupId>
    <artifactId>snowflake-1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>snowflake-1</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>net.snowflake</groupId>
            <artifactId>snowflake-jdbc</artifactId>
            <version>3.6.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web-services</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

I have to use a connection pool for this data source in my Spring boot application.

How can I use HikariCP connection pool in my application which can work perfectly fine with my Customized DataSource?

------EDIT --- Thanks for providing solution, finally my working code looks like

@Configuration
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class DBConfig {

    Logger logger = LoggerFactory.getLogger(DBConfig.class);

    @Bean
    JdbcTemplate jdbcTemplate() throws IllegalAccessException, InvocationTargetException, InstantiationException {
        logger.info("-----Configuring JDBCTemplate------");

        HikariConfig config = new HikariConfig();
        config.setDriverClassName("net.snowflake.client.jdbc.SnowflakeDriver");
        // config.setDataSourceProperties(properties);
        config.setJdbcUrl("jdbc:snowflake://<myserver>.snowflakecomputing.com/?warehouse=DEMO_WH&db=DEMO_DB&schema=PUBLIC");
        config.setUsername("<my_username>");
        config.setPassword("<my_password>");
        HikariDataSource ds = new HikariDataSource(config);

        return new JdbcTemplate(ds);
    }
}

Thank you for leaving example of working code. It saved me :) — user9347049, Oct 18 '21 at 13:38

score 3 · Accepted Answer · answered Dec 19 '18 at 06:33

3

See setting SnowflakeDriver with Hikari:

  HikariConfig config = new HikariConfig();    
  config.setDriverClassName("com.snowflake.client.jdbc.SnowflakeDriver");    
  config.setDataSourceProperties(properties);    
  config.setJdbcUrl(connectStr);    
  HikariDataSource ds = new HikariDataSource(config);

answered Dec 19 '18 at 06:33

Ori Marko

56,308
23
131
233

Thanks for this, I will try this solution and will update – Vaibhav Sharma Dec 19 '18 at 06:52
This solution worked or me, I will post the updated code, thanks :) – Vaibhav Sharma Dec 19 '18 at 08:27

Ian Lim · Answer 2 · 2018-12-19T08:41:07.950

If spring-jdbc is being included, Spring will automatically create JdbcTemplate based on the available DataSource. So If the above answers does not satisfy you, you may just try:

@Configuration
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
public class DBConfig {

    Logger logger = LoggerFactory.getLogger(DBConfig.class);

    // TAKE NOTE THAT THIS MIGHT ALREADY BE DONE BY SPRING
    @Bean
    protected JdbcTemplate jdbcTemplate( DataSource dataSource )
    {
        return new JdbcTemplate( dataSource );
    }    

    @Bean
    protected DataSource makeDataSource() throws IllegalAccessException, InvocationTargetException, InstantiationException {
        logger.info("-----Configuring JDBCTemplate------");
        SnowflakeBasicDataSource dataSource = new SnowflakeBasicDataSource();
        dataSource.setServerName("<myserver>.snowflakecomputing.com");
        dataSource.setUser("<my_username>");
        dataSource.setPassword("<my_password>");
        dataSource.setWarehouse("DEMO_WH");
        dataSource.setDatabaseName("DEMO_DB");
        dataSource.setSchema("PUBLIC");
        return dataSource;
    }
}

Thanks for the solution, however I want to use customized datasource in place of AutoConfigured datasource — Vaibhav Sharma, Dec 19 '18 at 08:26

score 1 · Answer 3 · answered Dec 19 '18 at 06:42

Hikari is default connection pool in Spring-boot 2+

We have nothing to do if we want to use Hikari in an application based on Spring Boot 2.x.

You can set different properties of connection pool thru application.yml / application.properties. Below is an example of application.yml:

spring:
  datasource
    hikari:
      maximumPoolSize: 4 # Specify maximum pool size
      minimumIdle: 1 # Specify minimum pool size
      driver-class-name: com.snowflake.client.jdbc.SnowflakeDriver

This is a useful link for configuring Hikari CP.

As you can see in my code, I have disable DatasourceAutoConfiguration for providing customized SnowflakeDataSource, so I dont think this will work with AutoConfiguration disabled. — Vaibhav Sharma, Dec 19 '18 at 06:51

Creating custom connection pool in Spring Boot application

3 Answers3

Linked