oradbaccess.java

文本文件数据提取

/**
 * 
 */
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
/**
 * @author syl
 *
 */
public class OraDBAccess {
	
	 private final String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
	    
	    //以下使用的syl就是Oracle里的表空间
	    private String oracleUrlToConnect = new String();
	    private Connection myConnection = null;
	    /**
	     * To load the jdbc driver
	     * 
	     */
	    public OraDBAccess()
	    {
	        try
	        {
	            Class.forName(oracleDriverName);
	        }catch(ClassNotFoundException ex)
	        {
	            System.out.println(getErrorMessage(ex,"The Driver loaded error,please contact to your Software Designer!").toString());
	        }
	    }
	    
	    public StringBuffer getErrorMessage(Exception ex,String alarmMessage)
	    {
	        StringBuffer errorStringBuffer = new StringBuffer();
	        errorStringBuffer.append(alarmMessage);
	        errorStringBuffer.append(ex.getMessage());
	        return errorStringBuffer;
	    }
	    
	    /**
	     * getConnection method 
	     * @return Connection
	     */
	    public Connection getConnection(String host, String sid, String name, String pwd)
	    {
	        try
	        {
	        	oracleUrlToConnect = "jdbc:oracle:thin:@"+host+":1521:"+sid;
	            this.myConnection = DriverManager.getConnection(oracleUrlToConnect,name,pwd);
	            
	        }catch(Exception ex)
	        {
	            System.out.println(getErrorMessage(ex,"Can not get connection,please contact to your Software Designer!").toString());
	        }
	        
	        return this.myConnection;
	        
	    }
	    
	    //将分好组的数据插入RECORD_BASEINFO和RECORD_VALUE数据表中
	    public void InsertGroupData(Connection conn, ArrayList<SiteData> groupDList) throws SQLException{
	    	int i,j,k;
	    	SensorData data = new SensorData();
	    	ValueDataItem dataItem = new ValueDataItem();
	    	String siteno = new String();
	    	String recordtime = new String();
	    	String meanval = new String();
	    	
	    	PreparedStatement mypst1 = conn.prepareStatement("insert into record_baseinfo(groupno,recordno,siteno,recordtime) values(?,?,?,?)");
	    	PreparedStatement mypst2 = conn.prepareStatement("insert into record_value(groupno,recordno,columnno,meanval,realflag)" +
				" values(?,?,?,?,?)");
    		for(i=0; i<groupDList.size(); i++){   	
    			System.out.println(i);
    	        for(k=0; k<groupDList.get(i).SiteData.size(); k++){
                 	data = groupDList.get(i).SiteData.get(k);
                 	siteno = data.SiteNo;
                 	recordtime = data.Datatime;
                 	//将基本信息插入RECORD_BASEINFO
              
	    			mypst1.setInt(1,i+1);
	    			mypst1.setInt(2,k+1);
	    			mypst1.setString(3,siteno);
	    			mypst1.setDate(4, Date.valueOf(recordtime));
	    			mypst1.execute();
                 	//将其它均值信息插入RECORD_VALUE
	    			int count = 0;
         	    	for(j=0; j<data.Value.size(); j++){
         	    		dataItem = data.Value.get(j);
     	    			if(dataItem.Statistic.equals("00003")){
     	    				meanval = dataItem.MeanVal;
     	    				
     	    				count ++;
    		    			mypst2.setInt(1,i+1);
    		    			mypst2.setInt(2,k+1);
    		    			mypst2.setInt(3,count);
    		    			if(meanval.equals("")||meanval.equals("Rat"))
    		    				mypst2.setNull(4, java.sql.Types.DOUBLE);
    		    			else   		    				
    		    				mypst2.setDouble(4,Double.valueOf(meanval));
    		    			if(dataItem.MeanVal.equals(""))
    		    				mypst2.setInt(5,0);
    		    			else
    		    				mypst2.setInt(5,1);
    		    			mypst2.execute();
         	    		}	    		  	    		
         	    	}
    	        }        	    		
    		}
    		System.out.println("Insert data over!");

	    }
	    
	    
	    //填补数据
	    public void FillBlankData(Connection conn) throws SQLException{
	    	
 	    	PreparedStatement mypst1 = conn.prepareStatement("select groupno,recordno,siteno,recordtime,columnno " +
	    			"from record_data_view where realflag = 0 order by groupno asc, recordno asc, columnno");
	    	//PreparedStatement mypst2 = conn.prepareStatement("select meanval from record_data_view " +
	    	//		"where  realflag = 1 and groupno=? and columnno=?  and siteno=? and  recordtime between ? and ? order by recordtime asc");
	    	Statement statement = conn.createStatement(); 
	    	String sql = new String();
	    	ResultSet rs1 = mypst1.executeQuery();
	    	int groupno;
	    	int recordno;
	    	String siteno = new String();
	    	Date recordtime;
	    	Date startDate = java.sql.Date.valueOf( "2009-04-30" );
	    	Date endDate = java.sql.Date.valueOf( "2009-04-30" );;
	    	int columnno;
	    	int count = 0;
	    	
	    	ResultSet rs2;
	    	long time;
	    	long basetime;
	    	while(rs1.next()){	
	    		groupno = rs1.getInt(1);
	    		recordno = rs1.getInt(2);
	    		siteno = rs1.getString(3);
	    		recordtime = rs1.getDate(4);
	    		columnno = rs1.getInt(5);
	    		//System.out.printf("%d,%d,%s,%s,%d\n",groupno,recordno,siteno,recordtime.toString(),columnno);
	    		//startDate = recordtime;
	    		basetime = recordtime.getTime();
	    		//System.out.println(basetime);
	    		time = basetime - 7*24*3600000;
	    		startDate.setTime(time);//向前7天
	    		//System.out.println(time);
	    		System.out.println(startDate.toString());
	    		//endDate = recordtime;
	    		time = basetime + 7*24*3600000;
	    		endDate.setTime(time);//向后7天	  
	    		//System.out.println(time);
	    		//System.out.println(endDate.toString());
	    	
	    		sql = "select avg(meanval) from record_data_view where realflag = 1 and "+
	    		    	 "groupno=" +String.valueOf(groupno)+
	    		    	 " and columnno="+String.valueOf(columnno)+
	    		    	 " and siteno='"+siteno+
	    		    	 "' and  recordtime between to_date('"+ startDate.toString()+
	    		    	 "','yy-mm-dd') and to_date('"+endDate.toString()+"','yy-mm-dd') "+
	    		    	 "order by recordtime asc";
	    		rs2 = statement.executeQuery(sql);
	    		
	    		count = 0;
	    		double avgval = 0;
	    		while(rs2.next()){
	    			avgval = rs2.getDouble(1);
	    			count ++;
	    			//System.out.printf("no=%d,meanval =%f\n",count,avgval);
	    		}
	    		
	    		//将计算好的平均值更新到表record_value表中
	    		if(avgval!=0){
	    			sql = "update record_value set meanval="+String.valueOf(avgval)+
	    		      " where groupno=" +String.valueOf(groupno)+
	    		    	 " and columnno="+String.valueOf(columnno)+	    		    
	    		    	 " and recordno="+String.valueOf(recordno);
	    		}
	    		else{
	    			sql = "update record_value set meanval=null"+
	    		      " where groupno=" +String.valueOf(groupno)+
	    		    	 " and columnno="+String.valueOf(columnno)+
	    		    	 " and recordno="+String.valueOf(recordno);
	    		}
	    		
	    		statement.executeQuery(sql);
	    	}
	
	    }
	    
