quiz

https://h-saeneul.tistory.com/71

DB와 연동되는 경품 추첨 이벤트 구현 QUIZ

>> 선생님 풀이

 

-- QUIZ, SQL Developer

-- 상품ID/상품명/당첨확률/남은수량/초기수량

 

CREATE SEQUENCE event_prize_id_seq NOCACHE NOCYCLE;

 

CREATE TABLE eventprizes (

    prize_id NUMBER(3)

        CONSTRAINT prize_id_pk PRIMARY KEY,

    prize_name VARCHAR2(20),

    prize_rate NUMBER(3, 3),

    remain_qty NUMBER(3),

    init_qty NUMBER(3)         

);

 

ALTER TABLE eventprizes RENAME CONSTRAINT prize_id_pk TO event_prize_id_pk;

SELECT * FROM eventprizes;

SELECT * FROM user_constraints WHERE table_name = 'EVENTPRIZES';

DROP TABLE eventprizes;

 

 

 

 

package database.quiz.event;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

 

public class DBConnector {

 

public static String driverPath = "oracle.jdbc.driver.OracleDriver";

public static String url = "jdbc:oracle:thin:@localhost:1521:XE";

public static String id = "testuser";

public static String pw = "1234";

 

static { // 한 번만 불러오기 위해서 static

try {

Class.forName(driverPath);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

 

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(url, id, pw);

}

 

}

 

 

package database.quiz;

 

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

 

import database.quiz.event.EventDao;

import database.quiz.event.model.Prize;

 

public class J06_PrizeEvent_2

        EventDao eventDao = new EventDao();

        List<Prize> box = new ArrayList<>(); // Prize 객체를 저장할 ArrayList인 box를 초기화

 

        public J06_PrizeEvent_2() {

                //각 상품의 확률에 따라 box 리스트에 상품을 추가

                //데이터베이스에 있는 각 상품은 그 확률에 비례하여 box 리스트에 여러 번 추가됩니다.

                //만약 어떤 상품의 확률이 0.05 (즉, 5%)이라면, 1000번 반복돌면서 해당 상품을 50번 box 리스트에 추가

                List<Prize> prizeList = eventDao.selectAllPrizes();

        

                int count =0;

                

                // 확률을 prize_rate NUMBER(3, 3) 즉 0.001까지 가능하다.

                for(Prize p : prizeList) {

                        int r = (int)(p.getPrize_rate() * 1000);

                        for(int i =0; i < r; ++i) {

                                box.add(p);

                                ++count;

                        }

                }

                while(count < 1000){

                        box.add(null);

                        ++count;

                }

                

        }

        

        public Prize draw() throws SQLException {

                if (eventDao.getAllprizesRemainQty() <= 0) {

                        throw new SQLException("모든 상품 소진");

                }

                

                //return box.get((int)(Math.random()*1000));

                //box 리스트에서 상품을 무작위로 추첨하여 반환

                

                Prize p = box.get((int)(Math.random()*1000));

                // DB에 수량 감소 반영

                // 추첨한 상품의 ID를 이용하여 EventDao 클래스의 reduceRemainQty 메서드를 호출하여 상품의 수량을 감소

                if(p != null) {

                        int result = eventDao.reduceRemainQty(p.getPrize_id());

                        

                        if(result == 1) {

                                System.out.println("이미 수량이 0이라 뽑지 못했습니다.");

                                return p;

                        }

                        

                        // 감소시킨뒤 현재 수량 읽어서 프로그램 반영

                        Prize p2 = eventDao.getPrize(p.getPrize_id());

                        p.setRemain_qty(p2.getRemain_qty());

                }

                

                return p;

        }

 

        public static void main(String[] args) {

                

                //EventDao eventDao = new EventDao();

                //System.out.println(eventDao.selectAllPrizes()); // 뽑기할 수 있는 그런 배열, 확률테이블 등등 을 만들 수 있다.

                J06_PrizeEvent_2 event = new J06_PrizeEvent_2();

 

                //while (true) -> 무한루프를 돌리면 DB가 이 속도를 따라 잡지 못해서  Listener refused the connection with the following error:~~ 에러출력

                for(int i = 0 ; i < 100; ++i){

                        Prize p;

                        try {

                                p = event.draw();

                        } catch (SQLException e) {

                                System.out.println("모든 상품 소진");

                                e.printStackTrace();

                                break;

                        }

                        

                        if(p != null) {

                        System.out.println(p);

                        }

                }

 

        }

 

}

 

 

