一、数据库字段介绍
CREATE TABLE DB_TABLE (
ID_F INT;//数据库ID,
NAME_F STRING;//数据库名,
HISTORY_MAX_COUNT SHORT;//历史表最大个数(默认100),
DESC_F STRING;//数据库描述,
HISTORY_PAGE_CACHE_MAX_COUNT SHORT;//历史表缓存页数(默认 10000),
HISTORY_MAX_INTERVAL SHORT;//历史表最大时长(单位小时,默认30),
IS_REFFER BOOLEAN;//是否引用库ID TRUE或者FALSE,
HISTORY_CACHE_MAX_COUNT SHORT;//历史表每页最大缓存数,
HISTORY_MAX_SIZE SHORT;//历史表大小(默认1000),
REFFER_ID INT;//引用数据库ID
)
二、创建新数据库
由于上篇文章已经介绍了如何连接ProcessDB数据库,这里就不过多赘述了,直接上代码
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
/**创建新数据库
* 所需字段
* 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 ('TEST75','TEST73',2000,50,300,30,12000,'true',1002)";
// 运行sql发起请求
int executeUpdate = statement.executeUpdate(sql);
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.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.
int i = 0;
while (i < 1) {
i++;
// sql
/**
* 删除数据库 T_TABLE 所需字段 name_f 要删除的数据库名称
*/
String sql = "delete from DB_TABLE where name_f = 'TEST75'";
// 运行sql发起请求
statement.executeUpdate(sql);
System.out.println("Delete 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.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
/**修改数据库信息 DB_TABLE
* 所需字段
* name_f 数据库名称(例如:TEST01)
* 可修改字段
* 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);
*/
String sql = "Update DB_TABLE set desc_f = 'D20 DESC' where name_f= 'D20'";
// 运行sql发起请求
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 {
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
/**查询数据库信息 DB_TABLE
* 所需字段
* name_f 数据库名称
* 可查询字段
* 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);
* REFFER_ID 引用库ID
*/
// String sql = "select name_f,desc_f,HISTORY_MAX_SIZE,HISTORY_MAX_INTERVAL,HISTORY_MAX_COUNT,HISTORY_CACHE_MAX_COUNT,HISTORY_PAGE_CACHE_MAX_COUNT,REFFER_ID from DB_TABLE where name_f ='TEST01'";
String sql = "select * from DB_TABLE where name_f ='PUBLIC'";
// 运行sql发起请求
rs = statement.executeQuery(sql);
System.out.println("Query DataBase Success");
while (rs.next()) {
// read the result set
System.out.println("数据库名称:" + rs.getString("name_f") + ",数据库描述:" + rs.getString("desc_f")+ ",历史表大小:" + rs.getString("HISTORY_MAX_SIZE")
+ ",历史表最大时长:" + rs.getString("HISTORY_MAX_INTERVAL")+",\n"+ "历史表最大个数:" + rs.getString("HISTORY_MAX_COUNT")+ ",历史表最大缓存数:" + rs.getString("HISTORY_CACHE_MAX_COUNT")
+ ",历史表缓存页数:" + rs.getString("HISTORY_PAGE_CACHE_MAX_COUNT")+ ",引用库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 DataBase Success
数据库名称:PUBLIC,数据库描述:System database.,历史表大小:1000,历史表最大时长:720,
历史表最大个数:10000,历史表最大缓存数:100,历史表缓存页数:10000,引用库ID:0
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
/**查询数据库信息 DB_TABLE
* 所需字段
* name_f 数据库名称 ,若要查询该连接下所有的数据库信息,则传空字符串
* 可查询字段
* 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);
* REFFER_ID 引用库ID
*/
String sql = "select * from DB_TABLE where name_f =''";
// 运行sql发起请求
rs = statement.executeQuery(sql);
System.out.println("Query DataBase Success");
while (rs.next()) {
// read the result set
System.out.println("name_f:" + rs.getString("name_f") + ",desc_f:" + rs.getString("desc_f")+ ",HISTORY_MAX_SIZE:" + rs.getString("HISTORY_MAX_SIZE")
+ ",HISTORY_MAX_INTERVAL:" + rs.getString("HISTORY_MAX_INTERVAL")+",\n"+ "HISTORY_MAX_COUNT:" + rs.getString("HISTORY_MAX_COUNT")+ ",HISTORY_CACHE_MAX_COUNT:" + rs.getString("HISTORY_CACHE_MAX_COUNT")
+ ",HISTORY_PAGE_CACHE_MAX_COUNT:" + rs.getString("HISTORY_PAGE_CACHE_MAX_COUNT")+ ",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 DataBase Success
name_f:D30,desc_f:GGGGG,HISTORY_MAX_SIZE:777,HISTORY_MAX_INTERVAL:30,
HISTORY_MAX_COUNT:100,HISTORY_CACHE_MAX_COUNT:20,HISTORY_PAGE_CACHE_MAX_COUNT:10000,REFFER_ID:0
name_f:TEST74,desc_f:TEST007,HISTORY_MAX_SIZE:1500,HISTORY_MAX_INTERVAL:70,
HISTORY_MAX_COUNT:170,HISTORY_CACHE_MAX_COUNT:25,HISTORY_PAGE_CACHE_MAX_COUNT:5000,REFFER_ID:1002
name_f:TEST01,desc_f:TEST007,HISTORY_MAX_SIZE:1500,HISTORY_MAX_INTERVAL:70,
HISTORY_MAX_COUNT:170,HISTORY_CACHE_MAX_COUNT:25,HISTORY_PAGE_CACHE_MAX_COUNT:5000,REFFER_ID:0
name_f:TEST73,desc_f:TEST007,HISTORY_MAX_SIZE:1500,HISTORY_MAX_INTERVAL:70,
HISTORY_MAX_COUNT:170,HISTORY_CACHE_MAX_COUNT:25,HISTORY_PAGE_CACHE_MAX_COUNT:5000,REFFER_ID:1002
name_f:D99,desc_f:,HISTORY_MAX_SIZE:0,HISTORY_MAX_INTERVAL:0,
HISTORY_MAX_COUNT:0,HISTORY_CACHE_MAX_COUNT:0,HISTORY_PAGE_CACHE_MAX_COUNT:0,REFFER_ID:0
name_f:TEST012,desc_f:,HISTORY_MAX_SIZE:0,HISTORY_MAX_INTERVAL:0,
HISTORY_MAX_COUNT:0,HISTORY_CACHE_MAX_COUNT:0,HISTORY_PAGE_CACHE_MAX_COUNT:0,REFFER_ID:0
name_f:PUBLIC,desc_f:System database.,HISTORY_MAX_SIZE:1000,HISTORY_MAX_INTERVAL:720,
HISTORY_MAX_COUNT:10000,HISTORY_CACHE_MAX_COUNT:100,HISTORY_PAGE_CACHE_MAX_COUNT:10000,REFFER_ID:0
name_f:D20,desc_f:HHHHH,HISTORY_MAX_SIZE:0,HISTORY_MAX_INTERVAL:0,
HISTORY_MAX_COUNT:0,HISTORY_CACHE_MAX_COUNT:0,HISTORY_PAGE_CACHE_MAX_COUNT:0,REFFER_ID:0
Count: 8