dbmanager.java

基于struct结构的jsp

package com.ntsky.bbs.service.db;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.log4j.Logger ;

import com.ntsky.util.*;
import com.ntsky.bbs.service.User;
import com.ntsky.bbs.service.Manager;
import com.ntsky.bbs.service.UserFactory;
import com.ntsky.datasource.DBConnectionManager;


/**
 * <p>Title: Ntsky OpenSource BBS</p>
 * <p>Description: 管理员用户的相关操作</p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: 天网文化发展有限公司</p>
 * @author 姚君林
 * @version 1.0
 */

public class DBManager implements Manager{
    private final static Logger logger = Logger.getLogger(DBManager.class);
    private final static String IS_EXIST_USER = "SELECT * FROM t_user WHERE usrName=?;" ;
    private final static String SET_ADMIN_PASSWORD = "INSERT INTO t_admin(adName,adPassWd,adFlag,adLastLogin,adLastLoginIp) VALUES(?,?,?,?,?);" ;
    private final static String IS_ADMIN_USER = "SELECT * FROM t_admin where adName=? and adPassWd=?;" ;
    private final static String UP_ADMIN_LOG = "UPDATE t_admin SET adLastLogin=?,adLastLoginIp=? WHERE adName=?;";
    private final static String SEL_ADMIN_LOG = "SELECT adLastLogin,adLastLoginIp FROM t_admin WHERE adName=?;";
    //安装成功更新用户角色
    private final static String UP_SETUPUSER_ROLES = "UPDATE t_user SET usrRole=-1 WHERE usrName=?;";
    private final static String SEL_ADMIN_ARRAY = "SELECT adName FROM t_admin;";

