[mini project] 8. 도서관 관리 시스템 - 대여 테이블

1. BookRentVo

package BookRent;

import java.sql.Date;

public class BookRentVo {
   private int num;
   private String id;
   private int booknum;
   private Date rent_date;
   private Date return_date;
   private int  연체일;
   
   
   public BookRentVo(int num, String id, int booknum, Date rent_date, Date return_date) {
      this.num = num;
      this.id = id;
      this.booknum = booknum;
      this.rent_date = rent_date;
      this.return_date = return_date;
   }
   
    public BookRentVo(int num, String id, int booknum, int 연체일) {
      this.num = num;
      this.id = id;
      this.booknum = booknum;
      this.연체일 = 연체일;
   }

   public BookRentVo() {}

    public int getNum() {
      return num;
   }

   public void setNum(int num) {
      this.num = num;
   }

   public String getId() {
      return id;
   }

   public void setId(String id) {
      this.id = id;
   }

   public int getBooknum() {
      return booknum;
   }

   public void setBooknum(int booknum) {
      this.booknum = booknum;
   }

   public Date getRent_date() {
      return rent_date;
   }

   public void setRent_date(Date rent_date) {
      this.rent_date = rent_date;
   }

   public int get연체일() {
      return 연체일;
   }

   public void set연체일(int 연체일) {
      this.연체일 = 연체일;
   }

   public Date getReturn_date() {
      return return_date;
   }

   public void setReturn_date(Date return_date) {
      this.return_date = return_date;
   }
   
   
   @Override
   public String toString() {
      return "BookRentVo [번호 = " + num + ", 아이디 = " + id + ", 책 번호 = " + booknum + ", 대여일 = " + rent_date
            + ", 반납일 = " + return_date + "]";
   }
   
   
}

 

2. BookRentDao

package BookRent;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import conn.DBConnect;
import member.MemberService;

public class BookRentDao {
	private DBConnect dbconn;
	private MemberService ms;

	public BookRentDao() {
		dbconn = DBConnect.getInstance();

	}

