jar

PIJDBCDriver.jar
需要的来我链接自取

java代码

				
				
String dasName = "10.109.x.xxx";
String dataSourceName = "10.109.x.xxx";
String isTrustedConnection = "No";
String protocolOrder = "Https/Soap:5461,NetTcp:5462";
String logLevel = "0";



Connection connection = null;
PreparedStatement ps = null;
Statement statement = null;
ResultSet result = null;
String sql = null;
       
       
String url = "";
String driverClassName = "com.osisoft.jdbc.Driver";
ResultSet resultSet = null;
Properties properties = new Properties();

    
String interval = interVal;
String starttime = startTime;
String endtime = endTime;
TagStrs = TagStrs.replace("@", "/");
String[] alltags_pis = TagStrs.split("\\,");
String alltags_pi = null;
for(int i=0;i<alltags_pis.length;i++){
	if(alltags_pi == null){
		alltags_pi = "'"+ alltags_pis[i]+"'";
	}else{
		alltags_pi =alltags_pi + ","+ "'"+ alltags_pis[i]+"'";
	}
}
	
String head = tableHead;

if("".equals(interval)||interval==null){
	interval = "realtime";
}

if("".equals(endtime)||endtime==null){//结束时间没有 就当前时间 五分钟
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	Calendar nowTime = Calendar.getInstance();
	nowTime.add(Calendar.MINUTE, -5);
	endtime = sdf.format(nowTime.getTime());
	if("".equals(starttime)||starttime==null){// 起始时间 调整到 结束时间的 1H 
		nowTime.add(Calendar.HOUR, -1);
		starttime = sdf.format(nowTime.getTime());
	}
}
	
	
if("avg".equals(interval)){//平均值
	sql = "select tag,'" + endtime + "' as time ,"
			//+ " avg(value) as svalue, "
			+ " avg(cast ( (case when substr(cast(value as string),1,1)='.' then concat('0',cast(value as string))  else  cast(value as string)  end ) as Float64) ) as svalue , "
			+ " max(cast ( (case when substr(cast(value as string),1,1)='.' then concat('0',cast(value as string))  else  cast(value as string)  end ) as Float64) ) as maxvalue ,"
			+ " min(cast ( (case when substr(cast(value as string),1,1)='.' then concat('0',cast(value as string))  else  cast(value as string)  end ) as Float64) ) as minvalue "
			+ " from [piarchive]..[piinterp2] where ( tag in  (" + alltags_pi + ") ) ";
	sql = sql + " and status = 0 and (time between  '" + starttime + "' and  '" + endtime + "')  "  ;
	sql = sql + " and timestep  = '1m' group by tag order by tag ";
}else if("realtime".equals(interval)){//最新值
	sql = "select a.tag,a.time,a.value as svalue  from [piarchive]..[pisnapshot] a,pipoint b where a.tag = b.tag and a.status = 0 and a.tag in (" + alltags_pi + ")  order by a.tag";
}else{
	sql = "select tag,time,value as svalue,status  from [piarchive]..[piinterp2] where ( tag in  (" + alltags_pi + ") ) ";
	sql = sql + " and status = 0 and (time between  '" + starttime + "' and  '" + endtime + "')  "  ;
	sql = sql + " and timestep  = '" + interval + "m' order by tag,time ";
}
	

//url = "jdbc:piintegrator://" + dasName;
//url = "jdbc:pioledb://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";
url = "jdbc:pioledb://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";
//System.out.println(url);
properties.put("EnableCertificateValidation", "No");//启用证书验证
properties.put("TrustedConnection", isTrustedConnection);
properties.put("ProtocolOrder", protocolOrder);

properties.put("LogConsole", "True");
properties.put("LogLevel", logLevel);

properties.put("user", "PIVisionServer");
properties.put("password", "Password!");

properties.put("Port", "5461");
    
 
String headavg = "值";
String headmax = "与上一时间点值之差";
String headmin = "";

if ("avg".equals(interval)){//平均值
	headavg = "平均值";  
	 headmax = "最大值";
	 headmin = "最小值";
}else if("realtime".equals(interval)){//最新值
		headavg = "最新值";  
	 headmax = "";
	 headmin = "";
}
       

try{
    Class.forName(driverClassName).newInstance();
    connection = DriverManager.getConnection(url, properties);  
    System.out.println("最终的带值的sql-----------------------------------------时间"+new DATE()+"--------------!!!!!!!-----------------------="+sql);
    ps = connection.prepareStatement(sql);
    //System.out.println("1");
statement = connection.createStatement();
	//System.out.println("2");
	result = ps.executeQuery();
	//System.out.println("3");
	
	String LastTagName = "!@#$%";
	double LastTagValue = 0;
	
	while (result.next()) {
		double sValueDiff = 0;
		String  sName = String.valueOf(result.getString("tag"));
		//System.out.println("sName ="+sName);
		
		String  sTime = dateFomate( String.valueOf(result.getString("time")));
		//System.out.println("sTimeYYYY ="+String.valueOf(result.getString("time")));
		//System.out.println("sTime ="+sTime);
		double  sValue = result.getDouble("sValue");
		//System.out.println("sValue ="+sValue);
		
		if(LastTagName.equals(sName)){
			sValueDiff = sValue - LastTagValue;
			//System.out.println("sValueDiff ="+sValueDiff);
		}
		
		LastTagName = sName;
        LastTagValue =sValue;
        
		double minvalue = 0;
		if("avg".equals(interval)){
			sValueDiff = result.getDouble("maxvalue");
			minvalue = result.getDouble("minvalue");
		}
	}
      }catch (Exception ex) {
          System.err.println(ex);  
      }finally {
          try {
              if(resultSet != null) resultSet.close();
              if(connection != null) connection.close();
          } catch (SQLException ex) {
              System.err.println(ex);
          }
          
      } 
}