ProcessDB实时/时序数据库——JDBC_Prepared查询历史统计数据

ProcessDB

已于 2022-12-14 15:50:35 修改

阅读量190

点赞数

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

于 2022-12-12 17:15:00 首次发布

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

版权

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

16 篇文章 0 订阅

订阅专栏

前言

前文提到了用statement的方式，操作ProcessDB时序数据库，我们实际工作中可能更常使用的是 PreparedStatement方式，优点如下：1、可以防止SQL注入攻击。 2、进行动态查询。3、执行更快。4、简化拼接过程。所以，我在这里着重讲解下如何用PreparedStatement方式，操作ProcessDB实时/时序数据库的历史统计数据

一、历史统计数据字段介绍

CREATE TABLE STAT_TABLE ( 

 ID_F                        INT;//数据点ID,

 NAME_F                      STRING;//数据点名称,

 AVG_F                       DOUBLE;//统计平均值,

 ON_DURATION_F               INT;//持续时间,

 MIN_F                       DOUBLE;//统计最小值,

 TIME_F                      STRING;//数据时间,

 LAST_VAL_F                  DOUBLE;//最后值,

 ULTRA_LIMIT_DURATION_F      INT;//超限制时间,

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

 MAX_F                       DOUBLE;//统计最大值,

 FIRST_VAL_F                 DOUBLE;//第一值,

 LAST_TIME_F                 DATE;//最后时间,

 FIRST_TIME_F                DATE;//第一值时间,

 FLOW_F                      DOUBLE;//流量值,

 COUNT_F                     INT;//统计数据数量,

 ULTRA_LIMIT_COUNT_F         INT;//超限制数量,

 QUALITY_F                   STRING;//数据质量,

 OUTPUT_F                    DOUBLE;//产量

 )

