目录
前言
前文提到了用statement的方式，操作ProcessDB时序数据库，我们实际工作中可能更常使用的是 PreparedStatement方式，优点如下：1、可以防止SQL注入攻击。 2、进行动态查询。3、执行更快。4、简化拼接过程。所以，我在这里着重讲解下如何用PreparedStatement方式，操作ProcessDB实时/时序数据库的数据库对象
一、数据库字段介绍
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
)二、创建新数据库
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.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;
int i = 0;
while (i < 1) {
i++;
// sql
/**删除数据库 DB_TABLE
* 所需字段
* name_f 要删除的数据库名称（例如：TEST）
*
*/
String sql = "delete from DB_TABLE where name_f = ?";
String param1 = "D7";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
ps.executeUpdate();
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.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
/**修改数据库信息 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 = ?,HISTORY_MAX_SIZE=?, HISTORY_MAX_INTERVAL = ? ,HISTORY_MAX_COUNT= ?,HISTORY_CACHE_MAX_COUNT=?,HISTORY_PAGE_CACHE_MAX_COUNT=? where name_f= ?";
// 运行sql发起请求
String param1 = "ccc";
String param2 = "3000";
String param3 = "3000";
String param4 = "3000";
String param5 = "3000";
String param6 = "3000";
String param7 = "D7";
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);
// 运行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
/**查询数据库信息 DB_TABLE
* 所需字段
* name_f 数据库名称 ,若要查询该连接下所有的数据库信息，则传空字符串
* 或者 id_f 数据库id ，根据数据库id进行精确查询
* 可查询字段
* id_f 数据库id
* 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 id_f = ?";
String sql2 = "select * from DB_TABLE where name_f = ?";
String param1 = "1009";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
rs = ps.executeQuery();
System.out.println("Query DataBase 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")+ ",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
id_f:1009,name_f:D7,desc_f:ccc,HISTORY_MAX_SIZE:3000,HISTORY_MAX_INTERVAL:3000,
HISTORY_MAX_COUNT:3000,HISTORY_CACHE_MAX_COUNT:3000,HISTORY_PAGE_CACHE_MAX_COUNT:3000,REFFER_ID:1004
Count: 1六、查询该连接下所有数据库
若要查询连接下的所有数据库，则要把NAME_F字段设为查询条件，并传空字符串即可
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
/**查询数据库信息 DB_TABLE
* 所需字段
* name_f 数据库名称 ,若要查询该连接下所有的数据库信息，则传空字符串
* 或者 id_f 数据库id ，根据数据库id进行精确查询
* 可查询字段
* id_f 数据库id
* 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 = ?";
String param1 = "";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, param1);
// 运行sql发起请求
rs = ps.executeQuery();
System.out.println("Query DataBase 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")+ ",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
id_f:1005,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
id_f:1001,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
id_f:1007,name_f:D77,desc_f:HHHHH,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:1004
id_f:1004,name_f:D20,desc_f:ccc,HISTORY_MAX_SIZE:3000,HISTORY_MAX_INTERVAL:3000,
HISTORY_MAX_COUNT:3000,HISTORY_CACHE_MAX_COUNT:3000,HISTORY_PAGE_CACHE_MAX_COUNT:3000,REFFER_ID:0
id_f:1003,name_f:D99,desc_f:HHHHH,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:1004
id_f: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
id_f:1008,name_f:D89,desc_f:,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
id_f:1009,name_f:D7,desc_f:ccc,HISTORY_MAX_SIZE:3000,HISTORY_MAX_INTERVAL:3000,
HISTORY_MAX_COUNT:3000,HISTORY_CACHE_MAX_COUNT:3000,HISTORY_PAGE_CACHE_MAX_COUNT:3000,REFFER_ID:1004
id_f:1006,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
Count: 9
218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