	public void B_rent(int i) {

		Connection conn = dbconn.conn();

		String sql = "insert into BookRent values (seq_BookRent.nextval, ?, ?, sysdate, null)";
		String sql2 = "update Book set stock_b = 0 where booknum = ?";

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

			pstmt.setString(1, MemberService.Loginid);
			pstmt.setInt(2, i);

			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setInt(1, i);

			pstmt.executeQuery();
			pstmt2.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void B_return(int i) { // service에서 videoRentVo(로그인자가 빌린도서를 보여주고 반납할 videonum을 입력
		Connection conn = dbconn.conn(); // 입력받은 videonum을 videoRentVo에서 찾아서 vo반환.

		String sql = "update BookRent set return_date = sysdate where booknum = ?";
		String sql2 = "update Book set stock_b = 1 where Booknum = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i);

			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setInt(1, i);

			pstmt.executeQuery();
			pstmt2.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void SearchRDByBN(int booknum) {
		BookRentVo vo = null;
		Connection conn = dbconn.conn();
		String sql = "select return_date from BookRent where booknum = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, booknum);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				vo = new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ArrayList<BookRentVo> selectID(String id) {
		ArrayList<BookRentVo> list = new ArrayList<BookRentVo>();
		Connection conn = dbconn.conn();
		String sql = "select * from BookRent where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	
	public ArrayList<BookRentVo> selectAll() {
		ArrayList<BookRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from BookRent order by num desc";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<BookRentVo> selectByReturnOver() {
		ArrayList<BookRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select num,id,booknum,trunc(sysdate - (rent_date +14)) from bookrent where (rent_date +14) < sysdate and return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<BookRentVo> selectByReturn() {
		ArrayList<BookRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select  * from BookRent where return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	// 회원 로그인 후 자기가 빌린 책 목록 보는거
	public ArrayList<BookRentVo> selectById() {
		ArrayList<BookRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from BookRent where id  = ? and return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, MemberService.Loginid);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new BookRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	
}

 

3. BookRentService

package BookRent;

import java.util.ArrayList;
import java.util.Scanner;

import VideoRent.VideoRentVo;
import book.BookDao;
import book.BookVo;
import member.MemberService;

import video.VideoVo;

public class BookRentService {
	private BookRentDao dao;

	public BookRentService() {
		dao = new BookRentDao();
	}

	public void BookRent(Scanner sc) {
		System.out.println("< 책 대여 >");
		BookVo vo1 = new BookVo();
		BookDao dao1 = new BookDao();
		boolean flag = true;
		int booknum = 0;
		
		while (flag) {
			System.out.print("책 번호 : ");
			booknum = sc.nextInt();
			vo1 = dao1.selectByBookNum(booknum);
			if (vo1 == null) {
				System.out.println("없는 책번호");
			} else {
				if (vo1.getStock_b() == 1) {

					dao.B_rent(booknum);
					flag = false;
				} else if (vo1.getStock_b() == 0) {
					System.out.println("재고가 없습니다");
				}
			}
		}
		System.out.println(booknum + "번 책이 대여 되었습니다.");
	}

	public void BookReturn(Scanner sc) {
		System.out.println("< 책 반납 >");
		BookRentVo vo = new BookRentVo();
		BookVo vo1 = new BookVo();
		BookDao dao1 = new BookDao();
		BookVo bv = new BookVo();
		boolean flag = true;
		int booknum = 0;
		
		while (flag) {
			System.out.print("책 번호 : ");
			booknum = sc.nextInt();
			vo1 = dao1.selectByBookNum(booknum);
			if (vo1 == null) {
				System.out.println("없는 책번호");
			} else {
				if (vo1.getStock_b() == 1) {
					System.out.println("반납할 책이 아닙니다");
				} else if (vo1.getStock_b() == 0) {
					System.out.println(booknum + "번 책이 반납됐습니다");
					dao.B_return(booknum);
					flag = false;
				}
			}
		}
	}

	public void selectId(Scanner sc) {
		System.out.println("< 이름으로 자료 찾기 >");
		System.out.print("이름 : ");
		String id = sc.next();
		ArrayList<BookRentVo> list = dao.selectID(id);
		for (BookRentVo vo : list) {
			System.out.println(vo);
		}
	}

	public ArrayList<BookRentVo> selectIdforUser() {
		MemberService ps = new MemberService();
		BookRentDao bd = new BookRentDao();
		ArrayList<BookRentVo> list = new ArrayList();

		if (ps.Loginid.equals(bd.selectID(ps.Loginid))) {
			System.out.println(list);
		} else {
			System.out.println("아이디에 해당 하는 자료가 없습니다");
		}

		return list;
	}

	public void getAll() {
		System.out.println("전체 책 대여기록");
		ArrayList<BookRentVo> list = dao.selectAll();
		for (BookRentVo vo : list) {
			if (vo != null) {
			System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getBooknum() + "\t" + vo.getRent_date()
					+ "\t" + vo.getReturn_date());
		} else {
			System.out.println("책 대여 기록이 없습니다");
		}
		}
	}

	public void selectByRent() {
		System.out.println("미반납 현황");
		ArrayList<BookRentVo> list = dao.selectByReturn();
		for (BookRentVo vo : list) {
			System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getBooknum() + "\t" + vo.getRent_date()
					+ "\t" + vo.getReturn_date());
		}
	}

	public void selectById() {
		System.out.println("책 대여목록");
		ArrayList<BookRentVo> list = dao.selectById();
		
		if (list.isEmpty()) {
			System.out.println("책 대여목록이 없습니다");
		} else {
			System.out.println("번호" + "\t" + "아아디" + "\t" + "책번호" + "\t" + "대여일" + "\t" + "반납일");
		for (BookRentVo vo : list) {
			System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getBooknum() + "\t" + vo.getRent_date()
					+ "\t" + vo.getReturn_date());
		}
		}
	}

	public void selectByReturnOver() {
	      System.out.println("연체 현황");

	      ArrayList<BookRentVo> list = dao.selectByReturnOver();

	      if (list.isEmpty()) {
	         System.out.println("연체자가 없습니다");
	      } else {
	         System.out.println("번호" + "\t" + "책제목" + "\t" + "책번호" + "\t" + "연체일");
	         for (BookRentVo vo : list) {
	            System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getBooknum() + "\t" + vo.get연체일());
	         }
	     }
	}

	
}

 

많은 것을 공부하게 해 준 RentSystem 등장. 

이렇게 vo 클래스에서 생성자를 꼭 다 안쓰고 만들어 줘도 된다는 것을 이번에 알았다. 이렇게 쓰니까 필요한 것만 쏙쏙 뽑아서 사용 할 수 있어서 너무 편리했다. 

 

그리고 감동실화 받은 sql 문 두개 작성.. 와 이것도 진짜 편리했다. 나는 이거 sql1 로 메서드 하나, sql2 로 메서드 하나 해서 둘이 붙여야 겠다 생각하고 있었는데 옆에 똑똑이 조원이 이렇게 딱! 정리를 해서 알려주는데 왜 이걸 생각 못했을까 생각도 들고 이래서 코딩은 같이 하는 거구나 생각도 들고 아직 한참 멀었구나 공부 더 열심히 해야겠다 생각도 들고 암튼 저걸로 인해 너무 편리해진 코드가 나왔다.  밑에 코드 역시 위에와 같은 video rent 코드이다. 

 

 

1.VideoRentVo

package VideoRent;

import java.sql.Date;

public class VideoRentVo {

