dbuserfactory.java

基于struct结构的jsp

package com.ntsky.bbs.service.db;

import java.util.*;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.apache.log4j.Logger ;

import com.ntsky.util.Timer;
import com.ntsky.util.TimerExpress;
import com.ntsky.util.MD5;
import com.ntsky.bbs.service.Roles;
import com.ntsky.util.CodeFilter;
import com.ntsky.bbs.service.User;
import com.ntsky.bbs.service.UserFactory;
import com.ntsky.bbs.service.Proxy;
import com.ntsky.bbs.service.UserProxy;
import com.ntsky.xml.bbs.XMLCreator;
import com.ntsky.xml.bbs.RolesXML;
import com.ntsky.datasource.DBConnectionManager;

/**
 * <p>Title: Ntsky OpenSource BBS</p>
 * <p>Description: 用户数据处理</p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: WWW.FM880.COM</p>
 * @author 姚君林
 * @version 1.0
 */


public class DBUserFactory extends UserFactory{

    private final static Logger logger = Logger.getLogger(DBUserFactory.class);

    private final static String IS_EXIST_USER = "SELECT * FROM t_user WHERE usrName=?;";
    private final static String CREATE_USER = "INSERT INTO t_user(usrName,usrSex,usrPasswd,usrEmail,usrQuestion,usrAnswer,usrRole,usrFace,usrRegTime,usrLastTime) VALUES(?,?,?,?,?,?,1,'/images/headpho/1.gif',?,?);";
    private final static String USER_LOGON = "SELECT usrId FROM t_user WHERE usrName=? and usrPasswd=?;";
    private final static String USER_INFO_USRNAME = "SELECT * FROM t_user WHERE usrName=?;";
    private final static String USER_INFO_USRID = "SELECT * FROM t_user WHERE usrId=?;";
    private final static String EDIT_USER = "UPDATE t_user SET usrEmail=?,usrQuestion=?,usrAnswer=?,usrIsOpen=?,usrRealName=?,usrBirthDay=?,usrFace=?,usrUrl=?,usrQicq=?,usrMSN=?,usrIdiograph=?,usrAddr=?,usrOccupation=?,usrEducation=?,usrInfo=? WHERE usrId=?;";
    private final static String NEWEST_USER = "SELECT * FROM t_user ORDER BY usrRegTime DESC LIMIT 0,1;";
    private final static String SUM_USER = "SELECT usrId FROM t_user;";
    private final static String UP_LOGON_INFO_USRID = "UPDATE t_user SET usrLoadTime=usrLoadTime+1,usrLastTime=? WHERE usrId=?;";
    private final static String UP_LOGON_INFO_USRNAME = "UPDATE t_user SET usrLoadTime=usrLoadTime+1,usrLastTime=? WHERE usrName=?;";
    private final static String SEL_ROLE_USER = "SELECT usrId,usrName FROM t_user WHERE usrRole=?;";
    private final static String UP_USER_ROLES="UPDATE t_user SET usrRole=? WHERE usrId=?;";
    //更新信息
    private final static String UP_USER_WEALTH = "UPDATE t_user SET usrWealth=usrWealth+2 WHERE usrName=?;";
    private final static String UP_USER_TOPIC = "UPDATE t_user SET usrTopic=usrTopic+1 WHERE usrName=?;";
    private final static String USER_WEALTH = "SELECT usrWealth FROM t_user WHERE usrName=?";

    private final static String IS_UP_USER = "SELECT usrWealth FROM t_user WHERE usrName=?;";
    private final static String UP_USER_REPLY = "UPDATE t_user SET usrReTopic=usrReTopic+1,usrWealth=usrWealth+1 WHERE usrName=?; ";

    private final static String USER_GOOD = "UPDATE t_user SET usrWealth=usrWealth+3,usrEliteTopic=usrEliteTopic+1 WHERE usrName=?;";

    //删除用户信息(将用户状态设置为不可见)
    private final static String CHANGE_USER_STATE = "UPDATE t_user SET usrIsDelete=1 WHERE usrId=?;";

    private final static String DEL_USER = "DELETE FROM t_user WHERE usrId=?;";
    private final static String UP_LOCK_USER = "UPDATE t_user SET usrIsDelete=0 WHERE usrId=?;";

    private final static String UP_USER_ROLE = "UPDATE t_user SET usrRole=? WHERE usrName=?;";
    private final static String UP_ROLES = "UPDATE t_user SET usrRole=? WHERE usrName=?;";

