zoneaction.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.pool.ProxyConn;
import biz.tbuy.common.logs.Elog;
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 ZoneAction extends BaseBean {
    
    public ZoneAction() {}
    
    /** method *****************************************************************
     * create "Zone"
     * @param zone
     * @return true false
     */
    public static boolean addZone(ZoneModel zone) {
        String sql = "insert into zone (z_name, z_comment, " +
                "  z_byUser, z_beLock, z_beRevoke, z_date) " +
                "values (?, ?, ?, ?, ?, now())";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, zone.getName());
            ps.setString(2, zone.getComment());
            ps.setString(3, zone.getByUser());
            ps.setBoolean(4, zone.getBeLock());
            ps.setBoolean(5, zone.getBeRevoke());
            return ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("ZoneAction.addZone()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return false;
    }
    
    /**
     * get a ZoneModel by ZoneModel's id
     * @param zoneId
     * @return zone
     */ 
    public static ZoneModel getZoneById(int zoneId) {
        ZoneModel zone = null;
        String sql = "select * from zone where z_num=? and z_beRevoke!=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, zoneId);
            ResultSet rs = ps.executeQuery();
            zone = getZones(rs).get(0);
        } catch (SQLException sqle) {
            Elog.log("ZoneAction.getZoneById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return zone;
    }
    
    /**
     * get ZoneModel by ForumModel
     * (to fine the ZoneModel by this ForumModel's id )
     * @param forum
     * @return zone
     */ 
    public static ZoneModel getZoneByForum(ForumModel forum) {
        ZoneModel zone = new ZoneModel();
        String sql = "select * from zone, forum where f_num=? and" +
                " zone.z_num=forum.f_byZone and z_beRevoke!=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, forum.getNum());
            ResultSet rs = ps.executeQuery();
            zone = getZones(rs).get(0);
        } catch (SQLException sqle) {
            Elog.log("ZoneAction.getZoneByForum()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return zone;
    }
    
    /**
     * 返回所有Zone，返回结果为List集合,并按zone.listNum,排序
     * @return a ArrayList of ZoneModel, list<ZoneModel>
     */
    public static List<ZoneModel> getZones() {
        String sql = "select * from zone " +
                "where z_beRevoke!=1 " +
                "order by z_listNum, z_num asc";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            return getZones(rs);
        } catch (SQLException sqle) {
            Elog.log("ZoneAction.getZones()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return null;
    }
    
    /**
     * 更新Zone,主要用于后台zone编辑
     * @param zone 需要被更新的ZoneModel
     * @return true 如果更新成功，否则返回 false
     */ 
    public static boolean updateZone(ZoneModel zone) {
        boolean isOk = true;
        String sql = "update zone set z_name=?, z_comment=?, " +
                "z_date=?, z_listNum=?, z_byUser=?, z_beLock=?, z_beRevoke=? " +
                "where z_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, zone.getName());
            ps.setString(2, zone.getComment());
            ps.setTimestamp(3, zone.getDate());
            ps.setInt(4, zone.getListNum());
            ps.setString(5, zone.getByUser());
            ps.setBoolean(6, zone.getBeLock());
            ps.setBoolean(7, zone.getBeRevoke());
            ps.setInt(8, zone.getNum());
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("ZoneAction.updateZone()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 通过ZoneModel的listNum属性排序Zone,即只更新zone的listNum
     * listNum越小的zone排序越靠前
     * @param zones 一个包含ZoneModel的list集合类型
     * @return true 如果排序成功,否则 false
     */
    public static boolean orderZones(List<ZoneModel> zones) {
        boolean isOk = true;
        String sql = "update zone set z_listNum=? where z_num=?";
        ProxyConn myConn = getConn();
        for (ZoneModel zone : zones) {
            try {
                PreparedStatement ps = myConn.prepareStatement(sql);
                ps.setInt(1, zone.getListNum());
                ps.setInt(2, zone.getNum());
                ps.executeUpdate();
            } catch (SQLException sqle) {
                Elog.log("ZoneAction.orderZones()/" + sqle.getMessage());
            } finally {
                myConn.close();
            }
        }
        return isOk;
    }
    
    /**
     * 通过ZoneModel.num 回收ZoneModel,并不删除该Zone,
     * 只是暂时放入回收筒,以供回收使用
     * @param zoneId ZoneModel's id
     * @return true 如果回收成功，否则返回 false
     */
    public static boolean revokeZoneById(int zoneId) {
        boolean isOk = true;
        String sql = "update zone set z_beRevoke=1 where z_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, zoneId);
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("bbs/ZoneAction.revokeZoneById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 通过ZoneModel.num 彻底删除ZoneModel
     * @param zoneId ZoneModel's id
     * @return true 如果删除成功，否则返回 false
     */
    public static boolean deleteZoneById(int zoneId) {
        boolean isOk = true;
        String sql = "delete from zone where z_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, zoneId);
            isOk = ps.executeUpdate() > 0;
        } catch (SQLException sqle) {
            Elog.log("bbs/ZoneAction.deleteZoneById()/" + sqle.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**************************************************************************
     * 关于被删除及被回收的专区,以下部分是关于获取及彻底删除已被回收的专区
     * 该方法获取已被回收的专区的总数量
     * @return total
     **************************************************************************/
    public static int getTotalRevokeZones() {
        int total = 0;
        String sql = "select * from zone where z_beRevoke=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (SQLException sqle) {
            Elog.log("Exception:ZoneAction:getTotalRevokeZones:" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取一定数量的“已被回收的专区”,该方法主要用于分页获取
     * @param startRow 获取的开始行
     * @param pageSize 从开始行获取一定的数量
     * @return revokeZones 被回收的专区
     */ 
    public static List<ZoneModel> getRevokeZones(int startRow, int pageSize) {
        List<ZoneModel> revokeZones = null;
        String sql = "select * from zone where z_beRevoke=1 " +
                "order by z_num desc " +
                "limit ?, ?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, startRow);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            revokeZones = getZones(rs);
        } catch (SQLException sqle) {
            Elog.log("Exception:ZoneAction:getRevokeZones:" + 
                    sqle.getMessage());
        } finally {
            myConn.close();
        }
        return revokeZones;
    }
    
    /**
     * 彻底删除所有已经被回收的专区信息,同时删除相关联的信息
     * 删除专区，相关讨论区，相关主题，相关回复信息，相关版主
     * @return true 如果正常删除,否则false
     */ 
    public static boolean clearRevokeZones() {
        boolean isOk = true;
        String sql = "delete zone, forum, topic, reply, master " +
                "from zone " +
                "left join forum on z_num=f_byZone " +
                "left join topic on f_num=t_byForum " +
                "left join reply on t_num=r_byTopic " +
                "left join master on m_byForum=f_num " +
                "where z_beRevoke=1 ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            isOk = false;
            Elog.log("Exception:ZoneAction:clearRevokeZones:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }

    /** private ****************************************************************
     * get Zones of list by ResultSet
     * @param rs
     * @return list 
     */
    private static List<ZoneModel> getZones(ResultSet rs) {
        List<ZoneModel> list = new ArrayList<ZoneModel>();
        try {
            while (rs.next()) {
                list.add(getZoneByRS(rs));
            }
        } catch (SQLException sqle) {
            Elog.log("bbs/ZoneAction.getZones()/" + sqle.getMessage());
        }
        return list;
    }
    
    /**
     * 通过该ResultSet的当前行获取相应的数据,并转为单个ZoneModel返回
     * 这里是真正从ResultSet中获取数据的地方，所有的ZoneModel都应该从
     * 这里获得数据
     * @param rs
     * @return zone
     */
    public static ZoneModel getZoneByRS(ResultSet rs) {
        ZoneModel zone = new ZoneModel();
        try {
            zone.setBeLock(rs.getBoolean("z_beLock"));
            zone.setBeRevoke(rs.getBoolean("z_beRevoke"));
            zone.setComment(rs.getString("z_comment"));
            zone.setDate(rs.getTimestamp("z_date"));
            zone.setListNum(rs.getInt("z_listNum"));
            zone.setName(rs.getString("z_name"));
            zone.setNum(rs.getInt("z_num"));
            zone.setByUser(rs.getString("z_byUser"));
        } catch (SQLException sqle) {
            Elog.log("bbs/ZoneAction.getZoneByRS()/" + sqle.getMessage());
        }
        return zone;
    }
}