subservicetypedao.java

采用JAVA开发

package com.gctech.sms.dao;

import java.util.Collection;
import java.util.Vector;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import com.gctech.sms.util.ConnectionManager;
import org.apache.log4j.Logger;
import com.gctech.sms.vo.SubmitServiceType;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;
import com.gctech.sms.vo.SubmitCommandObject;
import java.util.ArrayList;
import com.gctech.sms.core.DisplayInWeb;
import com.gctech.sms.core.Status;

/**
 * <p>Title: 订阅类型数据访问类。</p>
 * <p>Description:订阅类型数据访问类。 </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: gctech</p>
 * @author 王红宝
 * @version $Id: SubServiceTypeDao.java,v 1.2 2004/04/22 01:25:16 wanghb Exp $
 */

public class SubServiceTypeDao {
  public SubServiceTypeDao() {
  }

  public static int add(SubmitServiceType serviceType){
    int rt = -1;
    Connection con = null;
    PreparedStatement pstmt = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      pstmt = con.
          prepareStatement("insert into SubServiceType(ID,NAME,DESCRIPTION,STATUS)values(?,?,?,?)");
      pstmt.setString(1, IDGenerator.nextId(con));
      pstmt.setString(2, serviceType.getName());
      pstmt.setString(3, serviceType.getDesc());
      pstmt.setInt(4, serviceType.getStatus());

      pstmt.execute();
      rt = 0;
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( pstmt != null ){
        try {
          pstmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      return rt;
    }

  }
  /**
   * 得到所有的订阅类别，包括子内容。
   * 对象类型是SubmitServiceType，可以通过SubmitServiceType的getServices方法
   * 得到所有的订阅子类型。
   *
   * */
  public static Collection getAllSubmitService(){
    Map all = new TreeMap();
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.createStatement();
      String sql = "select s.ID as s_id,s.NAME as s_name,s.DESCRIPTION as s_desc,sc.ID,sc.NAME FROM SubServiceType s,"+
          "SubmitCommand sc where s.id=sc.SERVICETYPEID and sc.DISPLAYINWEB="+
          DisplayInWeb.DIPLAYED.getValue()+" and sc.status="+Status.ENABLED.getValue()+
          " order by s.addedon";
      logger.debug(sql);
      rs = stmt.executeQuery(sql);

      while ( rs.next() ){
        String key = rs.getString("s_id");
        Object o = all.get(key);
        SubmitServiceType st = null;
        if ( o == null ){
          st = new SubmitServiceType();
          st.setId(rs.getString(1));
          st.setName(rs.getString(2));
          st.setDesc(rs.getString(3));
          all.put(key, st);
        }else{
          st = (SubmitServiceType)o;
        }
        SubmitCommandObject sco = new SubmitCommandObject();
        sco.setId(rs.getString(4));
        sco.setName(rs.getString(5));
        st.addService(sco);
      }
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( rs != null ){
        try {
          rs.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    return all.values();
  }

  public static Collection findSubServiceType(int status){
    Collection rt = new Vector();
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    try{
      con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
      stmt = con.createStatement();
      StringBuffer sql = new StringBuffer("select ID,NAME,DESCRIPTION FROM SubServiceType where 1=1 ");
      if ( status != -1 )
        sql.append("and status=").append(status);
      sql.append(" order by addedon");
      logger.debug(sql);
      rs = stmt.executeQuery(sql.toString());
      while ( rs.next() ){
        SubmitServiceType st = new SubmitServiceType();
        st.setId(rs.getString(1));
        st.setName(rs.getString(2));
        st.setDesc(rs.getString(3));
        rt.add(st);
      }
    }catch( Throwable e ){
      logger.error(e, e);
    }finally{
      if ( rs != null ){
        try {
          rs.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( stmt != null ){
        try {
          stmt.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
      if ( con != null ){
        try {
          con.close();
        }
        catch (Throwable ex) {
          logger.error(ex, ex);
        }
      }
    }
    return rt;
  }

  /**
   * 得到订阅类列表。
   * 对象类型是SubmitServiceType。
   * */
  public static Collection getSubServiceType(){
    return findSubServiceType(-1);
  }

  public int deletesubtype(SubmitServiceType subtype){//删除数据
        Connection con = null;
        PreparedStatement pstmt = null;
        int rs = 0 ;
        try{
          con = ConnectionManager.getInstance().getConnection(SubServiceTypeDao.class);
          StringBuffer sql = new StringBuffer("delete from SUBSERVICETYPE where 1=1 ");
          sql.append( "and SUBSERVICETYPE.id = '" + subtype.getId() + "'");

          pstmt = con.prepareStatement(sql.toString());
          rs = pstmt.executeUpdate();

        }catch( Throwable e ){
          logger.error(e, e);
        }finally{
          if ( pstmt != null ){
            try {
              pstmt.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
          if ( con != null ){
            try {
              con.close();
            }
            catch (Throwable ex) {
              logger.error(ex, ex);
            }
          }
        }
        //返回数据
        return rs;
  }
  public int updateSubType(SubmitServiceType subtype) {//修改数据
     Connection con = null;
     PreparedStatement pstmt = null;
     int rs = 0 ;
     try{
       con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
       StringBuffer sql = new StringBuffer("update SUBSERVICETYPE set ");
       if (subtype.getName()!=null){
         if (subtype.getName().equals("@_@")){
             sql.append( " SUBSERVICETYPE.name = null, ");
         }else{
             sql.append( " SUBSERVICETYPE.name = '" + subtype.getName() + "',");
         }
       }
       if (subtype.getDesc()!=null){
         if (subtype.getDesc().equals("@_@")){
            sql.append( " SUBSERVICETYPE.DESCRIPTION = null, ");
         }else{
           sql.append( " SUBSERVICETYPE.DESCRIPTION = '" + subtype.getDesc() + "',");
         }
       }
       sql.append( " SUBSERVICETYPE.status = '"+subtype.getStatus()+"',");

       sql.setLength(sql.length()-1);
       sql.append( " where 1=1");
       if (subtype.getId()!=null){
         sql.append(" and SUBSERVICETYPE.id = '" + subtype.getId()+ "'");
       }
       logger.debug(sql.toString() + " update");
       pstmt = con.prepareStatement(sql.toString());

       rs = pstmt.executeUpdate();

     }catch( Throwable e ){
       logger.error(e, e);
     }finally{
       if ( pstmt != null ){
         try {
           pstmt.close();
         }
         catch (Throwable ex) {
           logger.error(ex, ex);
         }
       }
       if ( con != null ){
         try {
           con.close();
         }
         catch (Throwable ex) {
           logger.error(ex, ex);
         }
       }
     }
     //返回数据
     return rs;

   }
   public static SubmitServiceType[] getAllSubType(SubmitServiceType subtype) {//查询所有数据
         Connection con = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList list = new ArrayList();
         try{
           con = ConnectionManager.getInstance().getConnection(SpInfoDao.class);
           StringBuffer sql = new StringBuffer("select a.id,a.name,a.DESCRIPTION,a.status ");
           sql.append( " from SUBSERVICETYPE a");
           sql.append( " where 1=1 ");
           if ( subtype!=null && subtype.getId()!=null&&!subtype.getId().equals("")){
             sql.append( " and a.id = '" + subtype.getId() + "'");
           }

           logger.debug(sql.toString() + " sql");
           pstmt = con.prepareStatement(sql.toString());
           rs = pstmt.executeQuery();
           while (rs.next()){
             SubmitServiceType model = new SubmitServiceType();
             String id = rs.getString("id");
             if (!rs.wasNull()) model.setId(id);
             String name = rs.getString("name");
             if (!rs.wasNull()) model.setName(name);
             String des = rs.getString("DESCRIPTION");
             if (!rs.wasNull()) model.setDesc(des);
             int status = rs.getInt("status");
             model.setStatus(status);

             list.add(model);
           }
         }catch( Throwable e ){
           logger.error(e, e);
         }finally{
           if ( rs != null ){
             try {
               rs.close();
             }
             catch (Throwable ex) {
               logger.error(ex, ex);
             }
           }
           if ( pstmt != null ){
             try {
               pstmt.close();
             }
             catch (Throwable ex) {
               logger.error(ex, ex);
             }
           }
           if ( con != null ){
             try {
               con.close();
             }
             catch (Throwable ex) {
               logger.error(ex, ex);
             }
           }
         }
         //返回数据
         return (SubmitServiceType[])list.toArray(new SubmitServiceType[list.size()]);
   }


  static final Logger logger = Logger.getLogger(SubServiceTypeDao.class);
}