本文主要介绍 HSQLDB 的基本使用，文中所使用到的软件版本：Java 11.0.22、HSQLDB 2.7.2。

1、进程内模式

直接使用 JDBC 连接数据库即可，如果数据库不存在会自动创建。

1.1、file 数据库

@Test

public void inProcessFile() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:file:d:/temp/" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

private void business(Connection con) throws SQLException {

String tableName = "a_student";

Statement st = con.createStatement();

String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_schema)=? and upper(table_name)=?";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, "PUBLIC");

pst.setString(2, tableName.toUpperCase());

ResultSet rs = pst.executeQuery();

if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作

st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");

st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");

st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");

}

rs = st.executeQuery("select * from " + tableName);

while (rs.next()) {

log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));

}

}

1.2、mem 数据库

@Test

public void inProcessMem() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:mem:" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

1.3、res 数据库

@Test

public void inProcessRes() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码,数据库文件位于某个依赖 jar 文件的 db 目录中

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:res:db/" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

2、服务器模式

2.1、HyperSQL HSQL Server

可以通过如下命令启动HyperSQL HSQL Server，假设当前位于 HSQLDB 安装包的 data 目录中：

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:d:/temp/mydb --dbname.0 test #启动file数据库，数据库文件保存在d:/temp目录下，数据名称为 test

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 mem:mydb --dbname.0 test #启动mem数据库，数据名称为 test

可以添加其他参数来调整数据库的默认行为，查看所有参数：

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --help

相关参数如下：

Usage: java org.hsqldb.server.WebServer [options]

+-----------------+------------ +------------+------------------------------+

| OPTION | TYPE | DEFAULT | DESCRIPTION |

+-----------------+-------------+------------+------------------------------|

| --help | - | - | displays this message |

| --address | name|number | any | server inet address |

| --port | number | 80/443 | port at which server listens |

| --database.i | [type]spec | 0=test | name of database i |

| --dbname.i | alias | - | url alias for database i |

| --root | path | ./ | path to web root |

| --default_page | file | index.html | default web page |

| --silent | true|false | true | false => display all queries |

| --trace | true|false | false | display JDBC trace messages |

| --tls | true|false | | HTTPS (secure) sockets |

| --no_system_exit| true|false | false | do not issue System.exit() |

| --remote_open | true|false | false | can open databases remotely |

| --props | filepath | | file path of properties file |

+-----------------+-------------+------------+------------------------------+

The web server looks for a 'webserver.properties' file in the current directory

and loads properties from it if it exists.

Command line options override those loaded from the 'webserver.properties' file.

启动后使用 JDBC 访问数据库：

@Test

public void hsqlServer() throws SQLException {

String dbName = "test";

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:hsql://localhost:9001/" + dbName, "SA", "");

log.info("con={}", con);

business(con);

con.close();

}

2.2、HyperSQL HTTP Server

可以通过如下命令启动HyperSQL HTTP Server，假设当前位于 HSQLDB 安装包的 data 目录中：

java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer --database.0 file:d:/temp/mydb --dbname.0 test #启动file数据库，数据库文件保存在d:/temp目录下，数据名称为 test

java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer --database.0 mem:mydb --dbname.0 test #启动mem数据库，数据名称为 test

可以添加其他参数来调整数据库的默认行为，查看所有参数：

java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer --help

相关参数如下：

Usage: java org.hsqldb.server.WebServer [options]

+-----------------+------------ +------------+------------------------------+

| OPTION | TYPE | DEFAULT | DESCRIPTION |

+-----------------+-------------+------------+------------------------------|

| --help | - | - | displays this message |

| --address | name|number | any | server inet address |

| --port | number | 80/443 | port at which server listens |

| --database.i | [type]spec | 0=test | name of database i |

| --dbname.i | alias | - | url alias for database i |

| --root | path | ./ | path to web root |

| --default_page | file | index.html | default web page |

| --silent | true|false | true | false => display all queries |

| --trace | true|false | false | display JDBC trace messages |

| --tls | true|false | | HTTPS (secure) sockets |

| --no_system_exit| true|false | false | do not issue System.exit() |

| --remote_open | true|false | false | can open databases remotely |

| --props | filepath | | file path of properties file |

+-----------------+-------------+------------+------------------------------+

The web server looks for a 'webserver.properties' file in the current directory

and loads properties from it if it exists.

Command line options override those loaded from the 'webserver.properties' file.

启动后使用 JDBC 访问数据库：

@Test

public void httpServer() throws SQLException {

String dbName = "test";

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:http://localhost:80/" + dbName, "SA", "");

log.info("con={}", con);

business(con);

con.close();

}

2.3、HyperSQL HTTP Servlet

这种方式使用较少，这里就不详细介绍，可参考源文件 src/org/hsqldb/server/Servlet.java 查看详细信息。

3、混合模式

应用通过代码的方式启动数据库服务，应用内访问数据库可以使用进程模式，其他应用通过服务器模式访问。下面衍生通过代码分别启动HyperSQL HSQL Server 和HyperSQL HTTP Server，然后模拟其他应用访问数据库。

3.1、HyperSQL HSQL Server

@Test

