voicedao.java

采用JAVA开发

      if(aVoiceValueObject.getUserId()!=null){
      sql.append(" and A.USER_ID=").append(aVoiceValueObject.getUserId());
      }

      if(aVoiceValueObject.getUserName()!=null&&!aVoiceValueObject.getUserName().equals("")){
      sql.append(" and A.USER_NAME='"+aVoiceValueObject.getUserName()).append("'");
      }

      //-----Result between variable start and variable end
      if (start != -1 && end != -1){
      String mainSql = sql.toString();
      sql = new StringBuffer("select * from ( select a.*,rownum as tmprow from (").append(mainSql).append(") a where rownum <=").append(end).append(") where tmprow>= ").append(start);
      }

      ResultSet rs = null;
      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      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));
        String strUSER_NAME =rs.getString("USER_NAME");
       if(!rs.wasNull()) model.setUserName(strUSER_NAME.trim());
        list.add(model);
      }
      rs.close(); 
      prepStmt.close();
      return list ;
     } 

    public ArrayList supplements(Connection aConnection,VoiceValueObject[] manyVoiceValueObject,int start,int end) throws SQLException  { 
      //---- variable sql be provided througth analyze the ValueObject  
      StringBuffer sql = new StringBuffer("SELECT A.ID, A.CATALOG_ID, A.DESCRIPTION, A.COUNT, A.CREATE_DATE, A.PRICE, A.TYPE, A.NAME, A.USER_ID, A.USER_NAME FROM VOICE A WHERE 1 = 0 " );

      for(int i=0;i<manyVoiceValueObject.length;i++){
      sql.append( " or ( 1=1 ");
      if(manyVoiceValueObject[i].getId()!=null){
      sql.append(" and A.ID=").append(manyVoiceValueObject[i].getId());
      }

      if(manyVoiceValueObject[i].getCatalogId()!=null){
      sql.append(" and A.CATALOG_ID=").append(manyVoiceValueObject[i].getCatalogId());
      }

      if(manyVoiceValueObject[i].getDescription()!=null&&!manyVoiceValueObject[i].getDescription().equals("")){
      sql.append(" and A.DESCRIPTION='"+manyVoiceValueObject[i].getDescription()).append("'");
      }

      if(manyVoiceValueObject[i].getCount()!=null){
      sql.append(" and A.COUNT=").append(manyVoiceValueObject[i].getCount());
      }

      if(manyVoiceValueObject[i].getCreateDate()!=null){
      sql.append(" and A.CREATE_DATE=to_date('"+(manyVoiceValueObject[i].getCreateDate().getYear()+1900)+"/"+(manyVoiceValueObject[i].getCreateDate().getMonth()+1)+"/"+manyVoiceValueObject[i].getCreateDate().getDate()+"','YYYY/MM/DD')");
      }

      if(manyVoiceValueObject[i].getPrice()!=null){
      sql.append(" and A.PRICE=").append(manyVoiceValueObject[i].getPrice());
      }

      if(manyVoiceValueObject[i].getType()!=null){
      sql.append(" and A.TYPE=").append(manyVoiceValueObject[i].getType());
      }

      if(manyVoiceValueObject[i].getName()!=null&&!manyVoiceValueObject[i].getName().equals("")){
      sql.append(" and A.NAME='"+manyVoiceValueObject[i].getName()).append("'");
      }

      if(manyVoiceValueObject[i].getUserId()!=null){
      sql.append(" and A.USER_ID=").append(manyVoiceValueObject[i].getUserId());
      }

      if(manyVoiceValueObject[i].getUserName()!=null&&!manyVoiceValueObject[i].getUserName().equals("")){
      sql.append(" and A.USER_NAME='"+manyVoiceValueObject[i].getUserName()).append("'");
      }

      sql.append(")");
      }
      //-----Result between variable start and variable end
      if (start != -1 && end != -1){
      String mainSql = sql.toString();
      sql = new StringBuffer("select * from ( select a.*,rownum as tmprow from (").append(mainSql).append(") a where rownum <=").append(end).append(") where tmprow>= ").append(start);
      }

      ResultSet rs = null;
      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      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));
        String strUSER_NAME =rs.getString("USER_NAME");
       if(!rs.wasNull()) model.setUserName(strUSER_NAME.trim());
        list.add(model);
      }
      rs.close(); 
      prepStmt.close();
      return list ;
     } 

    public int size(Connection aConnection,VoiceQueryValue aVoiceQueryValue) throws SQLException  { 
      StringBuffer sql = new StringBuffer("select  count(*) tsize from VOICE A where 1=1 " );

      String condition = aVoiceQueryValue.toSql();
      sql.append(condition);
      ResultSet rs = null;
      int  size = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      rs = prepStmt.executeQuery();
      if(rs.next())  size = rs.getInt(1);
      rs.close(); 
      prepStmt.close();
      return size ;
     } 

    public ArrayList all(Connection aConnection) throws SQLException  { 
      StringBuffer sql = new StringBuffer("SELECT A.ID, A.CATALOG_ID, A.DESCRIPTION, A.COUNT, A.CREATE_DATE, A.PRICE, A.TYPE, A.NAME, A.USER_ID, A.USER_NAME FROM VOICE A WHERE 1 = 1 " );

      ResultSet rs = null;
      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      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));
        String strUSER_NAME =rs.getString("USER_NAME");
       if(!rs.wasNull()) model.setUserName(strUSER_NAME.trim());
        list.add(model);
      }
      rs.close(); 
      prepStmt.close();
      return list ;
     } 

    public ArrayList selectByQueryValue(Connection aConnection,VoiceQueryValue aVoiceQueryValue,int start,int end) throws SQLException  { 
      //---- variable sql be provided througth analyze the ValueObject  
      StringBuffer sql = new StringBuffer("SELECT A.ID, A.CATALOG_ID, A.DESCRIPTION, A.COUNT, A.CREATE_DATE, A.PRICE, A.TYPE, A.NAME, A.USER_ID, A.USER_NAME FROM VOICE A WHERE 1 = 1 " );

      String condition = aVoiceQueryValue.toSql();
      sql.append(condition);
      //-----Result between variable start and variable end
      if (start != -1 && end != -1){
      String mainSql = sql.toString();
      sql = new StringBuffer("select * from ( select a.*,rownum as tmprow from (").append(mainSql).append(") a where rownum <=").append(end).append(") where tmprow>= ").append(start);
      }

      ResultSet rs = null;
      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      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));
        String strUSER_NAME =rs.getString("USER_NAME");
       if(!rs.wasNull()) model.setUserName(strUSER_NAME.trim());
        list.add(model);
      }
      rs.close(); 
      prepStmt.close();
      return list ;
     } 

    public VoiceValueObject getByPrimaryKey(Connection aConnection,Integer id) throws SQLException  { 
      String sql = "select * from VOICE A where 1=1  and A.ID=?";
      ResultSet rs = null;
      ArrayList list = new ArrayList();
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      prepStmt.setInt(1,id.intValue());
      rs = prepStmt.executeQuery();
      while( rs.next() ) { 
        VoiceValueObject model = new VoiceValueObject();
      model.setId(id);
        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));
        String strUSER_NAME =rs.getString("USER_NAME");
       if(!rs.wasNull()) model.setUserName(strUSER_NAME.trim());
        list.add(model);
      }
      rs.close(); 
      prepStmt.close();
      return list.size()==0?null:(VoiceValueObject)list.get(0) ;
     } 

    public void  deleteByPrimaryKey(Connection aConnection,Integer id) throws SQLException  { 
      String sql = "delete  from VOICE where 1=1   and ID=?";
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      prepStmt.setInt(1,id.intValue());
       prepStmt.executeUpdate();
     } 

      //-----Codes above generated by dao 
 }