userhistorydao.java

采用JAVA开发

package com.gctech.sms.voice.dao;

import java.sql.* ;
import java.util.ArrayList;

  /* author:[e-mail] */
  public class UserHistoryDAO { 
      //-----Codes below generated by dao 

    public int insert(Connection aConnection,UserHistoryValueObject aUserHistoryValueObject) throws SQLException  { 
      String sql = " INSERT INTO USER_HISTORY A ( A.ID, A.USER_ID, A.VOICE_ID, A.CONSUME_DATE, A.TYPE, A.PRICE ) VALUES ( ?, ?, ?, ?, ?, ? )  " ;
      int result = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      if (aUserHistoryValueObject.getId()  == null){ 
       prepStmt.setNull(1, Types.INTEGER);
      }else{ 
       prepStmt.setInt(1, aUserHistoryValueObject.getId().intValue() ); 
      }      
      if (aUserHistoryValueObject.getUserId()  == null){ 
       prepStmt.setNull(2, Types.INTEGER);
      }else{ 
       prepStmt.setInt(2, aUserHistoryValueObject.getUserId().intValue() ); 
      }      
      if (aUserHistoryValueObject.getVoiceId()  == null){ 
       prepStmt.setNull(3, Types.INTEGER);
      }else{ 
       prepStmt.setInt(3, aUserHistoryValueObject.getVoiceId().intValue() ); 
      }      
      if (aUserHistoryValueObject.getConsumeDate()  == null){ 
       prepStmt.setNull(4, Types.TIMESTAMP);
      }else{ 
       prepStmt.setTimestamp(4, new Timestamp(aUserHistoryValueObject.getConsumeDate().getTime()) ); 
      }
      if (aUserHistoryValueObject.getType()  == null){ 
       prepStmt.setNull(5, Types.INTEGER);
      }else{ 
       prepStmt.setInt(5, aUserHistoryValueObject.getType().intValue() ); 
      }      
      if (aUserHistoryValueObject.getPrice()  == null){ 
       prepStmt.setNull(6, Types.INTEGER);
      }else{ 
       prepStmt.setInt(6, aUserHistoryValueObject.getPrice().intValue() ); 
      }      
      result = prepStmt.executeUpdate();
      prepStmt.close();
      return result ;
     } 

    public int update(Connection aConnection,UserHistoryValueObject aUserHistoryValueObject) throws SQLException  { 
      StringBuffer sql = new StringBuffer(" update USER_HISTORY A  set " );
            if(aUserHistoryValueObject.getUserId()!=null){
            if(aUserHistoryValueObject.getUserId().doubleValue() == -1.0)sql.append(" A.USER_ID= null,");
            else sql.append("  A.USER_ID=").append(aUserHistoryValueObject.getUserId()).append(",");
      }
      if(aUserHistoryValueObject.getVoiceId()!=null){
            if(aUserHistoryValueObject.getVoiceId().doubleValue() == -1.0)sql.append(" A.VOICE_ID= null,");
            else sql.append("  A.VOICE_ID=").append(aUserHistoryValueObject.getVoiceId()).append(",");
      }
      if(aUserHistoryValueObject.getConsumeDate()!=null){
            if(aUserHistoryValueObject.getConsumeDate().getTime()!=0)  sql.append(" A.CONSUME_DATE=to_date('"+(aUserHistoryValueObject.getConsumeDate().getYear()+1900)+"/"+(aUserHistoryValueObject.getConsumeDate().getMonth()+1)+"/"+aUserHistoryValueObject.getConsumeDate().getDate()+"/"+aUserHistoryValueObject.getConsumeDate().getHours()+"/"+aUserHistoryValueObject.getConsumeDate().getMinutes()+"/"+aUserHistoryValueObject.getConsumeDate().getSeconds()+"','YYYY/MM/DD/HH24/MI/SS'),");
            else sql.append(" A.CONSUME_DATE=null,");
      }
      if(aUserHistoryValueObject.getType()!=null){
            if(aUserHistoryValueObject.getType().doubleValue() == -1.0)sql.append(" A.TYPE= null,");
            else sql.append("  A.TYPE=").append(aUserHistoryValueObject.getType()).append(",");
      }
      if(aUserHistoryValueObject.getPrice()!=null){
            if(aUserHistoryValueObject.getPrice().doubleValue() == -1.0)sql.append(" A.PRICE= null,");
            else sql.append("  A.PRICE=").append(aUserHistoryValueObject.getPrice()).append(",");
      }

      //remove the last comma
      sql.setLength(sql.length()-1);
      sql .append( " where 1=1");
      if(aUserHistoryValueObject.getId()!=null){
      sql.append(" and A.ID=").append(aUserHistoryValueObject.getId());
      }

      int result = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      result = prepStmt.executeUpdate();
      prepStmt.close();
      return result ;
     } 

    public int updateByQueryValue(Connection aConnection,UserHistoryValueObject aUserHistoryValueObject,UserHistoryQueryValue aUserHistoryQueryValue) throws SQLException  { 
      StringBuffer sql = new StringBuffer(" update USER_HISTORY A  set " );
            if(aUserHistoryValueObject.getUserId()!=null){
            if(aUserHistoryValueObject.getUserId().doubleValue() == -1.0)sql.append(" A.USER_ID= null,");
            else sql.append("  A.USER_ID=").append(aUserHistoryValueObject.getUserId()).append(",");
      }
      if(aUserHistoryValueObject.getVoiceId()!=null){
            if(aUserHistoryValueObject.getVoiceId().doubleValue() == -1.0)sql.append(" A.VOICE_ID= null,");
            else sql.append("  A.VOICE_ID=").append(aUserHistoryValueObject.getVoiceId()).append(",");
      }
      if(aUserHistoryValueObject.getConsumeDate()!=null){
            if(aUserHistoryValueObject.getConsumeDate().getTime()!=0)  sql.append(" A.CONSUME_DATE=to_date('"+(aUserHistoryValueObject.getConsumeDate().getYear()+1900)+"/"+(aUserHistoryValueObject.getConsumeDate().getMonth()+1)+"/"+aUserHistoryValueObject.getConsumeDate().getDate()+"/"+aUserHistoryValueObject.getConsumeDate().getHours()+"/"+aUserHistoryValueObject.getConsumeDate().getMinutes()+"/"+aUserHistoryValueObject.getConsumeDate().getSeconds()+"','YYYY/MM/DD/HH24/MI/SS'),");
            else sql.append(" A.CONSUME_DATE=null,");
      }
      if(aUserHistoryValueObject.getType()!=null){
            if(aUserHistoryValueObject.getType().doubleValue() == -1.0)sql.append(" A.TYPE= null,");
            else sql.append("  A.TYPE=").append(aUserHistoryValueObject.getType()).append(",");
      }
      if(aUserHistoryValueObject.getPrice()!=null){
            if(aUserHistoryValueObject.getPrice().doubleValue() == -1.0)sql.append(" A.PRICE= null,");
            else sql.append("  A.PRICE=").append(aUserHistoryValueObject.getPrice()).append(",");
      }

      //remove the last comma
      sql.setLength(sql.length()-1);
      sql .append( " where 1=1");
      String condition = aUserHistoryQueryValue.toSql();
      sql.append(condition);
      int result = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      result = prepStmt.executeUpdate();
      prepStmt.close();
      return result ;
     } 

    public int delete(Connection aConnection,UserHistoryValueObject aUserHistoryValueObject) throws SQLException  { 
      StringBuffer sql = new StringBuffer(" delete  from  USER_HISTORY A where 1=1");

      if(aUserHistoryValueObject.getId()!=null){
      sql.append(" and A.ID=").append(aUserHistoryValueObject.getId());
      }

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

      if(aUserHistoryValueObject.getVoiceId()!=null){
      sql.append(" and A.VOICE_ID=").append(aUserHistoryValueObject.getVoiceId());
      }

      if(aUserHistoryValueObject.getConsumeDate()!=null){
      sql.append(" and A.CONSUME_DATE=to_date('"+(aUserHistoryValueObject.getConsumeDate().getYear()+1900)+"/"+(aUserHistoryValueObject.getConsumeDate().getMonth()+1)+"/"+aUserHistoryValueObject.getConsumeDate().getDate()+"','YYYY/MM/DD')");
      }

      if(aUserHistoryValueObject.getType()!=null){
      sql.append(" and A.TYPE=").append(aUserHistoryValueObject.getType());
      }

      if(aUserHistoryValueObject.getPrice()!=null){
      sql.append(" and A.PRICE=").append(aUserHistoryValueObject.getPrice());
      }

      int result = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      result = prepStmt.executeUpdate();
      prepStmt.close();
      return result ;
     } 

    public int deleteByQueryValue(Connection aConnection,UserHistoryQueryValue aUserHistoryQueryValue) throws SQLException  { 
      StringBuffer sql =new StringBuffer( " delete  from  USER_HISTORY A where 1=1  ");

      String condition = aUserHistoryQueryValue.toSql();
      sql.append(condition);
      int result = 0;
      PreparedStatement prepStmt = aConnection.prepareStatement( sql.toString() );
      result = prepStmt.executeUpdate();
      prepStmt.close();
      return result ;
     } 

    public ArrayList supplement(Connection aConnection,UserHistoryValueObject aUserHistoryValueObject,int start,int end) throws SQLException  { 
      //---- variable sql be provided througth analyze the ValueObject  
      StringBuffer sql = new StringBuffer("SELECT A.ID, A.USER_ID, A.VOICE_ID, A.CONSUME_DATE, A.TYPE, A.PRICE FROM USER_HISTORY A WHERE 1 = 1 " );

      if(aUserHistoryValueObject.getId()!=null){
      sql.append(" and A.ID=").append(aUserHistoryValueObject.getId());
      }

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

      if(aUserHistoryValueObject.getVoiceId()!=null){
      sql.append(" and A.VOICE_ID=").append(aUserHistoryValueObject.getVoiceId());
      }

      if(aUserHistoryValueObject.getConsumeDate()!=null){
      sql.append(" and A.CONSUME_DATE=to_date('"+(aUserHistoryValueObject.getConsumeDate().getYear()+1900)+"/"+(aUserHistoryValueObject.getConsumeDate().getMonth()+1)+"/"+aUserHistoryValueObject.getConsumeDate().getDate()+"','YYYY/MM/DD')");
      }

      if(aUserHistoryValueObject.getType()!=null){
      sql.append(" and A.TYPE=").append(aUserHistoryValueObject.getType());
      }

      if(aUserHistoryValueObject.getPrice()!=null){
      sql.append(" and A.PRICE=").append(aUserHistoryValueObject.getPrice());
      }

      //-----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);
      }