voicefacade.java

采用JAVA开发

package com.gctech.sms.voice;

import java.util.*;
import java.sql.*;
import org.apache.log4j.Logger;
import com.gctech.sms.voice.*;
import com.gctech.sms.voice.common.*;
import com.gctech.sms.voice.dao.*;

/**
 * web调用数据库系统接口
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author [e-mail]
 * @version 1.0
 */
public class VoiceFacade
{
  static Logger logger = Logger.getLogger(VoiceFacade.class);
  public VoiceFacade()
  {
  }

  /**
   * 所有的大类,返回所有BigCataLogValueObject
   * @throws VoiceException
   * @return List
   */
  public List allBigCatalog()
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      BigCatalogDAO dao = new BigCatalogDAO();
      return dao.all(conn);
    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 所有小类,返回所有的SmallCatalogValueObject
   * @throws VoiceException
   * @return List
   */
  public List allSmallCatalog()
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      SmallCatalogDAO dao = new SmallCatalogDAO();
      return dao.all(conn);
    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 得到指定类型的所有声讯文件资料
   * 返回结构按点击数排序
   * 如果smallCatalog为null,则忽略类别
   * @param smallCatalog Integer
   * @param startIndex int
   * @param endSIndex int
   * @throws VoiceException
   * @return VoiceValueObject[]
   */
  public VoiceValueObject[] getVoiceBySmallCatalog(Integer smallCatalog,
      int startIndex,int endIndex)
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      VoiceDAO dao = new VoiceDAO();
      VoiceQueryValue vo = new VoiceQueryValue();
      vo.setCatalogId(smallCatalog);
      List list = dao.selectByQueryValue(conn,vo,startIndex,endIndex);
      return(VoiceValueObject[])list.toArray(new VoiceValueObject[list.size()]);

    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 同上,查找总数
   * @param smallCatalog Integer
   * @param startIndex int
   * @param endSIndex int
   * @throws VoiceException
   * @return int
   */
  public int getVoiceSizeBySmallCatalog(Integer smallCatalog)
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      VoiceDAO dao = new VoiceDAO();
      VoiceQueryValue vo = new VoiceQueryValue();
      vo.setCatalogId(smallCatalog);
      return dao.size(conn,vo);
    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   *
   * @param bigCataLog Integer
   * @param startIndex int
   * @param endIndex int
   * @throws VoiceException
   * @return VoiceQueryValue[]
   */
  static String sql_vbb = "SELECT A.ID, A.CATALOG_ID, A.DESCRIPTION, A.COUNT, A.CREATE_DATE, A.PRICE, A.TYPE, A.NAME, A.USER_ID FROM VOICE A ,small_catalog t WHERE ";
  static String sql_all_vbb = "SELECT A.ID, A.CATALOG_ID, A.DESCRIPTION, A.COUNT, A.CREATE_DATE, A.PRICE, A.TYPE, A.NAME, A.USER_ID FROM VOICE A  WHERE type!=2 order by count desc ";

  public VoiceValueObject[] getVoiceByBigCatalog(Integer bigCatalog,
                                                 int startIndex,int endIndex)
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      String mainSql = sql_vbb;
      if(bigCatalog == null)
      {
        mainSql = sql_all_vbb ;
      }
      else
      {
        mainSql = sql_vbb + "t.id = a.catalog_id and t.big_id = " + bigCatalog +
            " order by a.count desc";
      }


      String sql = new StringBuffer(
          "select * from ( select a.*,rownum as tmprow from (").append(mainSql).
          append(") a where rownum <=").append(endIndex).append(
          ") where tmprow>= ").append(startIndex).toString();

      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = conn.prepareStatement(sql);
      ResultSet rs = prepStmt.executeQuery();
      while(rs.next())
      {
        VoiceValueObject model = new VoiceValueObject();
        int intID = rs.getInt("ID");
        if(!rs.wasNull())model.setId(new Integer(intID));
        int intCATALOG_ID = rs.getInt("CATALOG_ID");
        if(!rs.wasNull())model.setCatalogId(new Integer(intCATALOG_ID));
        String strDESCRIPTION = rs.getString("DESCRIPTION");
        if(!rs.wasNull())model.setDescription(strDESCRIPTION.trim());
        int intCOUNT = rs.getInt("COUNT");
        if(!rs.wasNull())model.setCount(new Integer(intCOUNT));
        model.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        int intPRICE = rs.getInt("PRICE");
        if(!rs.wasNull())model.setPrice(new Integer(intPRICE));
        int intTYPE = rs.getInt("TYPE");
        if(!rs.wasNull())model.setType(new Integer(intTYPE));
        String strNAME = rs.getString("NAME");
        if(!rs.wasNull())model.setName(strNAME.trim());
        int intUSER_ID = rs.getInt("USER_ID");
        if(!rs.wasNull())model.setUserId(new Integer(intUSER_ID));
        list.add(model);
      }
      rs.close();
      prepStmt.close();

      return(VoiceValueObject[])list.toArray(new VoiceValueObject[list.size()]);

    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 查询指定大类的总数
   * @param bigCatalog Integer
   * @throws VoiceException
   * @return int
   */
  static String sql_all_vbbs =
      "SELECT count(*) FROM VOICE A  where type != 2  ";

  static String sql_vbbs =
      "SELECT count(*) FROM VOICE A ,small_catalog t WHERE ";

  public int getVoiceSizeByBigCatalog(Integer bigCatalog)
      throws VoiceException
  {
    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      String mainSql = sql_vbb;
      if(bigCatalog == null)
      {
        mainSql = sql_all_vbbs;
      }
      else
      {
        mainSql = sql_vbbs + "t.id = a.catalog_id and t.big_id = " + bigCatalog +
            " ";
      }

      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = conn.prepareStatement(mainSql);
      ResultSet rs = prepStmt.executeQuery();
      if(rs.next())
      {
        return rs.getInt(1);
      }
      else
      {
        throw new Exception("SQL ERROR ");
      }

    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 得到指定记录范围的上传语音文件
   * @param startIndex int
   * @param endIndex int
   * @throws VoiceException
   * @return VoiceValueObject[]
   */
  public VoiceValueObject[] getVoiceByUser(int startIndex,int endIndex)
      throws VoiceException
  {

    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      VoiceDAO dao = new VoiceDAO();
      VoiceQueryValue vo = new VoiceQueryValue();
      //用户类型
      vo.setType(new Integer(2));
      List list = dao.selectByQueryValue(conn,vo,startIndex,endIndex);
      return(VoiceValueObject[])list.toArray(new VoiceValueObject[list.size()]);

    }
    catch(Exception ex)
    {
      logger.info(ex.getMessage());
      throw new VoiceException(ex.getMessage());
    }
    finally
    {
      DBHelper.cleanup(conn);
    }

  }

  /**
   * 得到所有用户上传的语音文件
   * @return int
   */
  public int getVoiceSizeByUser()
      throws VoiceException
  {

    Connection conn = null;
    try
    {
      conn = DBHelper.getConn();
      VoiceDAO dao = new VoiceDAO();
      VoiceQueryValue vo = new VoiceQueryValue();
      //用户类型
      vo.setType(new Integer(2));
      return dao.size(conn,vo);

    }
    catch(Exception ex)