SprintBoot part2 (2)

JDBC 시작하기

JDBC allows a java application to connect a relational database

크게 두 단계 레이어로 구성된다.

JDBC API
JDBS DB Driver

docker를 이용해 mysql 실행
IntelliJ 연결
maven dependency 추가

JDBC CURD 실습

1. jdbc connection

public class JdbcCustomerRepository {
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);

    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from customers");
            while(resultSet.next()){
                var name = resultSet.getString("name");
                var customerId = UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id"));
                logger.info("customer id =>{} ,customer name =>{}",customerId,name);
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }finally {
            try{
                //connection은 리소스가 커서 어플리케이션이나 db 부담이되니 꼭 쿼리실행후에 닫아줘야한다.
                if(connection != null) connection.close();
                if(statement != null) statement.close();
                if(resultSet != null) resultSet.close();
            }catch (SQLException e){
                logger.error("Got error while closing connection", e);
            }

        }
    }
}

예외처리 때문에 코드가 더러운데 java 10에서 이걸 보안해주는 방식이 생김

⇒ Connection이 AutoCloseable을 상속받아 블락이 끝났을때 자동으로 닫아준다.

public class JdbcCustomerRepository {
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);

    public static void main(String[] args) throws SQLException {
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            var statement = connection.createStatement();
            var resultSet = statement.executeQuery("select * from customers");
            ) {
            while(resultSet.next()){
                var name = resultSet.getString("name");
                var customerId = UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id"));
                logger.info("customer id =>{} ,customer name =>{}",customerId,name);
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
    }
}

2. select

public class JdbcCustomerRepository {
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);

    public List<String> findNames(String name){
        var SELECT_SQL = "select * from customers WHERE name ='%s'".formatted(name);
        logger.info(SELECT_SQL);
        List<String> names = new ArrayList<>();
        try(
                var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
                var statement = connection.createStatement();
                var resultSet = statement.executeQuery(SELECT_SQL);
        ) {
            while(resultSet.next()){
                var customerName = resultSet.getString("name");
                var customerId = UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id"));
                var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
                logger.info("customer id =>{}, customer name =>{}, createdAt ={}",customerId,name,createdAt);
                names.add(name);
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return names;
    }

    public static void main(String[] args) throws SQLException {
        var names = new JdbcCustomerRepository().findNames("tester01");
        System.out.println(names);
    }
}

SQL injecton

위와 같이 SQL 문자열 조합을 사용하였을때, select에 or 절을 삽입하여 원치않은 쿼리를 실행해 정보를 빼가는 것과 같은 SQL injection이 발생할수있다.

public static void main(String[] args) throws SQLException {
    var names = new JdbcCustomerRepository().findNames("tester01' or 'a'='a");
    System.out.println(names);
}

prepareStatement

sql 문자열 조합은 신중하게 사용하여야하고 잘 컨트롤 할 수 있어야한다.

prepareStatement를 사용하면 쿼리의 문장을 분석하고 컴파일, 실행 과정을 한번만 거친후 캐시에 담겨서 재사용된다. 때문에 쿼리문이 고정이 되어있기 때문에 중간에 쿼리를 변경할 수 없다.(다이나믹 쿼리가 안됨) 뿐만 아니라 캐시사용으로 성능에도 이점이 있다.

public List<String> findNames(String name){
      var SELECT_SQL = "select * from customers WHERE name = ?"; // ? 마크 이용
      logger.info(SELECT_SQL);
      List<String> names = new ArrayList<>();
      try(
           ntatement = connection.prepareStatement(SELECT_SQL);

      ) {
					// aoutoclose가 되는게 아니라 밖에..????
          statement.setString(1,name); // 인텍스는 1부터
          logger.info("statement =>{}",statement);
          try(var resultSet = statement.executeQuery();){
              while(resultSet.next()){
                  var customerName = resultSet.getString("name");
                  var customerId = UUID.nameUUIDFromBytes(resultSet.getBytes("customer_id"));
                  var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
                  logger.info("customer id =>{}, customer name =>{}, createdAt ={}",customerId,name,createdAt);
                  names.add(customerName);
              }
          }
      } catch (SQLException e) {
          logger.error("Got error while closing connection", e);
      }
      return names;
}

⇒ SQL injection에 있어 훨씬 안전한 코드를 작성할 수 있다.

3. CURD

public class JdbcCustomerRepository {
    private static final Logger logger = LoggerFactory.getLogger(JdbcCustomerRepository.class);
    private final String SELECT_ALL_SQL = "select * from customers";
    private final String SELECT_BY_NAME_SQL = "select * from customers WHERE name = ?";
    private final String INSERT_SQL = "INSERT INTO customers(customer_id,name,email)  VALUES (UUID_TO_BIN(?),?,?)";
    private final String UPDATE_BY_ID_SQL = "UPDATE customers SET name= ? WHERE customer_id = UUID_TO_BIN(?)";
    private final String DELETE_ALL_SQL = "DELETE FROM customers";

    public List<String> findALLNames(){
        List<String> names = new ArrayList<>();
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            var statement = connection.prepareStatement(SELECT_ALL_SQL);
            var resultSet = statement.executeQuery();
        ) {
            while(resultSet.next()){
                var customerName = resultSet.getString("name");
                var customerId =  toUUID(resultSet.getBytes("customer_id"));
                var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
                logger.info("customer id =>{}, customer name =>{}, createdAt ={}",customerId,customerName,createdAt);
                names.add(customerName);

            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return names;
    }

    public List<String> findByNames(String name){

        List<String> names = new ArrayList<>();
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            var statement = connection.prepareStatement(SELECT_BY_NAME_SQL);
        ) {
            statement.setString(1,name); // 인텍스는 1부
            logger.info("statement =>{}",statement);
            try(var resultSet = statement.executeQuery();){
                while(resultSet.next()){
                    var customerName = resultSet.getString("name");
                    var customerId =  toUUID(resultSet.getBytes("customer_id"));
                    var createdAt = resultSet.getTimestamp("created_at").toLocalDateTime();
                    logger.info("customer id =>{}, customer name =>{}, createdAt ={}",customerId,name,createdAt);
                    names.add(customerName);
                }
            }
        } catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return names;
    }

    public int insertCustomer(UUID customerId, String name, String email){
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            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;
    }

    public int updateCustomerName(String name, UUID customerId){
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            var statement = connection.prepareStatement(UPDATE_BY_ID_SQL);
        ) {
            statement.setString(1,name);
            statement.setBytes(2,customerId.toString().getBytes());
            logger.info("customer id =>{}, customer name =>{}",customerId,name);
            return statement.executeUpdate();
        }
        catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return 0;
    }

    public int deleteAllCustomer(){
        try(
            var connection = DriverManager.getConnection("jdbc:mysql://localhost/order_mgmt",id ,pw);
            var statement = connection.prepareStatement(DELETE_ALL_SQL);
        ) {
            return statement.executeUpdate();
        }
        catch (SQLException e) {
            logger.error("Got error while closing connection", e);
        }
        return 0;
    }

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

    public static void main(String[] args) throws SQLException {
        var customerRepository = new JdbcCustomerRepository();

        var count = customerRepository.deleteAllCustomer();
        logger.info("delete count => {}",count);
        customerRepository.insertCustomer(UUID.randomUUID(), "new-user","new-user@gmail.com");
        var customer2 = UUID.randomUUID();
        customerRepository.insertCustomer(customer2, "new-user2","new-user2@gmail.com");
        customerRepository.updateCustomerName("update-user",customer2);

        var names = customerRepository.findALLNames();
        System.out.println(names);
    }
}

