musicaction.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.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 MusicAction extends BaseBean{
    
    public MusicAction() {}
    
    /**
     * 插入一条Music记录
     */ 
    public static boolean addMusic(MusicModel music) {
        boolean isOk = false;
        String sql = "insert into " +
                "music (m_title, m_uri, m_singer, m_lrc, m_beRevoke, m_date) " +
                "values (?, ?, ?, ?, ?, now())";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, music.getTitle());
            ps.setString(2, music.getUri());
            ps.setString(3, music.getSinger());
            ps.setString(4, music.getLrc());
            ps.setBoolean(5, music.getBeRevoke());
            isOk = (ps.executeUpdate() > 0);
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:addMusic:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 获取所有未被移入回收筒的音乐信息
     * @return musics
     */
    public static List<MusicModel> getMusics() {
        List<MusicModel> musics = new ArrayList<MusicModel>();
        String sql = "select * from music where m_beRevoke!=1 order by m_num desc";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                musics.add(getMusicByRS(rs));
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getMusics:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return musics;
    }
    
    /**
     * 通过music的ID获得music信息
     * @param id music信息的主键id
     * @return music
     */
    public static MusicModel getMusicById(int id) {
        MusicModel music = null;
        String sql = "select * from music where m_beRevoke!=1 and m_num=? ";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                music = getMusicByRS(rs);
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getMusicById:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return music;
    }
    
    /**
     * 获取所有音乐信息的总数,该方法不会统计已经回收的音乐信息
     * @return total
     */ 
    public static int getTotalMusics() {
        int total = 0;
        String sql = "select * from music where m_beRevoke!=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getTotalMusics:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取一定数量的音乐信息，主要用于分页查询，返回结果为ArrayList,该方法不会
     * 统计已经被回收的音乐信息
     * @param startRow 查询的起始行
     * @param pageSize 查询的最高数量
     * @return musics 
     */ 
    public static List<MusicModel> getMusicsForPage(int startRow, int pageSize) {
        List<MusicModel> musics = new ArrayList<MusicModel>();
        String sql = "select * from music where m_beRevoke!=1 " +
                "order by m_num desc " +
                "limit ?, ?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, startRow);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                musics.add(getMusicByRS(rs));
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getMusicsForPage:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return musics;
    }
    
    /**
     * 更新music信息
     * @param music 被更新的MusicModel
     * @return true 如果更新成功， 否则false
     */
    public static boolean updateMusic(MusicModel music) {
        boolean isOk = false;
        String sql = "update music set m_title=?, m_uri=?, m_singer=?, m_lrc=?" +
                ", m_beRevoke=? where m_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setString(1, music.getTitle());
            ps.setString(2, music.getUri());
            ps.setString(3, music.getSinger());
            ps.setString(4, music.getLrc());
            ps.setBoolean(5, music.getBeRevoke());
            ps.setInt(6, music.getNum());
            isOk = ps.executeUpdate() > 0;
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:updateMusic:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 回收音乐信息,该方法不会彻底删除音乐信息,只是放入回收筒
     * @param music 被回收的MusicModel
     * @return true or false
     */
    public static boolean revoke(MusicModel music) {
        boolean isOk = false;
        String sql = "update music set m_beRevoke=1 where m_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, music.getNum());
            isOk = ps.executeUpdate() > 0;
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:revoke:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return isOk;
    }
    
    /**
     * 获取所有被回收的音乐信息的总数,即所有revoke的
     * @return total
     */
    public static int getTotalRevokes() {
        int total = 0;
        String sql = "select * from music where m_beRevoke=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.last();
            total = rs.getRow();
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getTotalRevokes:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return total;
    }
    
    /**
     * 获取一定量的已经被回收的音乐信息，该方法主要用于分页查询
     * @param startRow 获取信息的起始行
     * @param pageSize 获取的数量
     * @return musics 已回收的音乐信息
     */
    public static List<MusicModel> getRevokeMusicsForPage(int startRow, int pageSize) {
        List<MusicModel> musics = new ArrayList<MusicModel>();
        String sql = "select * from music where m_beRevoke=1 " +
                "order by m_num desc " +
                "limit ?, ?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ps.setInt(1, startRow);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                musics.add(getMusicByRS(rs));
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getRevokeMusicsForPage:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return musics;
    }
    
    /**
     * 获取所有已经被回收的music信息
     * @return musics
     */
    public static List<MusicModel> getAllRevokes() {
        List<MusicModel> musics = new ArrayList<MusicModel>();
        String sql = "select * from music where m_beRevoke=1";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                musics.add(getMusicByRS(rs));
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getAllRevokes:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return musics;
    }
    
    /**
     * 删除music信息，彻底删除，同时返回已经被成功删除的信息列表
     * @param musics 需要被删除的信息
     * @return musics 所有已经成功删除的music信息， 否则null
     */
    public static List<MusicModel> deleteMusics(List<MusicModel> musics) {
        if (musics == null || musics.isEmpty()) return null;
        List<MusicModel> musicsDel = new ArrayList<MusicModel>();
        String sql = "delete from music where m_num=?";
        ProxyConn myConn = getConn();
        try {
            PreparedStatement ps = myConn.prepareStatement(sql);
            for (MusicModel music : musics) {
                ps.setInt(1, music.getNum());
                if (ps.executeUpdate() > 0) musicsDel.add(music);
            }
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:deleteMusics:" + e.getMessage());
        } finally {
            myConn.close();
        }
        return musicsDel;
    }
    
    /**
     * 通过ResultSet获得其当前行的信息
     * @param rs
     * @return music
     */
    public static MusicModel getMusicByRS(ResultSet rs) {
        MusicModel music = new MusicModel();
        try {
            music.setBeRevoke(rs.getBoolean("m_beRevoke"));
            music.setDate(rs.getTimestamp("m_date"));
            music.setLrc(rs.getString("m_lrc"));
            music.setNum(rs.getInt("m_num"));
            music.setSinger(rs.getString("m_singer"));
            music.setTitle(rs.getString("m_title"));
            music.setUri(rs.getString("m_uri"));
        } catch (Exception e) {
            Elog.log("Exception:MusicAction:getMusicByRS:" + e.getMessage());
        }
        return music;
    }
}