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

public class ChatroomDAOImpl implements ChatroomDAO{
	private Connection con;
	private PreparedStatement pre;
	private ResultSet rs;
	
	
	
	/**
	 * 修改數據
	 */
	public void update(ChatroomPO po) throws SQLException {
		String sql = "update chatroom set name = ?, Maxusernum = ? where id = ?";
		con = DBUtil.getDBUtil().getConnection();
//		System.out.println(sql);
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, po.getName());
			pre.setInt(2, po.getMaxUserNum());
			pre.setInt(3, po.getId());
			int success = pre.executeUpdate();
			if(success > 0) {
				System.out.println("修改成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		
	}
	
	/**
	 * 查詢所有
	 */
	public List queryAll() throws SQLException {
		String sql = "select * from chatroom";
		List<ChatroomPO> list = new ArrayList<ChatroomPO>();
		con = DBUtil.getDBUtil().getConnection();
		try {
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while(rs.next()) {
				ChatroomPO po = new ChatroomPO();
				po.setId(rs.getInt("id"));
				po.setName(rs.getString("name"));
				po.setMaxUserNum(rs.getInt("Maxusernum"));
				list.add(po);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 模糊查詢
	 */
	public List<ChatroomPO> queryByName(String queryCondition) throws SQLException {
		String sql = "select * from chatroom where name like '%"+queryCondition+"%'";
		List<ChatroomPO> list = new ArrayList<ChatroomPO>();
		con = DBUtil.getDBUtil().getConnection();
		try {
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while(rs.next()) {
				ChatroomPO po = new ChatroomPO();
				po.setId(rs.getInt("id"));
				po.setName(rs.getString("name"));
				po.setMaxUserNum(rs.getInt("Maxusernum"));
				list.add(po);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}
	

	/**
	 * 刪除
	 */
	public void delete(int id) throws SQLException {
		String sql = "delete from chatroom where id = ?";
		con = DBUtil.getDBUtil().getConnection();
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			int success = pre.executeUpdate();
			if(success > 0) {
				System.out.println("刪除成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		
	}
	
	/**
	 * 添加
	 */
	public void insert(ChatroomPO po) throws SQLException {
		String sql = "insert into chatroom(name, maxusernum) values(?, ?)";
		con = DBUtil.getDBUtil().getConnection();
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, po.getName());
			pre.setInt(2,po.getMaxUserNum());
			int success = pre.executeUpdate();
			if(success > 0) {
				System.out.println("添加成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
	}

	public ChatroomPO queryById(int id) throws SQLException {
		String sql = "select * from chatroom where id = ?";
		con = DBUtil.getDBUtil().getConnection();
		ChatroomPO po = null;
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();
			while(rs.next()) {
				po = new ChatroomPO();
				po.setId(rs.getInt("id"));
				po.setName(rs.getString("name"));
				po.setMaxUserNum(rs.getInt("Maxusernum"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}
		return po;
	}

	
	
}