1. 디비와 테이블 만들기

create database productdb;

use productdb;

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    price INT,
    qty INT
) ENGINE=InnoDB
CHARACTER SET utf8mb4;

select * from product;

insert into product(id, name, price, qty) values(1, '바나나', 1000, 100);
insert into product(id, name, price, qty) values(2, '감자', 2000, 50);

2. Product 클래스 만들기

<aside> 💡

쌤이 int 말고 Integer 쓰라는데?? 왜지?

int n;

Integer num;

sout()

</aside>

package server;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {
    private Integer id;
    private String name;
    private Integer price;
    private Integer qty;
}

[참고] Lombok @Data 같은것들 설명

3. 레포지토리 만들어서 DB 테스트

[참고] 1, -1, 0을 리턴하는게 머지?

[참고] ResultSet이 머지?

ProductRepository

package server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ProductRepository {

    Connection conn = DBConnection.getConnection();

    // 1. insert(String name, int price, int qty)
    public int insert(String name, int price, int qty){
        String sql = "insert into product(name, price, qty) values(?,?,?)";
        try {
            // 2. 버퍼달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setInt(2, price);
            pstmt.setInt(3, qty);

            // 3. 쿼리전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1; // -1, 0, 1이 머지? 정리!!
    }

    // 2. deleteById(int id)
    public int deleteById(int id){
        String sql = "delete from product where id = ?";
        try {
            // 2. 버퍼달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            // 3. 쿼리전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1; // -1, 0, 1이 머지? 정리!!
    }

    // 3. findById(int id)
    public Product findById(int id){
        try {
            String sql = "select * from product where id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            // 조회해서 view로 응답받기
            ResultSet rs = pstmt.executeQuery(); // select할때!!

            // 커서 한칸 내리기
            boolean isRow = rs.next();

            // 행이 존재하면 프로젝션(열 선택하기)
            if(isRow){
                int c1 = rs.getInt("id");
                String c2 = rs.getString("name");
                int c3 = rs.getInt("price");
                int c4 = rs.getInt("qty");
                Product product = new Product(c1, c2, c3, c4);
                return product;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    // 4. findAll()
    public List<Product> findAll(){
        try {
            String sql = "select * from product";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            // 조회해서 view로 응답받기
            ResultSet rs = pstmt.executeQuery(); // select할때!!

            // 행이 존재하면 프로젝션(열 선택하기)
            List<Product> list = new ArrayList<>();
            while(rs.next()){
                int c1 = rs.getInt("id");
                String c2 = rs.getString("name");
                int c3 = rs.getInt("price");
                int c4 = rs.getInt("qty");
                Product product = new Product(c1, c2, c3, c4);
                list.add(product);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Arrays.asList();
    }
}

ProductRepositoryTest

package server;

import org.junit.jupiter.api.Test;

import java.util.List;

public class ProductRepositoryTest {

    ProductRepository repo = new ProductRepository();

    @Test
    public void deleteById_test(){
        // given
        int id = 1;

        // when
        int result = repo.deleteById(id);

        // eye
        System.out.println("result : "+result);
    }

    @Test
    public void findAll_test(){
        // when
        List<Product> list = repo.findAll();

        // eye
        for (Product p : list){
            System.out.println(p);
        }
    }

    @Test
    public void findById_test(){
        // given
        int id = 1;

        // when
        Product product = repo.findById(id);

        // eye
        System.out.println(product);
    }

    @Test
    public void insert_test(){
        // given
        String name = "딸기";
        int price = 1000;
        int qty = 10;

        // when
        int result = repo.insert(name, price, qty);

        // eye
        System.out.println("result : "+result);
    }
}

4. 클라이언트 소켓과 서버소켓의 통신