JDBC

DataSource

connection 관리 주체

Database Connection Pool(DBCP)

매번 connection 열고 닫는 많은 resource 요구한다. 이러한 문제를 해결하기 위해 커낵션을 미리 만들어 풀에 저장하는 방식이 Database Connection Pool이다. 커낵션을 저장하고 있는 풀은 Datasource가 관리한다.

Simple Driver DataSource

• 매번 connection을 data manager에서 가져온다.
• test용

HikariCP

• 기본으로 사용하는 connection pool

DataSource를 사용한 CRUD

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

JdbcTemplate

• DataSource를 사용할 경우 커낵션 생성하는 부분과 예외 처리하는 부분이 반복적으로 나타난다. 이러한 반복적인 요소들은 JdbcTemplate을 제거하여 사용할 수 있다.

• template callback 패턴 이용

• JdbcTemplate 을 사용하면 DataSource 는 사실 필요가 없다

  private final JdbcTemplate jdbcTemplate;
  
  public CustomerJdbcRepository(JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
  }

JdbcTemplate을 이용한 CRUD

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

Test Code

DataSource 사용

@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);

JdbctTemplate 사용

@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(TestInstance.Lifecycle.PER_CLASS):

  • test 클래스 당 인스턴스를 생성한다.
  • @BeforeAll, @AfterAll을 static method가 아닌 곳에서도 사용할 수 있다.

• @TestInstance(TestInstance.Lifecycle.PER_METHOD)
  test 함수 당 인스턴스를 생성한다.