ProcessDB实时/时序数据库——JDBC读写历史时序数据

ProcessDB

已于 2022-12-12 16:25:59 修改

阅读量160

点赞数

分类专栏： # ProcessDB的JDBC开发接口文章标签：时序数据库物联网数据库开发 ProcessDB jdbc

于 2022-11-08 15:50:05 首次发布

本文链接：https://blog.csdn.net/sun_race/article/details/127751951

版权

ProcessDB的JDBC开发接口专栏收录该内容

16 篇文章 0 订阅

订阅专栏

一、历史数据表字段介绍

CREATE TABLE HIS_TABLE ( 

 ID_F          INT;//数据点ID,

 NAME_F        STRING;//数据点名称,

 VALUE_F       STRING;//历史数据值,

 UNIT_F        STRING;//数据单位,

 QUALITY_F     STRING;//数据质量,

 TIME_F        STRING;//历史数据时间,

 MESSAGE_F     STRING;//历史数据值(特殊),

 DATE_TYPE     STRING;//数据类型,

 INTERVAL_F    INT;//历史数据时间间隔

 )

二、插入历史时序数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) throws Exception {
		try {
			Class.forName("com.processdb.jdbc.JDBC");
			Connection connection = null;
			Statement statement = null;
			try {
				// 对数据库进行连接
				connection = DriverManager.getConnection("jdbc:processdb://localhost:8301/instance01", "root", "root");
				statement = connection.createStatement();
				// 设置超时时间
				statement.setQueryTimeout(30); // set timeout to 30 sec.
				int i = 0;
				while (i < 1) {
					i++;
					// 插入sql
					/**插入历史数据表 HIS_TABLE
					 * 所需字段
					 * name_f = 'public.sys.PDB_OS_CPU_USAGE' 数据点的位置（必填）
					 * NAME_F 数据点的位置（必填）
					 * TIME_F 历史数据的时间（必填）
					 * DATE_TYPE 数据类型（必填）
					 * VALUE_F 插入数据的值（必填）
					 */
					String sql = "INSERT INTO HIS_TABLE (NAME_F,TIME_F,DATE_TYPE,VALUE_F) values ('D77.T77.P77','1669701648000','FLOAT32',888)";
					// 运行sql发起请求
					 statement.executeUpdate(sql);
					// 查询结果存储在ResultSet
					System.out.println("Insert history data into table: history_table.");}}
			 catch (SQLException e) {
				System.err.println(e.getMessage());
				e.printStackTrace();
			} finally {
				try {

					if (statement != null) {
						statement.close();
					}
					if (connection != null) {
						connection.close();
					}
				} catch (SQLException e) {
					System.err.println(e);
				}
			}
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return;
		}
		return;
	}
}

三、修改历史时序数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) {
		try {
			Class.forName("com.processdb.jdbc.JDBC");

			Connection connection = null;
			Statement statement = null;
			try {
				// 对数据库进行连接
				connection = DriverManager.getConnection("jdbc:processdb://localhost:8301/instance01", "root", "root");
				statement = connection.createStatement();
				// 设置超时时间
				statement.setQueryTimeout(30); // set timeout to 30 sec.
				int i = 0;
				while (i < 1) {
					i++;
					// sql
					/**修改实时数据 HIS_TABLE
					 * 所需字段
					 * name_f  数据库名称加数据表名称加数据点名称（例如：public.sys.PDB_OS_CPU_USAGE）
					 * TIME_F  历史数据时间
					 * 可修改字段
					 * value_f  历史数据数据值
					 */
					String sql = "Update  HIS_TABLE set value_f = 698 where name_f= 'D30.T31.P30' and TIME_f = '2022-11-08 09:39:34'";		
					// 运行sql发起请求
					 statement.executeUpdate(sql);
					System.out.println("Update historyData Success");
				}
			} catch (SQLException e) {
				System.err.println(e.getMessage());
				e.printStackTrace();
			} finally {
				try {

					if (statement != null) {
						statement.close();
					}
					if (connection != null) {
						connection.close();
					}
				} catch (SQLException e) {
					System.err.println(e);
				}
			}
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return;
		}
		return;
	}
}

四、查询历史时序数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) {
		try {
			Class.forName("com.processdb.jdbc.JDBC");

			Connection connection = null;
			Statement statement = null;
			try {
				// 对数据库进行连接
				connection = DriverManager.getConnection("jdbc:processdb://localhost:8301/instance01", "root", "root");
				statement = connection.createStatement();
				// 设置超时时间
				statement.setQueryTimeout(30); // set timeout to 30 sec.
				ResultSet rs = null;
				int i = 0;
				while (i < 1) {
					i++;
					// 查询sql
					/**查询历史数据表 HIS_TABLE
					 * 所需字段
					 * name_f='public.sys.PDB_OS_CPU_USAGE' 数据点的位置(必填)
					 * time_f>'2022-10-26 00:00:00' and time_f<'2022-10-26 15:35:18'数据时间段
					 * 超限
					 * 可查询字段
					 * ID_F 数据id
					 * NAME_F 数据点的位置
					 * TIME_F 历史数据时间
					 * QUALITY_F 数据质量是否完好
					 * VALUE_F 历史数据值
					 */
					//	String sql = "select  id_f,name_f,INTERVAL_F,time_f,QUALITY_F,value_f  from his_table where name_f='public.sys.PDB_OS_CPU_USAGE' and time_f>'2022-10-26 00:00:00' and time_f<'2022-10-26 14:36:37'";
					String sql = "select  *  from his_table where name_f='D30.T31.P30'  and time_f>'2022-11-08 09:50:00' and time_f<'2022-11-09 14:36:37'";
					// 运行sql发起请求
					rs = statement.executeQuery(sql);
					// 查询结果存储在ResultSet(可以返回多条结果)
					System.out.println("Query history data from table: his_table.");
					while (rs.next()) {
						// read the result set
					System.out.println("id_f:" + rs.getString("id_f") + ",name_f:" + rs.getString("name_f")+  ",INTERVAL_F:" + rs.getInt("INTERVAL_F")+ ",QUALITY_F:" + rs.getString("QUALITY_F")+  ",TIME_F:" + rs.getString("time_f") + ",value_f:" + rs.getString("value_f"));	}
					System.out.println("Count: " + rs.getRow());

				}
			} catch (SQLException e) {
				System.err.println(e.getMessage());
				e.printStackTrace();
			} finally {
				try {

					if (statement != null) {
						statement.close();
					}
					if (connection != null) {
						connection.close();
					}
				} catch (SQLException e) {
					System.err.println(e);
				}
			}
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return;
		}
		return;
	}
}

示例运行结果：

Query history data from table: his_table.
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 09:55:52.0,value_f:66.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 10:18:00.0,value_f:78.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 10:19:25.0,value_f:88.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 10:19:44.0,value_f:98.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 10:20:18.0,value_f:1000.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 10:20:33.0,value_f:150.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 14:44:26.0,value_f:56.0
id_f:1006000013,name_f:D30.T31.P30,INTERVAL_F:0,QUALITY_F:GOOD,TIME_F:2022-11-08 14:53:10.0,value_f:199.0
Count: 8