目录
前言
前文提到了用statement的方式，操作ProcessDB时序数据库，我们实际工作中可能更常使用的是 PreparedStatement方式，优点如下：1、可以防止SQL注入攻击。 2、进行动态查询。3、执行更快。4、简化拼接过程。所以，我在这里着重讲解下如何用PreparedStatement方式，操作ProcessDB实时/时序数据库的实时数据
一、数据表字段介绍
CREATE TABLE RT_TABLE (
ID_F INT;//数据点ID,
NAME_F STRING;//数据点名称,
VALUE_F STRING;//实时数据值,
UNIT_F STRING;//数据单位,
DESC_F STRING;//数据点描述,
QUALITY_F STRING;//数据质量,
TIME_F STRING;//数据时间,
DATE_TYPE STRING;//数据类型
)
二、添加实时数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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.
// 插入sql
/**插入实时数据表 RT_TABLE
* 所需字段
* name_f = 'public.sys.PDB_OS_CPU_USAGE' 数据点的位置（必填）
* NAME_F 数据点的位置（必填）
* TIME_F 实时数据的时间(可不填写，默认当前时间,若需要填写时间，则时间为时间戳格式，例如：1667800758000)
* DATE_TYPE 数据类型（必填）
* VALUE_F 插入数据的值（必填）
*/
String sql = "INSERT INTO RT_TABLE (NAME_F,DATE_TYPE,VALUE_F) values (?,?,?)";
// 运行sql发起请求
String param1 = "D7.T7.P7";
String param2 = "FLOAT32";
String param3 = "77777";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
ps.setString(2, param2);
ps.setString(3, param3);
// 运行sql发起请求
ps.executeUpdate();
System.out.println("Insert realtime data into table: rt_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.PreparedStatement;
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.
// sql
/**修改实时数据 RT_TABLE
* 所需字段
* name_f 数据库名称加数据表名称加数据点名称（例如：public.sys.PDB_OS_CPU_USAGE）
* 可修改字段
* value_f 历史数据数据值
*/
String sql = "Update RT_TABLE set value_f = ? where name_f= ? ";
// 运行sql发起请求
String param1 = "77";
String param2 = "D7.T7.P7";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
ps.setString(2, param2);
ps.executeUpdate();
System.out.println("Update realtimeData 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.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
/**查询实时数据表 RT_TABLE
* 所需字段
* name_f = 'public.sys.PDB_OS_CPU_USAGE' 数据点的位置（必填）
* 若需要查询某张表下所有点的数据，则输入库名加表名即可，例如：PUBLIC.SYS
* 查询一个数据库下所有点的实时数据同理，若查询该数据连接的所有数据，则name_f为空字符串即可
* 或者 id_f 数据点id ，根据数据点id进行精确查询
* 可查询字段
* ID_F 数据id
* NAME_F 数据点的位置
* DESC_F 实时数据描述
* TIME_F 实时数据的时间
* DATE_TYPE 数据类型
* UNIT_F 数据单位
* QUALITY_F 数据质量是否完好
* VALUE_F 查询数据的值
*/
String sql = "select * from RT_TABLE where id_f = ?";
String sql2 = "select id_f,name_f,DESC_F,TIME_F,DATE_TYPE,UNIT_F,QUALITY_F,VALUE_F from RT_TABLE where name_f = ?";
// 填充sql所需要的参数
String param = "1009000004";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param);
// 运行sql发起请求
rs = ps.executeQuery();
// 查询结果存储在ResultSet
System.out.println("Query realtime data from table: rt_table.");
while (rs.next()) {
// read the result set
System.out.println("id_f:" + rs.getString("id_f") + ",name_f:" + rs.getString("name_f")+ ",DESC_F:" + rs.getString("DESC_F")+ ",TIME_F:" + rs.getString("TIME_F")+ ",DATE_TYPE:" + rs.getString("DATE_TYPE")+
",UNIT_F:"+ rs.getString("UNIT_F")+ ",QUALITY_F:" + rs.getString("QUALITY_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 realtime data from table: rt_table.
id_f:1009000004,name_f:D7.T7.P7,DESC_F:hhhh,TIME_F:2022-11-22 15:22:46.0,DATE_TYPE:float32,UNIT_F:N/A,QUALITY_F:GOOD,VALUE_F:789.0
Count: 1五、批量查询实时数据
支持上级查询，只输入数据库名，则是该数据库下所有点的实时数据，输入数据库加表名，这是该数据表下的所有点的实时数据，若要查询该连接下的所有点的实时数据，则输入空字符串
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
/**查询实时数据表 RT_TABLE
* 所需字段
* name_f = 'public.sys.PDB_OS_CPU_USAGE' 数据点的位置（必填）
* 若需要查询某张表下所有点的数据，则输入库名加表名即可，例如：PUBLIC.SYS
* 查询一个数据库下所有点的实时数据同理，若查询该数据连接的所有数据，则name_f为空字符串即可
* 或者 id_f 数据点id ，根据数据点id进行精确查询
* 可查询字段
* ID_F 数据id
* NAME_F 数据点的位置
* DESC_F 实时数据描述
* TIME_F 实时数据的时间
* DATE_TYPE 数据类型
* UNIT_F 数据单位
* QUALITY_F 数据质量是否完好
* VALUE_F 查询数据的值
*/
String sql = "select * from RT_TABLE where name_f = ?";
// 填充sql所需要的参数
String param = "PUBLIC.SYS";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param);
// 运行sql发起请求
rs = ps.executeQuery();
// 查询结果存储在ResultSet
System.out.println("Query realtime data from table: rt_table.");
while (rs.next()) {
// read the result set
System.out.println("id_f:" + rs.getString("id_f") + ",name_f:" + rs.getString("name_f")+ ",DESC_F:" + rs.getString("DESC_F")+ ",TIME_F:" + rs.getString("TIME_F")+ ",DATE_TYPE:" + rs.getString("DATE_TYPE")+
",UNIT_F:"+ rs.getString("UNIT_F")+ ",QUALITY_F:" + rs.getString("QUALITY_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 realtime data from table: rt_table.
id_f:1001000001,name_f:PUBLIC.SYS.PDB_OS_CPU_USAGE,DESC_F:System total cpu load rate,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:%,QUALITY_F:GOOD,VALUE_F:2.8809228
id_f:1001000002,name_f:PUBLIC.SYS.PDB_OS_MEM_USAGE,DESC_F:System physical memory usage rate,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:%,QUALITY_F:GOOD,VALUE_F:88.0
id_f:1001000003,name_f:PUBLIC.SYS.PDB_DISK_READ_SPEED,DESC_F:System disk read speed,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:m/s,QUALITY_F:GOOD,VALUE_F:80.307724
id_f:1001000004,name_f:PUBLIC.SYS.PDB_DISK_WRITE_SPEED,DESC_F:System disk write speed,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:m/s,QUALITY_F:GOOD,VALUE_F:1646.3083
id_f:1001000005,name_f:PUBLIC.SYS.PDB_NET_READ_SPEED,DESC_F:System net read speed,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:m/s,QUALITY_F:GOOD,VALUE_F:0.0
id_f:1001000006,name_f:PUBLIC.SYS.PDB_NET_WRITE_SPEED,DESC_F:System net write speed,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:m/s,QUALITY_F:GOOD,VALUE_F:0.0
id_f:1001000007,name_f:PUBLIC.SYS.PDB_PROC_CPU_USAGE,DESC_F:ProcessDB cpu load rate,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:%,QUALITY_F:GOOD,VALUE_F:0.0
id_f:1001000008,name_f:PUBLIC.SYS.PDB_PROC_MEM_USED,DESC_F:ProcessDB physical memory used,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:22.0
id_f:1001000009,name_f:PUBLIC.SYS.PDB_FREE_DISK,DESC_F:System disk free space,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:uint32,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:60129
id_f:1001000010,name_f:PUBLIC.SYS.PDB_HANDLE_COUNT,DESC_F:Used handle count,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:uint32,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:641
id_f:1001000011,name_f:PUBLIC.SYS.PDB_OS_RUNNING_TIME,DESC_F:OS running time,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:H,QUALITY_F:GOOD,VALUE_F:6.532457
id_f:1001000012,name_f:PUBLIC.SYS.PDB_PROC_RUNNING_TIME,DESC_F:ProcessDB running time,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:float32,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:121.73333
id_f:1001000013,name_f:PUBLIC.SYS.PDB_PROC_THREAD_COUNT,DESC_F:ProcessDB thread count,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:int16,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:124
id_f:1001000014,name_f:PUBLIC.SYS.PDB_AVAILABLE_DISK,DESC_F:System disk available space,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:uint32,UNIT_F:M,QUALITY_F:GOOD,VALUE_F:60129
id_f:1001000015,name_f:PUBLIC.SYS.PDB_OS_TYPE,DESC_F:System disk available space,TIME_F:2022-11-22 15:31:53.0,DATE_TYPE:string,UNIT_F:N/A,QUALITY_F:BAD,VALUE_F:Windows
Count: 15
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
