attachdao.java

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

package com.hongshee.ejforum.data;

/**
 * <p>Title: AttachDAO.java</p>
 * <p>Description: Attachment data access object</p>
 * <p>Copyright: Hongshee Software (c) 2008</p>
 * @author jackie du
 * @version 1.0
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.io.File;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.hongshee.ejforum.common.AppContext;
import com.hongshee.ejforum.common.ForumSetting;
import com.hongshee.ejforum.common.AppException;
import com.hongshee.ejforum.common.IConstants;
import com.hongshee.ejforum.data.GroupDAO.GroupVO;
import com.hongshee.ejforum.data.UserDAO.UserInfo;
import com.hongshee.ejforum.util.AppUtils;
import com.hongshee.ejforum.util.PageUtils;
import com.hongshee.ejforum.util.MyFileUpload.UploadVO;

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

    protected AttachDAO()
    {}

    public static AttachDAO getInstance()
    {
        if (_dao == null)
        {
            _dao = new AttachDAO();
        }
        return _dao;
    } 
        
    /**
     * Add attachments to DB
     * @param 
     *      request - HttpServletRequest
     *      userID - User ID
     *      topicID - Topic ID
     *      replyID - Reply ID
     *      conn - DB Connection
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void addAttaches(ArrayList<UploadVO> attaches, String userID,
                            String topicID, String replyID, Connection conn) throws SQLException
    {
        PreparedStatement pstmtInsert = null;
        try
        {
            UploadVO aFile = null;
            pstmtInsert = conn.prepareStatement(adapter.Attach_Insert);
            for (int i=0; i<attaches.size(); i++)
            {
                aFile = attaches.get(i);
                pstmtInsert.setString(1, topicID);
                pstmtInsert.setString(2, replyID);
                pstmtInsert.setString(3, userID);
                pstmtInsert.setString(4, aFile.localname);
                pstmtInsert.setString(5, aFile.localID);
                pstmtInsert.setString(6, aFile.filename);
                pstmtInsert.setLong(7, aFile.filesize);
                pstmtInsert.setInt(8, aFile.credits);
                pstmtInsert.setString(9, aFile.title);
                pstmtInsert.setString(10, String.valueOf(aFile.state));
                pstmtInsert.addBatch();
            }
            pstmtInsert.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtInsert);
        }
    }

    /**
     * Update attachments to DB
     * @param 
     *      request - HttpServletRequest
     *      userID - User ID
     *      topicID - Topic ID
     *      replyID - Reply ID
     *      conn - DB Connection
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void updateAttaches(ArrayList<UploadVO> attaches, String userID,
                               String topicID, String replyID, Connection conn) throws SQLException
    {
        PreparedStatement pstmtUpdate = null;
        try
        {
            UploadVO aFile = null;
            ArrayList<UploadVO> insertAttaches = null;
            ArrayList<UploadVO> updateAttaches = null;
            
            if (attaches != null)
            {
                for (int i=0; i<attaches.size(); i++)
                {
                    aFile = attaches.get(i);
                    if (aFile.attachID != null && aFile.attachID.length() > 0)
                    {
                        if (updateAttaches == null)
                            updateAttaches = new ArrayList<UploadVO>();
                        updateAttaches.add(aFile);
                    }
                    else
                    {
                        if (insertAttaches == null)
                            insertAttaches = new ArrayList<UploadVO>();
                        insertAttaches.add(aFile);
                    }
                }
            }
            
            ArrayList<Object> paramValues = new ArrayList<Object>();
            paramValues.add(topicID);
            paramValues.add(replyID);
            this.execUpdateSql(adapter.Attach_RemoveByPost, paramValues, conn);
            
            if (insertAttaches != null && insertAttaches.size() > 0)
                addAttaches(insertAttaches, userID, topicID, replyID, conn);
            
            if (updateAttaches != null && updateAttaches.size() > 0)
            {
                pstmtUpdate = conn.prepareStatement(adapter.Attach_Update);
                for (int i=0; i<updateAttaches.size(); i++)
                {
                    aFile = updateAttaches.get(i);
                    pstmtUpdate.setInt(1, aFile.credits);
                    pstmtUpdate.setString(2, aFile.title);
                    pstmtUpdate.setString(3, String.valueOf(aFile.state));
                    pstmtUpdate.setString(4, aFile.attachID);
                    pstmtUpdate.addBatch();
                }
                pstmtUpdate.executeBatch();
            }
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
        }
    }
    
    /**
     * Get attachments of a topic and its replies
     * @param 
     *      topicID - Topic ID
     * @return Attachment list of post
     * @throws SQLException
     * @since 1.0
     */
    public ArrayList<AttachVO> getAttaches(String topicID, Connection conn)
                                                       throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.Attach_GetList);
            pstmtQuery.setString(1, topicID);
            rs = pstmtQuery.executeQuery();
            
            ArrayList<AttachVO> attaches = null;
            if(rs.next())
            {
                attaches = new ArrayList<AttachVO>();
                AttachVO aAttach = null;
                do
                {
                    aAttach = new AttachVO();
                    aAttach.topicID = topicID;
                    aAttach.replyID = rs.getString("replyID");
                    aAttach.attachID = rs.getString("attachID");
                    aAttach.localID = rs.getString("localID");
                    aAttach.filename = rs.getString("filename");
                    aAttach.filesize = rs.getInt("filesize");
                    aAttach.credits = rs.getInt("credits");
                    aAttach.title = rs.getString("title");
                    aAttach.downloads = rs.getInt("downloads");
                    aAttach.createTime = AppUtils.formatSQLTimeStr(rs.getTimestamp("createTime"));
                    
                    attaches.add(aAttach);
                }
                while(rs.next());
            }
            return attaches;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }

    /**
     * Get attachments of a topic or a reply
     * @param 
     *      topicID - Topic ID
     *      replyID - Reply ID
     * @return Attachment list of post
     * @throws SQLException
     * @since 1.0
     */
    public ArrayList<AttachVO> getAttaches(String topicID, String replyID, 
                                           Connection conn) throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.Attach_GetPostList);
            pstmtQuery.setString(1, topicID);
            pstmtQuery.setString(2, replyID);
            rs = pstmtQuery.executeQuery();
            
            ArrayList<AttachVO> attaches = null;
            if(rs.next())
            {
                attaches = new ArrayList<AttachVO>();
                AttachVO aAttach = null;
                do
                {
                    aAttach = new AttachVO();
                    aAttach.topicID = topicID;
                    aAttach.replyID = replyID;
                    aAttach.attachID = rs.getString("attachID");
                    aAttach.localID = rs.getString("localID");
                    aAttach.localname = rs.getString("localname");
                    aAttach.filename = rs.getString("filename");
                    aAttach.filesize = rs.getInt("filesize");
                    aAttach.credits = rs.getInt("credits");
                    aAttach.title = rs.getString("title");
                    aAttach.downloads = rs.getInt("downloads");
                    aAttach.createTime = AppUtils.formatSQLTimeStr(rs.getTimestamp("createTime"));
                    
                    attaches.add(aAttach);
                }
                while(rs.next());
            }
            return attaches;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }
    
    /**
     * Get an attachment
     * @param 
     *      attachID - Attachment ID
     *      request - HttpServletRequest
     * @return Attachment object
     * @throws SQLException
     * @since 1.0
     */
    public String[] downloadAttach(String attachID, HttpServletRequest request) throws Exception
    {
        Connection conn = dbManager.getConnection();
        try
        {
            AttachVO aAttach = getAttach(attachID, conn);
            if (aAttach == null)
                throw new AppException("此附件不存在，可能已经被删除。");
            
            HttpSession sess = request.getSession();
            String attachIDs = (String)sess.getAttribute("attachIDs");
            if (attachIDs == null || !attachIDs.contains("," + attachID + ",")) // Avoid to change credits repeatedly
            {
                UserInfo userinfo = PageUtils.getSessionUser(request);
                GroupVO aGroup = PageUtils.getGroupVO(userinfo);
                if (aGroup.rights.indexOf(IConstants.PERMIT_DOWNLOAD) < 0)
                    throw new AppException("您没有下载附件的权限。");
                
                if (userinfo == null || !userinfo.userID.equalsIgnoreCase(aAttach.userID)) // Exclude userself
                {
                    if (aAttach.credits <= 0)
                        aAttach.credits = 
                            Math.abs(ForumSetting.getInstance().getInt(ForumSetting.CREDITS, "download"));
                    if (aAttach.credits > 0)
                    {
                        if (userinfo == null || userinfo.credits < aAttach.credits)
                            throw new AppException("您的积分值不够，不能下载此附件。");
                        
                        ArrayList<Object> paramValues = new ArrayList<Object>();
                        paramValues.add(aAttach.credits);
                        paramValues.add(aAttach.userID);
                        this.execUpdateSql(adapter.User_IncCredits, paramValues, conn);
    
                        paramValues.clear();
                        paramValues.add(aAttach.credits);
                        paramValues.add(userinfo.userID);
                        this.execUpdateSql(adapter.User_DecCredits, paramValues, conn);
                        
                        userinfo.credits = userinfo.credits - aAttach.credits;
                        
                        ActionLogDAO.getInstance().addCreditsLog(
                                userinfo.userID, aAttach.userID, aAttach.credits, "下载附件", conn);
                    }
                    
                    ArrayList<Object> paramValues = new ArrayList<Object>();
                    paramValues.add(attachID);
                    this.execUpdateSql(adapter.Attach_IncDownloads, paramValues, conn);
                    
                    if (attachIDs == null)
                        sess.setAttribute("attachIDs", "," + attachID + ",");
                    else
                        sess.setAttribute("attachIDs", attachIDs + attachID + ",");
                }
            }
            String[] result = new String[2];
            result[0] = aAttach.filename;
            result[1] = aAttach.localname;
            return result;
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }
        
    /**
     * Get an attachment
     * @param 
     *      attachID - Attachment ID
     * @return Attachment object
     * @throws SQLException
     * @since 1.0
     */
    public AttachVO getAttach(String attachID, Connection conn)
                                             throws SQLException
    {
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.Attach_Select);
            pstmtQuery.setString(1, attachID);
            rs = pstmtQuery.executeQuery();
            
            AttachVO aAttach = null;
            if(rs.next())
            {
                aAttach = new AttachVO();
                aAttach.attachID = attachID;
                aAttach.userID = rs.getString("userID");
                aAttach.filename = rs.getString("filename");
                aAttach.localname = rs.getString("localname");
                aAttach.credits = rs.getInt("credits");
                aAttach.downloads = rs.getInt("downloads");
            }
            return aAttach;
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
    }

    public void cleanRecycledAttaches() throws SQLException
    {
        String filename = null;
        File aFile = null;
        String filepath = AppContext.getInstance().getRealPath() + "upload/";
        
        PreparedStatement pstmtQuery = null;
        PreparedStatement pstmtUpdate = null;
        ResultSet rs = null;
        Connection conn = dbManager.getConnection();
        try