accesstopicdao.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.Topic;
import cn.ialvin.sql.DBConnection;
import cn.ialvin.web.PageInfo;

public class AccessTopicDAO implements ITopicDAO {
	private DBConnection coxn = null;
	AccessTopicDAO(DBConnection coxn) { this.coxn = coxn; }

	private void updateTopicNum(int forum) {
		String sql = "SELECT Count(*) FROM [topic] WHERE [forum]=" + forum;
		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 [forum] SET [topic]=" + n + " WHERE [id]=" + forum);
			stmt.executeUpdate();
			coxn.closeStatement(stmt);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public boolean deleteTopic(int id) {
		boolean res = false;
		String sql = "DELETE FROM [topic] WHERE [id]=?";
		PreparedStatement stmt = null;
		Topic topic = this.getTopic(id);
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, id);
			stmt.executeUpdate();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		if (topic != null) {
			this.updateTopicNum(topic.getForum());
		}
		return res;
	}

	public Topic getTopic(int id) {
		Topic topic = null;
		String sql = "SELECT * FROM [topic] WHERE [id]=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				topic = new Topic();
				topic.setId(rs.getInt("id"));
				topic.setForum(rs.getInt("forum"));
				topic.setTitle(rs.getString("title"));
				topic.setContent(coxn.getLongText("content", rs).trim());
				topic.setPost(rs.getTimestamp("post"));
				topic.setUpdate(rs.getTimestamp("update"));
				topic.setAuthor(rs.getString("author"));
				topic.setInTop(rs.getBoolean("intop"));
				topic.setView(rs.getInt("view"));
				topic.setReply(rs.getInt("reply"));
				topic.setLastReply(rs.getTimestamp("replyT"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return topic;
	}

	public int insertTopicDAO(Topic topic) {
		int res = -1;
		String sql = "INSERT INTO [topic]([forum],[title],[content],[author]) " +
				"VALUES(?,?,?,?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setInt(1, topic.getForum());
			stmt.setString(2, topic.getTitle());
			stmt.setString(3, topic.getContent());
			stmt.setString(4, topic.getAuthor());
			stmt.executeUpdate();
			sql = "SELECT @@IDENTITY";
			coxn.closeStatement(stmt);
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				res = rs.getInt(1);
				topic.setId(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		this.updateTopicNum(topic.getForum());
		return res;
	}

	public boolean updateTopic(Topic topic) {
		boolean res = false;
		String sql = "UPDATE [topic] " +
				"SET [forum]=?,[title]=?,[content]=?,[intop]=?,[view]=?,[reply]=?,[replyT]=?,[update]=? " +
				"WHERE [id]=?";
		PreparedStatement stmt = null;
		try {
			stmt = this.coxn.prepareStatement(sql);
			stmt.setInt(1, topic.getForum());
			stmt.setString(2, topic.getTitle());
			stmt.setString(3, topic.getContent());
			stmt.setBoolean(4, topic.isInTop());
			stmt.setInt(5, topic.getView());
			stmt.setInt(6, topic.getReply());
			stmt.setTimestamp(7, topic.getLastReply());
			stmt.setTimestamp(8, topic.getUpdate());
			stmt.setInt(9, topic.getId());
			stmt.execute();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeStatement(stmt);
		}
		return res;
	}

	public ArrayList<Topic> getTopics(String usn, PageInfo page) {
		ArrayList<Topic> list = new ArrayList<Topic>();
		if (usn == null) return list;
		int offset = page.getOffsetRecord();
		int size = page.getPageSize();
		if (size < 1) size = 1;
		String sql = "SELECT TOP "+(offset+size)+" * FROM (SELECT * " +
				"FROM [topic] " +
				"WHERE [author]=? ORDER BY [intop] ASC, [replyT] DESC)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.coxn.prepareStatement("SELECT Count(*) FROM [topic] WHERE [author]=?");
			stmt.setString(1, usn);
			rs = stmt.executeQuery();
			if (rs.next()) {
				page.setTotalRecord(rs.getInt(1));
			}
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
			stmt = this.coxn.prepareStatement(sql);
			stmt.setString(1, usn);
			rs = stmt.executeQuery();
			for (int i=0; i<offset; i++) if (!rs.next()) break;
			while (rs.next()) {
				Topic topic = new Topic();
				topic.setId(rs.getInt("id"));
				topic.setForum(rs.getInt("forum"));
				topic.setTitle(rs.getString("title"));
				topic.setContent(coxn.getLongText("content", rs).trim());
				topic.setPost(rs.getTimestamp("post"));
				topic.setUpdate(rs.getTimestamp("update"));
				topic.setAuthor(rs.getString("author"));
				topic.setInTop(rs.getBoolean("intop"));
				topic.setView(rs.getInt("view"));
				topic.setReply(rs.getInt("reply"));
				topic.setLastReply(rs.getTimestamp("replyT"));
				list.add(topic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
		}
		return list;
	}

	public ArrayList<Topic> getTopics(int forum, PageInfo page) {
		ArrayList<Topic> list = new ArrayList<Topic>();
		int offset = page.getOffsetRecord();
		int size = page.getPageSize();
		if (size < 1) size = 1;
		String sql = "SELECT TOP "+(offset+size)+" * FROM (SELECT * " +
				"FROM [topic] " +
				"WHERE [forum]=? ORDER BY [intop] ASC, [replyT] DESC)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.coxn.prepareStatement("SELECT Count(*) FROM [topic] WHERE [forum]=?");
			stmt.setInt(1, forum);
			rs = stmt.executeQuery();
			if (rs.next()) {
				page.setTotalRecord(rs.getInt(1));
			}
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
			stmt = this.coxn.prepareStatement(sql);
			stmt.setInt(1, forum);
			rs = stmt.executeQuery();
			for (int i=0; i<offset; i++) {
				if (!rs.next()) break;
			}
			while (rs.next()) {
				Topic topic = new Topic();
				topic.setId(rs.getInt("id"));
				topic.setForum(rs.getInt("forum"));
				topic.setTitle(rs.getString("title"));
				topic.setContent(coxn.getLongText("content", rs).trim());
				topic.setPost(rs.getTimestamp("post"));
				topic.setUpdate(rs.getTimestamp("update"));
				topic.setAuthor(rs.getString("author"));
				topic.setInTop(rs.getBoolean("intop"));
				topic.setView(rs.getInt("view"));
				topic.setReply(rs.getInt("reply"));
				topic.setLastReply(rs.getTimestamp("replyT"));
				list.add(topic);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
		}
		return list;
	}

}