shortmsgdao.java

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

                this.execSelectCountSql(adapter.ShortMsg_GetUnreadCount, paramList, conn);
            
            paramList.add(0, count);
            this.execUpdateSql(adapter.User_StatUnreadSMs, paramList, conn);
            
            conn.commit();
            userinfo.unreadSMs = count;
        }
        catch(SQLException se)
        {
            conn.rollback();
            throw se;
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * Clean short messages that exceeds MAX SMS size of all users
     * @param none 
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void cleanOverflowMsgs() throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            int maxShortMsgs = 
                ForumSetting.getInstance().getInt(ForumSetting.MISC, "maxShortMsgs");
            
            conn = dbManager.getConnection();
            pstmtQuery = conn.prepareStatement(adapter.ShortMsg_StatUserCount);
            rs = pstmtQuery.executeQuery();
            
            ArrayList<String> users = null;
            while(rs.next())
            {
                if (rs.getInt("ct") > maxShortMsgs)
                {
                    if (users == null)
                        users = new ArrayList<String>();
                    users.add(rs.getString("userID"));
                }
            }

            if (users != null)
            {
                for (int i=0; i<users.size(); i++)
                {
                    cleanOverflowMsgs(users.get(i), maxShortMsgs, conn);
                }
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Clean short messages that exceeds MAX SMS size of an user
     * @param 
     *      userID - an user ID 
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    private void cleanOverflowMsgs(String userID, int maxShortMsgs, Connection conn) 
                                                                throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        PreparedStatement pstmtUpdate = null;
        ResultSet rs = null;
        try
        {
            int keepCount = maxShortMsgs / 2;
            int pageNo = keepCount;
            int pageRows = 1;

            String sql = adapter.getPageQuerySql(
                            new StringBuilder(adapter.ShortMsg_getOverflow),
                            pageNo, pageRows, keepCount);
            
            pstmtQuery = conn.prepareStatement(sql);
            pstmtQuery.setString(1,userID);
            rs = pstmtQuery.executeQuery();
            
            if(rs.next())
            {
                String createTime = rs.getString(1);
                if (createTime != null && createTime.length() > 0)
                {
                    pstmtUpdate = conn.prepareStatement(adapter.ShortMsg_cleanOverflow);
                    pstmtUpdate.setString(1, userID);
                    pstmtUpdate.setString(2, createTime);
                    pstmtUpdate.executeUpdate();
                }
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closePStatement(pstmtUpdate);
        }
    }
    
    /**
     * Get a short message content
     * @param 
     *      msgID - Message ID
     * @return Message content
     * @throws SQLException
     * @since 1.0
     */
    public String getMessage(HttpServletRequest request,
                             String msgID, boolean isInbox) throws Exception
    {
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            conn = dbManager.getConnection();
            pstmtQuery = conn.prepareStatement(adapter.ShortMsg_GetMessage);
            pstmtQuery.setString(1, msgID);
            rs = pstmtQuery.executeQuery();
            
            String result = null;
            if(rs.next())
            {
                result = rs.getString("message");

                char state = rs.getString("state").charAt(0);
                if (isInbox && state == 'N')
                {
                    setReadState(request, msgID, conn);
                }
            }
            if (result == null)
                result = "";
            return result;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * Get a short message
     * @param 
     *      msgID - Message ID
     * @return Message content
     * @throws SQLException
     * @since 1.0
     */
    public ShortMsgVO getShortMsg(String msgID) throws Exception
    {
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            conn = dbManager.getConnection();
            pstmtQuery = conn.prepareStatement(adapter.ShortMsg_Select);
            pstmtQuery.setString(1, msgID);
            rs = pstmtQuery.executeQuery();
            
            ShortMsgVO result = null;
            if(rs.next())
            {
                result = new ShortMsgVO();
                result.fromUser = rs.getString("fromUser");
                result.userID = rs.getString("userID");
                result.title = rs.getString("title");
                result.message = rs.getString("message");
                result.createTime = AppUtils.formatSQLTimeStr(rs.getTimestamp("createTime"));
            }
            return result;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Get short message list of user
     * @param 
     *      userID - User ID
     * @return Message list of user
     * @throws SQLException
     * @since 1.0
     */
    public Object[] getShortMsgs(String userID, String action,
                                 int pageNo, int pageRows) throws SQLException
    {
        Object[] result = new Object[2];
        String querySql = adapter.ShortMsg_GetList;
        String countSql = adapter.ShortMsg_GetCount;
        
        if (action != null && action.equals("outbox"))
        {
            querySql = adapter.ShortMsg_GetOutList;
            countSql = adapter.ShortMsg_GetOutCount;
        }
        
        int totalCount = 0;
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            conn = dbManager.getConnection();
            pstmtQuery = conn.prepareStatement(countSql);
            pstmtQuery.setString(1, userID);
            rs = pstmtQuery.executeQuery();
            if(rs.next())
            {
                totalCount = rs.getInt(1);
            }
            
            if (totalCount > 0)
            {
                dbManager.closeResultSet(rs);
                dbManager.closePStatement(pstmtQuery);
             
                querySql = adapter.getPageQuerySql(
                            new StringBuilder(querySql), pageNo, pageRows, totalCount);
            
                pstmtQuery = conn.prepareStatement(querySql);
                pstmtQuery.setString(1,userID);
                rs = pstmtQuery.executeQuery();
            
                ArrayList<ShortMsgVO> msgList = new ArrayList<ShortMsgVO>();
                ShortMsgVO aMsg = null;
            
                while(rs.next())
                {
                    aMsg = new ShortMsgVO();
                    if (action != null && action.equals("outbox"))
                    {
                        aMsg.userID = rs.getString("userID");
                        aMsg.fromUser = userID;
                    }
                    else
                    {
                        aMsg.userID = userID;
                        aMsg.fromUser = rs.getString("fromUser");
                    }
                    aMsg.msgID = rs.getString("msgID");
                    aMsg.title = rs.getString("title");
                    aMsg.nickname = rs.getString("nickname");
                    aMsg.state = rs.getString("state").charAt(0);
                    aMsg.createTime = AppUtils.formatSQLTimeStr(rs.getTimestamp("createTime"));

                    msgList.add(aMsg);
                }
                result[1] = msgList;
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
        // Get result page code
        if (totalCount > 0)
        {
            int pageCount = (totalCount - 1) / pageRows + 1;
            if (pageCount <= 1) return result;
            result[0] = PageUtils.getPageHTMLStr(totalCount, pageNo, pageRows, 0);
        }        
        return result;
    }

    public static class ShortMsgVO
    {
        public String msgID = null;
        public String title = null;
        public String message = null;
        public String userID = null;
        public String fromUser = null;
        public String nickname = null;
        public char state = 'N';
        public String createTime = null;
    }    
}