package net.lamp.blog.dao.impl;

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 net.lamp.blog.base.BaseDao;
import net.lamp.blog.common.PageRoll;
import net.lamp.blog.dao.RemarkDao;
import net.lamp.blog.entity.Remark;
import net.lamp.blog.util.DButil;
import net.lamp.blog.util.Utils;
/**
 * 评论管理:
 * 
 * @author zhaoxinguo
 *
 */
public class RemarkDaoImpl extends BaseDao<Remark> implements RemarkDao{

	/**
	 * 添加评论:
	 * 
	 */
	@Override
	public void addRemark(Remark remark) throws SQLException {
		
		Connection conn = null;
		String sql = "insert into remark (blogid,name,ip,message,createdate) values(?,?,?,?,?)";
		try {
			conn = DButil.getConnection();
			update(conn, sql, remark.getBlogid(),remark.getName(),remark.getIp(),remark.getMessage(),Utils.getCurrentTime());
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			DButil.releaseDBResource(null, null, conn);
		}
	}
	
	/**
	 * 根据博客blogid查询和博客相关联的评论信息:
	 * 
	 */
	@Override
	public List<Remark> list(int blogid, PageRoll pageRoll)
			throws SQLException {
		List<Remark> list = new ArrayList<Remark>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql1 = "select count(id) from remark";
		String sql2 = "SELECT id,blogid,NAME,ip,message,createdate FROM remark ORDER BY createdate DESC LIMIT ?,?";
		try {
			conn = DButil.getConnection();
			//-----------查询总记录数------------
			ps = conn.prepareStatement(sql1);
			rs = ps.executeQuery();
			if(rs.next()){
				pageRoll.setCountSize(rs.getInt(1));
			}
			//-------------查询当前页的记录-----------
			ps = conn.prepareStatement(sql2);
			ps.setInt(1, (pageRoll.getCurrPage()-1)*pageRoll.getPageSize());
			ps.setInt(2, pageRoll.getPageSize());
			rs = ps.executeQuery();
			while(rs.next()){
				Remark remark = new Remark();
				remark.setId(rs.getInt(1));
				remark.setBlogid(rs.getInt(2));
				remark.setName(rs.getString(3));
				remark.setIp(rs.getString(4));
				remark.setMessage(rs.getString(5));
				remark.setCreatedate(rs.getDate(6));
				list.add(remark);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			DButil.releaseDBResource(rs, ps, conn);
		}
		return list;
	}

	/**
	 * 通过评论表里面的name查找该用户评论过的博客信息:
	 * 
	 */
	@Override
	public List<Remark> ListRemarkBlog(String username) throws SQLException{
		Connection conn = null;
		List<Remark> list = null;
		String sql = "SELECT id,blogid,NAME,ip,message,createdate FROM remark r,userinfo u WHERE r.name = u.username AND u.username = ?";
		try {
			conn = DButil.getConnection();
			list = queryForList(conn, sql, username);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			DButil.releaseDBResource(null, null, conn);
		}
		return list;
	}

	/**
	 * 通过评论表的id删除评论信息:
	 * 
	 */
	@Override
	public void deleteRemarkByBlogId(int id) throws SQLException {
		Connection conn = null;
		String sql = "delete from remark where id = ?";
		try {
			conn = DButil.getConnection();
			update(conn, sql, id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			DButil.releaseDBResource(null, null, conn);
		}
	}
	
}