package database.quiz.event;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public class AdminDao {

// adminDao (Database Access Object) -> DB에 쿼리문 보내고 결과 반영하는 객체

 

// DB 관리하는 클래스를 만들겠다

 

// 1. DB에 새로운 상품을 추가하는 메서드

public int addPrize(String prize_name, Double prize_rate, Integer init_qty) {

 

try (Connection conn = DBConnector.getConnection();) {

conn.setAutoCommit(false);

 

String sql = "INSERT INTO eventPrizes(prize_id, prize_name, prize_rate, remain_qty, init_qty)"

+ "VALUES(event_prize_id_seq.nextval, ?, ?, ?, ?)";

 

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setString(1, prize_name);

pstmt.setDouble(2, prize_rate);

pstmt.setInt(3, init_qty);

pstmt.setInt(4, init_qty);

 

// insert 후 확률이 100% 넘는지 체크가 필요하다

pstmt.executeUpdate();

}

 

String sql2 = "SELECT sum(prize_rate) AS \"total_rate\"" + "FROM eventprizes";

 

try (PreparedStatement pstmt = conn.prepareStatement(sql2); ResultSet rs = pstmt.executeQuery();) {

 

// 한 행 꺼내기

rs.next();

Double total_rate = rs.getDouble(1);

 

if (total_rate > 1) {

System.out.println("총 확률이 100% 넘어서 상품 추가가 취소되었습니다.");

conn.rollback();

// return -1;

throw new SQLException("rate error");

} else {

System.out.println("상품이 정상적으로 추가되었습니다.");

conn.commit();

}

 

}

 

return 1;

} catch (SQLException e) {

e.printStackTrace();

}

return -1;

}

// 리턴타입이 int인 이유는 새로운 상품을 추가할때 INSERT 하니까 몇번 타입의 상품이 생기는지 알수 있어서 리턴 int 한다.

 

// 2. 기존의 상품을 삭제하는 메서드

public int deletePrize(int pk) {

// pk가 있어야 삭제할 수 있을것이다.

 

try (Connection conn = DBConnector.getConnection()) {

String sql = "DELETE FROM eventprizes WHERE prize_id = ?";

 

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setInt(1, pk);

return pstmt.executeUpdate();

}

} catch (SQLException e) {

e.printStackTrace();

}

 

return 0;

}

 

// 3. 기존 상품의 초기 수량을 변경하는 메서드

public int updateQty(int pk, int qty) {

try (Connection conn = DBConnector.getConnection()) {

String sql = "UPDATE eventprizes SET init_qty = ?"

+ "WHERE prize_id =?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setInt(1, qty);

pstmt.setInt(2, pk);

 

return pstmt.executeUpdate();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return -1;

}

 

}

 

// 4. 기존의 상품의 확률을 변경하는 메서드

public int updateRate(int pk, double rate) {

try (Connection conn = DBConnector.getConnection()) {

String sql = "UPDATE  eventprizes SET prize_rate = ?"

+ "WHERE prize_id =?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setDouble(1, rate);

pstmt.setInt(2, pk);

 

return pstmt.executeUpdate();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return -1;

}

 

}

// 5. 기존 상품 하나의 수량을 리셋하는 메서드

public int reset(int pk) {

try(Connection conn = DBConnector.getConnection()){

String sql = "UPDATE eventprizes SET remain_qty = init_qty "

+ "WHERE prize_id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setInt(1, pk);

 

return pstmt.executeUpdate();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return -1;

}

 

}

 

// 6. 기존의 모든 상품의 수량을 리셋하는 메서드

public int resetAll() {

try(Connection conn = DBConnector.getConnection()){

String sql = "UPDATE eventprizes SET remain_qty = init_qty FROM eventprizes ";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

return pstmt.executeUpdate();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return -1;

}

 

}

 

 

 

// test용

//        public static void main(String[] args) {

//                AdminDao dao = new AdminDao();

//                System.out.println(dao.deletePrize(1));

 

//                dao.addPrize("청소기", 0.6, 15);

//                dao.addPrize("컴퓨터", 0.8, 5);

 

//        }

 

