[MySQL] JDBC

 

1. booklist, memberlist, rentlist 테이블 생성

-아래 파일을 오라클에서 실행해 테이블 생성

create_table.sql

0.00MB

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문