二、查询历史统计数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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;

					// 查询sql
					/**查询统计数据表 STAT_TABLE
					 * 所需字段
					 * interval_f=3600 时间间隔 以秒为单位(不填写默认3600秒)
					 * 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'数据时间段(必填)
					 * STAT_BIT_MAP = 1000 统计控制单位 如要查询产量，则值为1000 查询超限为100   若都需要查询多个条件 则值例如为1100（1000+100）
					 * VALUE_F 查询超限时，所需的条件，设置超限界限
					 * ultralimitInterval 查询超限时，所需的条件,设置最短超限时长
					 * 可查询字段
					 * NAME_F 数据点位置
					 * TIME_F 时间
					 * AVG_F 统计平均值
					 * MAX_F 统计最大值
					 * MIN_F 统计最小值
					 * FLOW_F 流量
					 * COUNT_F 统计数据数量
					 * FIRST_VAL_F 第一值
					 * FIRST_TIME_F 第一值时间
					 * LAST_VAL_F 最后值
					 * LAST_TIME_F 最后时间 
					 * OUTPUT_F 产量 
					 * ON_DURATION_F 持续时间
					 * ULTRA_LIMIT_DURATION_F 超限制时间
					 * ULTRA_LIMIT_COUNT_F 超限制数量
					 */
					String sql = "select NAME_F,TIME_F,OUTPUT_F,AVG_F,MAX_F,MIN_F,FLOW_F,COUNT_F,FIRST_VAL_F,FIRST_TIME_F,LAST_VAL_F,LAST_TIME_F,OUTPUT_F,ON_DURATION_F,ULTRA_LIMIT_DURATION_F,ULTRA_LIMIT_COUNT_F  from STAT_TABLE where VALUE_F >? and STAT_BIT_MAP = ? and name_f=? and interval_f = ?  and time_f>? and time_f<? ";
					// 填充sql所需要的参数
					String param1 = "0";
					String param2 = "1000";
					String param3 = "D30.T31.P30";
					String param4 = "300";
					String param5 = "2022-11-10 11:50:00";
					String param6 = "2022-11-10 13:00:00";
					PreparedStatement ps = connection.prepareStatement(sql);
					ps.setString(1, param1);
					ps.setString(2, param2);
					ps.setString(3, param3);
					ps.setString(4, param4);
					ps.setString(5, param5);
					ps.setString(6, param6);

					// 运行sql发起请求
					rs = ps.executeQuery();
					// 查询结果存储在ResultSet
					System.out.println("Query statdata from table: stat_table.");
					while (rs.next()) {
						// read the result set
						System.out.println("NAME_F:" + rs.getString("NAME_F")+",TIME_F:"+rs.getString("TIME_F")+",AVG_F:"+rs.getString("AVG_F")+",MAX_F:"+rs.getString("MAX_F")+",MIN_F:"+rs.getString("MIN_F")+",FLOW_F:"+rs.getString("FLOW_F")
						+",COUNT_F:"+rs.getString("COUNT_F")+",FIRST_VAL_F:"+rs.getFloat("FIRST_VAL_F")+",FIRST_TIME_F:"+rs.getTime("FIRST_TIME_F")+",LAST_VAL_F:"+rs.getFloat("LAST_VAL_F")+",LAST_TIME_F:"+rs.getTime("LAST_TIME_F")
						+",OUTPUT_F:"+rs.getString("OUTPUT_F")+",ON_DURATION_F:"+rs.getFloat("ON_DURATION_F")+",ULTRA_LIMIT_DURATION_F:"+rs.getFloat("ULTRA_LIMIT_DURATION_F")+",ULTRA_LIMIT_COUNT_F:"+rs.getFloat("ULTRA_LIMIT_COUNT_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 statdata from table: stat_table.
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:00:00.0,AVG_F:81.85714285714286,MAX_F:98.0,MIN_F:57.0,FLOW_F:573.0,COUNT_F:7,FIRST_VAL_F:57.0,FIRST_TIME_F:11:33:47,LAST_VAL_F:98.0,LAST_TIME_F:12:04:59,OUTPUT_F:42.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:05:00.0,AVG_F:49.33779264214047,MAX_F:99.0,MIN_F:0.0,FLOW_F:14752.0,COUNT_F:299,FIRST_VAL_F:99.0,FIRST_TIME_F:12:05:00,LAST_VAL_F:97.0,LAST_TIME_F:12:09:59,OUTPUT_F:296.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:10:00.0,AVG_F:49.18456375838926,MAX_F:99.0,MIN_F:0.0,FLOW_F:14657.0,COUNT_F:298,FIRST_VAL_F:98.0,FIRST_TIME_F:12:10:00,LAST_VAL_F:95.0,LAST_TIME_F:12:14:59,OUTPUT_F:295.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:15:00.0,AVG_F:49.34782608695652,MAX_F:99.0,MIN_F:0.0,FLOW_F:14755.0,COUNT_F:299,FIRST_VAL_F:96.0,FIRST_TIME_F:12:15:00,LAST_VAL_F:94.0,LAST_TIME_F:12:19:59,OUTPUT_F:296.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:20:00.0,AVG_F:49.20469798657718,MAX_F:99.0,MIN_F:0.0,FLOW_F:14663.0,COUNT_F:298,FIRST_VAL_F:95.0,FIRST_TIME_F:12:20:00,LAST_VAL_F:92.0,LAST_TIME_F:12:24:59,OUTPUT_F:295.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:25:00.0,AVG_F:49.08080808080808,MAX_F:99.0,MIN_F:0.0,FLOW_F:14577.0,COUNT_F:297,FIRST_VAL_F:93.0,FIRST_TIME_F:12:25:00,LAST_VAL_F:89.0,LAST_TIME_F:12:29:59,OUTPUT_F:294.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:30:00.0,AVG_F:49.238255033557046,MAX_F:99.0,MIN_F:0.0,FLOW_F:14673.0,COUNT_F:298,FIRST_VAL_F:90.0,FIRST_TIME_F:12:30:00,LAST_VAL_F:87.0,LAST_TIME_F:12:34:59,OUTPUT_F:295.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:35:00.0,AVG_F:49.13131313131313,MAX_F:99.0,MIN_F:0.0,FLOW_F:14592.0,COUNT_F:297,FIRST_VAL_F:88.0,FIRST_TIME_F:12:35:00,LAST_VAL_F:84.0,LAST_TIME_F:12:39:59,OUTPUT_F:294.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:40:00.0,AVG_F:49.27181208053691,MAX_F:99.0,MIN_F:0.0,FLOW_F:14683.0,COUNT_F:298,FIRST_VAL_F:85.0,FIRST_TIME_F:12:40:00,LAST_VAL_F:82.0,LAST_TIME_F:12:44:59,OUTPUT_F:295.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:45:00.0,AVG_F:49.18181818181818,MAX_F:99.0,MIN_F:0.0,FLOW_F:14607.0,COUNT_F:297,FIRST_VAL_F:83.0,FIRST_TIME_F:12:45:00,LAST_VAL_F:79.0,LAST_TIME_F:12:49:59,OUTPUT_F:294.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:50:00.0,AVG_F:49.21212121212121,MAX_F:99.0,MIN_F:0.0,FLOW_F:14616.0,COUNT_F:297,FIRST_VAL_F:80.0,FIRST_TIME_F:12:50:00,LAST_VAL_F:76.0,LAST_TIME_F:12:54:59,OUTPUT_F:294.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
NAME_F:D30.T31.P30,TIME_F:2022-11-10 12:55:00.0,AVG_F:49.16216216216216,MAX_F:99.0,MIN_F:0.0,FLOW_F:14552.0,COUNT_F:296,FIRST_VAL_F:77.0,FIRST_TIME_F:12:55:00,LAST_VAL_F:72.0,LAST_TIME_F:12:59:59,OUTPUT_F:292.0,ON_DURATION_F:300.0,ULTRA_LIMIT_DURATION_F:0.0,ULTRA_LIMIT_COUNT_F:0.0
Count: 12