프로그래머스 백엔드 데브코스 23일차

SpringBoot Part2

JDBC CRUD

Try-with-Resources

아래 처럼 try catch문이 자동으로 자원을 해제해준다. java8부터 가능!
autocloseable을 상속받은 녀석들만 가능하다.

        try (
                
                var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt", "root", "1234");
                var statement = connection.createStatement();
                var resultSet = statement.executeQuery("select * from customers");
        ) {
            while (resultSet.next()) {
                logger.info("customer id -> {}, name -> {}",
                        UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id")),
                        resultSet.getString("name"));
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        
	// autocloseable을 상속받은 녀석들만 가능하다.
	public interface Connection  extends Wrapper, AutoCloseable { ... }

PrepareStatement

SQL Injection 공격을 막을 수 있다.

    public void findNames(String name) {
        var SELECT_SQL = "select * from customers WHERE name = ?";
        try (
				var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt", "root", "1234");
                var statement = connection.prepareStatement(SELECT_SQL);
        ) {
            statement.setString(1, name);
            try (var resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    var customerId = UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id"));
                    var customerName = resultSet.getString("name");
                    var createdAt = resultSet.getTimestamp("created_at");
                    logger.info("customer id -> {}, name -> {}, created_at -> {}",
                            customerId, customerName, createdAt != null ? createdAt.toLocalDateTime() : null);
                }
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
    }

UUID를 INSERT하기

UUID_TO_BIN(?)를 사용하면 Bytes로 넘긴 UUID가 SQL문에선 UUID로 변환되서 저장된다.

    private static final String INSERT_SQL = "INSERT INTO customers(customer_id, name, email) VALUES (UUID_TO_BIN(?), ?, ?)";

    public int insertCustomer(UUID customerId, String name, String email) {
        try (
                var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt", "root", "1234");
                var statement = connection.prepareStatement(INSERT_SQL);
        ) {
            statement.setBytes(1, customerId.toString().getBytes());
            statement.setString(2, name);
            statement.setString(3, email);
            return statement.executeUpdate();
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return 0;
    }

저장한 UUID를 제대로 가져오기

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