Spring JDBC

이전에는 드라이버를 통해 getConnection을 통하ㅕㅁㄴ 커넥션 얻어올수 있음
이렇게 매번 커넥션을 생성하고 닫으면 그 과정에서 많은 리소스가 소모
커넥션 풀 등장
데이터소스를 통해 커넥션을 얻거나 반납 가능
데이터베이스와 연관되어있는 커넥션을 미리 만들어두고 풀에다가 저장해두었다고 필요할때마다 꺼내씀 . 커넥션을 가져오는 행위를 데이터소스를통해 간ㅡㅇ close해도 끊는게아니라 풀엑 바난ㅂ 하는거 커넥션풀을 구현한 구현체 데이터소스를 쓰기위해 디펜더시 추가

1. HikariCP

1. dependency 추가

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
	<scope>test</scope>
</dependency>

> dependency를 추가함으로서 HikariCP사용가능 

2. Customer Entity 생성
![](https://velog.velcdn.com/images%2Fkcwthing1210%2Fpost%2F638a359a-b1ee-4a26-8bff-f3b3410c77c8%2Fimage.png)
``` java
public class Customer {

    private final UUID customerId;
    private String name;
    private final String email;
    private LocalDateTime lastLoginAt;
    private final LocalDateTime createdAt;

    public Customer(UUID customerId, String name, String email, LocalDateTime createdAt) {
        validateName(name);
        this.customerId = customerId;
        this.name = name;
        this.email = email;
        this.createdAt = createdAt;
    }

    private void validateName(String name){
        if(name.isBlank()){
            throw new RuntimeException("Name should not be blank");
        }
    }

    public Customer(UUID customerId, String name, String email, LocalDateTime lastLoginAt, LocalDateTime createdAt) {
        validateName(name);
        this.customerId = customerId;
        this.name = name;
        this.email = email;
        this.lastLoginAt = lastLoginAt;
        this.createdAt = createdAt;
    }

    public void changeNames(String name){
        validateName(name);
        this.name = name;
    }

    public void login(){
        this.lastLoginAt = LocalDateTime.now();
    }
    public UUID getCustomerId() {
        return customerId;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public LocalDateTime getLastLoginAt() {
        return lastLoginAt;
    }

    public LocalDateTime getCreatedAt() {
        return createdAt;
    }
}

• 변경여부를 생각해 final키워드 쓸건지 고려 (여기서는 이름은 변경될수 있고 CustomerId는 변경되면 안되기 때문에 final로 지정)
• 자바에서는 CamelCase형식으로 작성
• isblank: 뛰어쓰기만 포함하고 있거나 빈문자열이면 true 반환

3. CustomerRepository 생성
   datasource를 이용하면 풀에서 가져오게 됨
   디펜던시 인젝션을 이용해 구현체가 바뀌더라도 동작할 수 있게

//Datasource 에서 커넥션 가져오기
@Repository
public class CustomerJdbcRepositoryImpl implements CustomerRepository {
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);

    //주입받을 데이터소스 생성
    private final DataSource dataSource;
    public CustomerJdbcRepositoryImpl(DataSource dataSource, JdbcTemplate jdbcTemplate) {
        this.dataSource = dataSource;
    }

    @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 throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
    }

    @Override
    public Customer update(Customer customer) {
        try (
                var connection = dataSource.getConnection();
                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 throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
    }



    @Override
    public List<Customer> finaAll() {
        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 throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
        return allCustomers;
    }

    private void mapTocustomer(List<Customer> allCustomers, ResultSet resultSet) throws SQLException {
        var customerName = resultSet.getString("name");
        var email = resultSet.getString("email");
        var customerId = toUUID(resultSet.getBytes("customer_id"));
        var lastLoginAt = resultSet.getTimestamp("last_login_at") != null?
                resultSet.getTimestamp("last_login_at").toLocalDateTime():null;
        var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
        //logger.info("customer id -> {},name -> {}, createdAt -> {}", customerId, customerName, createdAt);
        allCustomers.add(new Customer(customerId,customerName,email,lastLoginAt,createdAt));
    }

    @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 throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
        return allCustomers.stream().findFirst();
    }


    @Override
    public Optional<Customer> findByName(String name) {
        List<Customer> allCustomers = new ArrayList<>();
        try (
                var connection = dataSource.getConnection();
                var statement = connection.prepareStatement("select * from customers WHERE name = ?");
        ) {
            statement.setString(1, name);
            try (var resultSet = statement.executeQuery()){
                while (resultSet.next()) {
                    mapTocustomer(allCustomers,resultSet);

                }
            }
        } catch (SQLException throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
        return allCustomers.stream().findFirst();
    }

    @Override
    public Optional<Customer> findByEmail(String email) {
        List<Customer> allCustomers = new ArrayList<>();
        try (
                var connection = dataSource.getConnection();
                var statement = connection.prepareStatement("select * from customers WHERE email = ?");
        ) {
            statement.setString(1, email);
            try (var resultSet = statement.executeQuery()){
                while (resultSet.next()) {
                    mapTocustomer(allCustomers,resultSet);

                }
            }
        } catch (SQLException throwable) {
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
        return allCustomers.stream().findFirst();
    }

    @Override
    public void deleteAll() {
        try (
                var connection = dataSource.getConnection();
                var statement = connection.prepareStatement("DELETE from customers");
        ) {
            statement.executeUpdate();
        }catch (SQLException throwable){
            logger.error("Got error while connection", throwable);
            throw new RuntimeException(throwable);
        }
    }

    static UUID toUUID(byte[] bytes){
        var byteBuffer = ByteBuffer.wrap(bytes);
        return new UUID(byteBuffer.getLong(),byteBuffer.getLong());
    }
}

4. 테스트

//Datasource 에서 커넥션 가져오기
@SpringJUnitConfig
class CustomerJdbcRepositoryImplTest {
    @Configuration
    @ComponentScan(basePackages = {"org.prgrms.kdt.customer"})
    static class Config{
        @Bean
        public DataSource dataSource(){
            var datasource =  DataSourceBuilder.create()
                    .url("jdbc:mysql://localhost/order_mgmt")
                    .username("root")
                    .password("test1234")
                    .type(HikariDataSource.class)
                    .build();
//            datasource.setMaximumPoolSize(1000);
//            datasource.setMinimumIdle(100);
            return datasource;
        }
    }
    @Autowired
    CustomerJdbcRepositoryImpl customerJdbcRepository;
    @Autowired
    DataSource dataSource;
    @Test
    @Disabled
    public void testHikariConnectionPool(){
        assertThat(dataSource.getClass(),is("<class com.zaxxer.hikari.HikariDataSource>"));
    }

    @Test
    @DisplayName("전체 고객을 조회 가능")
    public void testFindAll() throws InterruptedException {
        var customers = customerJdbcRepository.finaAll();
        assertThat(customers.isEmpty(), is(false));
        Thread.sleep(10000);
    }

    @Test
    @DisplayName("이름으로 고객을 조회할 수 있다")
    public void testFindByName() throws InterruptedException {
        var customers = customerJdbcRepository.findByName("new-uswer");
        assertThat(customers.isEmpty(), is(false));

        var unknown = customerJdbcRepository.findByName("unknown-uswer");
        assertThat(unknown.isEmpty(), is(true));

    }

    @Test
    @DisplayName("이메일로 고객을 조회할 수 있다")
    public void testFindByEmail() throws InterruptedException {
        var customers = customerJdbcRepository.findByEmail("new-user@gmail.com");
        assertThat(customers.isEmpty(), is(false));

        var unknown = customerJdbcRepository.findByEmail("unknown-user@gmail.com");
        assertThat(unknown.isEmpty(), is(true));
    }

    @Test
    @DisplayName("고객을 추가할 수 있다.")
    public void testInsert() throws InterruptedException {
        customerJdbcRepository.deleteAll();

        var newCustomers = new Customer(UUID.randomUUID(),"test1-user","test1-user@gmail.com",LocalDateTime.now());
        customerJdbcRepository.insert(newCustomers);
        var retrieveCustomer = customerJdbcRepository.findById(newCustomers.getCustomerId());
        assertThat(retrieveCustomer.isEmpty(),is(false));
        assertThat(retrieveCustomer.get(),samePropertyValuesAs(newCustomers)); //가지고온 Customer value들이 newCustomers와 같은지

    }
}

커넥션을 맺으면 쓰레드가 생기게 됨
@SpringJUnitConfig: 데이터소스가 종료가 안되고 유지

히카리데이터.클래스가 기본적으로 커넥션풀에 10개를 채워넣음

4. Jdbc Templete 이용
   @Repository
   public class CustomerJdbcRepositoryImpl implements CustomerRepository {
   private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);

   //주입받을 데이터소스 생성
   private final DataSource dataSource;
   private final JdbcTemplate jdbcTemplate;
   private static RowMapper<Customer> customerRowMapper = (resultSet, i) -> {
       var customerName = resultSet.getString("name");
       var email = resultSet.getString("email");
       var customerId = toUUID(resultSet.getBytes("customer_id"));
       var lastLoginAt = resultSet.getTimestamp("last_login_at") != null?
               resultSet.getTimestamp("last_login_at").toLocalDateTime():null;
       var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
       //logger.info("customer id -> {},name -> {}, createdAt -> {}", customerId, customerName, createdAt);
       return new Customer(customerId, customerName, email, lastLoginAt,createdAt);
   };
   
   public CustomerJdbcRepositoryImpl(DataSource dataSource, JdbcTemplate jdbcTemplate) {
   
       this.dataSource = dataSource;
       this.jdbcTemplate = jdbcTemplate;
   }
   
   @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;
   }
   
   @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 inserted");
       }
       return customer;
   }

@Override
public List<Customer> finaAll() {
    return jdbcTemplate.query("select * from customers", customerRowMapper);
}

private void mapTocustomer(List<Customer> allCustomers, ResultSet resultSet) throws SQLException {
    var customerName = resultSet.getString("name");
    var email = resultSet.getString("email");
    var customerId = toUUID(resultSet.getBytes("customer_id"));
    var lastLoginAt = resultSet.getTimestamp("last_login_at") != null?
            resultSet.getTimestamp("last_login_at").toLocalDateTime():null;
    var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
    //logger.info("customer id -> {},name -> {}, createdAt -> {}", customerId, customerName, createdAt);
    allCustomers.add(new Customer(customerId, customerName, email, lastLoginAt,createdAt));
}

@Override
public int count(){
    return jdbcTemplate.queryForObject("select count(*) from customers",Integer.class);
}
@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", 0);
        return Optional.empty();
    }
}


@Override
public Optional<Customer> findByName(String name) {
    try{
        return Optional.ofNullable(jdbcTemplate.queryForObject("select * from customers WHERE name=?",
                customerRowMapper,
                name));
    } catch (EmptyResultDataAccessException e){
        logger.error("Got empty result", 0);
        return Optional.empty();
    }
}

@Override
public Optional<Customer> findByEmail(String email) {
    try{
        return Optional.ofNullable(jdbcTemplate.queryForObject("select * from customers WHERE email=?",
                customerRowMapper,
                email));
    } catch (EmptyResultDataAccessException e){
        logger.error("Got empty result", 0);
        return Optional.empty();
    }
}

@Override
public void deleteAll() {
    var update = jdbcTemplate.update("DELETE from customers");
}

static UUID toUUID(byte[] bytes){
    var byteBuffer = ByteBuffer.wrap(bytes);
    return new UUID(byteBuffer.getLong(),byteBuffer.getLong());
}

}