ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

?? ?? ???? (DB ??, ???? ??)

?Download as PPTX, PDF?
?
_
_ce

??? : 2014? ??? ??? : 2015? 03? ?? ?? 3?? ??? : eugene@dcafe.xyz ? ??? ???? ????. DB ??? ??, ?? ? ???? ?? ?? ??? ????. ?? ??? ???? ???? PPT? ??????.

1 of 47
Downloaded 335 times
?? ??¡¯?' ???? DB ?? 0901289 ??? 1101131 ??? 1101196 ??? 1301131 ??? 1301243 ??? 1
? ? 1. ???? ?? 2. DB ?? 3. ???? ¨C DB ?? 4. ??? 5. ????? ???? ? 6. ? ??? ???? 2
1. ???? ?? ??? - ?? ?? 3
3-1. ???? 4 ?? - ?????
3-1. ???? 5 ?? - ?????
3-1. ???? ?? ?? ¨C ??? ?? ? 6
3-1. ???? ?? ? 7
2. DB ?? ???? ???? ?? ?? ??????. ????? ???? ?? ??? ??? ??? ??? ? ??. ???? ? ? ??? ??? ??. ???? ?? ??? ??? ??? ???. ?? ?? ?, ? ? ?? ?? ? (??? ??? ??)? ??? ??. ?? ?? ? ?? ??? ? ?? ??? ??? ? ??. ?? ??/?? ?? ??, ??(??? ??)? ???? ??? ? ??. ??? ? ??? ??. ??(??? ??)? ??? ??? ??? ? ??. ????? ??? ?? ??(3??)? ????. ?? ?? ??, ??? ??? ??? ?? ??? ????. /* ?? ?? */ ???? ? ? ???, ????, ??, ??????? ????. ??? ??? ?? 4?? ?? ??? ? ??. 8
???? ???? ?? ?? ??????. ????? ???? ?? ??? ??? ??? ??? ? ??. ???? ? ? ??? ??? ??. ???? ?? ??? ??? ??? ???. ?? ?? ?, ? ? ?? ?? ? (??? ??? ??)? ??? ??. ?? ?? ? ?? ??? ? ?? ??? ??? ? ??. ?? ??/?? ?? ??, ??(??? ??)? ???? ??? ? ??. ??? ? ??? ??. ??(??? ??)? ???? ??? ??? ??? ? ??. ????? ??? ?? ??(3??)? ????. ?? ?? ??, ??? ??? ??? ?? ??? ????. /* ?? ?? */ ???? ? ? ???, ????, ??, ??????? ????. ??? ??? ?? 4?? ?? ??? ? ??. 9
?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??, ?? ??, ???, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ?? ??, ?? ??? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 10
?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 11
?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 12
?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ????, ????, ???? ???? #????, ????, ?? ??/??? ?? #????, ??? ??/??? ??, ?? ?? #????, ??????, ?????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ? ?? #?????, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 13
14
2-3. DB?? 15 /* ?? ??? ?? */ CREATE TABLE customer ( customer_UID NUMBER(6) NOT NULL, customer_id VARCHAR2(30), customer_pw VARCHAR2(30), customer_name VARCHAR2(20), customer_tel NUMBER(12), CONSTRAINT customer_customer_UID_pk PRIMARY KEY(customer_UID) );
2-3. DB?? 16 /* ?? ??? ??*/ CREATE TABLE snack ( snack_UID NUMBER(6) NOT NULL, type varchar2(10), name VARCHAR2(20), price number(6), CONSTRAINT snack_snack_UID_pk PRIMARY KEY(snack_UID) ); /*??? ??? ?? */ CREATE TABLE memento ( memento_UID NUMBER(4) NOT NULL, name VARCHAR2(21), price number(6), CONSTRAINT memento_memento_UID_pk PRIMARY KEY(memento_UID) );
2-3. DB?? 17 /* ??? ?? ??? ?? */ create table memento_order ( m_order_uid number(6) not null, memento_UID number(6), order_quantity number(4), constraint memento_m_order_UID_pk primary key(m_order_uid), constraint memento_memento_uid_fk foreign key(memento_uid) references memento(memento_uid) ); /* ?? ?? ??? ?? */ create table snack_order ( s_order_uid number(6) not null, p_snack_uid number(6), p_order_quantity number(4), j_snack_uid number(6), j_order_quantity number(4), constraint snack_s_order_uid_pk primary key(s_order_uid), constraint snack_p_snack_uid_fk foreign key(p_snack_uid) references snack(snack_uid), constraint snack_j_snack_uid_fk foreign key( j_snack_uid) references snack(snack_uid) );
2-3. DB?? 18 /* ??? ??? ??*/ CREATE TABLE theater ( theater_UID NUMBER(6) NOT NULL, total_seat_number NUMBER(10), CONSTRAINT theater_theater_UID_pk PRIMARY KEY(theater_UID) ); /*?? ??? ??*/ CREATE TABLE seat ( theater_UID number(4) NOT NULL, seat_UID varchar2(10) NOT NULL, issue NUMBER(6) not null default 0, CONSTRAINT seat_pk PRIMARY KEY(seat_UID, theater_UID), constraint seat_theater_UID_fk foreign key(theater_UID) references theater(theater_UID) ); /*?? ??? ??*/ CREATE TABLE movie ( movie_UID NUMBER(6) NOT NULL, title VARCHAR2(20), CONSTRAINT movie_movie_UID_pk PRIMARY KEY(movie_UID) );
2-3. DB?? 19 /* ???? ??? ?? */ CREATE TABLE schedule ( schedule_UID NUMBER(6) NOT NULL, schedule_date DATE, movie_UID NUMBER(4), theater_UID NUMBER(4), CONSTRAINT schedule_schedule_UID_pk PRIMARY KEY(schedule_UID), CONSTRAINT schedule_movie_UID_fk FOREIGN KEY(movie_UID) REFERENCES movie(movie_UID), CONSTRAINT schedule_theater_UID_fk FOREIGN KEY(theater_UID) REFERENCES theater(theater_UID) ); /* ?? ??? ?? */ CREATE TABLE ticket ( ticket_UID NUMBER(6) NOT NULL, schedule_UID NUMBER(2), theater_UID NUMBER(4), seat_UID varchar2(10), price number(6), CONSTRAINT ticket_ticket_UID_pk PRIMARY KEY(ticket_UID), CONSTRAINT ticket_schedule_UID_fk FOREIGN KEY(schedule_UID) REFERENCES schedule(schedule_UID), constraint ticket_seat_fk foreign key(theater_UID, seat_UID) references seat(theater_UID, seat_UID) );
2-3. DB?? 20 /* ?? ?? ??? ?? */ create table reservation ( reservation_UID number(6) not null, ticket1_uid number(6), ticket2_uid number(6), ticket3_uid number(6), ticket4_uid number(7), constraint reservation_reservation_UID_pk primary key(reservation_UID), constraint reservation_ticket1_uid_fk foreign key(ticket1_uid) references ticket(ticket_uid), constraint reservation_ticket2_uid_fk foreign key(ticket2_uid) references ticket(ticket_uid), constraint reservation_ticket3_uid_fk foreign key(ticket3_uid) references ticket(ticket_uid), constraint reservation_ticket4_uid_fk foreign key(ticket4_uid) references ticket(ticket_uid) ); /* ?? ??? ?? */ create table payment ( payment_UID number(6) not null, total_price number(8), payment_option varchar2(20), payment_date date, customer_UID number(6), m_order_UID number(6), s_order_UID number(6), reservation_UID number(6), constraint payment_payment_UID_pk primary key(payment_UID), constraint payment_customer_uid_fk FOREIGN KEY(customer_UID)REFERENCES customer(customer_UID), CONSTRAINT payment_m_order_UID_fk FOREIGN KEY(m_order_UID)REFERENCES memento_order(m_order_UID), CONSTRAINT payment_s_order_UID_fk FOREIGN KEY(s_order_UID)REFERENCES snack_order(s_order_UID), CONSTRAINT payment_reservation_UID_fk FOREIGN KEY(reservation_UID)REFERENCES reservation(reservation_UID) );
3. ???? ¨C DB?? /* ?? (??) ?? */ insert into snack values (1, '??', '?????', 5000); insert into snack values (2, '??', '?????', 5000); insert into snack values (3, '??', '??????', 5000); insert into snack values (4, '??', '??????', 5000); insert into snack values (5, '??', '??', 3000); insert into snack values (6, '??', '???', 3000); insert into snack values (7, '??', '???', 3000); 21 /* ?? (??) ?? */ insert into movie values(1, 'interstellar'); insert into movie values(2, 'killyourdarlings'); insert into movie values(3, 'frank'); /* ??? (??) ?? */ insert into theater values(1, 69); insert into theater values(2, 69); insert into theater values(3, 69); /* ?? (??) ?? */ // ??? ?? ?? insert into seat values(1, 'A-1', 0); insert into seat values(1, 'A-2', 0); ...... insert into seat values(1, 'A-23', 0); ...... insert into seat values(3, 'C-23', 0); /* ??? (??) ?? */ insert into memento values (1, '??', 10000); insert into memento values (1, '??', 10000); insert into memento values (1, '???', 10000); /* ???? (??) ?? */ insert into schedule values(1, to_date(20141231, 'yyyy-mm-dd'), 1, 1); insert into schedule values(2, to_date(20140101, 'yyyy-mm-dd'), 2, 2); insert into schedule values(3, to_date(20140102, 'yyyy-mm-dd'), 3, 3);
3. ???? ¨C DB?? /* ?? (??) ?? */ insert into snack values (1, '??', '?????', 5000); insert into snack values (2, '??', '?????', 5000); insert into snack values (3, '??', '??????', 5000); insert into snack values (4, '??', '??????', 5000); insert into snack values (5, '??', '??', 3000); insert into snack values (6, '??', '???', 3000); insert into snack values (7, '??', '???', 3000); 22 /* ?? (??) ?? */ ???? ???? insert into movie values(1, 'interstellar'); insert into movie values(2, 'killyourdarlings'); insert into movie values(3, 'frank'); 1 interstellar 2 Killyourdarlings 3 frank /* ??? (??) ?? */ insert into theater values(1, 69); ????? ? ?? ? insert into theater values(2, 69); insert into theater values(3, 69); 1 69 2 69 3 69 /* ?? (??) ?? */ // ??? ?? ?? insert into seat values(1, 'A-1', 0); insert into seat values(1, 'A-2', 0); ...... insert into seat values(1, 'A-23', 0); ...... insert into seat values(3, 'C-23', 0); /* ??? (??) ?? */ insert into memento values (1, '??', 10000); insert into memento values (1, '??', 10000); insert into memento values (1, '???', 10000); ????? ???? ???? 1 A-1 0 2 A-2 0 3 A-3 0 ¡­. ¡­.. ¡­¡­ /* ???? (??) ?? */ insert ?????? into schedule ???? values(???? 1, to_date(????? 20141231, 'yyyy-mm-dd'), 1, 1); insert into schedule values(2, to_date(20140101, 'yyyy-mm-dd'), 2, 2); insert into schedule values(3, to_date(20140102, 'yyyy-mm-dd'), 3, 3); 1 20141231 1 1 2 20140101 2 2 ???? ?? ?? ?? 1 ?? ????? 5000 2 ?? ????? 5000 3 ?? ?????? 5000 4 ?? ?????? 5000 5 ?? ?? 3000 6 ?? ??? 3000 7 ?? ??? 3000 ????? ?? ?? 1 ?? 10000 2 ?? 10000 3 ??? 10000
2-3. DB?? 23 /* customer_UID? ?? sequence ?? */ create sequence customer_UID; /* ticket_UID? ?? sequence ?? */ create sequence ticket_UID; /* ?? ??! */ // ?? ??? ?? ?? try { String query = "select schedule_uid, seat.theater_uid, seat_uid from schedule, seat where schedule.theater_uid = seat.theater_uid"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()){ String inQuery = "insert into ticket values (ticket_uid.nextval, " + rs.getInt(1) + ", " + rs.getInt(2) + ", '" + rs.getString(3) + "', 8000)"; Statement inStmt = con.createStatement(); ResultSet inRs = inStmt.executeQuery(inQuery); } } catch(SQLException e) { e.printStackTrace(); } /* ??? ?? sequence ?? */ create sequence reservation_uid; /* ??? ?? sequence ?? */ create sequence payment_UID;
2-3. DB?? 24 /* customer_UID? ?? sequence ?? */ create sequence customer_UID; /* ticket_UID? ?? sequence ?? */ create sequence ticket_UID; /* ?? ??! */ // ?? ??? ?? ?? try { String query = "select schedule_uid, seat.theater_uid, seat_uid from schedule, seat where schedule.theater_uid = seat.theater_uid"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()){ String inQuery = "insert into ticket values (ticket_uid.nextval, " + rs.getInt(1) + ", " + rs.getInt(2) + ", '" + rs.getString(3) + "', 8000)"; Statement inStmt = con.createStatement(); ResultSet inRs = inStmt.executeQuery(inQuery); } } catch(SQLException e) { e.printStackTrace(); } /* ??? ?? sequence ?? */ create sequence reservation_uid; /* ??? ?? sequence ?? */ create sequence payment_UID; ???? ?????? ????? ???? ?? 1 1 1 A-1 8000
3. ???? ¨C DB?? 25 class MovieInfo { String title; String image; } DB? ??? ???? ?? ??! class SeatInfo { String seat_UID; int issue; } class MyPayment { int total_price; String option; java.sql.Date date; int my_UID; int reservation_UID; int m_order_uid; int s_order_uid; } class Customer { String id; String pw; String name; int tel; } class Selected { String movie_title; int theater; int num_of_person; String seat; int p_snack; int p_snack_quantity; int j_snack; int j_snack_quantity; int memento; int memento_quantity; String payment_option; }
public boolean ????????????(String ???) { try { String query = "select * from customer where customer_id='"+???+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } 3-2. ???? ¨C DB?? 26
3-2. ???? ¨C DB?? 27 public void signUp(String id, String pw, String name, String telStr) { StringTokenizer st = new StringTokenizer(telStr, "-"); String str = ""; while(st.hasMoreTokens()) { str += st.nextToken(); } int tel = Integer.parseInt(str); try { String query = "insert into customer values (customer_uid.nextval, '"+id+"', '"+pw+"', '"+name+"', "+tel+")"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
3-2. ???? ¨C DB?? 28 public boolean ?????????(String ???) { try { String query = "select * from customer where customer_id='"+???+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; }
3-2. ???? ¨C DB?? 29 public boolean ??????????(String ????) { try { String query = "select * from customer where customer_pw='"+????+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; }
3-2. ???? ¨C DB?? 30 /* ??? ?? ?? ?? ?? ?? ????! */ try { String query = "select customer_UID from customer where customer_id = '" + id +"'"; Statement statement = con.createStatement(); ResultSet rs = statement.executeQuery(query); while(rs.next()) { myPayment.my_UID = rs.getInt(1); } System.out.println("my_UID : " + myPayment.my_UID); } catch (SQLException e) { e.printStackTrace(); } /* ???? ????! */ try { String query = "select title from movie"; Statement statement = con.createStatement(); ResultSet rs = statement.executeQuery(query); while(rs.next()) { MovieInfo mi = new MovieInfo(); mi.title = rs.getString(1); mi.image = "./images/" + mi.title + ".png"; movieInfo.add(mi); } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); }
3-2. ???? ¨C DB?? 31 /* ????? ????! */ try { String query = "select theater_UID from schedule where movie_UID = (select movie_UID from movie where title = '" + selected.movie_title +"')"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()) { selected.theater = rs.getInt(1); } System.out.println( selected.movie_title + ", " + selected.theater); } catch (SQLException e1) { e1.printStackTrace(); }
3-2. ???? ¨C DB?? 32 /* ?? ??(?? ??) ????! */ try { String query = "select seat_UID, issue from seat where theater_UID = " + selected.theater; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); seatInfo = new SeatInfo[rs.getRow()]; rs.beforeFirst(); while(rs.next()){ char[] tmp_uid = rs.getString(1).toCharArray(); String tmpStr = ""; for (int i = 2; i < tmp_uid.length; i++) { tmpStr += tmp_uid[i]; } int seatInfoIndex = (int)((tmp_uid[0])-'A')*24 + Integer.parseInt(tmpStr); seatInfo[seatInfoIndex] = new SeatInfo(); seatInfo[seatInfoIndex].seat_UID = rs.getString(1); seatInfo[seatInfoIndex].issue = rs.getInt(2); } // ?? ??? ???? JCheckBox ??! for (int i = 0; i < seatInfo.length; i++) { if (seatInfo[i].issue == 1) { seat[(int)(i/24)][i%24].setEnabled(false); } } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); }
3-2. ???? ¨C DB?? 33 // ?? (???, ???) ????! try { String query = "select name, price from snack where type = '??'"; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIV E, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); listP = new String[rs.getRow() + 1]; popcornPrice = new int[rs.getRow() + 1]; rs.beforeFirst(); listP[0] = "??"; popcornPrice[0] = 0; int listIndex = 0; while(rs.next()) { listP[++listIndex] = rs.getString(1); popcornPrice[listIndex] = rs.getInt(2); } query = "select name, price from snack where type = '??'"; rs = stmt.executeQuery(query); rs.last(); listJ = new String[rs.getRow() + 1]; juicePrice = new int[rs.getRow() + 1]; rs.beforeFirst(); listJ[0] = "??"; juicePrice[0] = 0; listIndex = 0; while(rs.next()) { listJ[++listIndex] = rs.getString(1); juicePrice[listIndex] = rs.getInt(2); } } catch(SQLException e) { e.printStackTrace(); }
3-2. ???? ¨C DB?? 34 // ??? ????! try { String query = "select name from memento"; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); listS = new String[rs.getRow() + 1]; rs.beforeFirst(); listS[0] = "???"; int listIndex = 1; while(rs.next()) { listS[listIndex++] = rs.getString(1); } } catch(SQLException e) { e.printStackTrace(); }
3-2. ???? ¨C DB?? 35 if ((selected.p_snack >0 || selected.j_snack >0)) { String p_order = (selected.p_snack>0?""+selected.p_snack:"null") + ", null"; String j_order = (selected.j_snack>0?""+selected.j_snack:"null") + ", null"; // ?? ????? DB? ?? query = "insert into snack_order values ¡° + "(snack_order_uid.nextVal, " + p_order + ", " + j_order + ")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select s_order_uid from snack_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.s_order_uid = rs.getInt(1); } else { myPayment.s_order_uid = -1; }
3-2. ???? ¨C DB?? 36 if ((selected.p_snack >0 || selected.j_snack >0)) { String p_order = (selected.p_snack>0?""+selected.p_snack:"null") + ", null"; String j_order = (selected.j_snack>0?""+selected.j_snack:"null") + ", null"; // ?? ????? DB? ?? query = "insert into snack_order values ¡° + "(snack_order_uid.nextVal, " + p_order + ", " + j_order + ")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select s_order_uid from snack_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.s_order_uid = rs.getInt(1); } else { myPayment.s_order_uid = -1; } ???? ?????? ?????? ?????? ?????? 141201 001 2 002 3
4. ?? ?? 37 if (selected.memento >0) { // ??? ????? DB? ?? query = "insert into memento_order values " + "(memento_order_uid.nextVal, " + selected.memento + ", " + selected.memento_quantity +")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select m_order_uid from memento_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.m_order_uid = rs.getInt(1); } else { myPayment.m_order_uid = -1; }
4. ?? ?? 38 if (selected.memento >0) { // ??? ????? DB? ?? query = "insert into memento_order values " + "(memento_order_uid.nextVal, " + selected.memento + ", " + selected.memento_quantity +")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select m_order_uid from memento_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.m_order_uid = rs.getInt(1); } else { myPayment.m_order_uid = -1; } ???? ????? ??????? 241217 002 3
4. ?? ?? 39 // ??? ??? ????? ?? ?, ????? ????. int[] ticket_UID = new int[4]; for (int i = 0; i < selected_seat.length; i++) { System.out.println(selected.theater + ", " + selected_seat[i]); query = "update seat set issue = 1 where theater_UID = " + selected.theater + " and seat_UID = '" + selected_seat[i]+ "'"; rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select ticket_UID from ticket where theater_UID = " + selected.theater + " and seat_UID = '" + selected_seat[i] + "'"; rs = stmt.executeQuery(query); while(rs.next()) ticket_UID[i] = rs.getInt(1); }
4. ?? ?? 40 // ????? DB? ???? ????? ????. query = "insert into reservation values " + "(reservation_uid.nextval, " + ticket_UID[0] +", ¡° + (ticket_UID[1]>0?ticket_UID[1]:null) +", " + (ticket_UID[2]>0?ticket_UID[2]:null) +", ¡° + (ticket_UID[3]>0?ticket_UID[3]:null) +")"; rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select reservation_uid from reservation"; rs = stmt.executeQuery(query); rs.last(); myPayment.reservation_UID = rs.getInt(1);
4. ?? ?? 41 // ????? DB? ???? ????? ????. query = "insert into reservation values " + "(reservation_uid.nextval, " + ticket_UID[0] +", ¡° + (ticket_UID[1]>0?ticket_UID[1]:null) +", " + (ticket_UID[2]>0?ticket_UID[2]:null) +", ¡° + (ticket_UID[3]>0?ticket_UID[3]:null) +")"; ?? ?? ??1 ?? ??2 ?? ??3 ?? ??4 ?? 371217 1001 1010 1011 1100 rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select reservation_uid from reservation"; rs = stmt.executeQuery(query); rs.last(); myPayment.reservation_UID = rs.getInt(1);
4. ?? ?? 42 // ?? ?? ??? DB? ?? try { String query = "insert into payment values (payment_uid.nextVal, " + myPayment.total_price + ", '" + myPayment.option + "', to_date('" + today + "', 'MM-DD-YYYY'), " + myPayment.my_UID + ", " + (myPayment.m_order_uid>0?myPayment.m_order_uid:null) + ", " + (myPayment.s_order_uid>0?myPayment.s_order_uid:null) + ", " + myPayment.reservation_UID + ")"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); } catch(SQLException exception) { exception.printStackTrace(); }
4. ?? ?? 43 // ?? ?? ??? DB? ?? try { String query = "insert into payment values (payment_uid.nextVal, " + myPayment.total_price + ", '" + myPayment.option + "', to_date('" + today + "', 'MM-DD-YYYY'), " + myPayment.my_UID + ", " + (myPayment.m_order_uid>0?myPayment.m_order_uid:null) + ", " + (myPayment.s_order_uid>0?myPayment.s_order_uid:null) + ", " + myPayment.reservation_UID + ")"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); } catch(SQLException exception) { exception.printStackTrace(); } ???????? ?? ???? ???? ???? ???? ???? ???? ???? ?????????????? ???????????? ???????? 941214 27000 ?? 20141207 0 241217 141201 371217
4. ??? 44 ???? ??
5. ????? ??? ???? ? 45 1. ?? ?? ?, ???? ??? ???? ??? ? ¡ú Excel? ???? ??? ??! (?? ?? : here) 2. Oracle DB? JAVA?? ?? ?? ??? ????? ?? ???. ¡ú ??? ?? ???? ??! (NLS_CHARACTERSET? KO16KSC5601?? ??!) ¡ú ?? ?? ?? (alter session set nls_date_format='yyyymmdd hh24:mi:ss';)
6. ? ??? ???? 46 1. ??? : ???? ??, DB ??? ?? 2. ??? : DB ?? ?? 3. ??? : DB ?? ?? & ??? ?? 4. ??? : ???? ??, PPT ?? 5. ??? : DB??, ????-DB??, PPT ??
47

More Related Content

?? ?? ???? (DB ??, ???? ??)

  • 1. ?? ??¡¯?' ???? DB ?? 0901289 ??? 1101131 ??? 1101196 ??? 1301131 ??? 1301243 ??? 1
  • 2. ? ? 1. ???? ?? 2. DB ?? 3. ???? ¨C DB ?? 4. ??? 5. ????? ???? ? 6. ? ??? ???? 2
  • 3. 1. ???? ?? ??? - ?? ?? 3
  • 4. 3-1. ???? 4 ?? - ?????
  • 5. 3-1. ???? 5 ?? - ?????
  • 6. 3-1. ???? ?? ?? ¨C ??? ?? ? 6
  • 8. 2. DB ?? ???? ???? ?? ?? ??????. ????? ???? ?? ??? ??? ??? ??? ? ??. ???? ? ? ??? ??? ??. ???? ?? ??? ??? ??? ???. ?? ?? ?, ? ? ?? ?? ? (??? ??? ??)? ??? ??. ?? ?? ? ?? ??? ? ?? ??? ??? ? ??. ?? ??/?? ?? ??, ??(??? ??)? ???? ??? ? ??. ??? ? ??? ??. ??(??? ??)? ??? ??? ??? ? ??. ????? ??? ?? ??(3??)? ????. ?? ?? ??, ??? ??? ??? ?? ??? ????. /* ?? ?? */ ???? ? ? ???, ????, ??, ??????? ????. ??? ??? ?? 4?? ?? ??? ? ??. 8
  • 9. ???? ???? ?? ?? ??????. ????? ???? ?? ??? ??? ??? ??? ? ??. ???? ? ? ??? ??? ??. ???? ?? ??? ??? ??? ???. ?? ?? ?, ? ? ?? ?? ? (??? ??? ??)? ??? ??. ?? ?? ? ?? ??? ? ?? ??? ??? ? ??. ?? ??/?? ?? ??, ??(??? ??)? ???? ??? ? ??. ??? ? ??? ??. ??(??? ??)? ???? ??? ??? ??? ? ??. ????? ??? ?? ??(3??)? ????. ?? ?? ??, ??? ??? ??? ?? ??? ????. /* ?? ?? */ ???? ? ? ???, ????, ??, ??????? ????. ??? ??? ?? 4?? ?? ??? ? ??. 9
  • 10. ?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??, ?? ??, ???, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ?? ??, ?? ??? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 10
  • 11. ?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 11
  • 12. ?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ???? ??? ????? ??, ??? ??/?????? ?? ?? #????, ??????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ?, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 12
  • 13. ?? ? ?? # : primary key ??(??) #????, ???, ????, ??, ???? ?? #????, ????, ????, ? ??, ????, ????, ???? ???? #????, ????, ?? ??/??? ?? #????, ??? ??/??? ??, ?? ?? #????, ??????, ?????, ????, ?? ?? #????, ??, ??, ??, ????, ?¡­ ??? #?????, ? ?? ? ?? #?????, #????, ?? ?? ?? ???? #??????, ????, ????, ????? ?? #????, ??, ??, ?? ??? #?????, ??, ?? 13
  • 14. 14
  • 15. 2-3. DB?? 15 /* ?? ??? ?? */ CREATE TABLE customer ( customer_UID NUMBER(6) NOT NULL, customer_id VARCHAR2(30), customer_pw VARCHAR2(30), customer_name VARCHAR2(20), customer_tel NUMBER(12), CONSTRAINT customer_customer_UID_pk PRIMARY KEY(customer_UID) );
  • 16. 2-3. DB?? 16 /* ?? ??? ??*/ CREATE TABLE snack ( snack_UID NUMBER(6) NOT NULL, type varchar2(10), name VARCHAR2(20), price number(6), CONSTRAINT snack_snack_UID_pk PRIMARY KEY(snack_UID) ); /*??? ??? ?? */ CREATE TABLE memento ( memento_UID NUMBER(4) NOT NULL, name VARCHAR2(21), price number(6), CONSTRAINT memento_memento_UID_pk PRIMARY KEY(memento_UID) );
  • 17. 2-3. DB?? 17 /* ??? ?? ??? ?? */ create table memento_order ( m_order_uid number(6) not null, memento_UID number(6), order_quantity number(4), constraint memento_m_order_UID_pk primary key(m_order_uid), constraint memento_memento_uid_fk foreign key(memento_uid) references memento(memento_uid) ); /* ?? ?? ??? ?? */ create table snack_order ( s_order_uid number(6) not null, p_snack_uid number(6), p_order_quantity number(4), j_snack_uid number(6), j_order_quantity number(4), constraint snack_s_order_uid_pk primary key(s_order_uid), constraint snack_p_snack_uid_fk foreign key(p_snack_uid) references snack(snack_uid), constraint snack_j_snack_uid_fk foreign key( j_snack_uid) references snack(snack_uid) );
  • 18. 2-3. DB?? 18 /* ??? ??? ??*/ CREATE TABLE theater ( theater_UID NUMBER(6) NOT NULL, total_seat_number NUMBER(10), CONSTRAINT theater_theater_UID_pk PRIMARY KEY(theater_UID) ); /*?? ??? ??*/ CREATE TABLE seat ( theater_UID number(4) NOT NULL, seat_UID varchar2(10) NOT NULL, issue NUMBER(6) not null default 0, CONSTRAINT seat_pk PRIMARY KEY(seat_UID, theater_UID), constraint seat_theater_UID_fk foreign key(theater_UID) references theater(theater_UID) ); /*?? ??? ??*/ CREATE TABLE movie ( movie_UID NUMBER(6) NOT NULL, title VARCHAR2(20), CONSTRAINT movie_movie_UID_pk PRIMARY KEY(movie_UID) );
  • 19. 2-3. DB?? 19 /* ???? ??? ?? */ CREATE TABLE schedule ( schedule_UID NUMBER(6) NOT NULL, schedule_date DATE, movie_UID NUMBER(4), theater_UID NUMBER(4), CONSTRAINT schedule_schedule_UID_pk PRIMARY KEY(schedule_UID), CONSTRAINT schedule_movie_UID_fk FOREIGN KEY(movie_UID) REFERENCES movie(movie_UID), CONSTRAINT schedule_theater_UID_fk FOREIGN KEY(theater_UID) REFERENCES theater(theater_UID) ); /* ?? ??? ?? */ CREATE TABLE ticket ( ticket_UID NUMBER(6) NOT NULL, schedule_UID NUMBER(2), theater_UID NUMBER(4), seat_UID varchar2(10), price number(6), CONSTRAINT ticket_ticket_UID_pk PRIMARY KEY(ticket_UID), CONSTRAINT ticket_schedule_UID_fk FOREIGN KEY(schedule_UID) REFERENCES schedule(schedule_UID), constraint ticket_seat_fk foreign key(theater_UID, seat_UID) references seat(theater_UID, seat_UID) );
  • 20. 2-3. DB?? 20 /* ?? ?? ??? ?? */ create table reservation ( reservation_UID number(6) not null, ticket1_uid number(6), ticket2_uid number(6), ticket3_uid number(6), ticket4_uid number(7), constraint reservation_reservation_UID_pk primary key(reservation_UID), constraint reservation_ticket1_uid_fk foreign key(ticket1_uid) references ticket(ticket_uid), constraint reservation_ticket2_uid_fk foreign key(ticket2_uid) references ticket(ticket_uid), constraint reservation_ticket3_uid_fk foreign key(ticket3_uid) references ticket(ticket_uid), constraint reservation_ticket4_uid_fk foreign key(ticket4_uid) references ticket(ticket_uid) ); /* ?? ??? ?? */ create table payment ( payment_UID number(6) not null, total_price number(8), payment_option varchar2(20), payment_date date, customer_UID number(6), m_order_UID number(6), s_order_UID number(6), reservation_UID number(6), constraint payment_payment_UID_pk primary key(payment_UID), constraint payment_customer_uid_fk FOREIGN KEY(customer_UID)REFERENCES customer(customer_UID), CONSTRAINT payment_m_order_UID_fk FOREIGN KEY(m_order_UID)REFERENCES memento_order(m_order_UID), CONSTRAINT payment_s_order_UID_fk FOREIGN KEY(s_order_UID)REFERENCES snack_order(s_order_UID), CONSTRAINT payment_reservation_UID_fk FOREIGN KEY(reservation_UID)REFERENCES reservation(reservation_UID) );
  • 21. 3. ???? ¨C DB?? /* ?? (??) ?? */ insert into snack values (1, '??', '?????', 5000); insert into snack values (2, '??', '?????', 5000); insert into snack values (3, '??', '??????', 5000); insert into snack values (4, '??', '??????', 5000); insert into snack values (5, '??', '??', 3000); insert into snack values (6, '??', '???', 3000); insert into snack values (7, '??', '???', 3000); 21 /* ?? (??) ?? */ insert into movie values(1, 'interstellar'); insert into movie values(2, 'killyourdarlings'); insert into movie values(3, 'frank'); /* ??? (??) ?? */ insert into theater values(1, 69); insert into theater values(2, 69); insert into theater values(3, 69); /* ?? (??) ?? */ // ??? ?? ?? insert into seat values(1, 'A-1', 0); insert into seat values(1, 'A-2', 0); ...... insert into seat values(1, 'A-23', 0); ...... insert into seat values(3, 'C-23', 0); /* ??? (??) ?? */ insert into memento values (1, '??', 10000); insert into memento values (1, '??', 10000); insert into memento values (1, '???', 10000); /* ???? (??) ?? */ insert into schedule values(1, to_date(20141231, 'yyyy-mm-dd'), 1, 1); insert into schedule values(2, to_date(20140101, 'yyyy-mm-dd'), 2, 2); insert into schedule values(3, to_date(20140102, 'yyyy-mm-dd'), 3, 3);
  • 22. 3. ???? ¨C DB?? /* ?? (??) ?? */ insert into snack values (1, '??', '?????', 5000); insert into snack values (2, '??', '?????', 5000); insert into snack values (3, '??', '??????', 5000); insert into snack values (4, '??', '??????', 5000); insert into snack values (5, '??', '??', 3000); insert into snack values (6, '??', '???', 3000); insert into snack values (7, '??', '???', 3000); 22 /* ?? (??) ?? */ ???? ???? insert into movie values(1, 'interstellar'); insert into movie values(2, 'killyourdarlings'); insert into movie values(3, 'frank'); 1 interstellar 2 Killyourdarlings 3 frank /* ??? (??) ?? */ insert into theater values(1, 69); ????? ? ?? ? insert into theater values(2, 69); insert into theater values(3, 69); 1 69 2 69 3 69 /* ?? (??) ?? */ // ??? ?? ?? insert into seat values(1, 'A-1', 0); insert into seat values(1, 'A-2', 0); ...... insert into seat values(1, 'A-23', 0); ...... insert into seat values(3, 'C-23', 0); /* ??? (??) ?? */ insert into memento values (1, '??', 10000); insert into memento values (1, '??', 10000); insert into memento values (1, '???', 10000); ????? ???? ???? 1 A-1 0 2 A-2 0 3 A-3 0 ¡­. ¡­.. ¡­¡­ /* ???? (??) ?? */ insert ?????? into schedule ???? values(???? 1, to_date(????? 20141231, 'yyyy-mm-dd'), 1, 1); insert into schedule values(2, to_date(20140101, 'yyyy-mm-dd'), 2, 2); insert into schedule values(3, to_date(20140102, 'yyyy-mm-dd'), 3, 3); 1 20141231 1 1 2 20140101 2 2 ???? ?? ?? ?? 1 ?? ????? 5000 2 ?? ????? 5000 3 ?? ?????? 5000 4 ?? ?????? 5000 5 ?? ?? 3000 6 ?? ??? 3000 7 ?? ??? 3000 ????? ?? ?? 1 ?? 10000 2 ?? 10000 3 ??? 10000
  • 23. 2-3. DB?? 23 /* customer_UID? ?? sequence ?? */ create sequence customer_UID; /* ticket_UID? ?? sequence ?? */ create sequence ticket_UID; /* ?? ??! */ // ?? ??? ?? ?? try { String query = "select schedule_uid, seat.theater_uid, seat_uid from schedule, seat where schedule.theater_uid = seat.theater_uid"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()){ String inQuery = "insert into ticket values (ticket_uid.nextval, " + rs.getInt(1) + ", " + rs.getInt(2) + ", '" + rs.getString(3) + "', 8000)"; Statement inStmt = con.createStatement(); ResultSet inRs = inStmt.executeQuery(inQuery); } } catch(SQLException e) { e.printStackTrace(); } /* ??? ?? sequence ?? */ create sequence reservation_uid; /* ??? ?? sequence ?? */ create sequence payment_UID;
  • 24. 2-3. DB?? 24 /* customer_UID? ?? sequence ?? */ create sequence customer_UID; /* ticket_UID? ?? sequence ?? */ create sequence ticket_UID; /* ?? ??! */ // ?? ??? ?? ?? try { String query = "select schedule_uid, seat.theater_uid, seat_uid from schedule, seat where schedule.theater_uid = seat.theater_uid"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()){ String inQuery = "insert into ticket values (ticket_uid.nextval, " + rs.getInt(1) + ", " + rs.getInt(2) + ", '" + rs.getString(3) + "', 8000)"; Statement inStmt = con.createStatement(); ResultSet inRs = inStmt.executeQuery(inQuery); } } catch(SQLException e) { e.printStackTrace(); } /* ??? ?? sequence ?? */ create sequence reservation_uid; /* ??? ?? sequence ?? */ create sequence payment_UID; ???? ?????? ????? ???? ?? 1 1 1 A-1 8000
  • 25. 3. ???? ¨C DB?? 25 class MovieInfo { String title; String image; } DB? ??? ???? ?? ??! class SeatInfo { String seat_UID; int issue; } class MyPayment { int total_price; String option; java.sql.Date date; int my_UID; int reservation_UID; int m_order_uid; int s_order_uid; } class Customer { String id; String pw; String name; int tel; } class Selected { String movie_title; int theater; int num_of_person; String seat; int p_snack; int p_snack_quantity; int j_snack; int j_snack_quantity; int memento; int memento_quantity; String payment_option; }
  • 26. public boolean ????????????(String ???) { try { String query = "select * from customer where customer_id='"+???+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } 3-2. ???? ¨C DB?? 26
  • 27. 3-2. ???? ¨C DB?? 27 public void signUp(String id, String pw, String name, String telStr) { StringTokenizer st = new StringTokenizer(telStr, "-"); String str = ""; while(st.hasMoreTokens()) { str += st.nextToken(); } int tel = Integer.parseInt(str); try { String query = "insert into customer values (customer_uid.nextval, '"+id+"', '"+pw+"', '"+name+"', "+tel+")"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
  • 28. 3-2. ???? ¨C DB?? 28 public boolean ?????????(String ???) { try { String query = "select * from customer where customer_id='"+???+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; }
  • 29. 3-2. ???? ¨C DB?? 29 public boolean ??????????(String ????) { try { String query = "select * from customer where customer_pw='"+????+"'"; Statement statement = con.createStatement(); ResultSet resultSet = statement.executeQuery(query); if(resultSet.next()) { try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; }
  • 30. 3-2. ???? ¨C DB?? 30 /* ??? ?? ?? ?? ?? ?? ????! */ try { String query = "select customer_UID from customer where customer_id = '" + id +"'"; Statement statement = con.createStatement(); ResultSet rs = statement.executeQuery(query); while(rs.next()) { myPayment.my_UID = rs.getInt(1); } System.out.println("my_UID : " + myPayment.my_UID); } catch (SQLException e) { e.printStackTrace(); } /* ???? ????! */ try { String query = "select title from movie"; Statement statement = con.createStatement(); ResultSet rs = statement.executeQuery(query); while(rs.next()) { MovieInfo mi = new MovieInfo(); mi.title = rs.getString(1); mi.image = "./images/" + mi.title + ".png"; movieInfo.add(mi); } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); }
  • 31. 3-2. ???? ¨C DB?? 31 /* ????? ????! */ try { String query = "select theater_UID from schedule where movie_UID = (select movie_UID from movie where title = '" + selected.movie_title +"')"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()) { selected.theater = rs.getInt(1); } System.out.println( selected.movie_title + ", " + selected.theater); } catch (SQLException e1) { e1.printStackTrace(); }
  • 32. 3-2. ???? ¨C DB?? 32 /* ?? ??(?? ??) ????! */ try { String query = "select seat_UID, issue from seat where theater_UID = " + selected.theater; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); seatInfo = new SeatInfo[rs.getRow()]; rs.beforeFirst(); while(rs.next()){ char[] tmp_uid = rs.getString(1).toCharArray(); String tmpStr = ""; for (int i = 2; i < tmp_uid.length; i++) { tmpStr += tmp_uid[i]; } int seatInfoIndex = (int)((tmp_uid[0])-'A')*24 + Integer.parseInt(tmpStr); seatInfo[seatInfoIndex] = new SeatInfo(); seatInfo[seatInfoIndex].seat_UID = rs.getString(1); seatInfo[seatInfoIndex].issue = rs.getInt(2); } // ?? ??? ???? JCheckBox ??! for (int i = 0; i < seatInfo.length; i++) { if (seatInfo[i].issue == 1) { seat[(int)(i/24)][i%24].setEnabled(false); } } } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); }
  • 33. 3-2. ???? ¨C DB?? 33 // ?? (???, ???) ????! try { String query = "select name, price from snack where type = '??'"; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIV E, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); listP = new String[rs.getRow() + 1]; popcornPrice = new int[rs.getRow() + 1]; rs.beforeFirst(); listP[0] = "??"; popcornPrice[0] = 0; int listIndex = 0; while(rs.next()) { listP[++listIndex] = rs.getString(1); popcornPrice[listIndex] = rs.getInt(2); } query = "select name, price from snack where type = '??'"; rs = stmt.executeQuery(query); rs.last(); listJ = new String[rs.getRow() + 1]; juicePrice = new int[rs.getRow() + 1]; rs.beforeFirst(); listJ[0] = "??"; juicePrice[0] = 0; listIndex = 0; while(rs.next()) { listJ[++listIndex] = rs.getString(1); juicePrice[listIndex] = rs.getInt(2); } } catch(SQLException e) { e.printStackTrace(); }
  • 34. 3-2. ???? ¨C DB?? 34 // ??? ????! try { String query = "select name from memento"; Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(query); rs.last(); listS = new String[rs.getRow() + 1]; rs.beforeFirst(); listS[0] = "???"; int listIndex = 1; while(rs.next()) { listS[listIndex++] = rs.getString(1); } } catch(SQLException e) { e.printStackTrace(); }
  • 35. 3-2. ???? ¨C DB?? 35 if ((selected.p_snack >0 || selected.j_snack >0)) { String p_order = (selected.p_snack>0?""+selected.p_snack:"null") + ", null"; String j_order = (selected.j_snack>0?""+selected.j_snack:"null") + ", null"; // ?? ????? DB? ?? query = "insert into snack_order values ¡° + "(snack_order_uid.nextVal, " + p_order + ", " + j_order + ")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select s_order_uid from snack_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.s_order_uid = rs.getInt(1); } else { myPayment.s_order_uid = -1; }
  • 36. 3-2. ???? ¨C DB?? 36 if ((selected.p_snack >0 || selected.j_snack >0)) { String p_order = (selected.p_snack>0?""+selected.p_snack:"null") + ", null"; String j_order = (selected.j_snack>0?""+selected.j_snack:"null") + ", null"; // ?? ????? DB? ?? query = "insert into snack_order values ¡° + "(snack_order_uid.nextVal, " + p_order + ", " + j_order + ")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select s_order_uid from snack_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.s_order_uid = rs.getInt(1); } else { myPayment.s_order_uid = -1; } ???? ?????? ?????? ?????? ?????? 141201 001 2 002 3
  • 37. 4. ?? ?? 37 if (selected.memento >0) { // ??? ????? DB? ?? query = "insert into memento_order values " + "(memento_order_uid.nextVal, " + selected.memento + ", " + selected.memento_quantity +")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select m_order_uid from memento_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.m_order_uid = rs.getInt(1); } else { myPayment.m_order_uid = -1; }
  • 38. 4. ?? ?? 38 if (selected.memento >0) { // ??? ????? DB? ?? query = "insert into memento_order values " + "(memento_order_uid.nextVal, " + selected.memento + ", " + selected.memento_quantity +")"; rs = stmt.executeQuery(query); // ???? ?? ????! query = "select m_order_uid from memento_order"; rs = stmt.executeQuery(query); rs.last(); myPayment.m_order_uid = rs.getInt(1); } else { myPayment.m_order_uid = -1; } ???? ????? ??????? 241217 002 3
  • 39. 4. ?? ?? 39 // ??? ??? ????? ?? ?, ????? ????. int[] ticket_UID = new int[4]; for (int i = 0; i < selected_seat.length; i++) { System.out.println(selected.theater + ", " + selected_seat[i]); query = "update seat set issue = 1 where theater_UID = " + selected.theater + " and seat_UID = '" + selected_seat[i]+ "'"; rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select ticket_UID from ticket where theater_UID = " + selected.theater + " and seat_UID = '" + selected_seat[i] + "'"; rs = stmt.executeQuery(query); while(rs.next()) ticket_UID[i] = rs.getInt(1); }
  • 40. 4. ?? ?? 40 // ????? DB? ???? ????? ????. query = "insert into reservation values " + "(reservation_uid.nextval, " + ticket_UID[0] +", ¡° + (ticket_UID[1]>0?ticket_UID[1]:null) +", " + (ticket_UID[2]>0?ticket_UID[2]:null) +", ¡° + (ticket_UID[3]>0?ticket_UID[3]:null) +")"; rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select reservation_uid from reservation"; rs = stmt.executeQuery(query); rs.last(); myPayment.reservation_UID = rs.getInt(1);
  • 41. 4. ?? ?? 41 // ????? DB? ???? ????? ????. query = "insert into reservation values " + "(reservation_uid.nextval, " + ticket_UID[0] +", ¡° + (ticket_UID[1]>0?ticket_UID[1]:null) +", " + (ticket_UID[2]>0?ticket_UID[2]:null) +", ¡° + (ticket_UID[3]>0?ticket_UID[3]:null) +")"; ?? ?? ??1 ?? ??2 ?? ??3 ?? ??4 ?? 371217 1001 1010 1011 1100 rs = stmt.executeQuery(query); /* ???? ???. */ query = "commit"; rs = stmt.executeQuery(query); query = "select reservation_uid from reservation"; rs = stmt.executeQuery(query); rs.last(); myPayment.reservation_UID = rs.getInt(1);
  • 42. 4. ?? ?? 42 // ?? ?? ??? DB? ?? try { String query = "insert into payment values (payment_uid.nextVal, " + myPayment.total_price + ", '" + myPayment.option + "', to_date('" + today + "', 'MM-DD-YYYY'), " + myPayment.my_UID + ", " + (myPayment.m_order_uid>0?myPayment.m_order_uid:null) + ", " + (myPayment.s_order_uid>0?myPayment.s_order_uid:null) + ", " + myPayment.reservation_UID + ")"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); } catch(SQLException exception) { exception.printStackTrace(); }
  • 43. 4. ?? ?? 43 // ?? ?? ??? DB? ?? try { String query = "insert into payment values (payment_uid.nextVal, " + myPayment.total_price + ", '" + myPayment.option + "', to_date('" + today + "', 'MM-DD-YYYY'), " + myPayment.my_UID + ", " + (myPayment.m_order_uid>0?myPayment.m_order_uid:null) + ", " + (myPayment.s_order_uid>0?myPayment.s_order_uid:null) + ", " + myPayment.reservation_UID + ")"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); } catch(SQLException exception) { exception.printStackTrace(); } ???????? ?? ???? ???? ???? ???? ???? ???? ???? ?????????????? ???????????? ???????? 941214 27000 ?? 20141207 0 241217 141201 371217
  • 44. 4. ??? 44 ???? ??
  • 45. 5. ????? ??? ???? ? 45 1. ?? ?? ?, ???? ??? ???? ??? ? ¡ú Excel? ???? ??? ??! (?? ?? : here) 2. Oracle DB? JAVA?? ?? ?? ??? ????? ?? ???. ¡ú ??? ?? ???? ??! (NLS_CHARACTERSET? KO16KSC5601?? ??!) ¡ú ?? ?? ?? (alter session set nls_date_format='yyyymmdd hh24:mi:ss';)
  • 46. 6. ? ??? ???? 46 1. ??? : ???? ??, DB ??? ?? 2. ??? : DB ?? ?? 3. ??? : DB ?? ?? & ??? ?? 4. ??? : ???? ??, PPT ?? 5. ??? : DB??, ????-DB??, PPT ??
  • 47. 47