	    //将填补好的数据从数据库中取出，存文件Group1.data，Group2.data,.....,GroupN.data
	    public void GenerateTargetFile(Connection conn) throws SQLException, IOException{
	    	Statement statement = conn.createStatement(); 
	    	Statement statement2 = conn.createStatement(); 
	    	String sql = new String("select distinct(groupno) from record_data_view");
	    	ResultSet rs = statement.executeQuery(sql);
	    	ResultSet rs2;
	    	int recordno;
	    	int colno;
	    	Date recordtime;
	    	double meanval;
	    	int count = 0;
	    	int oldrecordno = 0;
	    	int oldcolno = 0;
	    	String siteno = new String();
	    	String filename = new String();
	    	String str = new String();
	    	while(rs.next()){	
	    		//System.out.println(String.valueOf(rs.getInt(1)));
	    		count ++;
				String index = String.valueOf(count);
				filename = "group"+index+".data";
				File file = new File(filename);
				FileWriter out = new FileWriter(file);
		        BufferedWriter bw = new BufferedWriter(out);
	    		sql = "select recordno,siteno,recordtime,columnno,meanval from record_data_view where groupno="+String.valueOf(rs.getInt(1)) +
	    		      " order by siteno asc,recordtime asc, columnno asc";
	    		rs2 = statement2.executeQuery(sql);
	    		int subcount = 0;
	    		while(rs2.next()){
	    			subcount ++;
	    			recordno = rs2.getInt(1);
	    			siteno = rs2.getString(2);
	    			recordtime = rs2.getDate(3);
	    			colno = rs2.getInt(4);
	    			meanval = rs2.getDouble(5);
	    			//System.out.println(String.valueOf(recordno));
	    			if(subcount==1){//第一条	    
	    				if(meanval==0){
	    					str = siteno + ";"+recordtime.toString()+";";
	    				}
	    				else{
	    					str = siteno + ";"+recordtime.toString()+";"+java.lang.Double.toString(meanval);    			
	    				}
	    				
	    				bw.write(str);
	    				
	    			}
	    			else{
	    				if(oldrecordno==recordno){//行号相同
	    					if(oldcolno!=colno){//列号不同，紧跟着写文件
	    						if(meanval==0){
	    	    					str = ";";
	    	    				}
	    	    				else{
	    	    					str = ";" + String.valueOf(meanval);
	    	    				}
	    						bw.write(str);
	    					}
	    				}
	    				else{//行号不同
	    					bw.write("\n");
	    					if(meanval==0){
		    					str = siteno + ";"+recordtime.toString()+";";
		    				}
		    				else{
		    					str = siteno + ";"+recordtime.toString()+";"+java.lang.Double.toString(meanval); 
		    				}
		    				bw.write(str);
	    				}
	    			}		
	    			oldrecordno = recordno;
	    			oldcolno = colno;
	    		}
	    		bw.close();
	    		out.close();
	    	}
	    	System.out.println("finished out target files!");
	    }
	    
}