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.po.ImgPO;
import com.lovo.util.DBUtil;

public class ImgDAOImpl implements ImgDAO {
	private Connection con = null;

	private PreparedStatement st = null;

	private ResultSet rs = null;

	public ImgPO queryByUrl(String url) throws SQLException {

		String sql = "select * from img where url =?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setString(1, url);
			rs = st.executeQuery();
			ImgPO po = null;
			while (rs.next()) {
				po = new ImgPO();
				po.setId(rs.getInt("id"));
				po.setUrl(rs.getString("url"));
			}
			return po;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(st);
			DBUtil.getDBUtil().close(con);
		}
	}

	public ImgPO queryById(int id) throws SQLException {
		String sql = "select * from img where id =?";
		ImgPO po = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while (rs.next()) {
				po = new ImgPO();
				po.setId(rs.getInt("id"));
				po.setUrl(rs.getString("url"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(st);
			DBUtil.getDBUtil().close(con);
		}
		return po;
	}

	public List<ImgPO> queryImgByAreaId(int id) throws SQLException {
		String sql = "select * from img i, img_area a where a.id =?";
		ImgPO po = null;
		List<ImgPO> list = new ArrayList<ImgPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while (rs.next()) {
				po = new ImgPO();
				po.setId(rs.getInt("id"));
				po.setUrl(rs.getString("url"));
				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 List<ImgPO> queryImgByBlockId(int id) throws SQLException {
		String sql = "select * from img i, img_block b where b.id =?";
		ImgPO po = null;
		List<ImgPO> list = new ArrayList<ImgPO>();
		try {
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while (rs.next()) {
				po = new ImgPO();
				po.setId(rs.getInt("id"));
				po.setUrl(rs.getString("url"));
				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;
	}

}