connection 관리 주체
매번 connection 열고 닫는 많은 resource 요구한다. 이러한 문제를 해결하기 위해 커낵션을 미리 만들어 풀에 저장하는 방식이 Database Connection Pool이다. 커낵션을 저장하고 있는 풀은 Datasource가 관리한다.
spring-boot-starter-jdbc
의존성 필요
private final DataSource dataSource;
...
// CREATE
@Override
public Customer insert(Customer customer) {
try (
var connection = dataSource.getConnection();
var statement = connection.prepareStatement("INSERT INTO customers(customer_id, name, email, created_at) values(UUID_TO_BIN(?), ?, ?, ?)");
) {
statement.setBytes(1, customer.getCustomerId().toString().getBytes());
statement.setString(2, customer.getName());
statement.setString(3, customer.getEmail());
statement.setTimestamp(4, Timestamp.valueOf(customer.getCreatedAt()));
var executeUpdate = statement.executeUpdate();
if(executeUpdate != 1) {
throw new RuntimeException("Nothing was inserted");
}
return customer;
} catch (SQLException e) {
logger.error("Got error while closing connection", e);
throw new RuntimeException(e);
}
}
// READ
@Override
public List<Customer> findAll() {
List<Customer> allCustomers = new ArrayList<>();
try (
var connection = dataSource.getConnection();
var statement = connection.prepareStatement("select * from customers");
var resultSet = statement.executeQuery();
) {
while(resultSet.next()) {
mapToCustomer(allCustomers, resultSet);
}
} catch (SQLException e) {
logger.error("Got error while closing connection", e);
throw new RuntimeException(e);
}
return allCustomers;
}
@Override
public Optional<Customer> findById(UUID customerId) {
List<Customer> allCustomers = new ArrayList<>();
try (
var connection = dataSource.getConnection();
var statement = connection.prepareStatement("select * from customers where customer_id = UUID_TO_BIN(?)");
) {
statement.setBytes(1, customerId.toString().getBytes());
try(var resultSet = statement.executeQuery()) {
while (resultSet.next()){
mapToCustomer(allCustomers, resultSet);
}
}
} catch (SQLException e) {
logger.error("Got error while closing connection", e);
throw new RuntimeException(e);
}
return allCustomers.stream().findFirst();
}
// UPDATE
@Override
public Customer update(Customer customer) {
try (
var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt", "root", "root1234!");
var statement = connection.prepareStatement("update customers set name = ?, email = ?, last_login_at = ? where customer_id=UUID_TO_BIN(?)");
) {
statement.setString(1, customer.getName());
statement.setString(2, customer.getEmail());
statement.setTimestamp(3, customer.getLastLoginAt() != null ? Timestamp.valueOf(customer.getLastLoginAt()) : null);
statement.setBytes(4, customer.getCustomerId().toString().getBytes());
var executeUpdate = statement.executeUpdate();
if(executeUpdate != 1) {
throw new RuntimeException("Nothing was updated");
}
return customer;
} catch (SQLException e) {
logger.error("Got error while closing connection", e);
throw new RuntimeException(e);
}
}
// DELETE
@Override
public void deleteAll() {
try (
var connection = dataSource.getConnection();
var statement = connection.prepareStatement("DELETE FROM customers");
) {
statement.executeUpdate();
} catch (SQLException e) {
logger.error("Got error while closing connection", e);
throw new RuntimeException(e);
}
}
private void mapToCustomer(List<Customer> allCustomers, ResultSet resultSet) throws SQLException {
var customerId = toUUID(resultSet.getBytes("customer_id"));
var customerName = resultSet.getString("name");
var email = resultSet.getString("email");
var lastLoginAt = resultSet.getTimestamp("last_login_at") != null ?
resultSet.getTimestamp("last_login_at").toLocalDateTime() : null;
var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
allCustomers.add(new Customer(customerId, customerName, email, lastLoginAt, createdAt));
}
private static UUID toUUID(byte[] bytes) {
var byteBuffer = ByteBuffer.wrap(bytes);
return new UUID(byteBuffer.getLong(), byteBuffer.getLong());
}
DataSource를 사용할 경우 커낵션 생성하는 부분과 예외 처리하는 부분이 반복적으로 나타난다. 이러한 반복적인 요소들은 JdbcTemplate을 제거하여 사용할 수 있다.
template callback 패턴 이용
JdbcTemplate 을 사용하면 DataSource 는 사실 필요가 없다
private final JdbcTemplate jdbcTemplate;
public CustomerJdbcRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
// CREATE
@Override
public Customer insert(Customer customer) {
var update = jdbcTemplate.update("INSERT INTO customers(customer_id, name, email, created_at) values(UUID_TO_BIN(?), ?, ?, ?)",
customer.getCustomerId().toString().getBytes(),
customer.getName(),
customer.getEmail(),
Timestamp.valueOf(customer.getCreatedAt()));
if(update != 1) {
throw new RuntimeException("Nothing was inserted");
}
return customer;
}
// READ
@Override
public List<Customer> findAll() {
return jdbcTemplate.query("select * from customers", customerRowMapper);
}
@Override
public Optional<Customer> findById(UUID customerId) {
try {
return Optional.ofNullable(jdbcTemplate.queryForObject("select * from customers where customer_id = UUID_TO_BIN(?)", customerRowMapper, customerId.toString().getBytes()));
}catch(EmptyResultDataAccessException e) {
logger.error("Got empty result", e);
return Optional.empty();
}
}
// UPDATE
@Override
public Customer update(Customer customer) {
var update = jdbcTemplate.update("update customers set name = ?, email = ?, last_login_at = ? where customer_id=UUID_TO_BIN(?)",
customer.getName(),
customer.getEmail(),
customer.getLastLoginAt() != null ? Timestamp.valueOf(customer.getLastLoginAt()) : null,
customer.getCustomerId().toString().getBytes()
);
if(update != 1) {
throw new RuntimeException("Nothing was updated");
}
return customer;
}
// DELETE
@Override
public void deleteAll() {
jdbcTemplate.update("DELETE FROM customers");
}
private static final RowMapper<Customer> customerRowMapper = (resultSet, rowNum) -> {
var customerId = toUUID(resultSet.getBytes("customer_id"));
var customerName = resultSet.getString("name");
var email = resultSet.getString("email");
var lastLoginAt = resultSet.getTimestamp("last_login_at") != null ?
resultSet.getTimestamp("last_login_at").toLocalDateTime() : null;
var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
return new Customer(customerId, customerName, email, lastLoginAt, createdAt);
};
@Configuration
@ComponentScan(
basePackages = {"org.prgms.kdt.customer"}
)
static class Config {
@Bean
public DataSource dataSource() {
var datasource = DataSourceBuilder.create()
.url(URL)
.username(USER_NAME)
.password(PASSWORD)
.type(HikariDataSource.class)
.build();
return datasource;
}
}
datasource.setMaximumPoolSize(100);
datasource.setMinimumIdle(100);
@Configuration
@ComponentScan(
basePackages = {"org.prgms.kdt.customer"}
)
static class Config {
@Bean
public DataSource dataSource() {
var datasource = DataSourceBuilder.create()
.url(URL)
.username(USER_NAME)
.password(PASSWORD)
.type(HikariDataSource.class)
.build();
return datasource;
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
@SpringJUnitConfig
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class CustomerJdbcRepositoryTest {
@Test
@Order(1)
...
}
TestMethodOrder
를 이용해 @Order
에 의한 실행 순서를 보장하도록 설정한다. @Order(1)
와 같이 숫자를 지정해 테스트 메서드 실행 순서를 정할 수 있다.
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
:
@BeforeAll
, @AfterAll
을 static method가 아닌 곳에서도 사용할 수 있다.@TestInstance(TestInstance.Lifecycle.PER_METHOD)
test 함수 당 인스턴스를 생성한다.