Food_Log(DAO)

package com.yoon.ha;

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

import com.yoon.ha.dbcp.FoodLogJdbc;


public class FoodLogDao {
	// 연결
	private static Connection conn = null;
	private static Statement stmt = null;
	private static PreparedStatement pstmt = null;
	private static ResultSet rs = null;
	
	// CRUD쿼리문
	static String SELECT_ALL = "SELECT * FROM FOOD_LOG";
	static String SELECT_ONE = "SELECT * FROM FOOD_LOG WHERE NO=?";
	static String SELECT_NO = "SELECT NO FROM FOOD_LOG";
	static String INSERT = "INSERT INTO FOOD_LOG(FNAME,STORE,PRICE,STAR,DATE,TYPE,COMMENT,PICTURE) VALUES(?,?,?,?,?,?,?,?)";
	static String MODIFY = "UPDATE FOOD_LOG SET FNAME=?, STORE=?, PRICE=?, STAR=?, DATE=?, TYPE=?, COMMENT=?, PICTURE=? WHERE NO=?";
	static String DELETE = "DELETE FROM FOOD_LOG WHERE NO=?";
	
	// 추가
	public static void insert(FoodLogVo vo) {
		conn = FoodLogJdbc.getConnection();
		
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setString(1, vo.getFname());
			pstmt.setString(2, vo.getStore());
			pstmt.setString(3, vo.getPrice());
			pstmt.setString(4, vo.getStar());
			pstmt.setString(5, vo.getDate());
			pstmt.setString(6, vo.getType());
			pstmt.setString(7, vo.getComment());
			pstmt.setString(8, vo.getPfile());
			int sufa = pstmt.executeUpdate();
			
			if(sufa > 0) {
				System.out.println("���� ����");
				conn.commit();
			} else {
				System.out.println("���� ����");
				conn.rollback();
			}
		} catch (SQLException e) {
			System.out.println("���� ����");
			
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			e.printStackTrace();
		} finally {
			FoodLogJdbc.close(conn, pstmt, rs);
			FoodLogJdbc.close(stmt);
		}
	} // end of insert()
	
	// 전체목록
	public static List<FoodLogVo> selectAll() {
		List<FoodLogVo> list = new ArrayList<FoodLogVo>();
		conn = FoodLogJdbc.getConnection();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SELECT_ALL);
			while(rs.next()) {
				FoodLogVo food = new FoodLogVo();
				
				food.setNo(rs.getInt(1));
				food.setFname(rs.getString(2));
				food.setStore(rs.getString(3));
				food.setPrice(rs.getString(4));
				food.setStar(rs.getString(5));
				food.setDate(rs.getString(6));
				food.setType(rs.getString(7));
				food.setComment(rs.getString(8));
				food.setPfile(rs.getString(9));
				
				list.add(food);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			FoodLogJdbc.close(conn, stmt, rs);
		}
		
		return list;
	} // end of selectAll()
	
	// 상세보기
	public static FoodLogVo selectOne(FoodLogVo vo) {
		FoodLogVo food = null;
		conn = FoodLogJdbc.getConnection();
		
		try {
			pstmt=conn.prepareStatement(SELECT_ONE);
			pstmt.setInt(1, vo.getNo());
			rs = pstmt.executeQuery();
			if(rs.next()) {
				food = new FoodLogVo();
				food.setNo(rs.getInt(1));
				food.setFname(rs.getString(2));
				food.setStore(rs.getString(3));
				food.setPrice(rs.getString(4));
				food.setStar(rs.getString(5));
				food.setDate(rs.getString(6));
				food.setType(rs.getString(7));
				food.setComment(rs.getString(8));
				food.setPfile(rs.getString(9));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			FoodLogJdbc.close(conn, pstmt, rs);
		}
		return food;
	} // end of seletOne()
	
	//삭제
	public static void delete(FoodLogVo vo) {
		conn = FoodLogJdbc.getConnection();
		try {
			pstmt = conn.prepareCall(DELETE);
			pstmt.setInt(1, vo.getNo());
			int cnt = pstmt.executeUpdate();
			if(cnt > 0) {
				System.out.println("���� �Ϸ�!");
				conn.commit();
			} else {
				System.out.println("���� ����!");
				conn.rollback();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			FoodLogJdbc.close(conn, pstmt, rs);
		}
	} // end of delete()

	// 수정
	public static void modify(FoodLogVo vo) {
		conn = FoodLogJdbc.getConnection();
		try {
			System.out.println("modify vo :: " + vo.toString());
			pstmt = conn.prepareStatement(MODIFY);
			pstmt.setString(1, vo.getFname());
			pstmt.setString(2, vo.getStore());
			pstmt.setString(3, vo.getPrice());
			pstmt.setString(4, vo.getStar());
			pstmt.setString(5, vo.getDate());
			pstmt.setString(6, vo.getType());
			pstmt.setString(7, vo.getComment());
			pstmt.setString(8, vo.getPfile());
			pstmt.setInt(9, vo.getNo());
			
			int cnt  = pstmt.executeUpdate();	// update는 성공하면 1, 실패하면 0을 리턴한다.
			if(cnt > 0) {
				System.out.println("정상적으로 수정되었습니다!");
				conn.commit();
			} else {
				System.out.println("수정 중, 오류가 발생했습니다.");
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				System.out.println("���� ����!");
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			FoodLogJdbc.close(conn, pstmt, rs);
		}
	}
}