DBCP2 Connection pool in Spring Boot

JAR

implementation 'org.apache.commons:commons-dbcp2:2.0'

application.propeties

# dbcp2 settings
spring.datasource.dbcp2.initial-size=7
spring.datasource.dbcp2.max-total=20
spring.datasource.dbcp2.pool-prepared-statements=true

Example :

build.gradle

plugins {
    id 'java'
    id 'org.springframework.boot' version '2.0.5.RELEASE'
    id 'io.spring.dependency-management' version '1.0.7.RELEASE'
}

repositories {
    jcenter()
}

dependencies {
	implementation 'mysql:mysql-connector-java:8.0.19'
	implementation 'org.springframework:spring-jdbc:3.2.0.RELEASE'
	
	
	implementation 'org.springframework.boot:spring-boot-starter-jdbc:2.2.6.RELEASE'
	//connection pool
	//implementation 'com.zaxxer:HikariCP:3.4.2'
	//implementation 'org.apache.tomcat:tomcat-jdbc:9.0.33'	
	implementation 'org.apache.commons:commons-dbcp2:2.0'
	
	implementation 'org.springframework.boot:spring-boot-starter-tomcat:2.2.6.RELEASE'
	
		
	
	
	
    implementation 'com.google.guava:guava:28.0-jre'
    testImplementation 'junit:junit:4.12'
    implementation 'org.springframework.boot:spring-boot-dependencies:2.0.5.RELEASE'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
	implementation 'com.github.ulisesbocchio:jasypt-spring-boot-starter:3.0.2'
    

    components {
        withModule('org.springframework:spring-beans') {
            allVariants {
                withDependencyConstraints {
                    it.findAll { it.name == 'snakeyaml' }.each { it.version { strictly '1.19' } }
                }
            }
        }
    }
}

bootJar {
    mainClassName = 'GradleDemo.App'
}

task runJar{
	dependsOn 'assemble'
	dependsOn 'jar'
	doLast{
  		javaexec { 
    		main="-jar";
    		args = [
            	"build/libs/"+rootProject.name+".jar"
           	]
		} 
	}
}

application.properties

server.port=9090
#logging.level.org.springframework=DEBUG

#DataSource Setting
spring.datasource.url=jdbc:mysql://localhost:3306/mysql
spring.datasource.username=tyson
spring.datasource.password=tyson
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.type = org.apache.commons.dbcp2.BasicDataSource

# dbcp2 settings
spring.datasource.dbcp2.initial-size=7
spring.datasource.dbcp2.max-total=20
spring.datasource.dbcp2.pool-prepared-statements=true

App.java

package SpringBootMysql;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class App {

	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}

	@Autowired
	DataSource dataSource;

	@Bean
	public JdbcTemplate jdbcTemplate() throws SQLException {
		System.out.println(dataSource);
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate;
	}
}

Emp.java

package SpringBootMysql;

public class Emp {
	private String name;
	private String id;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
}

EmpController.java

package SpringBootMysql;

import java.util.Collection;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmpController {
	
	@Autowired
	EmpServiceDAO empServiceDAO;
	
	@GetMapping("/emp")
	public Collection<Map<String, Object>> getEmp() {
		return empServiceDAO.getEmp();
	}
}

EmpServiceDAO.java

package SpringBootMysql;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmpServiceDAO {
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	public Collection<Map<String, Object>> getEmp() {
		List<Emp> empList = new ArrayList<>();
		String sql="select * from emp";
		Collection<Map<String,Object>> rows = jdbcTemplate.queryForList(sql);
		return rows;
	}
}

Output :

Read Me :

http://commons.apache.org/proper/commons-dbcp/configuration.html