fileaction.java

tbuy1.1.5是在netbeans环境下用JSF技术编写的一个论坛tbuy1.1.5是在netbeans环境下用JSF技术编写的一个论坛

/*
 * 作者: 胡李青
 * qq: 31703299
 * Copyright (c) 2007 huliqing
 * 主页 http://www.tbuy.biz/
 * 你可以免费使用该软件，未经许可请勿作用于任何商业目的,如有技术问题请与本人联系！
 */

package biz.tbuy.bbs;

import biz.tbuy.bbs.bean.BaseBean;
import biz.tbuy.common.logs.Elog;
import biz.tbuy.common.pool.ProxyConn;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * @author huliqing
 * <p><b>qq：</b>31703299
 * <p><b>E-mail：</b>
 * <a href="mailto:[e-mail]">[e-mail]</a>
 * <p><b>Homepage：</b>
 * <a href="http://www.tbuy.biz/">http://www.tbuy.biz/</a>
 */
public class FileAction extends BaseBean{

    public FileAction() {
    }
    
    /**
     * 通过FileModel，将其插入数据库中
     * @param file 需要插入数据库中的上传文件信息
     * @return true 如果操作成功，否则返回false;
     */
    public static boolean addFile(FileModel file) {
        boolean isOk = false;
        String sql = "insert into upload (" +
                "up_name, " +        // 1 名称
                "up_type, " +        // 2 类型
                "up_size, " +        // 3 大小k
                "up_description, " + // 4 描述
                "up_byUser, " +      // 5 上传者
                "up_target, " +      // 6 目标类型
                "up_byTarget, " +    // 7 相关主题
                "up_beRevoke, " +    // 8 是否已经被回收
                "up_suffix, " +      // 9 后缀
                "up_date) values (?, ?, ?, ?, ?, ?, ?, ?, ?, now())";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, file.getName());
            ps.setString(2, file.getType());
            ps.setLong(3, file.getSize());
            ps.setString(4, file.getDescription());
            ps.setString(5, file.getByUser());
            ps.setString(6, file.getTarget());
            ps.setInt(7, file.getByTarget());
            ps.setBoolean(8, file.getBeRevoke());
            ps.setString(9, file.getSuffix());
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("FileAction:addFile:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 通过该方法获得topic所相关联的上传文件
     * 该方法不会获取已经被revoke的数据
     * @param topicId 通过该topicId获取其上传文件
     * @return files 包含FileModel的ArrayList
     */
    public static List<FileModel> getFilesByTopicId(int topicId) {
        List<FileModel> files = new ArrayList<FileModel>();
        String sql = "select * from upload " +
                "where up_byTarget=? " +
                "and up_target='t' " +
                "and up_beRevoke!=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, topicId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                files.add(getFileByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("FileAction:getFilesByTopicId:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return files;
    }
    
    /**
     * 获取所有已经脱离连接的file,即已经成为垃圾信息的file,比如主题已经被删除!
     * 或者回复信息已经删除，那么他们的附件信息也应该移除!
     * @return files 
     */
    public static List<FileModel> getJunkFiles() {
        List<FileModel> files = new ArrayList<FileModel>();
        String sql1 = "select * from upload " +
                "where up_target='t' " +
                "and (up_byTarget not in " +
                "(select t_num from topic))";
        String sql2 = "select * from upload " +
                "where up_target='r' " +
                "and (up_byTarget not in " +
                "(select r_num from reply))";
        ProxyConn myConn = getConn();
        try {
            // 查出所有已经失效的主题附件信息
            PreparedStatement ps = myConn.prepareStatement(sql1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                files.add(getFileByRS(rs));
            }
            // 查出所有已经失效的回复附件
            ps = myConn.prepareStatement(sql2);
            rs = ps.executeQuery();
            while (rs.next()) {
                files.add(getFileByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("FileAction:getJunkFiles:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return files;
    }
    
    /**
     * 该方法主要用于删除多个file信息,同时返回已经成功删除的列表
     * 该方法会彻底将信息从数据库中清理出去
     * @param files 将被彻底删除的file信息
     * @return filesDel or null
     */
    public static List<FileModel> deleteFiles(List<FileModel> files) {
        if (files == null || files.isEmpty()) return null;
        List<FileModel> filesDel = new ArrayList<FileModel>();
        String sql = "delete from upload where up_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            for (FileModel file : files) {
                ps.setInt(1, file.getNum());
                if (ps.executeUpdate() > 0) {
                    filesDel.add(file);
                }
            }
        } catch (SQLException sqle) {
            Elog.log("FileAction:deleteFiles:" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return filesDel;
    }
    
    /**
     * 通过该ResultSet的当前行获取相应的数据,并转为单个FileModel返回
     * 这里是真正从ResultSet中获取数据的地方，所有的FileModel都应该从
     * 这里获得数据
     * @param rs a ResultSet
     * @return file FileModel
     */ 
    public static FileModel getFileByRS(ResultSet rs) {
        FileModel file = new FileModel();
        try {
            file.setNum(rs.getInt("up_num"));
            file.setBeRevoke(rs.getBoolean("up_beRevoke"));
            file.setTarget(rs.getString("up_target"));
            file.setByTarget(rs.getInt("up_byTarget"));
            file.setByUser(rs.getString("up_byUser"));
            file.setDate(rs.getTimestamp("up_date"));
            file.setDescription(rs.getString("up_description"));
            file.setName(rs.getString("up_name"));
            file.setSize(rs.getLong("up_size"));
            file.setType(rs.getString("up_type"));
            file.setSuffix(rs.getString("up_suffix"));
        } catch (SQLException sqle) {
            Elog.log("FileAction:getFileByRS:" + sqle.getMessage());
        }
        return file;
    }
}