submitcommanddao.java

采用JAVA开发

            if (!rs.wasNull()) model.setSample(sample);
            String provider = rs.getString("provider");
            if (!rs.wasNull()) model.setProvider(provider);
            String servicetypeid = rs.getString("servicetypeid");
            if (!rs.wasNull()) model.setServiceTypeId(servicetypeid);
            int submitfeetype = rs.getInt("SUBMITFEETYPE");
            if (!rs.wasNull()) model.setSubmitFeeType(submitfeetype);
            String feestand = rs.getString("FEESTAND");
            if (!rs.wasNull()) model.setFeeDesc(feestand);
            double price = rs.getDouble("price");
            if (!rs.wasNull()) model.setPrice(price);
            String sendfreq = rs.getString("SENDFREQ");
            if (!rs.wasNull()) model.setSendFreque(sendfreq);
            String name = rs.getString("name");
            if (!rs.wasNull()) model.setName(name);
            int status = rs.getInt("STATUS");
            model.setStatus(status);
            int displayinweb = rs.getInt("DISPLAYINWEB");
            model.setDisplayInWeb(displayinweb);

            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 (SubmitCommandObject[])list.toArray(new SubmitCommandObject[list.size()]);
  }
  public int deleteMo(SubmitCommandObject infovalue) throws SQLException {//删除数据
        Connection con = null;
        PreparedStatement pstmt = null;
        int rs = 0 ;
        try{
          con = ConnectionManager.getInstance().getConnection(SmsServiceDao.class);
          StringBuffer sql = new StringBuffer("delete from submitcommand where 1=1 ");
          sql.append( "and submitcommand.id = '" + infovalue.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 updateMo(SubmitCommandObject infovalue) throws SQLException {//更新数据
    Connection con = null;
    PreparedStatement pstmt = null;
    int rs = 0 ;
    try{
      con = ConnectionManager.getInstance().getConnection(SmsServiceDao.class);
      StringBuffer sql = new StringBuffer("update submitcommand set ");

      sql.append( " submitcommand.feebusinessid = '" + infovalue.getFeeBusinessId() + "',");
      sql.append( " submitcommand.cntbusinessid = '" + infovalue.getCntBusinessId() + "',");
      sql.append( " submitcommand.keyword = '" + infovalue.getKeyWord() + "',");
      sql.append( " submitcommand.name = '" + infovalue.getName() + "',");
      sql.append( " submitcommand.description = '" + infovalue.getDescription() + "',");
      sql.append( " submitcommand.sample = '" + infovalue.getSample() + "',");
      sql.append( " submitcommand.provider = '" + infovalue.getProvider() + "',");
      sql.append( " submitcommand.servicetypeid = '" + infovalue.getServiceTypeId() + "',");
      sql.append( " submitcommand.price = '" + infovalue.getPrice() + "',");
      sql.append( " submitcommand.SUBMITFEETYPE = '" + infovalue.getSubmitFeeType()+ "',");
      sql.append( " submitcommand.FEESTAND = '" + infovalue.getFeeDesc() + "',");
      sql.append( " submitcommand.SENDFREQ = '" + infovalue.getSendFreque() + "',");
      sql.append( " submitcommand.STATUS = '" + infovalue.getStatus() + "',");
      sql.append( " submitcommand.DISPLAYINWEB = '" + infovalue.getDisplayInWeb() + "',");

      sql.setLength(sql.length()-1);
      sql.append( " where 1=1");
      if (infovalue.getId()!=null){
        sql.append(" and submitcommand.id = '" + infovalue.getId() + "'");
      }
      logger.debug(sql.toString() + "infovalue.getId()" + infovalue.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 SubmitCommandObject[] getByMo(SubmitCommandObject infovalue) throws SQLException {//查询数据
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();
        try{
          con = ConnectionManager.getInstance().getConnection(SmsServiceDao.class);
          StringBuffer sql = new StringBuffer("select a.id,a.feebusinessid,a.cntbusinessid,");
          sql.append( "a.keyword,a.name,a.description,a.sample,a.provider,a.servicetypeid,");
          sql.append( " a.PRICE,a.SUBMITFEETYPE,a.FEESTAND,a.SENDFREQ,");
          sql.append( " a.STATUS,a.DISPLAYINWEB");
          sql.append( " from submitcommand a");
          sql.append( " where 1=1 ");
          if (infovalue!=null && infovalue.getId()!=null && !infovalue.getId().equals("")){
            sql.append( " and a.id='" + infovalue.getId() + "'");
          }
          if (infovalue!=null && infovalue.getProvider()!=null && !infovalue.getProvider().equals("")){
            sql.append( " and a.provider='" + infovalue.getProvider() + "'");
          }
          if (infovalue!=null && String.valueOf(infovalue.getDisplayInWeb())!=null && !String.valueOf(infovalue.getDisplayInWeb()).equals("")){
            sql.append( " and a.DISPLAYINWEB='" + infovalue.getDisplayInWeb() + "'");
          }
          //logger.equals(sql.toString());
          logger.debug(sql.toString());
          pstmt = con.prepareStatement(sql.toString());
          rs = pstmt.executeQuery();
          while (rs.next()){
            SubmitCommandObject model = new SubmitCommandObject();
            String id = rs.getString("id");
            if (!rs.wasNull()) model.setId(id);
            String feeid = rs.getString("feebusinessid");
            if (!rs.wasNull()) model.setFeeBusinessId(feeid);
            String cntid = rs.getString("cntbusinessid");
            if (!rs.wasNull()) model.setCntBusinessId(cntid);
            String keyword = rs.getString("keyword");
            if (!rs.wasNull()) model.setKeyWord(keyword);
            String desc = rs.getString("description");
            if (!rs.wasNull()) model.setDescription(desc);
            String sample = rs.getString("sample");
            if (!rs.wasNull()) model.setSample(sample);
            String provider = rs.getString("provider");
            if (!rs.wasNull()) model.setProvider(provider);
            String servicetypeid = rs.getString("servicetypeid");
            if (!rs.wasNull()) model.setServiceTypeId(servicetypeid);
            int submitfeetype = rs.getInt("SUBMITFEETYPE");
            if (!rs.wasNull()) model.setSubmitFeeType(submitfeetype);
            String feestand = rs.getString("FEESTAND");
            if (!rs.wasNull()) model.setFeeDesc(feestand);
            double price = rs.getDouble("price");
            if (!rs.wasNull()) model.setPrice(price);
            String sendfreq = rs.getString("SENDFREQ");
            if (!rs.wasNull()) model.setSendFreque(sendfreq);
            String name = rs.getString("name");
            if (!rs.wasNull()) model.setName(name);
            int status = rs.getInt("STATUS");
            model.setStatus(status);
            int displayinweb = rs.getInt("DISPLAYINWEB");
            model.setDisplayInWeb(displayinweb);

            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 (SubmitCommandObject[])list.toArray(new SubmitCommandObject[list.size()]);
  }
  public static SubmitCommandObject[] getOrderMo(int num) throws SQLException {//查询所有数据

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();
        try{
          con = ConnectionManager.getInstance().getConnection(SmsServiceDao.class);
          StringBuffer sql = new StringBuffer("select a.id,a.feebusinessid,a.cntbusinessid,");
          sql.append( "a.keyword,a.name,a.description,a.sample,a.provider,a.servicetypeid,");
          sql.append( " a.PRICE,a.SUBMITFEETYPE,a.FEESTAND,a.SENDFREQ,");
          sql.append( " a.STATUS,a.DISPLAYINWEB");
          //程序修改为取出用户已经订阅并排序的数据和用户没有订阅的数据
          sql.append( " ,sum(decode(b.status,'1',1,0)) cnt");
          //sql.append( " from submitcommand a,(");
          sql.append( " from submitcommand a,SUBMITRECORD b");
          sql.append( " where a.status='"+Status.ENABLED.getValue()+"' and a.id = b.submitid (+)");
          sql.append( " group by a.id,a.feebusinessid,a.cntbusinessid,");
          sql.append( " a.keyword,a.name,a.description,a.sample,a.provider,a.servicetypeid, ");
          sql.append( " a.PRICE,a.SUBMITFEETYPE,a.FEESTAND,a.SENDFREQ, ");
          sql.append( " a.STATUS,a.DISPLAYINWEB ");
          sql.append( " order by cnt desc ");
          /*
          sql.append( " select count(*) cnt,b.submitid id from SUBMITRECORD b ");
          sql.append( " where b.status='1' group by b.SubmitId ) c ");
          sql.append( " where a.id = c.id ");
          sql.append( " order by c.cnt desc");*/
          //logger.equals(sql.toString());
          logger.debug(sql.toString());
          pstmt = con.prepareStatement(sql.toString());
          rs = pstmt.executeQuery();
          while (rs.next() && num>0){
            SubmitCommandObject model = new SubmitCommandObject();
            String id = rs.getString("id");
            if (!rs.wasNull()) model.setId(id);
            String feeid = rs.getString("feebusinessid");
            if (!rs.wasNull()) model.setFeeBusinessId(feeid);
            String cntid = rs.getString("cntbusinessid");
            if (!rs.wasNull()) model.setCntBusinessId(cntid);
            String keyword = rs.getString("keyword");
            if (!rs.wasNull()) model.setKeyWord(keyword);
            String desc = rs.getString("description");
            if (!rs.wasNull()) model.setDescription(desc);
            String sample = rs.getString("sample");
            if (!rs.wasNull()) model.setSample(sample);
            String provider = rs.getString("provider");
            if (!rs.wasNull()) model.setProvider(provider);
            String servicetypeid = rs.getString("servicetypeid");
            if (!rs.wasNull()) model.setServiceTypeId(servicetypeid);
            int submitfeetype = rs.getInt("SUBMITFEETYPE");
            if (!rs.wasNull()) model.setSubmitFeeType(submitfeetype);
            String feestand = rs.getString("FEESTAND");
            if (!rs.wasNull()) model.setFeeDesc(feestand);
            double price = rs.getDouble("price");
            if (!rs.wasNull()) model.setPrice(price);
            String sendfreq = rs.getString("SENDFREQ");
            if (!rs.wasNull()) model.setSendFreque(sendfreq);
            String name = rs.getString("name");
            if (!rs.wasNull()) model.setName(name);
            int status = rs.getInt("STATUS");
            model.setStatus(status);
            int displayinweb = rs.getInt("DISPLAYINWEB");
            model.setDisplayInWeb(displayinweb);
            num--;
            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 (SubmitCommandObject[])list.toArray(new SubmitCommandObject[list.size()]);
  }


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