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

public class ConfigDAOImpl implements ConfigDAO {
	
	private Connection con = null;

	private PreparedStatement pre = null;

	private ResultSet rs = null;

	public void delete(int id) throws SQLException {
		String sql = "delete from config where id = ?";
		con = DBUtil.getDBUtil().getConnection();
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	public void insert(ConfigPO config) throws SQLException {
		String sql = "insert into config(purpose, descontent, value) values(?, ?, ?)";
		con = DBUtil.getDBUtil().getConnection();
		
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, config.getPurpose());
			pre.setString(2, config.getDescontent());
			pre.setInt(3, config.getValue());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	public List<ConfigPO> query() throws SQLException {
		String sql = "select * from config";
		con = DBUtil.getDBUtil().getConnection();
		List<ConfigPO> list = new ArrayList<ConfigPO>();
		try {
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while(rs.next()) {
				ConfigPO config = new ConfigPO();
				config.setId(rs.getInt("id"));
				config.setPurpose(rs.getString("purpose"));
				config.setDescontent(rs.getString("descontent"));
				config.setValue(rs.getInt("value"));
				list.add(config);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return list;
	}
	
	public int queryVlaueByPurpose(String purpose) throws SQLException {
		String sql = "select value from config where purpose = ?";
		con = DBUtil.getDBUtil().getConnection();
		int value = 0;
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, purpose);
			rs = pre.executeQuery();
			while(rs.next()) {
				value = rs.getInt("value");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return value;
	}

	public void update(ConfigPO config) throws SQLException {
		String sql = "update config set purpose = ?, descontent = ?, value = ? where id = ?";
		con = DBUtil.getDBUtil().getConnection();
		
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, config.getPurpose());
			pre.setString(2, config.getDescontent());
			pre.setInt(3, config.getValue());
			pre.setInt(4, config.getId());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	public ConfigPO queryById(int id) throws SQLException {
		String sql = "select * from config where id = ?";
		con = DBUtil.getDBUtil().getConnection();
		ConfigPO config = null;
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			while(rs.next()) {
				config = new ConfigPO();
				config.setId(rs.getInt("id"));
				config.setPurpose(rs.getString("purpose"));
				config.setDescontent(rs.getString("descontent"));
				config.setValue(rs.getInt("value"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return config;
	}

}