package com.book.dao;

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

import com.book.bean.BooksVO;

public class BooksDAO {
	
	// JDBC 관련 변수
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	// SQL 명령
	private final String BOOKS_INSERT = "INSERT INTO BOOKS VALUES (?,?,?,?)";
	private final String BOOKS_LIST = "SELECT * FROM BOOKS ORDER BY CODE ASC";
	private final String BOOKS_UPDATE = "UPDATE BOOKS SET NAME=?, PRICE=?, MAKER=? WHERE CODE=?";
	private final String BOOKS_DELETE = "DELETE FROM BOOKS WHERE NAME=?";
	private final String BOOKS_CHECK = "SELECT * FROM BOOKS WHERE NAME =?";
	// 도서 등록
	public int insertBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_INSERT);
			pstmt.setString(1, vo.getCode());
			pstmt.setString(2, vo.getName());
			pstmt.setInt(3, vo.getPrice());
			pstmt.setString(4, vo.getMaker());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 목록
	public List<BooksVO> getBooksList(BooksVO vo) {
		List<BooksVO> list = new ArrayList<BooksVO>();
		
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_LIST);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				vo = new BooksVO();
				vo.setCode(rs.getString("CODE"));
				vo.setName(rs.getString("NAME"));
				vo.setPrice(rs.getInt("PRICE"));
				vo.setMaker(rs.getString("MAKER"));
				list.add(vo);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		
		return list; 
	}
	
	// 도서 정보 수정
	public int updateBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_UPDATE);
			pstmt.setString(1, vo.getName());
			pstmt.setInt(2, vo.getPrice());
			pstmt.setString(3, vo.getMaker());
			pstmt.setString(4, vo.getCode());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 삭제
	public int deleteBooks(BooksVO vo) {
		int su = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_DELETE);
			pstmt.setString(1, vo.getName());
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 확인
	public BooksVO checkBooks(BooksVO vo) {
		BooksVO check_vo = null;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(BOOKS_CHECK);
			pstmt.setString(1, vo.getName());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				check_vo = new BooksVO();
				check_vo.setCode(rs.getString("CODE"));
				check_vo.setMaker(rs.getString("MAKER"));
				check_vo.setName(rs.getString("NAME"));
				check_vo.setPrice(rs.getInt("PRICE"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return check_vo; 
	}
}