ProcessDB实时/时序数据库——JDBC操作数据表对象

ProcessDB

已于 2022-12-12 14:52:35 修改

阅读量183

点赞数

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

于 2022-11-07 09:42:26 首次发布

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

版权

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

16 篇文章 0 订阅

订阅专栏

一、数据表字段介绍

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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	@SuppressWarnings("deprecation")
	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.
				ResultSet rs = null;
				int i = 0;
				while (i < 1) {
					i++;
					// sql
					/**创建数据表 T_TABLE
					 * 所需字段
					 * name_f  数据库名称加新建数据表名称（例如：TEST.TEST008）
					 * desc_f  数据表描述
					 * IS_REFFER 是否引用表 TRUE或者FALSE
					 * REFFER_ID 引用表id
					 * TIME_OUT 超时时间(单位秒)
					 */
					String sql = "INSERT INTO T_TABLE (name_f,desc_f,IS_REFFER,TIME_OUT) values ('D30.T32','TEST008',false,88)";		
					// 运行sql发起请求
					int executeUpdate = statement.executeUpdate(sql);
					
					System.out.println("Create 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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	@SuppressWarnings("deprecation")
	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.
				ResultSet rs = null;
					// sql
					/**删除数据表 T_TABLE
					 * 所需字段
					 * name_f  数据库名称加数据表名称（例如：TEST.TEST008）
					 */
					String sql = "delete from T_TABLE where name_f = 'D30.T32'";	
					// 运行sql发起请求
					statement.executeUpdate(sql);
					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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	@SuppressWarnings("deprecation")
	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.
				ResultSet rs = null;
				int i = 0;
				while (i < 1) {
					i++;
					// sql
					/**创建数据表 T_TABLE
					 * 所需字段
					 * name_f  数据库名称加新建数据表名称名称（例如：TEST.TEST008）
					 * 可修改字段
					 * desc_f  数据表描述
					 * TIME_OUT 超时时间(单位秒) 
					 */
					String sql = "Update  T_TABLE set TIME_OUT = 900 , desc_f = 'test007' where name_f= 'D30.T31'";		
					// 运行sql发起请求
					int executeUpdate = statement.executeUpdate(sql);
					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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
	@SuppressWarnings("deprecation")
	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.
				ResultSet rs = null;
				int i = 0;
				while (i < 1) {
					i++;
					// sql
					/**查询数据表信息 T_TABLE
					 * 所需字段
					 * name_f   数据库名称加数据表名称（例如：TEST.TEST008） 必填
					 * 可查询字段
					 * name_f  数据表名称
					 * desc_f  数据表描述
					 * TIME_OUT 超时时间(单位秒) 
                     * REFFER_ID 引用库ID
                     * ID_F    数据表ID
					 */
					String sql = "select * from T_TABLE where name_f ='TEST01.TEST002'";		
					// 运行sql发起请求
					 rs = statement.executeQuery(sql);
					System.out.println("Query T_TABLE Success");
					while (rs.next()) {
						// read the result set
					System.out.println(" 数据表名称:" + rs.getString("name_f") + ",数据表描述:" + rs.getString("desc_f")
					+",超时时间:" + rs.getString("TIME_OUT")+  ",引用库ID:" + rs.getString("REFFER_ID")+  ",数据表ID:" + rs.getString("ID_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 T_TABLE Success
 数据表名称:TEST002,数据表描述:TEST004,超时时间:280,引用库ID:10020001,数据表ID:10020002
Count: 1

六、查询数据表列表

若要查询某个库下所有的表信息，则只需填写数据库名称即可，若要查询该连接下所有表的信息，则传空字符串

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

/*
 * JDBC查询数据表信息
 */
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
					/**查询数据表信息 T_TABLE
					 * 所需字段
					 * name_f   数据库名称加数据表名称（例如：TEST.TEST008） 必填
					 * 若要查询某个库下所有的表信息，则只需填写数据库名称即可，若要查询该连接下所有表的信息，则传空字符串
					 * 可查询字段
					 * name_f  数据表名称
					 * desc_f  数据表描述
					 * TIME_OUT 超时时间(单位秒) 
                     * REFFER_ID 引用库ID
                     * ID_F    数据表ID
					 */
					String sql = "select * from T_TABLE where name_f ='PUBLIC'";		
					// 运行sql发起请求
					 rs = statement.executeQuery(sql);
					System.out.println("Query T_TABLE Success");
					while (rs.next()) {
						// read the result set
					System.out.println("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
name_f:HMI_DEMO,desc_f:Demo, read and write,TIME_OUT:0,REFFER_ID:0
name_f:CLUSTER,desc_f:Cluster multi instances, read on,TIME_OUT:0,REFFER_ID:0
name_f:OBJECT,desc_f:Object stat, read only,TIME_OUT:0,REFFER_ID:0
name_f:SESSION,desc_f:Session stat, read only,TIME_OUT:0,REFFER_ID:0
name_f:SYS,desc_f:Cpu memroy and disk stat, read o,TIME_OUT:0,REFFER_ID:0
name_f:IOT_METRICS,desc_f:Iot metrics,TIME_OUT:0,REFFER_ID:0
name_f:SAMPLE,desc_f:Sample, read only,TIME_OUT:0,REFFER_ID:0
name_f:JOB,desc_f:Job and task stat, read only,TIME_OUT:0,REFFER_ID:0
name_f:CACHE,desc_f:Cache stat, read only,TIME_OUT:0,REFFER_ID:0
name_f:STANDBY,desc_f:Hot and backup, read only,TIME_OUT:0,REFFER_ID:0
name_f:VIP,desc_f:Virtual ip, read only,TIME_OUT:0,REFFER_ID:0
name_f:LICENSE,desc_f:License info,TIME_OUT:0,REFFER_ID:0
name_f:STORAGE,desc_f:Storage stat, read only,TIME_OUT:0,REFFER_ID:0
name_f:IMAGE,desc_f:Image object and data to other i,TIME_OUT:1852776548,REFFER_ID:0
Count: 14