-
[MySQL] JDBCDataBase/MySQL 2022. 9. 9. 16:37
1. booklist, memberlist, rentlist 테이블 생성
-아래 파일을 오라클에서 실행해 테이블 생성
2. rentdetail view 생성(sql 파일)
create view rentdetail as select date_format(a.rentdate,'%y-%m-%d') as rd, a.num as rentnum, c.name as mname, b.subject, b.rentprice, a.discount, (b.rentprice-a.discount) as rentprice2 from rentlist a, booklist b, memberlist c where b.num=a.bnum and c.num=a.mnum order by rentnum desc;
3. DBManager
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBManager { private static String driver="com.mysql.cj.jdbc.Driver"; private static String url="jdbc:mysql://localhost:3306/scott"; private static String id="root"; private static String pw="adminuser"; public static Connection getConnection() { Connection con = null; try { Class.forName(driver); con = DriverManager.getConnection(url, id, pw); } catch (ClassNotFoundException e) {e.printStackTrace(); } catch (SQLException e) {e.printStackTrace();} return con; } public static void close( Connection con, PreparedStatement pstmt, ResultSet rs ) { try { if(con!=null) con.close(); if(pstmt!=null) pstmt.close(); if(rs!=null) rs.close(); } catch (SQLException e) { e.printStackTrace();} } }
4. DTO
public class RentDto { private String rentdate; private int num; private int bnum; private int mnum; private int discount; public String getRentdate() { return rentdate; } public void setRentdate(String rentdate) { this.rentdate = rentdate; } public int getNum() { return num; } public void setNum(int num) { this.num = num; } public int getBnum() { return bnum; } public void setBnum(int bnum) { this.bnum = bnum; } public int getMnum() { return mnum; } public void setMnum(int mnum) { this.mnum = mnum; } public int getDiscount() { return discount; } public void setDiscount(int discount) { this.discount = discount; } } public class RentDetailDto { private String rentdate; private int num; private int mnum; private String mname; private int bnum; private String subject; private int rentprice; private int discount; private int rentprice2; public String getRentdate() { return rentdate; } public void setRentdate(String rentdate) { this.rentdate = rentdate; } public int getNum() { return num; } public void setNum(int num) { this.num = num; } public int getMnum() { return mnum; } public void setMnum(int mnum) { this.mnum = mnum; } public String getMname() { return mname; } public void setMname(String mname) { this.mname = mname; } public int getBnum() { return bnum; } public void setBnum(int bnum) { this.bnum = bnum; } public String getSubject() { return subject; } public void setSubject(String subject) { this.subject = subject; } public int getRentprice() { return rentprice; } public void setRentprice(int rentprice) { this.rentprice = rentprice; } public int getDiscount() { return discount; } public void setDiscount(int discount) { this.discount = discount; } public int getRentprice2() { return rentprice2; } public void setRentprice2(int rentprice2) { this.rentprice2 = rentprice2; } }
5. DTO
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; public class RentDao { private RentDao() {} private static RentDao itc=new RentDao(); public static RentDao getInstance() {return itc;} Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; // select public ArrayList<RentDetailDto> selectAll() { ArrayList<RentDetailDto> list= new ArrayList<RentDetailDto>(); String sql="select *from rentdetail"; con=DBManager.getConnection(); try { pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()) { RentDetailDto rdto = new RentDetailDto(); rdto.setRentdate( rs.getString("rd") ); rdto.setNum( rs.getInt("rentnum")); rdto.setMname( rs.getString("mname") ); rdto.setSubject( rs.getString("subject") ); rdto.setRentprice( rs.getInt("rentprice") ); rdto.setDiscount( rs.getInt("discount") ); rdto.setRentprice2( rs.getInt("rentprice2") ); list.add(rdto); } } catch (SQLException e) {e.printStackTrace(); } finally {DBManager.close(con,pstmt,rs);} return list; } // insert public boolean checkBookNum(int bnum) { boolean result = false; String sql = "select * from booklist where num=?"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setInt(1, bnum); rs = pstmt.executeQuery(); if( rs.next() ) result = true; } catch (SQLException e) { } finally { DBManager.close(con, pstmt, rs); } return result; } public boolean checkMemberNum(int mnum) { boolean result = false; String sql = "select * from memberlist where num=?"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setInt(1, mnum); rs = pstmt.executeQuery(); if( rs.next() ) result = true; } catch (SQLException e) { } finally { DBManager.close(con, pstmt, rs); } return result; } public int insertRent(RentDto rdto) { int result = 0; String sql = "insert into rentlist( bnum, mnum, discount ) values( ?, ?, ?)"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setInt(1, rdto.getBnum() ); pstmt.setInt(2, rdto.getMnum() ); pstmt.setInt(3, rdto.getDiscount() ); result = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally {DBManager.close( con, pstmt, rs ); } return result; } // update public RentDetailDto getRentDetail(int num) { RentDetailDto rdto = null; String sql = "select * from rentdetail where rentnum = ?"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setInt(1, num); rs = pstmt.executeQuery(); if( rs.next() ) { rdto = new RentDetailDto(); rdto.setRentdate( rs.getString("rd") ); rdto.setNum( rs.getInt("rentnum") ); rdto.setSubject( rs.getString("subject") ); rdto.setMname( rs.getString("mname") ); rdto.setRentprice( rs.getInt("rentprice") ); rdto.setDiscount( rs.getInt("discount") ); rdto.setRentprice2( rs.getInt("rentprice2") ); } } catch (SQLException e) {e.printStackTrace(); } finally { DBManager.close(con, pstmt, rs); } return rdto; } public RentDto getRent(int num) { RentDto rdto = null; String sql = "select date_format(rentdate, '%Y-%m-%d') as rd," + " num, bnum, mnum, discount " + " from rentlist where num = ?"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setInt(1, num); rs = pstmt.executeQuery(); if( rs.next() ) { rdto = new RentDto(); rdto.setRentdate( rs.getString("rd") ); rdto.setNum( rs.getInt("num") ); rdto.setBnum( rs.getInt("bnum") ); rdto.setMnum( rs.getInt("mnum") ); rdto.setDiscount( rs.getInt("discount") ); } } catch (SQLException e) {e.printStackTrace(); } finally { DBManager.close(con, pstmt, rs); } return rdto; } public int updateRent(RentDto rdto) { int result = 0; String sql = "update rentlist set rentdate = " + " str_to_date(concat('',?,''), '%Y-%m-%d') , " + " bnum =?, mnum=?, discount=? where num=?"; con = DBManager.getConnection(); try { pstmt = con.prepareStatement(sql); pstmt.setString( 1, rdto.getRentdate() ); pstmt.setInt(2, rdto.getBnum() ); pstmt.setInt(3, rdto.getMnum() ); pstmt.setInt(4, rdto.getDiscount() ); pstmt.setInt( 5, rdto.getNum() ); result = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { DBManager.close( con, pstmt, rs ); } return result; } // delete public int deleteRent(int num) { int result = 0; con = DBManager.getConnection(); String sql = "delete from rentlist where num=?"; try { pstmt= con.prepareStatement(sql); pstmt.setInt(1, num); result = pstmt.executeUpdate(); } catch (SQLException e) {e.printStackTrace(); } finally {DBManager.close( con, pstmt, rs ); } return result; } }
6. Main
import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Scanner; public class RentMain { public static void main(String[] args) { Scanner sc=new Scanner(System.in); while(true) { System.out.println("\n---메뉴 선택---"); System.out.printf("1. 데이터 열람 2. 데이터 추가 "); System.out.printf("3. 데이터 수정 4. 데이터 삭제 "); System.out.printf("5. 프로그램 종료. >> 메뉴선택: "); String choice=sc.nextLine(); if(choice.equals("5")) break; switch(choice) { case "1": select(); break; case "2": insert(sc); break; case "3": update(sc); break; case "4": delete(sc); break; default: System.out.println("메뉴 선택이 잘못되었음"); } } System.out.println("프로그램 종료"); } private static void delete(Scanner sc) { RentDao rdao=RentDao.getInstance(); RentDto rdto=null; System.out.printf("삭제할 대여건의 number을 입력: "); String input; while(true) { input=sc.nextLine(); if(input.equals("")) System.out.println("number 입력 필수"); else break; } int num=Integer.parseInt(input); rdto=rdao.getRent(num); if(rdto==null) { System.out.println("입력한 number의 대여건 없음"); return; } rdao.deleteRent(num); } private static void update(Scanner sc) { RentDao rdao=RentDao.getInstance(); System.out.printf("수정할 대여변호: "); int num=0; String input=""; RentDetailDto rddto=null; RentDto rdto=null; while(true) { input=sc.nextLine(); if(input.equals("")) { System.out.printf("수정할 대여번호: "); continue; } num=Integer.parseInt(input); rddto=rdao.getRentDetail(num); rdto=rdao.getRent(num); if(rddto==null) System.out.println("입력한 number의 대여건이 없음. 다시 입력: "); else break; } SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); java.util.Date d=null; while(true) { System.out.printf("기존 날짜: %s\t수정할 날짜 입력(yyyy-MM-dd) ->",rddto.getRentdate()); input=sc.nextLine(); if(input.equals("")) break; try { d=sdf.parse(input); // java.sql.Date s=new java.sql.Date(d.getTime()); // rdto.setRentdate(s); rdto.setRentdate(input); break; } catch (ParseException e) {e.printStackTrace(); System.out.println("날짜를 입력양식에 맞춰서 다시 입력(yyyy-MM-dd): "); } } int bnum=0; System.out.printf("기존도서제목: %s\t수정할 도서번호 입력 -> ",rddto.getSubject()); while(true) { input=sc.nextLine(); if(input.equals("")) break; bnum=Integer.parseInt(input); boolean state=rdao.checkBookNum(bnum); if(state) { rdto.setBnum(bnum); break; } else System.out.printf("입력한 도서번호의 도서가 존재하지 않음. 다시 입력: "); } int mnum=0; System.out.printf("회원이름: %s\t수정할 회원 이름 -> ",rddto.getMname()); while(true) { input=sc.nextLine(); if(input.equals("")) break; mnum=Integer.parseInt(input); boolean state=rdao.checkBookNum(mnum); if(state) { rdto.setMnum(mnum); break; } else System.out.printf("입력한 회원번호의 도서가 존재하지 않음. 다시 입력: "); } System.out.printf("할인금액: "); String discount=sc.nextLine(); if(!discount.equals("")) rdto.setDiscount(Integer.parseInt(discount)); int result=rdao.updateRent(rdto); if(result==1) System.out.println("레코드 수정 성공"); else System.out.println("레코드 수정 실패"); } private static void insert(Scanner sc) { RentDao rdao=RentDao.getInstance(); RentDto rdto=new RentDto(); int bnum=0; String input=""; System.out.printf("대여할 도서번호: "); while(true) { input=sc.nextLine(); if(input.equals("")) { System.out.println("대여할 도서번호: "); continue; } bnum=Integer.parseInt(input); boolean state=rdao.checkBookNum(bnum); if(state) break; else System.out.printf("도서가 존재하지 않음. 다시 입력: "); } System.out.printf("대여 회원번호: "); int mnum=0; while(true) { input=sc.nextLine(); if(input.equals("")) { System.out.println("대여 회원번호: "); continue; } mnum=Integer.parseInt(input); boolean state=rdao.checkMemberNum(mnum); if(state) break; else System.out.printf("회원의 대여도서가 존재하지 않음. 다시 입력: "); } rdto.setBnum(bnum); rdto.setMnum(mnum); System.out.printf("할인금액: "); input=sc.nextLine(); int discount=0; if(!input.equals("")) discount=Integer.parseInt(input); rdto.setDiscount(discount); int result=rdao.insertRent(rdto); if(result==1) System.out.println("레코드 추가 성공"); else System.out.println("레코드 추가 실패"); } private static void select() { RentDao rdao=RentDao.getInstance(); ArrayList<RentDetailDto> list = rdao.selectAll(); System.out.printf("날짜\t\t순번\t\t회원이름\t대여금액"); System.out.println("\t할인금액\t실제대여료\t도서제목"); System.out.println("---------------------------------" + "---------------------------------------------------"); for(RentDetailDto rdto: list) { System.out.printf("%s\t%d\t\t%s\t\t%d\t\t%d\t\t%d\t\t%s\n", rdto.getRentdate(), rdto.getNum(), rdto.getMname(), rdto.getRentprice(), rdto.getDiscount(), rdto.getRentprice2(), rdto.getSubject()); } } }
실행결과
select문 insert문 update문 delete문 'DataBase > MySQL' 카테고리의 다른 글
[MySQL] command, limit, offset (0) 2022.09.09 [MySQL] Function (0) 2022.09.09 [MySQL] Table 생성, 제약조건, 컬럼 추가/수정, 외래키 설정, insert (0) 2022.09.09 [MySQL] 설치 (1) 2022.09.09