4. UUID

UUID type 4 : 타입 통일해서 사용하기

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

UUID customerId =  toUUID(string.getBytes());

출처 - 해리 : SpringBoot Part1

'Back-end 데브코스 > week 03 - 05 TIL (Spring)' 카테고리의 다른 글

[TIL] 221110 - SpringBoot Part2 : Embedded DB, Named Parameter Template, 트랜잭션 (0)	2022.11.11
[TIL] 221109 - SpringBoot Part2 : Spring의 JDBC지원 (0)	2022.11.10
[TIL] 221107 - SpringBoot Part2 : Spring Test 시작하기 (0)	2022.11.08
[TIL] 221104 - SpringBoot Part1 : logging, SpringBoot (2)	2022.11.06
[TIL] 221103 - SpringBoot Part1 - 애플리케이션 속성 관리 (0)	2022.11.06

📎 clip code 📎

[TIL] 221108 - SpringBoot Part2 : JDBC

SprintBoot part2 (2)

JDBC 시작하기

JDBC CURD 실습

1. jdbc connection

2. select

3. CURD

4. UUID

'Back-end 데브코스 > week 03 - 05 TIL (Spring)' 카테고리의 다른 글

댓글

티스토리툴바

[TIL] 221108 - SpringBoot Part2 : JDBC

SprintBoot part2 (2)

JDBC 시작하기

JDBC CURD 실습

1. jdbc connection

2. select

3. CURD

4. UUID

'Back-end 데브코스 > week 03 - 05 TIL (Spring)' 카테고리의 다른 글

관련글

댓글

티스토리툴바