목표

1. ⭕ Customer CRUD를 위해, CustomerRepository interface 만들고 CustomerJdbcRepository로 구현해보기

결과

• CustomerRepository interface

  public interface CustomerRepository {
        Customer insert(Customer customer);
  
        Customer update(Customer customer);
  
        // insert + update -> Customer save(Customer customer);
  
        int count();
  
        List<Customer> findAll();
  
        Optional<Customer> findById(UUID customerId);
  
        Optional<Customer> findByName(String name);
  
        Optional<Customer> findByEmail(String email);
  
        void deleteAll();
    }

• CustomerJdbcRepository

  @Repository
  public class CustomerJdbcRepository implements CustomerRepository {
  
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);
  
    private static DataSource dataSource;
  
    public CustomerJdbcRepository(DataSource dataSource) {
        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("Noting was inserted");
            return customer;
        }catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
    }
  
    @Override
    public Customer update(Customer customer) {
        try (
                var connection = dataSource.getConnection();
                var statement = connection.prepareStatement("UPDATE customers SET name = ?, last_login_at = ? WHERE customer_id = UUID_TO_BIN(?)");
        ) {
            statement.setString(1, customer.getName());
            statement.setTimestamp(2, customer.getLastLoginAt() != null ? Timestamp.valueOf(customer.getLastLoginAt()) : null);
            statement.setBytes(3, customer.getCustomerId().toString().getBytes());
            var executeUpdate = statement.executeUpdate();
            if(executeUpdate != 1) throw new RuntimeException("Noting was updated");
            return customer;
        }catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
    }
  
    @Override
    public int count() {
        return 0;
    }
  
    @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(resultSet, allCustomers);
            }
        } catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
  
        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(resultSet, allCustomers);
                }
            }
        } catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
  
        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(resultSet, allCustomers);
                }
            }
        } catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
  
        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(resultSet, allCustomers);
                }
            }
        } catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
  
        return allCustomers.stream().findFirst();
    }
  
    @Override
    public void deleteAll() {
        try (
                var connection = dataSource.getConnection();
                var statement = connection.prepareStatement("DELETE FROM customers");
        ) {
            statement.executeUpdate();
        } catch (SQLException exception) {
            logger.error("Got error while connection", exception);
            throw new RuntimeException(exception);
        }
    }
  
    static UUID toUUID(byte[] bytes) { // 4버전의 UUID로 형변환 하기 위해
        var byteBuffer = ByteBuffer.wrap(bytes);
        return new UUID(byteBuffer.getLong(), byteBuffer.getLong());
    }
  
    private void mapToCustomer(ResultSet resultSet, List<Customer> customers) 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();
  
        customers.add(new Customer(customerId, customerName, email, lastLoginAt, createdAt));
    }

Reference
프로그래머스 백엔드 데브코스