userdao.java

EasyJForum 是一个基于 Java 技术的免费社区论坛软件系统

                                                                    throws Exception
    {
        String result = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try
        {
            String userID = PageUtils.getParam(request,"userID");
            String passwd = PageUtils.getParam(request,"pwd");
            
            conn = dbManager.getConnection();
            pstmt = conn.prepareStatement(adapter.User_Login);
            pstmt.setString(1, userID);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                char state = rs.getString(2).charAt(0);
                if (state == 'N' || state == 'P') // Normal, Seal Posting
                {
                    int loginCount = rs.getInt(3);
                    Timestamp updateTime = rs.getTimestamp(4);
                    if (updateTime == null
                            || updateTime.getTime() < (System.currentTimeMillis()-86400000)) // 24*3600*1000
                    {
                        loginCount = 0;  // Unlock user
                    }
                    else if (loginCount >= 5)
                    {
                        result = "帐户已经被锁定，请在24小时后再尝试登录";
                        return result;
                    }
                    
                    String digest = AppUtils.digestData(passwd);
                    String pwd = rs.getString(1);
                    if (pwd != null && pwd.equals(digest))
                    {
                        result = "OK";
                        loginCount = 0;

                        UserInfo userinfo = getUserInfo(userID, rs);
                        if (userinfo != null)
                            userinfo.remoteIP = request.getRemoteAddr();
                        
                        HttpSession sess = request.getSession();
                        // sess.setMaxInactiveInterval(1200);  //1200s, 20 minutes
                        sess.setAttribute("userinfo", userinfo);
                        
                        Set<HttpSession> sessions = AppContext.getInstance().getSessions();
                        sessions.add(sess);
                        
                        String cookietime = request.getParameter("cookietime");
                        long loginExpire = 0;
                        try
                        {
                            loginExpire = Long.parseLong(cookietime)*1000; // Convert to milliseconds
                        }
                        catch(Exception e){ /* Ignored */ }
                        
                        if (loginExpire > 0)
                        {
                            loginExpire = System.currentTimeMillis() + loginExpire;
                        }
                        
                        String ejf_lsessid = 
                            AppUtils.encode32(String.valueOf(loginExpire) + "|" + userID);
                        
                        Cookie c = new Cookie("ejf_lsessid", ejf_lsessid); 
                        if (loginExpire > 0)
                            c.setMaxAge(86400000);  // 24*3600*1000, 1000 days
                        else
                            c.setMaxAge(0);
                        c.setPath(request.getContextPath());
                        response.addCookie(c);
                        
                        Timestamp stamp = new Timestamp(loginExpire);
                        
                        ArrayList<Object> paramList = new ArrayList<Object>();
                        paramList.add(loginCount);
                        paramList.add(stamp);
                        paramList.add(userID);
                        this.execUpdateSql(adapter.User_ModLoginExpire, paramList, conn);
                    }
                    else
                    {
                        loginCount = loginCount + 1;
                        if (loginCount >= 5)
                        {
                            result = "帐户已被锁定，请在24小时后再尝试登录";
                        }
                        else
                        {
                            result = "用户名和密码不匹配，您还可以尝试" 
                                   + String.valueOf(5-loginCount) + "次";
                        }
                        ArrayList<Object> paramList = new ArrayList<Object>();
                        paramList.add(String.valueOf(loginCount));
                        paramList.add(userID);
                        this.execUpdateSql(adapter.User_ModLoginCount, paramList, conn);
                    }
                }
                else
                {
                    result = "此用户尚未激活或已被封锁";
                }
            }
            else
            {
                result = "用户名不存在";
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmt);
            dbManager.closeConnection(conn);
        }
        return result;
    }

    /**
     * Do user logout
     * @param 
     *      request - HttpServletRequest
     *      response - HttpServletResponse
     * @return none
     * @throws none
     * @since 1.0
     */
    public void doLogout(HttpServletRequest request, HttpServletResponse response) 
    {
        HttpSession sess = request.getSession(false);
        if (sess != null)
            sess.invalidate();
        
        Cookie c = new Cookie("ejf_lsessid", null);   
        c.setMaxAge(0);
        c.setPath(request.getContextPath());
        response.addCookie(c);   
    }
    
    /**
     * Quick login when session is keeped in cookie 
     * @param 
     *      loginUser - last login user ID
     *      loginExpire - login expire time
     * @return UserInfo
     * @throws Exception
     * @since 1.0
     */
    public UserInfo doQuickLogin(String loginUser, long loginExpire, 
                                               HttpServletRequest request) throws Exception
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        UserInfo userinfo = null;
        try
        {
            conn = dbManager.getConnection();
            pstmt = conn.prepareStatement(adapter.User_QuickLogin);
            pstmt.setString(1, loginUser);
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                Timestamp loginStamp = rs.getTimestamp(1);
                if (loginStamp != null && loginStamp.getTime()/1000 == loginExpire/1000) // Trim millis, Client & Server login id must be same
                {
                    userinfo = getUserInfo(loginUser, rs);
                    if (userinfo != null)
                        userinfo.remoteIP = request.getRemoteAddr();
                    // Re-construct session
                    HttpSession sess = request.getSession();
                    // sess.setMaxInactiveInterval(1200);  //1200s, 20 minutes
                    sess.setAttribute("userinfo", userinfo);

                    Set<HttpSession> sessions = AppContext.getInstance().getSessions();
                    sessions.add(sess);
                    
                    ArrayList<Object> paramList = new ArrayList<Object>();
                    paramList.add(loginUser);
                    this.execUpdateSql(adapter.User_ModLastVisited, paramList, conn);
                }
            }
            return userinfo;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmt);
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Identify if user ID is existed in DB
     * @param 
     *      userID - user ID
     * @return true or false
     * @throws SQLException
     * @since 1.0
     */
    public boolean isExistedID(String userID) throws SQLException
    {
        Connection conn = null;
        try
        {
            conn = dbManager.getConnection();
            return isExistedID(userID, conn);
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }

    public boolean isExistedID(String userID, Connection conn) throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.User_IsExistedID);
            pstmtQuery.setString(1, userID);
            rs = pstmtQuery.executeQuery();
            
            boolean result = false;
            if(rs.next())
            {
                if (rs.getInt(1) >= 1)
                {
                    result = true;
                }
            }
            return result;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }

    /**
     * Identify if user email is existed in DB
     * @param 
     *      email - user email
     * @return true or false
     * @throws SQLException
     * @since 1.0
     */
    public boolean isExistedMail(String email) throws SQLException
    {
        Connection conn = null;
        try
        {
            conn = dbManager.getConnection();
            return isExistedMail(email, conn);
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }

    private boolean isExistedMail(String email, Connection conn) throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.User_IsExistedMail);
            pstmtQuery.setString(1, email);
            rs = pstmtQuery.executeQuery();
            
            boolean result = false;
            
            if(rs.next())
            {
                if (rs.getInt(1) >= 1)
                {
                    result = true;
                }
            }
            return result;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }

    private boolean isExistedMail(String email, String userID, Connection conn) 
                                                            throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.User_GetIDFromMail);
            pstmtQuery.setString(1, email);
            rs = pstmtQuery.executeQuery();
            
            boolean result = false;
            
            if(rs.next())
            {
                if (!rs.getString(1).equalsIgnoreCase(userID))
                {
                    result = true;
                }
            }
            return result;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }
    
    public String[] getEmailsFromIDs(String[] userIDs, Connection conn) 
                                                    throws SQLException
    {
        String inSql = "select userID,email from ejf_user where userID in";
        StringBuilder sbuf = new StringBuilder(inSql);
        sbuf.append(" (");
        for (int i=0; i<userIDs.length; i++)
        {
            if (i > 0) sbuf.append(",");
            sbuf.append("'").append(userIDs[i]).append("'");
        }
        sbuf.append(")");

        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            String[] emails = new String[userIDs.length];

            pstmtQuery = conn.prepareStatement(sbuf.toString());
            rs = pstmtQuery.executeQuery();
            
            while(rs.next())
            {
                for (int i=0; i<userIDs.length; i++)
                {
                    if (userIDs[i].equalsIgnoreCase(rs.getString(1)))
                    {
                        emails[i] = rs.getString(2);
                        break;
                    }
                }
            }
            return emails;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }