userdao.java

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

        finally
        {
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
        // Send notice
        if (userList != null && userList.size() > 0)
        {
            this.sendNotice(request, userList);
        }
    }

    /**
     * Batch decrease users' posts and credits
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void decPostsAndCredits(String[] users, int[] attaches, 
                                   UserInfo userinfo, int postCredits, 
                                   Connection conn) throws Exception
    {
        PreparedStatement pstmtUpdate = null;
        try
        {
            int attachCredits = ForumSetting.getInstance().getInt(ForumSetting.CREDITS, "upload");
            pstmtUpdate = conn.prepareStatement(adapter.User_DecPostsAndCredits);
            for (int i=0; i<users.length; i++)
            {
                if (users[i] == null || users[i].trim().length() == 0)
                    continue;
                if (attaches[i] == 0) 
                    continue;
                pstmtUpdate.setInt(1, postCredits + attaches[i] * attachCredits);
                pstmtUpdate.setString(2, users[i]);
                pstmtUpdate.addBatch();
                
                if (userinfo != null && userinfo.userID.equalsIgnoreCase(users[i]))
                {
                    userinfo.credits = 
                        userinfo.credits - postCredits - attaches[i] * attachCredits;
                }
            }
            pstmtUpdate.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
        }
    }

    /**
     * Batch modify users' credits
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void modifyCredits(String[] users, int credits, 
                              Connection conn) throws Exception
    {
        PreparedStatement pstmtUpdate = null;
        try
        {
            if (credits > 0)
                pstmtUpdate = conn.prepareStatement(adapter.User_IncCredits);
            else
                pstmtUpdate = conn.prepareStatement(adapter.User_DecCredits);
            
            for (int i=0; i<users.length; i++)
            {
                if (users[i] == null || users[i].trim().length() == 0)
                    continue;
                pstmtUpdate.setInt(1, credits);
                pstmtUpdate.setString(2, users[i]);
                pstmtUpdate.addBatch();
            }
            pstmtUpdate.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
        }
    }
    
    /**
     * Build search user where sql clause by query conditions
     * @param 
     *      request - HttpServletRequest
     *      paramValues - Array list to be filled with search parameter
     * @return Where sql string
     * @throws none
     * @since 1.0
     */
    private String buildSearchWhereSql(HttpServletRequest request, ArrayList<Object> paramValues)
    {
        StringBuilder whereSql = new StringBuilder(" where 1=1");
            
        String userID = PageUtils.getParam(request, "userID");
        if (userID != null && userID.length() > 0)
        {
            if (userID.indexOf('*') >= 0)
            {
                whereSql.append(" and userID like ?");
                paramValues.add(userID.replace('*', '%'));
            }
            else
            {
                whereSql.append(" and userID=?");
                paramValues.add(userID);
            }
        }
            
        String state = PageUtils.getParam(request, "state");
        if (state != null && state.length() > 0)
        {
            whereSql.append(" and state=?");
            paramValues.add(state);
        }
            
        String[] groupIDs = request.getParameterValues("groupID");
        if (groupIDs != null && groupIDs.length > 0)
        {
            if (groupIDs.length > 1 || groupIDs[0].length() > 0) // not is '无限制'
            {
                whereSql.append(" and (1=0");
                String[] credits = null;
                for (int i=0; i<groupIDs.length; i++)
                {
                    if (groupIDs[i].length() == 0) continue;
                    if (groupIDs[i].length() == 1)
                    {
                        whereSql.append(" or groupID=?");
                        paramValues.add(groupIDs[i]);
                    }
                    else if (groupIDs[i].indexOf('_') > 0)
                    {
                        credits = groupIDs[i].split("_");
                        whereSql.append(" or (credits>=? and credits<?)");
                        paramValues.add(credits[0]);
                        paramValues.add(credits[1]);
                    }
                }
                whereSql.append(")");
            }
        }
            
        String advanceOptions = request.getParameter("advanceOptions");
        if (advanceOptions.equalsIgnoreCase("yes"))
        {
            String email = PageUtils.getParam(request, "email");
            if (email != null && email.length() > 0)
            {
                if (email.indexOf('*') >= 0)
                {
                    whereSql.append(" and email like ?");
                    paramValues.add(email.replace('*', '%'));
                }
                else
                {
                    whereSql.append(" and email=?");
                    paramValues.add(email);
                }
            }
                
            String maxCredits = PageUtils.getParam(request, "maxCredits");
            if (maxCredits != null && maxCredits.length() > 0)
            {
                try
                {
                    Integer.parseInt(maxCredits);  // Check integer
                    whereSql.append(" and credits<=?");
                    paramValues.add(maxCredits);
                }
                catch(Exception e){ /* Ignored */ }
            }
                
            String minCredits = PageUtils.getParam(request, "minCredits");
            if (minCredits != null && minCredits.length() > 0)
            {
                try
                {
                    Integer.parseInt(minCredits);  // Check integer
                    whereSql.append(" and credits>=?");
                    paramValues.add(minCredits);
                }
                catch(Exception e){ /* Ignored */ }
            }
                
            String maxPosts = PageUtils.getParam(request, "maxPosts");
            if (maxPosts != null && maxPosts.length() > 0)
            {
                try
                {
                    Integer.parseInt(maxPosts);  // Check integer
                    whereSql.append(" and posts>=?");
                    paramValues.add(maxPosts);
                }
                catch(Exception e){ /* Ignored */ }
            }

            String minPosts = PageUtils.getParam(request, "minPosts");
            if (minPosts != null && minPosts.length() > 0)
            {
                try
                {
                    Integer.parseInt(minPosts);  // Check integer
                    whereSql.append(" and posts>=?");
                    paramValues.add(minPosts);
                }
                catch(Exception e){ /* Ignored */ }
            }

            String remoteIP = PageUtils.getParam(request, "remoteIP");
            if (remoteIP != null && remoteIP.length() > 0)
            {
                whereSql.append(" and remoteIP like ?");
                paramValues.add(remoteIP + "%");
            }
                
            String maxCreateTime = PageUtils.getParam(request, "maxCreateTime");
            if (maxCreateTime != null && maxCreateTime.length() > 0)
            {
                try
                {
                    java.sql.Date createTime = java.sql.Date.valueOf(maxCreateTime);  // Check date format
                    whereSql.append(" and createTime<=?");
                    paramValues.add(createTime);
                }
                catch(Exception e){ /* Ignored */ }
            }
                
            String minCreateTime = PageUtils.getParam(request, "minCreateTime");
            if (minCreateTime != null && minCreateTime.length() > 0)
            {
                try
                {
                    java.sql.Date createTime = java.sql.Date.valueOf(minCreateTime);  // Check date format
                    whereSql.append(" and createTime>=?");
                    paramValues.add(createTime);
                }
                catch(Exception e){ /* Ignored */ }
            }

            String maxLastVisited = PageUtils.getParam(request, "maxLastVisited");
            if (maxLastVisited != null && maxLastVisited.length() > 0)
            {
                try
                {
                    java.sql.Date lastVisited = java.sql.Date.valueOf(maxLastVisited);  // Check date format
                    whereSql.append(" and lastVisited<=?");
                    paramValues.add(lastVisited);
                }
                catch(Exception e){ /* Ignored */ }
            }

            String minLastVisited = PageUtils.getParam(request, "minLastVisited");
            if (minLastVisited != null && minLastVisited.length() > 0)
            {
                try
                {
                    java.sql.Date lastVisited = java.sql.Date.valueOf(minLastVisited);  // Check date format
                    whereSql.append(" and lastVisited>=?");
                    paramValues.add(lastVisited);
                }
                catch(Exception e){ /* Ignored */ }
            }
        }
        return whereSql.toString();
    }
    
    /**
     * Search user by query conditions
     * @param 
     *      request - HttpServletRequest
     *      userList - User info list
     * @return Search result count
     * @throws SQLException
     * @since 1.0
     */
    public int searchUser(HttpServletRequest request, ArrayList<UserInfo> userList) 
                                                                throws SQLException
    {
        int totalCount = 0;
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            int resultCount = 50;
            try
            {
                resultCount = Integer.parseInt(PageUtils.getParam(request, "resultCount"));
            }
            catch(Exception e){ /* Ignored */ }

            int pageNo = 1;
            int pageRows = resultCount;

            ArrayList<Object> paramValues = new ArrayList<Object>();
            String whereSql = this.buildSearchWhereSql(request, paramValues); 
            
            String countSql = "select COUNT(*) from ejf_user" + whereSql;  
            
            // Get search result count first
            conn = dbManager.getConnection();
            totalCount = this.execSelectCountSql(countSql, paramValues, conn);
            
            if (totalCount > 0)
            {
                // Do search, only get 1 page
                String querySql = adapter.getPageQuerySql(
                                    new StringBuilder(adapter.User_QueryInfo + whereSql),
                                    pageNo, pageRows, totalCount);
                
                pstmtQuery = conn.prepareStatement(querySql);
                for (int i=0; i<paramValues.size(); i++)
                {
                    pstmtQuery.setObject(i+1, paramValues.get(i));
                }
                rs = pstmtQuery.executeQuery();
            
                UserInfo userinfo = null;
                while(rs.next())
                {
                    userinfo = new UserInfo();
                    userinfo.userID = rs.getString("userID");
                    userinfo.nickname = rs.getString("nickname");
                    userinfo.posts = rs.getInt("posts");
                    userinfo.credits = rs.getInt("credits");
                    userinfo.groupID = rs.getString("groupID").charAt(0);
                    userinfo.state = rs.getString("state").charAt(0);
                    userList.add(userinfo);
                }
            }
            return totalCount;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * Search user by query conditions
     * @param 
     *      request - HttpServletRequest
     * @return Search result count
     * @throws SQLException
     * @since 1.0
     */
    public int searchUser(HttpServletRequest request) throws SQLException
    {
        Connection conn = null;
        try
        {
            ArrayList<Object> paramValues