package com.lovo.dao;

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

import com.lovo.po.AreaPO;
import com.lovo.po.BlockPO;
import com.lovo.util.DBUtil;

public class BlockDAOImpl implements BlockDAO {
	
	private Connection con = null;

	private PreparedStatement st = null;

	private ResultSet rs = null;
	
		public void insert(BlockPO po) throws SQLException {
			String sql = "insert into block (name,imgid,descontent,area_id) values (?,?,?,?)";
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setString(1, po.getName());
				st.setInt(2, po.getImgId());
				st.setString(3, po.getDescribe());
				st.setInt(4, po.getArea().getId());
				st.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}	
		}
		
		public void delete(int id) throws SQLException {
			String sql = "delete from block where id= ?";
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setInt(1, id);
				st.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}
			
		}
		
		public void update(BlockPO po) throws SQLException {
			String sql = "update block set name=?, imgid = ?, descontent = ? where id=?";
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setString(1, po.getName());
				st.setInt(2, po.getImgId());
				st.setString(3, po.getDescribe());
				st.setInt(4, po.getId());
				st.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}
			
		}
		
		public List<BlockPO> queryBlockByAreaId(int id) throws SQLException {
			ArrayList<BlockPO> list = new ArrayList<BlockPO>();
			String sql = "select * from block where area_id = ?";
			
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setInt(1, id);
				rs = st.executeQuery();
				while(rs.next()){
					BlockPO po = new BlockPO();
					po.setId(rs.getInt("id"));
					po.setName(rs.getString("name"));
					po.setImgId(rs.getInt("imgid"));
					po.setDescribe(rs.getString("descontent"));
					int areaID=rs.getInt("area_id");
					AreaPO areaPO=new AreaPO();
					areaPO.setId(areaID);
					po.setArea(areaPO);
					list.add(po);
				}
				
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(rs);
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}
			return list;
		}		
		public BlockPO queryBlockById(int id) throws SQLException {
			String sql = "select * from block where id = ?";
			
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setInt(1, id);
				rs = st.executeQuery();
				BlockPO po=null;
				while(rs.next()){
					po = new BlockPO();
					AreaPO area = new AreaPO();
					area.setId(rs.getInt("area_id"));
					po.setId(rs.getInt("id"));
					po.setArea(area);
					po.setName(rs.getString("name"));
					po.setImgId(rs.getInt("imgid"));
					po.setDescribe(rs.getString("descontent"));
				}
				return po;
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(rs);
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}
		}

		public void deleteByAreaID(int areaID) throws SQLException {
			String sql = "delete from block where area_id= ?";
			try{
				con = DBUtil.getDBUtil().getConnection();
				st = con.prepareStatement(sql);
				st.setInt(1, areaID);
				st.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				throw e;
			}finally{
				DBUtil.getDBUtil().close(st);
				DBUtil.getDBUtil().close(con);
			}
			
		}		
}