public static void main(String[] args) {

//                AdminDao dao = new AdminDao();

//                dao.addPrize("청소기", 0.08, 15);

//                dao.addPrize("컴퓨터", 0.02, 5);

//                dao.addPrize("냉장고", 0.02, 2); // 일단 추가하여 J06_PrizeEvent_2 메인에서 확인하기 위함

}

}

 

 

package database.quiz.event.model;

 

public class Prize {

        // 데이터를 저장하기 위한 프라이즈 타입

        

        int prize_id;

        String prize_name;

        double prize_rate;

        int remain_qty;

        int init_qty;

        

        // 데이터 담아놓는 데이터 클래스

        public Prize(int prize_id, String prize_name, double prize_rate, int remain_qty, int init_qty) {

                this.init_qty = init_qty;

                this.prize_id = prize_id;

                this.prize_name = prize_name;

                this.prize_rate = prize_rate;

                this.remain_qty = remain_qty;

        }

        

        public int getInit_qty() {

                return init_qty;

        }

        

        public String getPrize_name() {

                return prize_name;

        }

 

        public int getPrize_id() {

                return prize_id;

        }

 

        public double getPrize_rate() {

                return prize_rate;

        }

 

        public int getRemain_qty() {

                return remain_qty;

        }

// this.re~~ 마우스 우클릭 >  Source -> Generate Getters and Setters >

// 각 필드에 대한 getter 또는 setter를 자동생성 할 수 있다.(이클립스의 기능)

        

        //수량을 수정

        public void setRemain_qty(int remain_qty) {

                this.remain_qty = remain_qty;

        }

        

        @Override

        public String toString() {

                return String.format("%s[%d개 남음]", prize_name, remain_qty);

        }

}

 

 

package database.quiz.event;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

 

import database.quiz.event.model.Prize;

 

public class EventDao {

 

        // 1. 모든 상품을 리스트로 읽어오는 메서드

        public List<Prize> selectAllPrizes() {

 

                try (Connection conn = DBConnector.getConnection()) {

                        String sql = "SELECT * FROM eventprizes";

                        try (PreparedStatement pstmt = conn.prepareStatement(sql);

                                        ResultSet rs = pstmt.executeQuery()) {

                                List<Prize> list = new ArrayList<>();

 

                                while(rs.next()) {

                                        list.add(new Prize(

                                                        rs.getInt(1),

                                                        rs.getString(2),

                                                        rs.getDouble(3),

                                                        rs.getInt(4),

                                                        rs.getInt(5)

                                                ));

                                }

                                return list;

                        }

                } catch (SQLException e) {

                        e.printStackTrace();

                        return null;

                }

 

        }

        

        // 2. 상품의 수량을 1 감소시키는 메서드

        public int reduceRemainQty(int pk) {

                try(Connection conn = DBConnector.getConnection()) {

                        String sql = "UPDATE eventprizes SET remain_qty = remain_qty - 1 "

                                        + "WHERE prize_id = ?";

                        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

                                pstmt.setInt(1, pk);

                                return pstmt.executeUpdate();

                        }

                } catch (SQLException e) {

                        e.printStackTrace();                        

                        return -1;

                }

                

        }

        

        //3. 해당 상품의 정보를 읽어오는 메서드

        

        public Prize getPrize(int pk) {

                try(Connection conn = DBConnector.getConnection()) {

                        String sql = "SELECT * FROM eventprizes WHERE prize_id = ?";

                        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

                                pstmt.setInt(1, pk);

 

                                try(ResultSet rs = pstmt.executeQuery()){

                                        rs.next();

                                        return new Prize(

                                                        rs.getInt(1),

                                                        rs.getString(2),

                                                        rs.getDouble(3),

                                                        rs.getInt(4),

                                                        rs.getInt(5));

                                }

                        }

                } catch (SQLException e) {

                        e.printStackTrace();                        

                }

                return null;

                

        }

        

        //4. 남은 모든 상품의 수량을 조회하는 메서드

        public int getAllprizesRemainQty() {

                

                try(Connection conn = DBConnector.getConnection()) {

                        String sql = "SELECT sum(remain_qty) FROM eventprizes";

                        

                        try(PreparedStatement pstmt = conn.prepareStatement(sql);

                                ResultSet rs = pstmt.executeQuery();                        

                        ){

                                rs.next();

                                return rs.getInt(1); // 수량을 조회해

                        }

                } catch (SQLException e) {

                        e.printStackTrace();

                        return -1;

                }

                

        }

}