Hikari Connection pool in Spring Boot

What is connection pool?

• Many Apache projects support interaction with a relational database.
• Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds.
• Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large.
• Accordingly, developers often wish to share a “pool” of open connections between all of the application’s current users.
• The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required.

Need of Connection Pool?

• Connecting and disconnecting from a database is an expensive operation. By using pooling you can write your code to open and close connections but the pool decides when to actually do it, leaving a certain number of connections open for a certain time.
• Performance. Connecting to the database is expensive and slow. Pooled connections can be left physically connected to the database, and shared amongst the various components that need database access. That way the connection cost is paid for once and amortized across all the consuming components.
• Diagnostics. If you have one sub-system responsible for connecting to the database, it becomes easier to diagnose and analyze database connection usage.
• Maintainability. Again, if you have one sub-system responsible for handing out database connections, your code will be easier to maintain than if each component connected to the database itself.

Important links :

https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-connect-to-production-database

First we setup database with tables like below :

Spring Code below :

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;

import com.zaxxer.hikari.HikariDataSource;

@SpringBootApplication
public class App{

	@Autowired
	HikariDataSource ds;

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

	@Bean
	public JdbcTemplate jdbcTemplate() throws SQLException {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
		return jdbcTemplate;
	}

	@Bean
	public HikariDataSource getDataSource() throws SQLException {
		HikariDataSource hds = new HikariDataSource();
		hds.setJdbcUrl("jdbc:mysql://localhost:3306/mysql");
		hds.setUsername("tyson");
		hds.setPassword("tyson");
		return hds;
	}
}

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;
	}
}

application.properties

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

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.connection-timeout=40000
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=8
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=false