1. 概述
本文将探讨 Java 对 HarperDB 的支持能力——一款兼具 NoSQL 灵活性与 SQL 强大功能的高性能数据库。毫无疑问，标准 Java 数据库连接（JDBC）使其能轻松集成主流 BI 工具、报表工具、ETL 工具及各类定制应用。同时，它还提供 REST API 用于数据库管理和操作。
不过，JDBC 显著简化了 HarperDB 在应用中的集成流程，能大幅加速开发进程。
本文将使用 Java Test Container 库 启动 HarperDB Docker 容器，通过实际案例演示完整集成方案。
接下来通过示例深入探索 HarperDB 的 JDBC 支持能力。
2. JDBC 库
HarperDB 提供了 JDBC 驱动，需在 pom.xml 中手动引入：
<dependency>
<groupId>com.baeldung</groupId>
<artifactId>java-harperdb</artifactId>
<version>4.2</version>
<scope>system</scope>
<systemPath>${project.basedir}/lib/cdata.jdbc.harperdb.jar</systemPath>
</dependency>
⚠️ 该驱动未发布到公共 Maven 仓库，需从本地目录或私有仓库导入。
3. 建立 JDBC 连接
在执行 SQL 语句前，我们先探讨如何获取 java.sql.Connection 对象。
先看第一种方式：
@Test
void whenConnectionInfoInURL_thenConnectSuccess() {
assertDoesNotThrow(() -> {
final String JDBC_URL = "jdbc:harperdb:Server=127.0.0.1:" + port + ";User=admin;Password=password;";
try (Connection connection = DriverManager.getConnection(JDBC_URL)) {
connection.createStatement().executeQuery("select 1");
logger.info("Connection Successful");
}
});
}
与传统关系型数据库连接方式类似，区别仅在于 JDBC URL 的 jdbc:harperdb: 前缀。生产环境中密码应加密后再传入 URL。
再看第二种方式：
@Test
void whenConnectionInfoInProperties_thenConnectSuccess() {
assertDoesNotThrow(() -> {
Properties prop = new Properties();
prop.setProperty("Server", "127.0.0.1:" + port);
prop.setProperty("User", "admin");
prop.setProperty("Password", "password");
try (Connection connection = DriverManager.getConnection("jdbc:harperdb:", prop)) {
connection.createStatement().executeQuery("select 1");
logger.info("Connection Successful");
}
});
}
这里改用 Properties 对象传递连接参数到 DriveManager。
为追求最佳性能，应用通常采用连接池。HarperDB 的 JDBC 驱动同样支持：
@Test
void whenConnectionPooling_thenConnectSuccess() {
assertDoesNotThrow(() -> {
HarperDBConnectionPoolDataSource harperdbPoolDataSource = new HarperDBConnectionPoolDataSource();
final String JDBC_URL = "jdbc:harperdb:UseConnectionPooling=true;PoolMaxSize=2;Server=127.0.0.1:" + port
+ ";User=admin;Password=password;";
harperdbPoolDataSource.setURL(JDBC_URL);
try(Connection connection = harperdbPoolDataSource.getPooledConnection().getConnection()) {
connection.createStatement().executeQuery("select 1");
logger.info("Connection Successful");
}
});
}
通过 UseConnectionPooling=true 启用连接池，并需使用 HarperDBConnectionPoolDataSource 驱动类获取连接。其他连接属性可参考官方文档。
4. 创建 Schema 和表
HarperDB 提供 RESTful 数据库操作 API 用于配置管理，也支持创建数据库对象和执行 SQL CRUD 操作。
但不支持 Create Table、Create Schema 等 DDL 语句。取而代之的是，**HarperDB 提供存储过程**：
@Test
void whenExecuteStoredToCreateTable_thenSuccess() throws SQLException {
final String CREATE_TABLE_PROC = "CreateTable";
try (Connection connection = getConnection()) {
CallableStatement callableStatement = connection.prepareCall(CREATE_TABLE_PROC);
callableStatement.setString("SchemaName", "Prod");
callableStatement.setString("TableName", "Subject");
callableStatement.setString("PrimaryKey", "id");
Boolean result = callableStatement.execute();
ResultSet resultSet = callableStatement.getResultSet();
while (resultSet.next()) {
String tableCreated = resultSet.getString("Success");
assertEquals("true", tableCreated);
}
}
}
通过 CallableStatement 调用 CreateTable 存储过程，在 Prod schema 中创建 Subject 表。该过程接收 SchemaName、TableName 和 PrimaryKey 参数。有趣的是，无需显式创建 schema——若不存在会自动创建。
类似地，其他存储过程（如 CreateHarperSchema、DropSchema、DropTable 等）也可通过 CallableStatement 调用。
5. CRUD 支持
HarperDB JDBC 驱动支持完整的 CRUD 操作，可通过 java.sql.Statement 和 java.sql.PreparedStatement 对表进行增删改查。
5.1. 数据模型
先准备测试数据。假设存在 Demo schema，包含三张表：
Subject 和 Teacher 是主表，Teacher_Details 存储教师授课信息。注意：HarperDB 不支持外键约束，因此 teacher_id 和 subject_id 字段无约束关系。
Subject 表数据示例：
[
{"id":1, "name":"English"},
{"id":2, "name":"Maths"},
{"id":3, "name":"Science"}
]
Teacher 表数据示例：
[
{"id":1, "name":"James Cameron", "joining_date":"04-05-2000"},
{"id":2, "name":"Joe Biden", "joining_date":"20-10-2005"},
{"id":3, "name":"Jessie Williams", "joining_date":"04-06-1997"},
{"id":4, "name":"Robin Williams", "joining_date":"01-01-2020"},
{"id":5, "name":"Eric Johnson", "joining_date":"04-05-2022"},
{"id":6, "name":"Raghu Yadav", "joining_date":"02-02-1999"}
]
Teacher_Details 表数据示例：
[
{"id":1, "teacher_id":1, "subject_id":1},
{"id":2, "teacher_id":1, "subject_id":2},
{"id":3, "teacher_id":2, "subject_id":3 },
{"id":4, "teacher_id":3, "subject_id":1},
{"id":5, "teacher_id":3, "subject_id":3},
{"id":6, "teacher_id":4, "subject_id":2},
{"id":7, "teacher_id":5, "subject_id":3},
{"id":8, "teacher_id":6, "subject_id":1},
{"id":9, "teacher_id":6, "subject_id":2},
{"id":15, "teacher_id":6, "subject_id":3}
]
所有表的 id 字段均为主键。
5.2. 使用 Insert 创建记录
向 Subject 表插入新科目：
@Test
void givenStatement_whenInsertRecord_thenSuccess() throws SQLException {
final String INSERT_SQL = "insert into Demo.Subject(id, name) values "
+ "(4, 'Social Studies'),"
+ "(5, 'Geography')";
try (Connection connection = getConnection()) {
Statement statement = connection.createStatement();
assertDoesNotThrow(() -> statement.execute(INSERT_SQL));
assertEquals(2, statement.getUpdateCount());
}
}
使用 java.sql.Statement 插入两条记录。
更优方案是使用 java.sql.PreparedStatement（以 Teacher 表为例）：
@Test
void givenPrepareStatement_whenAddToBatch_thenSuccess() throws SQLException {
final String INSERT_SQL = "insert into Demo.Teacher(id, name, joining_date) values"
+ "(?, ?, ?)";
try (Connection connection = getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SQL);
preparedStatement.setInt(1, 7);
preparedStatement.setString(2, "Bret Lee");
preparedStatement.setString(3, "07-08-2002");
preparedStatement.addBatch();
preparedStatement.setInt(1, 8);
preparedStatement.setString(2, "Sarah Glimmer");
preparedStatement.setString(3, "07-08-1997");
preparedStatement.addBatch();
int[] recordsInserted = preparedStatement.executeBatch();
assertEquals(2, Arrays.stream(recordsInserted).sum());
}
}
通过参数化 SQL 和批处理方法（addBatch() + *executeBatch()*）提升性能。批量处理对海量数据插入至关重要，HarperDB 的 JDBC 驱动支持此特性极具价值。
5.3. 使用 Insert Into Select 创建记录
HarperDB JDBC 驱动支持运行时创建临时表，后续可通过单条 insert into select 语句将数据导入目标表。类似批处理，此特性可减少数据库调用次数。
实战示例：
@Test
void givenTempTable_whenInsertIntoSelectTempTable_thenSuccess() throws SQLException {
try (Connection connection = getConnection()) {
Statement statement = connection.createStatement();
assertDoesNotThrow(() -> {
statement.execute("insert into Teacher#TEMP(id, name, joining_date) "
+ "values('12', 'David Flinch', '04-04-2014')");
statement.execute("insert into Teacher#TEMP(id, name, joining_date) "
+ "values('13', 'Stephen Hawkins', '04-07-2017')");
statement.execute("insert into Teacher#TEMP(id, name, joining_date) "
+ "values('14', 'Albert Einstein', '12-08-2020')");
statement.execute("insert into Teacher#TEMP(id, name, joining_date) "
+ "values('15', 'Leo Tolstoy', '20-08-2022')");
});
assertDoesNotThrow(() -> statement.execute("insert into Demo.Teacher(id, name, joining_date) "
+ "select id, name, joining_date from Teacher#TEMP"));
ResultSet resultSet = statement.executeQuery("select count(id) as rows from Demo.Teacher where id in"
+ " (12, 13, 14, 15)");
resultSet.next();
int totalRows = resultSet.getInt("rows");
assertEquals(4, totalRows);
}
}
临时表命名必须遵循 [表名]#TEMP 格式（如 Teacher#TEMP）。执行 insert 语句时自动创建临时表，四条记录插入后，通过单条 insert into select 导入目标 Teacher 表。
5.4. 读取表记录
先用 java.sql.Statement 查询 Subject 表：
@Test
void givenStatement_whenFetchRecord_thenSuccess() throws SQLException {
final String SQL_QUERY = "select id, name from Demo.Subject where name = 'Maths'";
try (Connection connection = getConnection()) {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(SQL_QUERY);
while (resultSet.next()) {
Integer id = resultSet.getInt("id");
String name = resultSet.getString("name");
assertNotNull(id);
logger.info("Subject id:" + id + " Subject Name:" + name);
}
}
}
executeQuery() 成功执行并返回结果。
验证 java.sql.PreparedStatement 支持，执行带 JOIN 的复杂查询：
@Test
void givenPreparedStatement_whenExecuteJoinQuery_thenSuccess() throws SQLException {
final String JOIN_QUERY = "SELECT t.name as teacher_name, t.joining_date as joining_date, s.name as subject_name "
+ "from Demo.Teacher_Details AS td "
+ "INNER JOIN Demo.Teacher AS t ON t.id = td.teacher_id "
+ "INNER JOIN Demo.Subject AS s on s.id = td.subject_id "
+ "where t.name = ?";
try (Connection connection = getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(JOIN_QUERY);
preparedStatement.setString(1, "Eric Johnson");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String teacherName = resultSet.getString("teacher_name");
String subjectName = resultSet.getString("subject_name");
String joiningDate = resultSet.getString("joining_date");
assertEquals("Eric Johnson", teacherName);
assertEquals("Maths", subjectName);
}
}
}
不仅执行了参数化查询，还验证了 HarperDB 可对非结构化数据执行 JOIN 操作。
5.5. 读取用户自定义视图
HarperDB 驱动支持创建用户自定义视图。这类虚拟视图适用于无法直接访问表查询的场景（如通过工具使用驱动时）。
在 UserDefinedViews.json 文件中定义视图：
{
"View_Teacher_Details": {
"query": "SELECT t.name as teacher_name, t.joining_date as joining_date, s.name as subject_name from Demo.Teacher_Details AS td
INNER JOIN Demo.Teacher AS t ON t.id = td.teacher_id INNER JOIN Demo.Subject AS s on s.id = td.subject_id"
}
}
该视图通过 JOIN 关联所有表获取教师授课详情，默认 schema 为 UserViews。
驱动通过连接属性 Location 指定的目录查找 UserDefinedViews.json。使用方式：
@Test
void givenUserDefinedView_whenQueryView_thenSuccess() throws SQLException {
URL url = ClassLoader.getSystemClassLoader().getResource("UserDefinedViews.json");
String folderPath = url.getPath().substring(0, url.getPath().lastIndexOf('/'));
try(Connection connection = getConnection(Map.of("Location", folderPath))) {
PreparedStatement preparedStatement = connection.prepareStatement("select teacher_name,subject_name"
+ " from UserViews.View_Teacher_Details where subject_name = ?");
preparedStatement.setString(1, "Science");
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
assertEquals("Science", resultSet.getString("subject_name"));
}
}
}
程序将 UserDefinedViews.json 所在目录路径传递给 getConnection() 方法，驱动即可在 View_Teacher_Details 视图上执行查询，获取所有教授“科学”课程的教师信息。
5.6. 缓存读写
应用常缓存高频访问数据以提升性能。HarperDB 驱动支持将数据缓存到本地磁盘或数据库。
本文示例使用嵌入式 Derby 数据库 作为缓存，也支持选择其他数据库。
实现方式：
@Test
void givenAutoCache_whenQuery_thenSuccess() throws SQLException {
URL url = ClassLoader.getSystemClassLoader().getResource("test.db");
String folderPath = url.getPath().substring(0, url.getPath().lastIndexOf('/'));
logger.info("Cache Location:" + folderPath);
try(Connection connection = getConnection(Map.of("AutoCache", "true", "CacheLocation", folderPath))) {
PreparedStatement preparedStatement = connection.prepareStatement("select id, name from Demo.Subject");
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
logger.info("Subject Name:" + resultSet.getString("name"));
}
}
}
使用两个连接属性：AutoCache 和 CacheLocation。AutoCache=true 表示所有表查询将自动缓存到 CacheLocation 指定位置。此外，驱动还支持通过 CACHE 语句进行显式缓存。
5.7. 更新记录
用 java.sql.Statement 更新教师授课科目：
@Test
void givenStatement_whenUpdateRecord_thenSuccess() throws SQLException {
final String UPDATE_SQL = "update Demo.Teacher_Details set subject_id = 2 "
+ "where teacher_id in (2, 5)";
final String UPDATE_SQL_WITH_SUB_QUERY = "update Demo.Teacher_Details "
+ "set subject_id = (select id from Demo.Subject where name = 'Maths') "
+ "where teacher_id in (select id from Demo.Teacher where name in ('Joe Biden', 'Eric Johnson'))";
try (Connection connection = getConnection()) {
Statement statement = connection.createStatement();
assertDoesNotThrow(() -> statement.execute(UPDATE_SQL));
assertEquals(2, statement.getUpdateCount());
}
try (Connection connection = getConnection()) {
assertThrows(SQLException.class, () -> connection.createStatement().execute(UPDATE_SQL_WITH_SUB_QUERY));
}
}
直接使用教师和科目 ID 的更新语句成功执行。但尝试从其他表查询 ID 值时失败，因为 HarperDB 当前不支持子查询。
改用 java.sql.PreparedStatement 更新：
@Test
void givenPreparedStatement_whenUpdateRecord_thenSuccess() throws SQLException {
final String UPDATE_SQL = "update Demo.Teacher_Details set subject_id = ? "
+ "where teacher_id in (?, ?)";
try (Connection connection = getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_SQL);
preparedStatement.setInt(1, 1);
//HarperDB 驱动不支持以下操作
//Integer[] teacherIds = {4, 5};
//Array teacherIdArray = connection.createArrayOf(Integer.class.getTypeName(), teacherIds);
preparedStatement.setInt(2, 4);
preparedStatement.setInt(3, 5);
assertDoesNotThrow(() -> preparedStatement.execute());
assertEquals(2, preparedStatement.getUpdateCount());
}
}
HarperDB JDBC 驱动不支持创建 java.sql.Array 对象，无法在 in 子句中传递数组参数。因此需多次调用 setInt() 设置教师 ID，这是明显的缺陷，可能带来诸多不便。
5.8. 删除记录
执行 delete 语句删除 Teacher_Details 表记录：
@Test
void givenStatement_whenDeleteRecord_thenSuccess() throws SQLException {
final String DELETE_SQL = "delete from Demo.Teacher_Details where teacher_id = 6 and subject_id = 3";
try (Connection connection = getConnection()) {
Statement statement = connection.createStatement();
assertDoesNotThrow(() -> statement.execute(DELETE_SQL));
assertEquals(1, statement.getUpdateCount());
}
}
java.sql.Statement 成功删除记录。
使用 java.sql.PreparedStatement 的版本：
@Test
void givenPreparedStatement_whenDeleteRecord_thenSuccess() throws SQLException {
final String DELETE_SQL = "delete from Demo.Teacher_Details where teacher_id = ? and subject_id = ?";
try (Connection connection = getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(DELETE_SQL);
preparedStatement.setInt(1, 6);
preparedStatement.setInt(2, 2);
assertDoesNotThrow(() -> preparedStatement.execute());
assertEquals(1, preparedStatement.getUpdateCount());
}
}
成功执行参数化删除语句。
6. 总结
本文深入探讨了 HarperDB 的 JDBC 支持能力。作为 NoSQL 数据库，HarperDB 通过 JDBC 驱动使 Java 应用能执行 SQL 操作，但部分 SQL 特性尚不支持。
此外，该驱动未完全遵循 JDBC 协议规范，但通过用户自定义视图、临时表、缓存等特性弥补了不足。
本文示例代码可在 GitHub 获取。