DAO 메소드를 클래스로 실행

SQLday_7 정리 (2021.11.26 금요일)

연습문제

• db4 패키지 생성
• product 테이블 사용
• ProductDTO
• ProductDAO
• 생성자에서 DB 연결
• insertProduct() : 데이터 저장 (insert)
• selectProduct() : 데이터 출력 (select)
• ProductEx
• 데이터 입력하고
• insertProduct() 호출해서 데이터 저장
• selectProduct() 호출해서 데이터 출력

ProductDTO

package productDB5;

public class ProductDTO {
    String prdNo;
    String prdName;
    int prdPrice;
    String prdMaker;
    String prdColor;
    String ctgNo;

    public ProductDTO(String prdNo, String prdName, int prdPrice, String prdMaker, String prdColor, String ctgNo) {
        this.prdNo = prdNo;
        this.prdName = prdName;
        this.prdPrice = prdPrice;
        this.prdMaker = prdMaker;
        this.prdColor = prdColor;
        this.ctgNo = ctgNo;
    }

    public String getPrdNo() {
        return prdNo;
    }

    public void setPrdNo(String prdNo) {
        this.prdNo = prdNo;
    }

    public String getPrdName() {
        return prdName;
    }

    public void setPrdName(String prdName) {
        this.prdName = prdName;
    }

    public int getPrdPrice() {
        return prdPrice;
    }

    public void setPrdPrice(int prdPrice) {
        this.prdPrice = prdPrice;
    }

    public String getPrdMaker() {
        return prdMaker;
    }

    public void setPrdMaker(String prdMaker) {
        this.prdMaker = prdMaker;
    }

    public String getPrdColor() {
        return prdColor;
    }

    public void setPrdColor(String prdColor) {
        this.prdColor = prdColor;
    }

    public String getCtgNo() {
        return ctgNo;
    }

    public void setCtgNo(String ctgNo) {
        this.ctgNo = ctgNo;
    }
}

ProductDAO

package productDB5;

import db5.StudentDTO;

import java.sql.*;

public class ProductDAO {
    Connection connection;
    PreparedStatement preparedStatement;
    ResultSet resultset;


    // DB연결 생성자
    public ProductDAO() {
        try {
            String url = "jdbc:mysql://localhost:3306/sqldb6?serverTimezone=UTC";
            String user = "root";
            String pwd = "";

            connection = DriverManager.getConnection(url, user, pwd);

            if(connection != null) {
                System.out.println("연결 성공");
            }
        } catch (Exception e) {
            System.out.println("연결 오류 발생!");
            e.printStackTrace();
        }
    }
    // select Method
    public void selectProduct()  {
        try {
            String sql = "select * from product order by prdNo";
            preparedStatement = connection.prepareStatement(sql);
            resultset = preparedStatement.executeQuery(sql);

            while (resultset.next()) {
                String prdNo = resultset.getString(1);
                String prdName = resultset.getString(2);
                int prdPrice = resultset.getInt(3);
                String prdMaker = resultset.getString(4);
                String prdColor = resultset.getString(5);
                String ctgNo = resultset.getString(6);
                System.out.format("%-10s \t %-10s\t %-4d\t %-20s\t %13s\t %s  \n",
                        prdNo, prdName, prdPrice, prdMaker, prdColor, ctgNo);
            }
        } catch(Exception e) {
            System.out.println("select 오류 발생!");
            e.printStackTrace();
        }
    }
    // insert Method
    public void insertProduct(ProductDTO productDTO) {
        try{
            String sql = "insert into product values(?, ?, ?, ?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, productDTO.getPrdNo());
            preparedStatement.setString(2, productDTO.getPrdName());
            preparedStatement.setInt(3, productDTO.getPrdPrice());
            preparedStatement.setString(4, productDTO.getPrdMaker());
            preparedStatement.setString(5, productDTO.getPrdColor());
            preparedStatement.setString(6, productDTO.getCtgNo());
            int result = preparedStatement.executeUpdate();
            if(result > 0) {
                System.out.println("성공");
            }
        }catch (Exception e) {
            System.out.println("insert 오류 발생!");
            e.printStackTrace();
        }
    }
}

ProductInsert

package productDB5;

import student.StudentDAO;
import student.StudentDTO;

import java.util.Scanner;

public class ProductInsert {
    public void productInsert() {

    Scanner scan = new Scanner(System.in);
    ProductDAO productDAO = new ProductDAO();

    System.out.print("제품 번호 입력 ");
    String prdNo = scan.nextLine();

    System.out.print("제품명 입력 ");
    String prdName = scan.nextLine();

    System.out.print("가격 입력 ");
    int prdPrice = scan.nextInt();
        scan.nextLine();

    System.out.print("제조사 입력 ");
    String prdMaker = scan.nextLine();

    System.out.print("색상 입력 ");
    String prdColor = scan.nextLine();

    System.out.print("카테고리 입력 ");
    String ctgNo = scan.nextLine();

    ProductDTO productDTO =
            new ProductDTO(prdNo, prdName, prdPrice, prdMaker, prdColor, ctgNo);
    productDAO.insertProduct(productDTO);
    }
}

ProductSelect

package productDB5;

public class ProductSelect {
    public void productSelect() {
        ProductDAO productDAO = new ProductDAO();
        productDAO.selectProduct();
    }
}

ProductEx

package productDB5;

import java.util.Scanner;

public class ProductEx {
    public static void main(String[] args) {
        System.out.println("************************************");
        System.out.println("\t 제품 관리 프로그램");
        System.out.println("************************************");
        System.out.println("\t 메뉴에서 선택");
        System.out.println("************************************");
        System.out.println("1. 제품 등록");
        System.out.println("2. 제품 정보 조회");
        System.out.println("3. 제품 정보 수정");
        System.out.println("4. 제품 정보 삭제");
        System.out.println("5. 종료");
        System.out.println("************************************");
        Scanner scan = new Scanner(System.in);
        int choice = scan.nextInt();
        switch (choice) {
            case 1:
                ProductInsert productInsert = new ProductInsert();
                productInsert.productInsert();
                break;
            case 2:
                ProductSelect productSelect = new ProductSelect();
                productSelect.productSelect();
                break;
            case 3:
                break;
            case 4:
                break;
            case 5:
                break;
            default:
                System.out.println("잘못된 입력");
                break;
        }
    }
}