[mini project] 7. 도서관 관리 시스템 - 비디오 관리 테이블

 

1. VideoVO 

package video;

public class VideoVo {
	
	private int videonum;
	private String title;
	private String genre;
	private int stock_v; 
	
	public VideoVo () {}

	public VideoVo(int videonum, String title, String genre) {
		super();
		this.videonum = videonum;
		this.title = title;
		this.genre = genre;
		
	}
	

	public VideoVo(int videonum, String title, String genre, int stock_v) {
		super();
		this.videonum = videonum;
		this.title = title;
		this.genre = genre;
		this.stock_v = stock_v;
	}

	public int getvideonum() {
		return videonum;
	}

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

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getGenre() {
		return genre;
	}

	public void setGenre(String genre) {
		this.genre = genre;
	}

	public int getstock_v() {
		return stock_v;
	}

	public void setstock_v(int stock_v) {
		this.stock_v = stock_v;
	}

	@Override
	public String toString() {
		return "bookVo [책 번호 = " + videonum + ", 제목 = " + title + ", 장르 = " + genre + ", 재고 = " + stock_v + "]";
	}

	
}

 

2. VideoDao

package video;

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

import book.BookVo;
import conn.DBConnect;

public class VideoDao {

	private DBConnect dbconn;

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

	public void insert(VideoVo vo) {
		
		Connection conn = dbconn.conn();
		String sql = "insert into video values (seq_videonum.nextval, ?, ?, 1)";

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

			pstmt.setString(1, vo.getTitle());
			pstmt.setString(2, vo.getGenre());

			int num = pstmt.executeUpdate();
			System.out.println(vo.getTitle() + "비디오가 추가 되었습니다");

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


	public void update(VideoVo vo) {
		Connection conn = dbconn.conn();

		String sql = "update video set title=?,genre=? where videonum = ?";

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

			pstmt.setString(1, vo.getTitle());
			pstmt.setString(2, vo.getGenre());
			pstmt.setInt(3, vo.getvideonum());

			int num = pstmt.executeUpdate();
			System.out.println("비디오" + num + "개가 수정 되었습니다");

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

	public void delete(int i) {
		Connection conn = dbconn.conn();
		String sql = "delete from video where videonum = ?";

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

			pstmt.setInt(1, i);

			int num = pstmt.executeUpdate();
			System.out.println("비디오" + num + "개가 삭제 되었습니다");

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<VideoVo> selectAll(){
		
		ArrayList<VideoVo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from video order by videonum asc";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

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

	public ArrayList<VideoVo> selectByTitle(String title) {

		ArrayList<VideoVo> list = new ArrayList<>();

		Connection conn = dbconn.conn();
		String sql = "select * from video where title = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, title);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
			}

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

	public ArrayList<VideoVo> selectByGenre(String genre) {

		ArrayList<VideoVo> list = new ArrayList<>();

		Connection conn = dbconn.conn();
		String sql = "select * from video where genre = ? ";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, genre);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new VideoVo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
			}

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

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

	
}

 

3. VideoService

package video;

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

import book.BookVo;

public class VideoService {

	private VideoDao dao;

	public VideoService() {
		dao = new VideoDao();
	}

	public void insert(Scanner sc) {

		System.out.println("< 비디오 등록 >");
		System.out.println("제목 : ");
		sc.nextLine();
		String title = sc.nextLine();
		System.out.println("장르 : ");
		String genre = sc.nextLine();
		dao.insert(new VideoVo(0, title, genre, 1));

	}

	public void getAll() {

		System.out.println("비디오 전체 목록");
		ArrayList<VideoVo> list = dao.selectAll();
		System.out.println("번호" + "\t" + "제목" + "\t" + "장르" + "\t" + "재고");
		for (VideoVo vo : list) {
			System.out.println(vo.getvideonum() + "\t" + vo.getTitle() + "\t" + vo.getGenre() + "\t" + vo.getstock_v());
		}
	}

	public void edit(Scanner sc) {

		System.out.println("< 등록한 비디오 수정 >");
		getAll();
		System.out.println("번호를 입력하세요");
		int videonum = sc.nextInt();
		System.out.println("제목을 입력하세요");
		String title = sc.next();
		System.out.println("장르를 입력하세요");
		String genre = sc.next();

		dao.update(new VideoVo(videonum, title, genre));

	}

	public void delVideo(Scanner sc) {

		System.out.println("< 삭제 >");
		getAll();
		System.out.println("삭제할 번호를 입력해주세요");
		int i = sc.nextInt();
		dao.delete(i);

	}

	public void selectbytitle(Scanner sc) {

		System.out.println("< 제목 검색 >");
		System.out.println("제목을 입력하세요");
		sc.nextLine();
		String title = sc.nextLine();

		ArrayList<VideoVo> list = dao.selectByTitle(title);

		if (list.size() == 0) {
			System.out.println("해당 제목이 없습니다");
		} else {
			System.out.println(list);
		}
	}

	public void selectbygerne(Scanner sc) {

		System.out.println("< 장르 검색 >");
		System.out.println("장르를 입력하세요");
		sc.nextLine();
		String genre = sc.nextLine();

		ArrayList<VideoVo> list = dao.selectByGenre(genre);

		if (list.size() == 0) {
			System.out.println("찾으시는 장르가 없습니다");
		} else {
			System.out.println(list);
		}
	}

}

 

책과 정말 비슷한 비디오 관리 패키지 역시 어려움 없이 해 낼 수 있다. 여기서 또 주의점을 배웠는데 비슷한 두가지를 카피해서 이름만 바꿀때는 정말 꼼꼼하게 모든 걸 살펴 볼 필요가 있다는 것!