accessmanagerdao.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.Manager;
import cn.ialvin.sql.DBConnection;

public class AccessManagerDAO implements IManagerDAO {
	private DBConnection coxn;
	AccessManagerDAO(DBConnection coxn) { this.coxn = coxn; }
	public boolean deleteManager(int id) {
		boolean res = false;
		String sql = "DELETE FROM [manager] WHERE [id]=" + id;
		PreparedStatement stmt = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.executeUpdate();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		return res;
	}
	public Manager getManager(int id) {
		Manager mgr = null;
		String sql = "SELECT * FROM [manager] WHERE [id]=" + id;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				mgr = new Manager();
				mgr.setId(rs.getInt("id"));
				mgr.setUSN(rs.getString("usr"));
				mgr.setForum(rs.getInt("forum"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return mgr;
	}
	public ArrayList<Manager> getManagers() {
		ArrayList<Manager> list = new ArrayList<Manager>();
		String sql = "SELECT * FROM [manager] ORDER BY [forum] ASC";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Manager mgr = new Manager();
				mgr.setId(rs.getInt("id"));
				mgr.setUSN(rs.getString("usr"));
				mgr.setForum(rs.getInt("forum"));
				list.add(mgr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return list;
	}
	public int insertManager(Manager mgr) {
		int res = -1;
		String sql = "INSERT INTO [manager]([usr], [forum]) VALUES(?, ?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setString(1, mgr.getUSN());
			stmt.setInt(2, mgr.getForum());
			stmt.executeUpdate();
			sql = "SELECT @@IDENTITY";
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				res = rs.getInt(1);
				mgr.setId(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return res;
	}
	public boolean updateManager(Manager mgr) {
		boolean res = false;
		String sql = "UPDATE [manager] SET [usr]=?, [forum]=? WHERE [id]=?";
		PreparedStatement stmt = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setString(1, mgr.getUSN());
			stmt.setInt(2, mgr.getForum());
			stmt.setInt(3, mgr.getId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		return res;
	}
	public ArrayList<Manager> getManagers(int forumid) {
		ArrayList<Manager> list = new ArrayList<Manager>();
		String sql = "SELECT * FROM [manager] WHERE [forum]=" + forumid;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Manager mgr = new Manager();
				mgr.setId(rs.getInt("id"));
				mgr.setUSN(rs.getString("usr"));
				mgr.setForum(rs.getInt("forum"));
				list.add(mgr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return list;
	}
	public boolean isManager(String usn, int forumid) {
		boolean res = false;
		String sql = "SELECT Count(*) FROM [manager] WHERE [usr]=? AND [forum]=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.coxn.prepareStatement(sql);
			stmt.setString(1, usn);
			stmt.setInt(2, forumid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				if (rs.getInt(1) > 0)
					res = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
		}
		return res;
	}
	public boolean isManager(String usn) {
		boolean res = false;
		String sql = "SELECT TOP 1 [id] FROM [manager] WHERE [usr]=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setString(1, usn);
			rs = stmt.executeQuery();
			res = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return res;
	}
}