Spring ‐ 커넥션풀과 데이터소스 - dnwls16071/Backend_Study_TIL GitHub Wiki
- 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API
- JDBC 기능
- Connection : 연결
- Statement : SQL 내용
- ResultSet : SQL 요청에 대한 응답
❗각 DB마다 다르기에 DB 엔진을 바꿀 때마다 코드를 바꿔야한다는 문제가 발생한다.
- 애플리케이션 로직은 DB 드라이버를 통해 커넥션을 조회한다.
- DB 드라이버는 DB와 TCP/IP 커넥션을 연결한다.
- DB 드라이버는 연결이 완료되면 ID와 PW, 기타 정보를 DB에 전달한다.
- DB는 ID, PW를 통해 인증을 완료하고 내부에 DB 세션을 생성한다.
- DB는 커넥션 생성이 완료되었다는 응답을 보낸다.
- DB 드라이버는 커넥션 객체를 생성해서 클라이언트에 반환한다.
- 애플리케이션을 시작하는 시점에 커넥션 풀은 필요한 만큼 미리 커넥션을 확보해서 커넥션 풀에 보관한다.
- 커넥션 풀에 보관된 커넥션은 이미 DB와 TCP/IP로 커넥션이 연결된 상태이기 때문에 언제든지 즉시 SQL을 DB에 전달할 수 있다.
- 애플리케이션 로직에서는 DB 드라이버를 통한 커넥션 확보를 하지 않아도 커넥션 풀에 이미 생성된 커넥션을 그대로 가져다 쓰기만 하면 된다.
- 커넥션을 사용하고 나면 종료하는 것이 아니라 다음에 다시 사용할 수 있도록 해당 커넥션을 그대로 커넥션 풀에 반환한다. 이 때, 커넥션을 종료하는 것이 아니라 커넥션이 살아있는 상태로 커넥션 풀에 반환해야 한다는 것이다.
❗대표적인 커넥션 풀 오픈소스는 HikariCP가 있다.
❗Spring Boot 2.0부터는 기본적으로 HikariCP를 사용한다.
- DataSource는 커넥션을 획득하는 방법을 추상화한 인터페이스이다.
- 인터페이스 핵심 기능은 커넥션 조회이다.
- 대부분의 커넥션 풀은 DataSource 인터페이스를 이미 구현해두었으므로 DataSource에만 의존하도록 애플리케이션 로직을 구현하면 된다.
@Slf4j
public class ConnectionTest {
@Test
@DisplayName("DriverManager")
void driverManager() throws SQLException {
// 커넥션 획득시마다 필요한 파라미터를 넘겨야 한다.
Connection con1 = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Connection con2 = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//19:38:27.489 [Test worker] INFO com.jwj.db1.jdbc.connection.ConnectionTest -- connection=conn0: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class org.h2.jdbc.JdbcConnection
//19:38:27.493 [Test worker] INFO com.jwj.db1.jdbc.connection.ConnectionTest -- connection=conn1: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class org.h2.jdbc.JdbcConnection
log.info("connection={}, class={}", con1, con1.getClass());
log.info("connection={}, class={}", con2, con2.getClass());
}
@Test
@DisplayName("DriverManagerDataSource")
void dataSourceManagerDataSource() throws SQLException {
// 초기 세팅시 설정값을 넘긴다.
DriverManagerDataSource dataSource = new DriverManagerDataSource(URL, USERNAME, PASSWORD);
//19:40:26.793 [Test worker] INFO com.jwj.db1.jdbc.connection.ConnectionTest -- connection=conn0: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class org.h2.jdbc.JdbcConnection
//19:40:26.797 [Test worker] INFO com.jwj.db1.jdbc.connection.ConnectionTest -- connection=conn1: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class org.h2.jdbc.JdbcConnection
useDataSource(dataSource);
}
private void useDataSource(DataSource dataSource) throws SQLException {
Connection con1 = dataSource.getConnection();
Connection con2 = dataSource.getConnection();
log.info("connection={}, class={}", con1, con1.getClass());
log.info("connection={}, class={}", con2, con2.getClass());
}
}@Slf4j
public class ConnectionTest {
private void useDataSource(DataSource dataSource) throws SQLException {
Connection con1 = dataSource.getConnection();
Connection con2 = dataSource.getConnection();
log.info("connection={}, class={}", con1, con1.getClass());
log.info("connection={}, class={}", con2, con2.getClass());
}
@Test
@DisplayName("HikariDataSource")
void dataSourceConnectionPool() throws SQLException, InterruptedException {
// DataSource 인터페이스에 의존적으로 개발을 하지만 추가적인 설정을 위해 HikariDataSource로 받는다.
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setJdbcUrl(URL);
hikariDataSource.setUsername(USERNAME);
hikariDataSource.setPassword(PASSWORD);
hikariDataSource.setMaximumPoolSize(10);
hikariDataSource.setPoolName("MyPool");
useDataSource(hikariDataSource);
Thread.sleep(1000);
}
}실행 결과
19:50:58.837 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- MyPool - configuration:
19:50:58.841 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- allowPoolSuspension.............false
19:50:58.841 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- autoCommit......................true
19:50:58.841 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- catalog.........................none
19:50:58.841 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- connectionInitSql...............none
19:50:58.841 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- connectionTestQuery.............none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- connectionTimeout...............30000
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- dataSource......................none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- dataSourceClassName.............none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- dataSourceJNDI..................none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- dataSourceProperties............{password=<masked>}
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- driverClassName.................none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- exceptionOverrideClassName......none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- healthCheckProperties...........{}
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- healthCheckRegistry.............none
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- idleTimeout.....................600000
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- initializationFailTimeout.......1
19:50:58.842 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- isolateInternalQueries..........false
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- jdbcUrl.........................jdbc:h2:tcp://localhost/~/db1
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- keepaliveTime...................0
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- leakDetectionThreshold..........0
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- maxLifetime.....................1800000
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- maximumPoolSize.................10
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- metricRegistry..................none
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- metricsTrackerFactory...........none
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- minimumIdle.....................10
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- password........................<masked>
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- poolName........................"MyPool"
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- readOnly........................false
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- registerMbeans..................false
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- scheduledExecutor...............none
19:50:58.843 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- schema..........................none
19:50:58.844 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- threadFactory...................internal
19:50:58.844 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- transactionIsolation............default
19:50:58.844 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- username........................"sa"
19:50:58.844 [Test worker] DEBUG com.zaxxer.hikari.HikariConfig -- validationTimeout...............5000
19:50:58.844 [Test worker] INFO com.zaxxer.hikari.HikariDataSource -- MyPool - Starting...
19:50:58.846 [Test worker] DEBUG c.z.hikari.util.DriverDataSource -- Loaded driver with class name org.h2.Driver for jdbcUrl=jdbc:h2:tcp://localhost/~/db1
19:50:58.884 [Test worker] INFO com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn0: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:58.886 [Test worker] INFO com.zaxxer.hikari.HikariDataSource -- MyPool - Start completed.
19:50:58.890 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn1: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:58.890 [Test worker] INFO c.j.d.jdbc.connection.ConnectionTest -- connection=HikariProxyConnection@2061440682 wrapping conn0: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class com.zaxxer.hikari.pool.HikariProxyConnection
19:50:58.891 [Test worker] INFO c.j.d.jdbc.connection.ConnectionTest -- connection=HikariProxyConnection@1482748887 wrapping conn1: url=jdbc:h2:tcp://localhost/~/db1 user=SA, class=class com.zaxxer.hikari.pool.HikariProxyConnection
19:50:58.925 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Connection not added, stats (total=2, active=2, idle=0, waiting=0)
19:50:58.991 [MyPool housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Pool stats (total=2, active=2, idle=0, waiting=0)
19:50:58.995 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn2: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.027 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=3, active=2, idle=1, waiting=0)
19:50:59.030 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn3: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.064 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=4, active=2, idle=2, waiting=0)
19:50:59.067 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn4: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.101 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=5, active=2, idle=3, waiting=0)
19:50:59.104 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn5: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.140 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=6, active=2, idle=4, waiting=0)
19:50:59.143 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn6: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.177 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=7, active=2, idle=5, waiting=0)
19:50:59.180 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn7: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.215 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=8, active=2, idle=6, waiting=0)
19:50:59.220 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn8: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.251 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=9, active=2, idle=7, waiting=0)
19:50:59.256 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Added connection conn9: url=jdbc:h2:tcp://localhost/~/db1 user=SA
19:50:59.291 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - After adding stats (total=10, active=2, idle=8, waiting=0)
19:50:59.291 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Connection not added, stats (total=10, active=2, idle=8, waiting=0)
19:50:59.291 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool -- MyPool - Connection not added, stats (total=10, active=2, idle=8, waiting=0)