public void hsqlServer2() throws Exception {

HsqlProperties p = new HsqlProperties();

//三种数据库类型，根据需要选择合适的一个

p.setProperty("server.database.0","file:d:/temp/mydb");

//p.setProperty("server.database.0","mem:mydb");

//p.setProperty("server.database.0","res:db/test");//数据库文件test.xx位于某个依赖jar文件的 db 目录中

p.setProperty("server.dbname.0","test");

Server server = new Server();

server.setProperties(p);

server.start();

CountDownLatch countDownLatch = new CountDownLatch(1);

new Thread(() -> {

try {

//模拟其他应用访问

hsqlServer();

} catch (Exception e) {

e.printStackTrace();

}

countDownLatch.countDown();

}).start();

countDownLatch.await();

server.shutdownCatalogs(1);

server.stop();

}

Server 的属性配置参数可参考 2.1 中启动数据库时的命令行参数。

3.2、HyperSQL HTTP Server

@Test

public void httpServer2() throws Exception {

HsqlProperties p = new HsqlProperties();

//三种数据库类型，根据需要选择合适的一个

//p.setProperty("server.database.0","file:d:/temp/mydb");

//p.setProperty("server.database.0","mem:mydb");

p.setProperty("server.database.0","res:db/test");//数据库文件test.xx位于某个依赖jar文件的 db 目录中

p.setProperty("server.dbname.0","test");

WebServer webServer = new WebServer();

webServer.setProperties(p);

webServer.start();

CountDownLatch countDownLatch = new CountDownLatch(1);

new Thread(() -> {

try {

//模拟其他应用访问

httpServer();

} catch (Exception e) {

e.printStackTrace();

}

countDownLatch.countDown();

}).start();

countDownLatch.await();

webServer.stop();

}

WebServer 的属性配置参数可参考 2.2 中启动数据库时的命令行参数。

完整代码：

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;

import org.hsqldb.Server;

import org.hsqldb.persist.HsqlProperties;

import org.hsqldb.server.WebServer;

import org.junit.Test;

import java.sql.*;

import java.util.concurrent.CountDownLatch;

@Slf4j

public class HSQLCase {

@Test

public void inProcessFile() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:file:d:/temp/" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

@Test

public void inProcessMem() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:mem:" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

@Test

public void inProcessRes() throws SQLException {

String dbName = "test";

//用户名密码为第一次连接设置的密码,数据库文件位于某个依赖 jar 文件的 db 目录中

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:res:db/" + dbName, "admin", "123456");

log.info("con={}", con);

business(con);

con.close();

}

@Test

public void hsqlServer() throws SQLException {

String dbName = "test";

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:hsql://localhost:9001/" + dbName, "SA", "");

log.info("con={}", con);

business(con);

con.close();

}

@Test

public void httpServer() throws SQLException {

String dbName = "test";

Connection con = JdbcUtil.getConnection("org.hsqldb.jdbc.JDBCDriver", "jdbc:hsqldb:http://localhost:80/" + dbName, "SA", "");

log.info("con={}", con);

business(con);

con.close();

}

@Test

public void hsqlServer2() throws Exception {

HsqlProperties p = new HsqlProperties();

//三种数据库类型，根据需要选择合适的一个

p.setProperty("server.database.0","file:d:/temp/mydb");

//p.setProperty("server.database.0","mem:mydb");

//p.setProperty("server.database.0","res:db/test");//数据库文件test.xx位于某个依赖jar文件的 db 目录中

p.setProperty("server.dbname.0","test");

Server server = new Server();

server.setProperties(p);

server.start();

CountDownLatch countDownLatch = new CountDownLatch(1);

new Thread(() -> {

try {

//模拟其他应用访问

hsqlServer();

} catch (Exception e) {

e.printStackTrace();

}

countDownLatch.countDown();

}).start();

countDownLatch.await();

server.shutdownCatalogs(1);

server.stop();

}

@Test

public void httpServer2() throws Exception {

HsqlProperties p = new HsqlProperties();

//三种数据库类型，根据需要选择合适的一个

//p.setProperty("server.database.0","file:d:/temp/mydb");

//p.setProperty("server.database.0","mem:mydb");

p.setProperty("server.database.0","res:db/test");//数据库文件test.xx位于某个依赖jar文件的 db 目录中

p.setProperty("server.dbname.0","test");

WebServer webServer = new WebServer();

webServer.setProperties(p);

webServer.start();

CountDownLatch countDownLatch = new CountDownLatch(1);

new Thread(() -> {

try {

//模拟其他应用访问

httpServer();

} catch (Exception e) {

e.printStackTrace();

}

countDownLatch.countDown();

}).start();

countDownLatch.await();

webServer.stop();

}

private void business(Connection con) throws SQLException {

String tableName = "a_student";

Statement st = con.createStatement();

String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_schema)=? and upper(table_name)=?";

PreparedStatement pst = con.prepareStatement(sql);

pst.setString(1, "PUBLIC");

pst.setString(2, tableName.toUpperCase());

ResultSet rs = pst.executeQuery();

if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作

st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");

st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");

st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");

}

rs = st.executeQuery("select * from " + tableName);

while (rs.next()) {

log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));

}

}

}

HSQLCase.java

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;

@Slf4j

public class JdbcUtil {

private JdbcUtil() {}

public static Connection getConnection(String driver, String url, String username, String password) {

Connection con = null;

try {

Class.forName(driver);

con = DriverManager.getConnection(url, username, password);

} catch (ClassNotFoundException | SQLException e) {

log.warn("url={},username={},password={}", url, username, password);

e.printStackTrace();

}

return con;

}

}

JdbcUtil.java