๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Back-end ๋ฐ๋ธŒ์ฝ”์Šค/week 03 - 05 TIL (Spring)

[TIL] 221108 - SpringBoot Part2 : JDBC

by young-ji 2022. 11. 8.

SprintBoot part2 (2)

 

JDBC ์‹œ์ž‘ํ•˜๊ธฐ

JDBC allows a java application to connect a relational database

ํฌ๊ฒŒ ๋‘ ๋‹จ๊ณ„ ๋ ˆ์ด์–ด๋กœ ๊ตฌ์„ฑ๋œ๋‹ค.

 

  • JDBC API
  • JDBS DB Driver

 

  1. docker๋ฅผ ์ด์šฉํ•ด mysql ์‹คํ–‰
  2. IntelliJ ์—ฐ๊ฒฐ
  3. 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

 

๋Œ“๊ธ€