目录
前言
前文提到了用statement的方式，操作ProcessDB时序数据库，我们实际工作中可能更常使用的是 PreparedStatement方式，优点如下：1、可以防止SQL注入攻击。 2、进行动态查询。3、执行更快。4、简化拼接过程。所以，我在这里着重讲解下如何用PreparedStatement方式，操作ProcessDB实时/时序数据库的数据表对象
一、数据表字段介绍
CREATE TABLE T_TABLE (
ID_F INT;//数据ID,
NAME_F STRING;//数据表名,
DESC_F STRING;//数据表描述,
IS_REFFER BOOLEAN;//是否引用表ID,
TIME_OUT INT;//超时时间(单位秒),
REFFER_ID INT;//引用数据表ID
)二、创建新数据表
具有联级创建功能，新建数据表时若上级数据库不存在，则默认创建一个与填写信息同名的数据库
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.
// sql
/**创建新数据库
* 所需字段
* name_f 数据库名称(必填)
* desc_f 数据库描述
* HISTORY_MAX_SIZE 历史表大小(默认1000)
* HISTORY_MAX_INTERVAL 历史表最大时长(单位小时，默认30)
* HISTORY_MAX_COUNT 历史表最大个数(默认100)
* HISTORY_CACHE_MAX_COUNT 历史表每页最大缓存数(默认 20)
* HISTORY_PAGE_CACHE_MAX_COUNT 历史表缓存页数(默认 10000);
* IS_REFFER 是否引用库ID TRUE或者FALSE
* REFFER_ID 引用库ID
*/
String sql = "INSERT INTO DB_TABLE (name_f,desc_f,HISTORY_MAX_SIZE,HISTORY_MAX_INTERVAL,HISTORY_MAX_COUNT,HISTORY_CACHE_MAX_COUNT,HISTORY_PAGE_CACHE_MAX_COUNT,IS_REFFER,REFFER_ID) values (?,?,?,?,?,?,?,?,?)";
// 运行sql发起请求
String param1 = "D7";
String param2 = "Description";
String param3 = "2000";
String param4 = "50";
String param5 = "300";
String param6 = "30";
String param7 = "1200";
String param8 = "true";
String param9 = "1004";
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);
ps.setString(7, param7);
ps.setString(8, param8);
ps.setString(9, param9);
// 运行sql发起请求
ps.executeUpdate();
System.out.println("Create DataBase 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.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
/**删除数据表 T_TABLE
* 所需字段
* name_f 数据库名称加数据表名称（例如：TEST.TEST008）
*/
String sql = "delete from T_TABLE where name_f = ?";
String param1 = "D7.T7";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
ps.executeUpdate();
System.out.println("Delete Table 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.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
/**创建数据表 T_TABLE
* 所需字段
* name_f 数据库名称加新建数据表名称名称（例如：TEST.TEST008）
* 可修改字段
* desc_f 数据表描述
* TIME_OUT 超时时间(单位秒)
*/
String sql = "Update T_TABLE set TIME_OUT = ?, desc_f = ? where name_f= ?";
// 运行sql发起请求
String param1 = "777";
String param2 = "777777777";
String param3 = "D7.T7";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
ps.setString(2, param2);
ps.setString(3, param3);
// 运行sql发起请求
ps.executeUpdate();
System.out.println("Update Table 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
/**查询数据表信息 T_TABLE
* 所需字段
* name_f 数据库名称加数据表名称（例如：TEST.TEST008） 必填
* 若要查询某个库下所有的表信息，则只需填写数据库名称即可，若要查询该连接下所有表的信息，则传空字符串
* 或者 id_f 数据表id ，根据数据表id进行精确查询
* 可查询字段
* ID_F 数据表id
* name_f 数据表名称
* desc_f 数据表描述
* TIME_OUT 超时时间(单位秒)
* REFFER_ID 引用库ID
* ID_F 数据表ID
*/
String sql = "select * from T_TABLE where id_f =?";
String sql2 = "select * from T_TABLE where name_f =?";
String param1 = "10090001";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
rs = ps.executeQuery();
System.out.println("Query T_TABLE Success");
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_OUT:" + rs.getString("TIME_OUT")+ ",REFFER_ID:" + rs.getString("REFFER_ID")); }
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 T_TABLE Success
id_f:10090001,name_f:T7,desc_f:777777777,TIME_OUT:777,REFFER_ID: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
/**查询数据表信息 T_TABLE
* 所需字段
* name_f 数据库名称加数据表名称（例如：TEST.TEST008） 必填
* 若要查询某个库下所有的表信息，则只需填写数据库名称即可，若要查询该连接下所有表的信息，则传空字符串
* 或者 id_f 数据表id ，根据数据表id进行精确查询
* 可查询字段
* ID_F 数据表id
* name_f 数据表名称
* desc_f 数据表描述
* TIME_OUT 超时时间(单位秒)
* REFFER_ID 引用库ID
* ID_F 数据表ID
*/
String sql = "select * from T_TABLE where name_f =?";
String param1 = "";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
rs = ps.executeQuery();
System.out.println("Query T_TABLE Success");
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_OUT:" + rs.getString("TIME_OUT")+ ",REFFER_ID:" + rs.getString("REFFER_ID")); }
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 T_TABLE Success
id_f:10010001,name_f:SYS,desc_f:Cpu memroy and disk stat, read o,TIME_OUT:0,REFFER_ID:0
id_f:10010002,name_f:JOB,desc_f:Job and task stat, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010003,name_f:SESSION,desc_f:Session stat, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010004,name_f:OBJECT,desc_f:Object stat, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010005,name_f:STORAGE,desc_f:Storage stat, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010006,name_f:CACHE,desc_f:Cache stat, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010007,name_f:IMAGE,desc_f:Image object and data to other i,TIME_OUT:1852776548,REFFER_ID:0
id_f:10010008,name_f:STANDBY,desc_f:Hot and backup, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010009,name_f:VIP,desc_f:Virtual ip, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010010,name_f:CLUSTER,desc_f:Cluster multi instances, read on,TIME_OUT:0,REFFER_ID:0
id_f:10010011,name_f:SAMPLE,desc_f:Sample, read only,TIME_OUT:0,REFFER_ID:0
id_f:10010012,name_f:HMI_DEMO,desc_f:Demo, read and write,TIME_OUT:0,REFFER_ID:0
id_f:10010013,name_f:IOT_METRICS,desc_f:Iot metrics,TIME_OUT:0,REFFER_ID:0
id_f:10010014,name_f:LICENSE,desc_f:License info,TIME_OUT:0,REFFER_ID:0
id_f:10020001,name_f:TEST001,desc_f:TEST004,TIME_OUT:280,REFFER_ID:0
id_f:10020002,name_f:TEST002,desc_f:TEST003,TIME_OUT:300,REFFER_ID:10020001
id_f:10020003,name_f:TEST003,desc_f:TEST007,TIME_OUT:36,REFFER_ID:0
id_f:10040001,name_f:10040001,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10040002,name_f:T2,desc_f:6666666,TIME_OUT:0,REFFER_ID:0
id_f:10040003,name_f:10040003,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10040004,name_f:10040004,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10040005,name_f:10040005,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10040006,name_f:T20,desc_f:TEST UPDATE,TIME_OUT:300,REFFER_ID:0
id_f:10040007,name_f:10040007,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10040008,name_f:10040008,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10060001,name_f:10060001,desc_f:,TIME_OUT:0,REFFER_ID:0
id_f:10060002,name_f:10060002,desc_f:TEST,TIME_OUT:0,REFFER_ID:0
id_f:10060003,name_f:10060003,desc_f:TEST,TIME_OUT:0,REFFER_ID:0
id_f:10060004,name_f:10060004,desc_f:TEST,TIME_OUT:0,REFFER_ID:0
id_f:10060005,name_f:10060005,desc_f:TEST,TIME_OUT:0,REFFER_ID:0
id_f:10060006,name_f:10060006,desc_f:TEST,TIME_OUT:0,REFFER_ID:0
id_f:10060007,name_f:T31,desc_f:TEST007,TIME_OUT:900,REFFER_ID:0
id_f:10070001,name_f:T77,desc_f:777777777,TIME_OUT:777,REFFER_ID:0
id_f:10090001,name_f:T7,desc_f:777777777,TIME_OUT:777,REFFER_ID:0
Count: 34
扫一扫
5851
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
