userop.java

博客管理系统。安装说明： 1、在SQLSERVER中创建blog数据库并创建登陆帐号blog：blog

package huc.blog.op;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import huc.blog.bean.User;
import huc.blog.util.PageObject;

public class UserOp extends BaseOp {
	
	/**
	 * （前台）用户登录
	 * @param user
	 * @return null:不存在该用户;user:存在该用户
	 */
	public User login(User user){
		conn = db.getConnection();	
		try {
			String sql = "select * from H_USER where userName = ? and userPass = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getUserPass());
			
			rs = pst.executeQuery();
			
			if(rs.next()){
				user.setId(rs.getString("id"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setNickName(rs.getString("nickName"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getString("birthday"));
				user.setCity(rs.getString("city"));
				user.setRegDate(rs.getString("regDate"));
				user.setUpdateTime(rs.getString("updateTime"));
				user.setSpaceName(rs.getString("spaceName"));
				user.setSpaceHit(rs.getInt("spaceHit"));
				user.setIcon(rs.getString("icon"));
				user.setIsCommend(rs.getInt("isCommend"));
			}
			else{
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally{
			close();
		}
		
		return user;
	}
	
	/**
	 * 检查该用户是否已经注册
	 * @param user
	 * @return true:存在;false:不存在
	 */
	public boolean checkExist(User user){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select * from H_USER where userName = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, user.getUserName());
			
			rs = pst.executeQuery();
			if(rs.next())
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		
		return flag;
	}

	/**
	 * 增加一个新用户
	 * @param user
	 * @return
	 */
	public boolean add(User user){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into H_USER(");
			sql.append("userName,userPass,nickName,email,birthday,city,");
			sql.append("regDate,updateTime,spaceName,spaceHit,icon,isCommend)");
			sql.append(" values(?,?,?,?,?,?,?,?,?,?,?,?)");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getUserPass());
			pst.setString(3, user.getNickName());
			pst.setString(4, user.getEmail());
			pst.setString(5, user.getBirthday());
			pst.setString(6, user.getCity());
			pst.setString(7, user.getRegDate());
			pst.setString(8, user.getUpdateTime());
			pst.setString(9, user.getSpaceName());
			pst.setInt(10, user.getSpaceHit());
			pst.setString(11, user.getIcon());
			pst.setInt(12, user.getIsCommend());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}

	/**
	 * 修改用户
	 * @param user
	 * @return true:更新成功;false:更新失败
	 */
	public boolean update(User user){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("update H_USER set ");
			sql.append(" userName=?,userPass=?,nickName=?,email=?,birthday=?,city=?,");
			sql.append(" regDate=?,updateTime=?,spaceName=?,spaceHit=?,icon=?,isCommend=?");
			sql.append(" where id = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getUserPass());
			pst.setString(3, user.getNickName());
			pst.setString(4, user.getEmail());
			pst.setString(5, user.getBirthday());
			pst.setString(6, user.getCity());
			pst.setString(7, user.getRegDate());
			pst.setString(8, user.getUpdateTime());
			pst.setString(9, user.getSpaceName());
			pst.setInt(10, user.getSpaceHit());
			pst.setString(11, user.getIcon());
			pst.setInt(12, user.getIsCommend());
			pst.setString(13, user.getId());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}

	/**
	 * 删除用户
	 * @param user
	 * @return true:更新成功;false:更新失败
	 */
	public boolean delete(User user){
		conn = db.getConnection();
		boolean flag = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from H_USER where id = ? or userName = ?");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, user.getId());
			pst.setString(2, user.getUserName());
			
			int line = pst.executeUpdate();
			if(line == 1)
				flag = true;
			else
				flag = false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return flag;
		}
		finally{
			close();
		}
		return flag;
	}

	/**
	 * 获得指定id的user
	 * @param id 指定查询的user的id
	 * @return
	 */
	public User getUserById(String id){
		conn = db.getConnection();
		User user = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select * from H_USER");
			sql.append(" where id = ? ");
			pst = conn.prepareStatement(sql.toString());
			pst.setString(1, id);
			
			rs = pst.executeQuery();
			if(rs.next()){
				user = new User();
				user.setId(rs.getString("id"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setNickName(rs.getString("nickName"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getString("birthday"));
				user.setCity(rs.getString("city"));
				user.setRegDate(rs.getString("regDate"));
				user.setUpdateTime(rs.getString("updateTime"));
				user.setSpaceName(rs.getString("spaceName"));
				user.setSpaceHit(rs.getInt("spaceHit"));
				user.setIcon(rs.getString("icon"));
				user.setIsCommend(rs.getInt("isCommend"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			close();
		}
		
		return user;
	}

	/**
	 * 列出符合查询条件的users
	 * @param otherSQL		指定查询的条件			like(" and param = value")
	 * @param orderBy		指定查询结果的排序方式	like(" order by time desc")
	 * @return
	 */
	public List getUsers(String otherSQL, String orderBy){
		return this.getUsers(0, otherSQL, orderBy);
	}
	
	/**
	 * 列出符合查询条件的users
	 * @param listNumber	指定列出符合查询条件的数目
	 * @param otherSQL		指定查询的条件			like(" and param = value")
	 * @param orderBy		指定查询结果的排序方式	like(" order by time desc")
	 * @return
	 */
	public List getUsers(int listNumber, String otherSQL, String orderBy){
		conn = db.getConnection();
		List result = new ArrayList();
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select ");
			if(listNumber != 0){
				sql.append(" top ");
				sql.append(listNumber);
			}
			sql.append(" * from H_USER");
			sql.append(" where 1 = 1 ");
			if(otherSQL != null && !otherSQL.trim().equals(""))
				sql.append(otherSQL);
			if(orderBy != null && !orderBy.trim().equals("")){
				sql.append(orderBy);
			}
			pst = conn.prepareStatement(sql.toString());
			
			rs = pst.executeQuery();
			while(rs.next()){
				User user = new User();
				
				user.setId(rs.getString("id"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setNickName(rs.getString("nickName"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getString("birthday"));
				user.setCity(rs.getString("city"));
				user.setRegDate(rs.getString("regDate"));
				user.setUpdateTime(rs.getString("updateTime"));
				user.setSpaceName(rs.getString("spaceName"));
				user.setSpaceHit(rs.getInt("spaceHit"));
				user.setIcon(rs.getString("icon"));
				user.setIsCommend(rs.getInt("isCommend"));
				
				result.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList();
		}
		finally{
			close();
		}
		
		return result;
	}
	
	/**
	 * 获得分页的数据
	 * @param page		封装的分页信息
	 * @param otherSQL	分页数据的查询条件	like(" and param = value")
	 * @param orderBy	分页数据的排序方式	like(" order by time desc")
	 * @return 封装的分页数据
	 */
	public PageObject getPage(PageObject page, String otherSQL, String orderBy){
		conn = db.getConnection();
		
		try {
			StringBuffer sql = new StringBuffer();
			//获得符合分页的对象的总记录数
			sql.append("select count(1) as total from H_USER where 1=1 ");
			if(otherSQL != null && !otherSQL.trim().equals(""))
				sql.append(otherSQL);

			pst = conn.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			if(rs.next() && rs.getInt("total") != 0)
				page.setTotal(rs.getInt("total"));
			else{
				page.setList(new ArrayList());
				page.setTotal(0);
				return page;
			}
			rs.close();

			//得到分页sql语句
			sql = this.getSQL(page, " * from H_USER ", otherSQL, orderBy);
			pst = conn.prepareStatement(sql.toString());
			rs = pst.executeQuery();

			List result = new ArrayList();
			while(rs.next()){
				User user = new User();
				
				user.setId(rs.getString("id"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setNickName(rs.getString("nickName"));
				user.setEmail(rs.getString("email"));
				user.setBirthday(rs.getString("birthday"));
				user.setCity(rs.getString("city"));
				user.setRegDate(rs.getString("regDate"));
				user.setUpdateTime(rs.getString("updateTime"));
				user.setSpaceName(rs.getString("spaceName"));
				user.setSpaceHit(rs.getInt("spaceHit"));
				user.setIcon(rs.getString("icon"));
				user.setIsCommend(rs.getInt("isCommend"));
				
				result.add(user);
			}
			page.setList(result);
		} catch (SQLException e) {
			e.printStackTrace();
			return page;
		}
		finally{
			close();
		}
		
		return page;
	}
    /**
     * 更新点击率
     * @param   userid  -   要更新的博客空间用户的id
     */
    public void updateHit(String userid){
        conn = db.getConnection();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("update H_USER set spaceHit=spaceHit+1 where id = ?");
            pst = conn.prepareStatement(sql.toString());
            pst.setString(1, userid);
            
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            close();
        }
    }
}