	private int num;
	private String id;
	private int videonum;
	private Date rent_date;
	private Date return_date;
	private int 연체일;

	public VideoRentVo() {
	}

	public VideoRentVo(int num, String id, int videonum, Date rent_date, Date return_date) {

		this.num = num;
		this.id = id;
		this.videonum = videonum;
		this.rent_date = rent_date;
		this.return_date = return_date;
	};

	public VideoRentVo(int num, String id, int videonum, int 연체일) {
		this.num = num;
		this.id = id;
		this.videonum = videonum;
		this.연체일 = 연체일;
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public int getVideonum() {
		return videonum;
	}

	public void setVideonum(int videonum) {
		this.videonum = videonum;
	}

	public Date getRent_date() {
		return rent_date;
	}

	public void setRent_date(Date rent_date) {
		this.rent_date = rent_date;
	}

	public Date getReturn_date() {
		return return_date;
	}

	public void setReturn_date(Date return_date) {
		this.return_date = return_date;
	}

	public int get연체일() {
		return 연체일;
	}

	public void set연체일(int 연체일) {
		this.연체일 = 연체일;
	}

	@Override
	public String toString() {
		return "VideoRentVo [번호 = " + num + ", 아이디 = " + id + ", 비디오 번호 = " + videonum + ", 대여일 = " + rent_date
				+ ", 반납일 = " + return_date + ", 연체일 = " + 연체일 + "]";
	}
	
	
}

 

2. VideoRentDao

package VideoRent;

import java.sql.Connection;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import conn.DBConnect;
import member.MemberService;

public class VideoRentDao {

	private DBConnect dbconn;

	public VideoRentDao() {
		dbconn = DBConnect.getInstance();
	}

