attachdao.java

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

        {
            pstmtUpdate = conn.prepareStatement(adapter.Attach_delete);
            pstmtQuery = conn.prepareStatement(adapter.Attach_GetRecycledList);
            pstmtQuery.setObject(1, AppUtils.getCurrentDateStr()); // Avoid clean uncommited records
            rs = pstmtQuery.executeQuery();
            
            while(rs.next())
            {
                filename = rs.getString(2);
                try
                {
                    aFile = new File(filepath + filename);
                    if (aFile.exists())
                    {
                        if (aFile.delete())
                        {
                            pstmtUpdate.setString(1, rs.getString(1));
                            pstmtUpdate.addBatch();
                        }
                    }
                }
                catch(Exception e){/*Ignored*/}
            }
            pstmtUpdate.executeBatch();
            
            cleanInvalidAvatars(conn);
            cleanInvalidAttaches(conn);
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
    }
    
    public void cleanInvalidAvatars(Connection conn) throws SQLException
    {
        String filepath = AppContext.getInstance().getRealPath() + "upload/avatar/";
        HashSet<String> avatarSet = new HashSet<String>();
        
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.Attach_GetAvatarList);
            rs = pstmtQuery.executeQuery();
            
            while(rs.next())
            {
                avatarSet.add(rs.getString(1));
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
        
        String filename = null;
        String folderName = null;
        File aFile = new File(filepath);
        File[] folders = aFile.listFiles();
        File[] files = null;
        
        if (folders != null)
        {
            for (int i=0; i<folders.length; i++)
            {
                folderName = folders[i].getName();
                if (!folders[i].isDirectory()) continue;
                if (folderName.startsWith("sample")) continue;
                
                files = folders[i].listFiles();
                if (files == null) continue;
                for (int j=0; j<files.length; j++)
                {
                    aFile = files[j]; 
                    filename = folderName + "/" + aFile.getName();
                    try
                    {
                        if (!avatarSet.contains(filename))
                            aFile.delete();
                    }
                    catch(Exception e){/*Ignored*/}
                }
            }
        }
    }

    public void cleanInvalidAttaches(Connection conn) throws SQLException
    {
        String filepath = AppContext.getInstance().getRealPath() + "upload/";
        HashSet<String> attachSet = new HashSet<String>();
        
        Calendar cal = Calendar.getInstance();
        String theMonthPath = String.valueOf(cal.get(Calendar.YEAR));
        int month = cal.get(Calendar.MONTH) + 1;
        if (month <= 9)
            theMonthPath = theMonthPath + "0";
        theMonthPath = theMonthPath + String.valueOf(month);
        
        cal.add(Calendar.MONTH, -1);
        String lastMonthPath = String.valueOf(cal.get(Calendar.YEAR));
        month = cal.get(Calendar.MONTH) + 1;
        if (month <= 9)
            lastMonthPath = lastMonthPath + "0";
        lastMonthPath = lastMonthPath + String.valueOf(month);
        
        String lastMonth1Day = String.valueOf(cal.get(Calendar.YEAR)) + "-"
                             + lastMonthPath.substring(4) + "-01";
        
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            pstmtQuery = conn.prepareStatement(adapter.Attach_Get2MonthList);
            pstmtQuery.setObject(1,lastMonth1Day);
            rs = pstmtQuery.executeQuery();
            
            while(rs.next())
            {
                attachSet.add(rs.getString(1));
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
        }
        
        String filename = null;
        String folderName = null;
        File aFile = new File(filepath);
        File[] folders = aFile.listFiles();
        File[] files = null;
        
        if (folders != null)
        {
            for (int i=0; i<folders.length; i++)
            {
                folderName = folders[i].getName();
                if (!folders[i].isDirectory()) continue;
                if (folderName.equals(theMonthPath)
                    || folderName.equals(lastMonthPath))
                {    
                    files = folders[i].listFiles();
                    if (files == null) continue;
                    for (int j=0; j<files.length; j++)
                    {
                        aFile = files[j]; 
                        filename = folderName + "/" + aFile.getName();
                        try
                        {
                            if (!attachSet.contains(filename))
                                aFile.delete();
                        }
                        catch(Exception e){/*Ignored*/}
                    }
                }
            }
        }
    }
    
    /**
     * 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 a.topicID = b.topicID");
        whereSql.append(" and a.state <> 'R' and b.state <> 'R'");

        String boardID = PageUtils.getParam(request, "boardID");
        if (boardID != null && boardID.length() > 0 && !boardID.equals("all"))
        {
            whereSql.append(" and b.boardID=?");
            paramValues.add(boardID);
        }
        
        String maxFilesize = PageUtils.getParam(request, "maxFilesize");
        if (maxFilesize != null && maxFilesize.length() > 0)
        {
            try
            {
                Integer.parseInt(maxFilesize);  // Check integer
                whereSql.append(" and a.filesize<=?");
                paramValues.add(maxFilesize);
            }
            catch(Exception e){ /* Ignored */ }
        }
                
        String minFilesize = PageUtils.getParam(request, "minFilesize");
        if (minFilesize != null && minFilesize.length() > 0)
        {
            try
            {
                Integer.parseInt(minFilesize);  // Check integer
                whereSql.append(" and a.filesize>=?");
                paramValues.add(minFilesize);
            }
            catch(Exception e){ /* Ignored */ }
        }

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

        String maxCredits = PageUtils.getParam(request, "maxCredits");
        if (maxCredits != null && maxCredits.length() > 0)
        {
            try
            {
                Integer.parseInt(maxCredits);  // Check integer
                whereSql.append(" and a.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 a.credits>=?");
                paramValues.add(minCredits);
            }
            catch(Exception e){ /* Ignored */ }
        }

        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 a.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 a.createTime>=?");
                paramValues.add(createTime);
            }
            catch(Exception e){ /* Ignored */ }
        }

        String filename = PageUtils.getParam(request, "filename");
        if (filename != null && filename.length() > 0)
        {
            if (filename.indexOf('*') >= 0)
            {
                whereSql.append(" and a.filename like ?");
                paramValues.add(filename.replace('*', '%'));
            }
            else
            {
                whereSql.append(" and a.filename=?");
                paramValues.add(filename);
            }
        }
        
        String userID = PageUtils.getParam(request, "userID");
        if (userID != null && userID.length() > 0)
        {
            if (userID.indexOf('*') >= 0)
            {
                whereSql.append(" and a.userID like ?");
                paramValues.add(userID.replace('*', '%'));
            }
            else
            {
                whereSql.append(" and a.userID=?");
                paramValues.add(userID);
            }
        }

        String title = PageUtils.getParam(request, "title");
        if (title != null && title.length() > 0)
        {
            whereSql.append(" and a.title like ?");
            paramValues.add("%" + title + "%");
        }
                
        return whereSql.toString();
    }
    
    /**
     * Search attachment
     * @param 
     *      request - HttpServletRequest
     * @return Attachment list
     * @throws SQLException
     * @since 1.0
     */
    public Object[] searchAttach(HttpServletRequest request, int pageNo) throws SQLException
    {
        Object[] result = new Object[2];
        
        ArrayList<Object> paramValues = new ArrayList<Object>();
        String whereSql = this.buildSearchWhereSql(request, paramValues); 
        
        String countSql = "select COUNT(*) from ejf_attach a, ejf_topic b" + whereSql;
        String querySql = adapter.Attach_Query + whereSql; 

        int pageRows = 15;

        int totalCount = 0;
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            conn = dbManager.getConnection();
            totalCount = this.execSelectCountSql(countSql, paramValues, conn);
            
            if (totalCount > 0)
            {
                querySql = adapter.getPageQuerySql(
                            new StringBuilder(querySql).append(" order by a.createTime DESC"),
                            pageNo, pageRows, totalCount);
                ArrayList<HashMap> attachList = this.execSelectSql(querySql, paramValues);
                result[1] = attachList;
            }
        }
        finally
        {
            dbManager.closeResultSet(rs);
            dbManager.closePStatement(pstmtQuery);
            dbManager.closeConnection(conn);
        }
        // Get result page code
        if (totalCount > 0)
        {
            result[0] = PageUtils.getPageHTMLStr(totalCount, pageNo, pageRows, 0);
        }        
        return result;
    }
    
    public static class AttachVO
    {
        public String attachID = null;
        public String topicID = null;
        public String replyID = null;
        public String userID = null;
        public String localname = null;
        public String filename = null;
        public int filesize = 0;
        public int credits = 0;
        public String localID = null;
        public String title = null;
        public int downloads = 0;
        // public char state = 'N';
        public String createTime = null;
    }    
}