    /**
     * 更新用户角色
     * @param usrRole String
     * @param usrName String
     */
    public void upUserRole(String usrRole ,String usrName) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UP_USER_ROLE);
            pstm.setString(1,usrRole);
            pstm.setString(2, usrName);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("改变用户角色信息出错 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 更新回复主题时用户的信息
     * @param usrName String
     */
    public void upRepUserInfo(String usrName) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UP_USER_REPLY);
            pstm.setString(1, usrName);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("更新回复主题时用户的信息发生错误 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 解除用户锁定
     * @param usrId String
     */
    public void unLockUser(String usrId){
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UP_LOCK_USER);
            pstm.setString(1, usrId);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("解除用户锁定出错 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 删除用户信息(将用户状态设置为不可见)
     * @param usrId String
     */
    public void changeUserState(String usrId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(CHANGE_USER_STATE);
            pstm.setString(1, usrId);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("删除用户错误（改变用户状态错误） : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 删除用户，物理删除
     * @param usrName String
     */
    public void delUser(String usrId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(DEL_USER);
            pstm.setString(1, usrId);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("删除用户错误 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 更新体力值
     * @param usrName String
     */
    public void upUserWealthDre(String usrName){
       Connection conn = DBConnectionManager.getInstance().getConnection();
       PreparedStatement pstm = null;
       ResultSet rs = null;
       String UP_USER_WEALTH1 = "UPDATE t_user SET usrWealth=usrWealth-3 WHERE usrName=?;";
       String UP_USER_WEALTH2 = "UPDATE t_user SET usrWealth=0 WHERE usrName=?;";
       try {
           pstm = conn.prepareStatement(IS_UP_USER);
           pstm.setString(1,usrName);
           rs = pstm.executeQuery();
           rs.next();
           int usrWealth = rs.getInt("usrWealth");
           rs.close();
           pstm.close();
           if(usrWealth<3){
               pstm = conn.prepareStatement(UP_USER_WEALTH2);
               pstm.setString(1,usrName);
               pstm.executeUpdate();
               pstm.close();
           }
           else{
               pstm = conn.prepareStatement(UP_USER_WEALTH1);
               pstm.setString(1,usrName);
               pstm.executeUpdate();
               pstm.close();
           }
       }
       catch (Exception ex) {
           logger.error("获取用户总数发生错误 : " + ex.getMessage());
       }
       finally{
           try {
               pstm.close();
           }
           catch (Exception ex) {
               logger.error("关闭 pstm 失败 error : " + ex.getMessage());
           }
           try {
               conn.close();
           }
           catch (Exception ex) {
               logger.error("关闭数据库连接失败 error : " +ex.getMessage());
           }
       }
   }


    /**
     * 获取用户总数
     * @return User
     */
    private int sumUser(){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int sumUser = 0;
        try {
            pstm = conn.prepareStatement(SUM_USER);
            rs = pstm.executeQuery();
            if(!rs.next()){
                sumUser = 0;
            }
            else{
                rs.last();
                sumUser = rs.getRow();
            }
        }
        catch (Exception ex) {
            logger.error("获取用户总数发生错误 : " + ex.getMessage());
        }
        finally{
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
        return sumUser;
    }

    /**
     * 最新注册用户信息
     */
    public User getNewestUser(){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = new DBUser();
        try {
            pstm = conn.prepareStatement(NEWEST_USER);
            rs = pstm.executeQuery();
            if(rs.next()){
                user.setIsUserExist(true);
                user.setUsrId(rs.getInt("usrId"));
                user.setUsrName(rs.getString("usrName"));
                user.setUsrPasswd(rs.getString("usrPasswd"));
                user.setUsrSex(rs.getString("usrSex"));
                user.setUsrQuestion(rs.getString("usrQuestion"));
                user.setUsrEmail(rs.getString("usrEmail"));
                user.setUsrAnswer(rs.getString("usrAnswer"));
                String usrRole = rs.getString("usrRole");
                //user.setUsrRoleMap(getStrMap(usrRole));
                user.setUsrRole(usrRole);
                /**
                 * 返回map集合
                 */
                user.setUsrIsOpen(rs.getString("usrIsOpen"));
                user.setUsrRealName(rs.getString("usrRealName"));
                user.setUsrBirthDay(rs.getString("usrBirthDay"));
                user.setUsrFace(rs.getString("usrFace"));
                user.setUsrUrl(rs.getString("usrUrl"));
                user.setUsrQicq(rs.getString("usrQicq"));
                user.setUsrMSN(rs.getString("usrMSN"));
                user.setUsrIdiograph(rs.getString("usrIdiograph"));
                user.setUsrAddr(rs.getString("usrAddr"));
                user.setUsrOccupation(rs.getString("usrOccupation"));
                user.setUsrEducation(rs.getString("usrEducation"));
                user.setUsrInfo(rs.getString("usrInfo"));
                user.setUsrWealth(rs.getString("usrWealth"));
                user.setUsrLoadTime(rs.getString("usrLoadTime"));
                user.setUsrRegTime(rs.getString("usrRegTime"));
                user.setUsrLastTime(rs.getString("usrLastTime"));
                user.setUsrTopic(rs.getInt("usrTopic"));
                user.setUsrReTopic(rs.getInt("usrReTopic"));
                user.setUsrDelTopic(rs.getInt("usrDelTopic"));
                user.setUsrEliteTopic(rs.getInt("usrEliteTopic"));
                /**
                 * 论坛总注册用户
                 */
                user.setSumUser(sumUser());
            }
        }
        catch (Exception ex) {
            logger.error("创建论坛用户发生错误 : " + ex.getMessage());
        }
        finally{
            try {
                pstm.close();