accesscommentdao.java

又一个课程设计 简易 JSP 论坛 功能较简单的那种, 界面上模仿了 Discuz JSP 本来就学的不行, 只是尽量实现了 MVC

package cn.ialvin.bbs.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.ialvin.bbs.bean.Comment;
import cn.ialvin.sql.DBConnection;
import cn.ialvin.web.PageInfo;

public class AccessCommentDAO implements ICommentDAO {
	private DBConnection coxn;
	AccessCommentDAO(DBConnection coxn) { this.coxn = coxn; }

	public void updateReplyNum(int topicid) {
		String sql = "SELECT Count(*) FROM [comment] WHERE [topic]=" + topicid;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int n = 0;
			if (rs.next()) {
				n = rs.getInt(1);
			}
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
			stmt = coxn.prepareStatement("UPDATE [topic] SET [reply]=" + n + " WHERE [id]=" + topicid);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
	}
	public int insertComment(Comment com) {
		int res = -1;
		if (com == null) return res;
		String sql = "INSERT INTO [comment]([topic], [content], [author]) " +
				"VALUES(?, ?, ?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, com.getTopic());
			stmt.setString(2, com.getContent());
			stmt.setString(3, com.getAuthor());
			stmt.executeUpdate();
			coxn.closeStatement(stmt);
			sql = "SELECT @@IDENTITY";
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				res = rs.getInt(1);
				com.setId(res);
			}
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		this.updateReplyNum(com.getTopic());
		return 0;
	}

	public boolean deleteComment(int id) {
		boolean res = false;
		String sql = "DELETE FROM [comment] WHERE id=" + id;
		PreparedStatement stmt = null;
		Comment com = this.getComment(id);
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.executeUpdate();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		if (com != null)
			this.updateReplyNum(com.getTopic());
		return res;
	}

	public ArrayList<Comment> getComments(int topic, PageInfo page) {
		ArrayList<Comment> list = null;
		if (page == null) return list;

		int offset = page.getOffsetRecord();
		int size = page.getPageSize();
		String sql = "SELECT TOP " + size + " * FROM [comment] WHERE [topic]=? ORDER BY [id] ASC";
		if (offset > 0) {
			sql = 
				"SELECT TOP " + size + " * " +
				"FROM [comment] " +
				"WHERE [topic]=? AND [id]>(" +
					"SELECT MAX([id]) " +
					"FROM (" +
						"SELECT TOP " + offset + " [id] " +
						"FROM [comment] " +
						"WHERE [topic]=? ORDER BY [id] ASC" +
					")" +
				") ORDER BY [id] ASC";
		}
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement("SELECT Count(*) FROM [comment] WHERE [topic]=?");
			stmt.setInt(1, topic);
			rs = stmt.executeQuery();
			if (rs.next()) {
				page.setTotalRecord(rs.getInt(1));
			}
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, topic);
			if (offset > 0) {
				stmt.setInt(2, topic);
			}
			rs = stmt.executeQuery();
			list = new ArrayList<Comment>();
			Comment comm;
			while (rs.next()) {
				comm = new Comment();
				comm.setId(rs.getInt("id"));
				comm.setTopic(rs.getInt("topic"));
				comm.setContent(coxn.getLongText("content", rs).trim());
				comm.setAuthor(rs.getString("author"));
				comm.setPost(rs.getTimestamp("post"));
				list.add(comm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return list;
	}

	public Comment getComment(int id) {
		Comment cmt = null;
		String sql = "SELECT * FROM [comment] WHERE [id]=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				cmt = new Comment();
				cmt.setId(rs.getInt("id"));
				cmt.setTopic(rs.getInt("topic"));
				cmt.setContent(coxn.getLongText("content", rs).trim());
				cmt.setAuthor(rs.getString("author"));
				cmt.setPost(rs.getTimestamp("post"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return cmt;
	}

}