bookmarkdao.java

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

package com.hongshee.ejforum.data;

/**
 * <p>Title: BookmarkDAO.java</p>
 * <p>Description: Bookmark data access object</p>
 * <p>Copyright: Copyright (c) 2007</p>
 * <p>Company: hongshee</p>
 * @author jackie
 * @version 1.0
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;

import com.hongshee.ejforum.common.ForumSetting;
import com.hongshee.ejforum.data.UserDAO.UserInfo;
import com.hongshee.ejforum.util.PageUtils;
import com.hongshee.ejforum.util.AppUtils;

public class BookmarkDAO extends EntityDAO
{
    private static BookmarkDAO _dao = null;

    protected BookmarkDAO()
    {}

    public static BookmarkDAO getInstance()
    {
        if (_dao == null)
        {
            _dao = new BookmarkDAO();
        }
        return _dao;
    } 
        
    /**
     * 添加一个论坛用户书签
     * @param 
     *      request - HttpServletRequest
     * @return 无
     * @throws SQLException
     * @since 1.0
     */
    public String addBookmark(HttpServletRequest request, UserInfo userinfo) throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtInsert = null;
        try
        {
            String subject = PageUtils.getParam(request,"subject");
            subject = subject.replace("<", "&lt;");
            subject = subject.replace(">", "&gt;");
            
            conn = dbManager.getConnection();
            pstmtInsert = conn.prepareStatement(adapter.Bookmark_Insert);
            pstmtInsert.setString(1, userinfo.userID);
            pstmtInsert.setString(2, PageUtils.getParam(request,"url"));
            pstmtInsert.setString(3, subject);
            pstmtInsert.setString(4, PageUtils.getParam(request,"board"));
            pstmtInsert.executeUpdate();
            
            return "OK";
        }
        finally
        {
            dbManager.closePStatement(pstmtInsert);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * 查询论坛用户书签列表
     * @param 
     *      userID - 论坛用户ID
     * @return 论坛书签列表
     * @throws SQLException
     * @since 1.0
     */
    public Object[] getBookmarks(String userID,
                                 int pageNo, int pageRows) throws SQLException
    {
        Object[] result = new Object[2];
        String querySql = adapter.Bookmark_GetList;
        String countSql = adapter.Bookmark_GetCount;
        
        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<BookmarkVO> markList = new ArrayList<BookmarkVO>();
                BookmarkVO aMark = null;
            
                while(rs.next())
                {
                    aMark = new BookmarkVO();
                    aMark.userID = userID;
                    aMark.markID = rs.getString("markID");
                    aMark.url = rs.getString("url");
                    aMark.title = rs.getString("title");
                    aMark.boardName = rs.getString("boardName");
                    aMark.createTime = AppUtils.formatSQLTimeStr(rs.getTimestamp("createTime"));

                    markList.add(aMark);
                }
                result[1] = markList;
            }
        }
        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;
    }

    /**
     * 删除论坛用户书签
     * @param 
     *      markID - 书签ID
     * @return 无
     * @throws SQLException
     * @since 1.0
     */
    public void deleteBookmarks(HttpServletRequest request) throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtUpdate = null;
        try
        {
            String[] markIDs = request.getParameterValues("markID");
            
            conn = dbManager.getConnection();
            pstmtUpdate = conn.prepareStatement(adapter.Bookmark_Delete);
                
            for (int i=0; i<markIDs.length; i++)
            {
                pstmtUpdate.setString(1, markIDs[i]);
                pstmtUpdate.addBatch();
            }
            pstmtUpdate.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
    }

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

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

            String sql = adapter.getPageQuerySql(
                            new StringBuilder(adapter.Bookmark_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.Bookmark_cleanOverflow);
                    pstmtUpdate.setString(1, userID);
                    pstmtUpdate.setString(2, createTime);
                    pstmtUpdate.executeUpdate();
                }
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closePStatement(pstmtUpdate);
        }
    }
    
    public static class BookmarkVO
    {
        public String markID = null;
        public String userID = null;
        public String url = null;
        public String title = null;
        public String boardName = null;
        public String createTime = null;
    }    
}