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

public class AccessUserDAO implements IUserDAO {
	private DBConnection coxn;
	AccessUserDAO(DBConnection coxn) { this.coxn = coxn; }
	public int addUser(User usr) {
		int res = -1;
		String sql = "Execute uAddUser ?, ?, ?, ?";
		//PARAMETERS [vusn] Text(12), [vpwd] Text(12), [vname] Text(12), [vemail] Text(255);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.coxn.prepareStatement(sql);
			stmt.setString(1, usr.getUsn());
			stmt.setString(2, usr.getPwd());
			stmt.setString(3, usr.getName());
			stmt.setString(4, usr.getEmail());
			stmt.executeUpdate();
			this.coxn.closeStatement(stmt);
			sql = "SELECT @@IDENTITY";
			stmt = this.coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				res = rs.getInt(1);
				usr.setId(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
		}
		return res;
	}
	public User getUser(String usn) {
		User usr = null;
		if (usn == null) return null;
		String sql = "SELECT * FROM [usr] WHERE [usn]=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.coxn.prepareStatement(sql);
			stmt.setString(1, usn);
			rs = stmt.executeQuery();
			if (rs.next()) {
				usr = new User();
				usr.setId(rs.getInt("id"));
				usr.setUsn(rs.getString("usn"));
				usr.setPwd(rs.getString("pwd"));
				usr.setEmail(rs.getString("email"));
				usr.setName(rs.getString("name"));
				usr.setSign(rs.getString("sign"));
				usr.setReg(rs.getTimestamp("reg"));
				usr.setHead(rs.getString("head"));
				usr.setRole(rs.getInt("role"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.coxn.closeResultSet(rs);
			this.coxn.closeStatement(stmt);
		}
		return usr;
	}
	public boolean deleteUser(String usn) {
		if (usn == null) return false;
		boolean res = false;
		String sql = "DELETE FROM [usr] WHERE [usn]=?";
		PreparedStatement stmt = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setString(1, usn);
			stmt.executeUpdate();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		return res;
	}
	public boolean updateUser(User usr) {
		boolean res = false;
		if (usr == null) return res;
		if (usr.getUsn() == null) return res;
		String sql = "UPDATE [usr] SET [pwd]=?, [email]=?, [name]=?, [sign]=?, " +
				"[head]=?, [role]=? WHERE [usn]=?";
		PreparedStatement stmt = null;
		try {
			stmt = coxn.prepareStatement(sql);
			stmt.setString(1, usr.getPwd());
			stmt.setString(2, usr.getEmail());
			stmt.setString(3, usr.getName());
			stmt.setString(4, usr.getSign());
			stmt.setString(5, usr.getHead());
			stmt.setInt(6, usr.getRole());
			stmt.setString(7, usr.getUsn());
			stmt.executeUpdate();
			res = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeStatement(stmt);
		}
		return res;
	}

	public ArrayList<User> getUsers(PageInfo page) {
		ArrayList<User> list = new ArrayList<User>();
		if (page == null) return list;
		int offset = page.getOffsetRecord();
		int size = page.getPageSize();
		String sql = "SELECT TOP "+(size)+" * FROM [usr] WHERE [role]<3 ORDER BY [id] DESC";
		if (offset > 0) {
			sql = "SELECt TOP "+size+" * FROM [usr] WHERE [role]<3 AND [id]<(SELECT Min([id]) FROM (SELECT TOP "+offset+" [id] FROM [usr] WHERE [role]<3 ORDER BY [id] DESC)) ORDER BY [id] DESC";
		}
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = coxn.prepareStatement("SELECT Count(*) FROM [usr] WHERE [role]<3");
			rs = stmt.executeQuery();
			if (rs.next()) {
				page.setTotalRecord(rs.getInt(1));
			}
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
			stmt = coxn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsn(rs.getString("usn"));
				u.setPwd(rs.getString("pwd"));
				u.setEmail(rs.getString("email"));
				u.setName(rs.getString("name"));
				u.setSign(rs.getString("sign"));
				u.setReg(rs.getTimestamp("reg"));
				u.setHead(rs.getString("head"));
				u.setRole(rs.getInt("role"));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			coxn.closeResultSet(rs);
			coxn.closeStatement(stmt);
		}
		return list;
	}
}