ProcessDB实时/时序数据库——JAVA连接ODBC查询历史数据

目录

一、历史数据字段介绍

二、查询历史时序数据

---

前言

上文已经介绍JAVA连接ODBC使用ProcessDB的基本操作，本文将针对历史数据的相关操作进行介绍

---

一、历史数据字段介绍

字段	注释
id_f	数据点id
name_f	数据点名
time_f	数据时间
query_his_type_f	历史数据类型
quality_f	数据质量
value_f	实时数据值
message_f	数据值(特殊特殊类型)
desc_f	数据点描述
interval_f	时间间隔（以秒为单位）

二、查询历史时序数据

我们以查询public库下sys表下PDB_OS_CPU_USAGE数据点的历史时序数据举例

代码如下：

  
  import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class Test {
	public static void main(String[] args) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			SimpleDateFormat dateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn = DriverManager.getConnection("jdbc:odbc:ProcessDB V2 Datasource", "root", "root");
			pstmt = conn.prepareStatement("select name_f,time_f,quality_f,value_f,id_f from his_table where interval_f = 60 and name_f='PUBLIC.SYS.PDB_OS_CPU_USAGE' and query_his_type_f='SAMPLE' and interval_f=0 and time_f>'2022-11-30 14:40:00' and time_f<'2022-11-30 15:00:00'");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Timestamp timestamp = rs.getTimestamp(2);
				System.out.println(rs.getString(1) + ","+ dateFmt.format(timestamp) + "," + rs.getInt(3)+ "," + rs.getFloat(4)+","+rs.getInt(5));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}

			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				pstmt = null;
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}
}

示例运行结果：

PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:40:00,0,2.582549,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:41:00,0,0.0,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:42:00,0,1.329122,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:43:00,0,1.287035,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:44:00,0,1.128098,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:45:00,0,0.0,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:46:00,0,1.121045,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:47:00,0,1.166921,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:48:00,0,0.993044,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:49:00,0,0.627003,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:50:00,0,0.830381,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:51:00,0,0.0,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:52:00,0,2.966756,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:53:00,0,1.384721,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:54:00,0,0.0,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:55:00,0,1.669481,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:56:00,0,2.345922,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:57:00,0,1.882745,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:58:00,0,0.0,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 14:59:00,0,1.453202,1001000001
PUBLIC.SYS.PDB_OS_CPU_USAGE,2022-11-30 15:00:00,0,0.0,1001000001