[자바 JDBC] 90. 상품 프로그램 만들기

1. DB와 연결

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/store";
        String username = "root";
        String password = "bitc5600!";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            return conn;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

2. 모델 만들기

package model;

// model -> db에 있는 table 데이터를 비슷하게 구현한 것
public class Store {
    private Integer id;
    private String name;
    private Integer price;
    private Integer qty;

    @Override
    public String toString() {
        return "Store{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", qty=" + qty +
                '}';
    }

    public Store(Integer id, String name, Integer price, Integer qty) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.qty = qty;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getPrice() {
        return price;
    }

    public Integer getQty() {
        return qty;
    }
}

3. DML 함수 만들기

package dao;

import model.Store;

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

// Data Access Object
public class StoreDAO {
    private Connection conn;

    public StoreDAO(Connection conn) {
        this.conn = conn;
    }

    // 1. 한건 조회
    public Store 한건조회(int id){
        try {
            // 2. 버퍼
            String sql = "select id, name, price, qty from store_tb where id = ?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);

            // 3. flush (rs = table 조회 결과 즉 view)
            ResultSet rs = psmt.executeQuery();

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

            if(isThere){
                Store model = new Store(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("price"),
                        rs.getInt("qty")
                );
                return model;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    // 2. 전체 조회
    public List<Store> 전체조회(){
        List<Store> models = new ArrayList<>();
        try {
            String sql = "select * from store_tb order by id desc";
            PreparedStatement psmt = conn.prepareStatement(sql);

            ResultSet rs = psmt.executeQuery();

            while(rs.next()){
                Store model = new Store(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("price"),
                        rs.getInt("qty")
                );
                models.add(model);
            }
            return models;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 3. 한건 추가
    public void 한건추가(String name, int price, int qty){
        try {
            String sql = "insert into store_tb (name, price, qty) values (?, ?, ?)";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, name);
            psmt.setInt(2, price);
            psmt.setInt(3, qty);

            int result = psmt.executeUpdate(); // write (insert, delete, update)
            
            if(result == 0){
                throw new RuntimeException("insert가 안됐어 ㅠㅠ");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 4. 한건 수정
    public void 한건수정(String name, int price, int qty, int id){
        try {
            String sql = "update store_tb set name = ?, price = ?, qty = ? where id = ?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, name);
            psmt.setInt(2, price);
            psmt.setInt(3, qty);
            psmt.setInt(4, id);

            int result = psmt.executeUpdate();

            if(result == 0){
                throw new RuntimeException("니가 준 번호가 없나봐!!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    // 5. 한건 삭제
    public void 한건삭제(int id){
        try {
            String sql = "delete from store_tb where id = ?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);

            int result = psmt.executeUpdate();

            if(result == 0){
                throw new RuntimeException("니가 준 번호가 없나봐!!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

4. 메인 실행하기

import dao.StoreDAO;
import model.Store;

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

public class StoreApp {
    public static void main(String[] args) {
        // 1. DB 연결 - 세션 만들어짐
        Connection conn = DBConnection.getConnection();

        // 2. DAO 연결
        StoreDAO dao = new StoreDAO(conn);

        // 3. 한건조회
        //Store model = dao.한건조회(3);
        //System.out.println(model);

        // 4. 한건추가
        //dao.한건추가("감자", 500, 2000);

        // 5. 한건수정
        //dao.한건수정("감자", 500, 10000, 3);

        // 6. 한건삭제
        //dao.한건삭제(1);

        // 7. 전체조회
        List<Store> models = dao.전체조회();
        for (Store model : models) {
            System.out.println(model);
        }
    }
}