    /**
     * 管理员数组
     * @return List
     */
    public List AdminArray(){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        List list = new ArrayList();
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(SEL_ADMIN_ARRAY);
            rs = pstm.executeQuery();
            while(rs.next()){
                logger.info("管理用户有 : " + rs.getString("adName"));
                list.add(rs.getString("adName"));
            }
        }
        catch (Exception ex) {
            logger.error("获取管理用户数组错误 : " + ex.getMessage());
            ex.printStackTrace();
        }
        finally{
            try{ rs.close(); } catch(Exception e){ logger.error("关闭指针错误 error : " + e.getMessage()); }
            try { pstm.close(); } catch (Exception ex) { logger.error("关闭 pstm 失败 error : " + ex.getMessage()); }
            try { conn.close(); } catch (Exception ex) { logger.error("关闭数据库连接失败 error : " +ex.getMessage()); }
       }
       return list;
    }

    /**
     * 创建管理用户
     * @return boolean
     */
    public boolean insAdmin(String adName,String adPassWd,String adLastLoginIp){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        boolean temp = false;
        Timer timer = new TimerExpress("yyyy-MM-dd HH:mm:ss");
        try {
            pstm = conn.prepareStatement(IS_EXIST_USER);
            pstm.setString(1,adName);
            rs = pstm.executeQuery();
            if(!rs.next()){
                logger.info("该用户不存在,请先注册用户!!!");
                temp = true ;
                try {
                    rs.close();
                }
                catch(Exception ex){
                    logger.error("关闭 rs 失败 error : " + ex.getMessage());
                }
                try {
                    pstm.close();
                }
                catch (Exception ex) {
                    logger.error("关闭 pstm 失败 error : " + ex.getMessage());
                }
            }
            else{
                pstm = conn.prepareStatement(SET_ADMIN_PASSWORD);
                pstm.setString(1, adName);
                pstm.setString(2, MD5.toMD5(adPassWd));
                pstm.setInt(3, -1);
                pstm.setString(4, timer.getNowDate());
                logger.info("当前时间 : " + timer.getNowDate());
                pstm.setString(5, adLastLoginIp);
                pstm.executeUpdate();
                /**
                 * 更新用户权限
                 */
                upUserRole(adName);
                logger.info("成功设置管理员密码!");
            }
        }
        catch (SQLException ex) {
            logger.error("创建论坛管理员错误!");
            System.out.println("insAdmin " + 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 temp;
    }


    /**
     * 判断管理用户
     */
    public boolean isAdminUser(String adName,String adPassWd){
        boolean isSuccess = false;
        Connection conn = DBConnectionManager.getInstance().getConnection();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(IS_ADMIN_USER);
            pstm.setString(1,adName);
            pstm.setString(2,MD5.toMD5(adPassWd));
            rs = pstm.executeQuery();
            if(rs.next()){
                logger.info(" 成功登陆后台 ");
                isSuccess = true;
            }
        }
        catch (Exception ex) {
            logger.info("登陆论坛错误!");
            ex.printStackTrace();
        }
        finally{
            try{ rs.close(); } catch(Exception e){ logger.error("关闭指针错误 error : " + e.getMessage()); }
            try { pstm.close(); } catch (Exception ex) { logger.error("关闭 pstm 失败 error : " + ex.getMessage()); }
            try { conn.close(); } catch (Exception ex) { logger.error("关闭数据库连接失败 error : " +ex.getMessage()); }
       }

       return isSuccess;
    }

    /**
    * 更新用户日志
    */
   public void upAdminLog(String adName, String adLastLoginIp) {
       Connection conn = DBConnectionManager.getInstance().getConnection(
           );
       Timer timer = new TimerExpress("yyyy-MM-dd HH:mm:ss");
       PreparedStatement pstm = null;
       try {
           pstm = conn.prepareStatement(UP_ADMIN_LOG);
           pstm.setString(1, timer.getNowDate());
           pstm.setString(2, adLastLoginIp);
           pstm.setString(3, adName);
           pstm.executeUpdate();
       }
       catch (Exception ex) {
           logger.error("更新管理日志信息失败 : " + ex.getMessage());
           ex.printStackTrace();
       }
       finally {
           try {
               pstm.close();
           }
           catch (Exception ex) {
               logger.error("关闭 pstm 失败 error : " + ex.getMessage());
           }
           try {
               conn.close();
           }
           catch (Exception ex) {
               logger.error("关闭数据库连接失败 error : " + ex.getMessage());
           }
       }
   }
   /**
    * 管理员日志信息
    */
   public String[] selAdminLog(String adName) {
       Connection conn = DBConnectionManager.getInstance().getConnection(
           );
       ResultSet rs = null;
       PreparedStatement pstm = null;
       String[] string = new String[2];
       try {
           pstm = conn.prepareStatement(SEL_ADMIN_LOG);
           pstm.setString(1, adName);
           rs = pstm.executeQuery();
           rs.next();
           string[0] = rs.getString("adLastLogin");
           string[1] = rs.getString("adLastLoginIp");
       }
       catch (Exception ex) {
           logger.error("获取管理日志信息失败 : " + ex.getMessage());
           ex.printStackTrace();
       }
       finally {
           try {
               rs.close();
           }
           catch (Exception ex) {
               logger.error("关闭 rs 失败 error : " + ex.getMessage());
           }
           try {
               pstm.close();
           }
           catch (Exception ex) {
               logger.error("关闭 pstm 失败 error : " + ex.getMessage());
           }
           try {
               conn.close();
           }
           catch (Exception ex) {
               logger.error("关闭数据库连接失败 error : " + ex.getMessage());
           }
       }
       return string;
   }

   /**
    * 更新用户权限
    */
   private void upUserRole(String adName) {
       Connection conn = DBConnectionManager.getInstance().getConnection(
           );
       PreparedStatement pstm = null;
       try {
           pstm = conn.prepareStatement(UP_SETUPUSER_ROLES);
           pstm.setString(1, adName);
           pstm.executeUpdate();
       }
       catch (Exception ex) {
           logger.error("更新用户权限信息失败 : " + ex.getMessage());
           ex.printStackTrace();
       }
       finally {
           try {
               pstm.close();
           }
           catch (Exception ex) {
               logger.error("关闭 pstm 失败 error : " + ex.getMessage());
           }
           try {
               conn.close();
           }
           catch (Exception ex) {
               logger.error("关闭数据库连接失败 error : " + ex.getMessage());
           }
       }
   }
}