commentservice.java

Ajax评论系统(结合Prototype+Freemarker分页)

package ajax.web.service;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import ajax.util.*;
import ajax.web.po.Comment;

/**
 * 评论服务层
 *
 * @author  陈智聪(<a href="mailto:[e-mail]">[e-mail]</a>)
 * @version 2.0 - 2009/02/10
 */
public class CommentService {
	
	private static DB db;
	private Connection conn;
	private PreparedStatement ps = null;
	
	/**
	 * 构造方法
	 */
	public CommentService() {
		init();
	}
	
	/**
	 * 初始化
	 */
	public void init() {
		db = new DB();
	}
	
	/**
	 * 发表评论
	 * @param comment
	 * @return 是否发表成功
	 */
	public boolean addComment(Comment comment) {
		conn = db.getConnection();
		boolean flag  = false;
		StringBuffer sql = new StringBuffer();
		sql.append("insert into tb_comment(nickname,content,post_time,client_ip)");
		sql.append(" values(?,?,?,?)");
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, comment.getNickname());
			ps.setString(2, comment.getContent());
			ps.setString(3, Function.getDateTime());
			ps.setString(4, comment.getClientIP());
			ps.execute();
			conn.commit();
			flag = true;
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.closeConnection();
		}
		return flag;
	}
	
	/**
	 * 删除评论
	 * @param id 评论的ID
	 * @return 是否删除成功
	 */
	public boolean delComment(int id) {
		conn = db.getConnection();
		boolean flag  = false;
		StringBuffer sql = new StringBuffer();
		sql.append("delete from tb_comment where id=?");
		
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, id);
			ps.execute();
			conn.commit();
			flag = true;
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.closeConnection();
		}
		return flag;
	}
	
	/**
	 * 获取记录总数
	 * @return
	 */
	public int getCommentRowCount() {
		conn = db.getConnection();
		int rowCount = 0;
		ResultSet rs = null;
		String sql = "select count(*) as total from tb_comment";
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			rowCount = rs.getInt("total");
			conn.commit();
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (rs != null) {	
					rs.close();
					rs = null;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}		
			this.closeConnection();
		}
		return rowCount;
	}
	
	/**
	 * 取得评论列表
	 * @param start 开始读取位置
	 * @param size 每次读取多少条记录
	 * @return
	 */
	public List<Comment> getCommentList(int start, int size) {
		conn = db.getConnection();
		List<Comment> list = new ArrayList<Comment>();
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM tb_comment t order by id desc limit ");
		sql.append(start).append(",").append(size);

		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				Comment comment = new Comment();
				comment.setId(rs.getInt(1));
				comment.setNickname(rs.getString(2));
				comment.setContent(rs.getString(3));
				comment.setPostTime(new StringBuffer().append(rs.getDate(4))
						.append(" ").append(rs.getTime(4)).toString());
				comment.setClientIP(rs.getString(5));
				list.add(comment);
			}
			conn.commit();
		} catch(SQLException e) {
			list = null;
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (rs != null) {	
					rs.close();
					rs = null;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}		
			this.closeConnection();
		}
		return list;
	}
	
	/**
	 * 释放数据库资源
	 */
	public void closeConnection() {
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		db.closeConnection(conn);
	}
}