	public void V_rent(int i) {

		Connection conn = dbconn.conn();

		String sql = "insert into VideoRent values (seq_VideoRent.nextval, ?, ?, sysdate, null)";
		String sql2 = "update Video set stock_v = 0 where videonum = ?";

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

			pstmt.setString(1, MemberService.Loginid);
			pstmt.setInt(2, i);

			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setInt(1, i);

			pstmt.executeQuery();
			pstmt2.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void V_return(int i) { // service에서 videoRentVo(로그인자가 빌린도서를 보여주고 반납할 videonum을 입력
		Connection conn = dbconn.conn(); // 입력받은 videonum을 videoRentVo에서 찾아서 vo반환.

		String sql = "update VideoRent set return_date = sysdate where videonum = ?";
		String sql2 = "update video set stock_v = 1 where videonum = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i);

			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setInt(1, i);

			pstmt.executeQuery();
			pstmt2.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	public VideoRentVo selectByVideoNum(int videoNum) {
		VideoRentVo vo = null;
		Connection conn = dbconn.conn();
		String sql = "select * from VideoRent where videonum = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, videoNum);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				vo = new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return vo;
	}

	public void SearchRDByVN(int videoNum) {
		VideoRentVo vo = null;
		Connection conn = dbconn.conn();
		String sql = "select return_date from VideoRent where videonum = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, videoNum);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				vo = new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ArrayList<VideoRentVo> selectID(String id) {
		ArrayList<VideoRentVo> list = new ArrayList<VideoRentVo>();
		Connection conn = dbconn.conn();
		String sql = "select * from VideoRent where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {
				list.add(new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<VideoRentVo> selectByReturnOver() {
		ArrayList<VideoRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select num,id,videonum,trunc(sysdate - (rent_date +14)) from videorent where (rent_date +14) < sysdate and return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<VideoRentVo> selectByReturn() {
		ArrayList<VideoRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select  * from VideoRent where return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	// 회원 로그인 후 자기가 빌린 책 목록 보는거
	public ArrayList<VideoRentVo> selectById() {
		ArrayList<VideoRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from VideoRent where id  = ? and return_date is null";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, MemberService.Loginid);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public ArrayList<VideoRentVo> selectAll() {
		ArrayList<VideoRentVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from VideoRent order by num desc";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoRentVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getDate(4), rs.getDate(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
		
		
	}

}

 

3. VideoRentSerivce

package VideoRent;

import java.util.ArrayList;

import java.util.Scanner;

import BookRent.BookRentVo;
import member.MemberService;
import video.VideoDao;
import video.VideoVo;

public class VideoRentService {

	private VideoRentDao dao;

	public VideoRentService() {
		dao = new VideoRentDao();
	}

	public void VideoRent(Scanner sc) {

		System.out.println("< 비디오 대여 >");
		VideoVo vo1 = new VideoVo();
		VideoDao dao1 = new VideoDao();
		boolean flag = true;
		int videonum = 0;

		while (flag) {
			System.out.println("번호 : ");
			videonum = sc.nextInt();
			vo1 = dao1.selectByVideoNum(videonum);
			if (vo1 == null) {
				System.out.println("없는 번호입니다");
			} else {
				if (vo1.getstock_v() == 1) {
					dao.V_rent(videonum);
					flag = false;
				} else if (vo1.getstock_v() == 0) {
					System.out.println("재고가 없습니다");
				}
			}
		}
		System.out.println(videonum + "번이 대여 되었습니다");
	}

	public void VideoReturn(Scanner sc) {

		System.out.println("< 비디오 반납 >");
		VideoRentVo vo = new VideoRentVo();
		VideoVo vo1 = new VideoVo();
		VideoDao dao1 = new VideoDao();
		VideoVo Vv = new VideoVo();

		boolean flag = true;
		int videonum = 0;

		while (flag) {
			System.out.print("번호 : ");
			videonum = sc.nextInt();
			vo1 = dao1.selectByVideoNum(videonum);
			if (vo1 == null) {
				System.out.println("없는 비디오 번호입니다");
			} else {
				if (vo1.getstock_v() == 1) {
					System.out.println("반납할 비디오가 아닙니다");
				} else if (vo1.getstock_v() == 0) {
					System.out.println(videonum + "번이 반납됐습니다");
					dao.V_return(videonum);
					flag = false;
				}
			}
		}
	}

	public void selectId(Scanner sc) {

		System.out.println("< 아이디로 기록 검색 >");
		System.out.println("아이디를 입력하세요");
		String id = sc.next();
		ArrayList<VideoRentVo> list = dao.selectID(id);
		for (VideoRentVo vo : list) {
			System.out.println(vo);
		}
	}

	// 멤버용 찾기
	public ArrayList<VideoRentVo> selectIdForUser() {

		MemberService ps = new MemberService();
		VideoRentDao vd = new VideoRentDao();
		ArrayList<VideoRentVo> list = new ArrayList();

		if (ps.Loginid.equals(vd.selectID(ps.Loginid))) {
			System.out.println(list);
		} else {
			System.out.println("자료 없음");
		}
		return list;
	}

	public void getAll() {
		System.out.println("전체 비디오 대여기록");
		ArrayList<VideoRentVo> list = dao.selectAll();
		for (VideoRentVo vo : list) {
			if (vo != null) {
				System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getVideonum() + "\t" + vo.getRent_date()
						+ "\t" + vo.getReturn_date());
			} else {
				System.out.println("비디오 대여 기록이 없습니다");
			}
		}
	}

	public void selectById() {
		System.out.println("비디오 대여목록");
		ArrayList<VideoRentVo> list = dao.selectById();

		if (list.isEmpty()) {
			System.out.println("비디오 대여목록이 없습니다");
		} else {
			System.out.println("번호" + "\t" + "아아디" + "\t" + "비디오번호" + "\t" + "대여일" + "\t" + "반납일");
			for (VideoRentVo vo : list) {
				System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getVideonum() + "\t" + vo.getRent_date()
						+ "\t" + vo.getReturn_date());
			}
		}

	}
	
	public void selectByRent() {
		System.out.println("미반납 현황");
		ArrayList<VideoRentVo> list = dao.selectByReturn();
		for (VideoRentVo vo : list) {
			System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getVideonum() + "\t" + vo.getRent_date()
					+ "\t" + vo.getReturn_date());
		}
	}

	public void selectByReturnOver() {
		System.out.println("연체 현황");

		ArrayList<VideoRentVo> list = dao.selectByReturnOver();

		if (list.isEmpty()) {
			System.out.println("연체자가 없습니다");
		} else {
			System.out.println("번호" + "\t" + "제목" + "\t" + "번호" + "\t" + "연체일");
			for (VideoRentVo vo : list) {
				System.out.println(vo.getNum() + "\t" + vo.getId() + "\t" + vo.getVideonum() + "\t" + vo.get연체일());
			}
		}
	}

}

이거 하면서 또 하나 우리의 발목을 잡았던 연체현황.

 

SQL1 : select num,id,videonum,trunc(sysdate - (rent_date +14)) from videorent where (rent_date +14) < sysdate and return_date is null;

→ 지금 연체일을 구할 떄 사용한 쿼리.

 

SQL2 : select num, id, booknum, trunc(sysdate - (rent_date+14))"delay date" from bookrent where return_date is null and trunc(sysdate - (rent_date+14)) >= 0; 

→  오라클에서 실행 해서 가져온 쿼리문 

 

 

 오라클에서는 SQL2가 실행 되는데 JAVA에서는 실행이 되지 않는 것이었다. 왜 인지는 아직도 모르겠고 그래서 결국 수정 한게 SQL1 이다.  왜 결과가 똑같이 나오는 쿼리문을 어디서는 되고 어디서는 안되고 그런 걸까.