本文主要介紹 HSQLDB 的基本使用,文中所使用到的軟體版本:Java 11.0.22、HSQLDB 2.7.2。 1、進程內模式 直接使用 JDBC 連接資料庫即可,如果資料庫不存在會自動創建。 1.1、file 資料庫 @Test public void inProcessFile() thr ...
本文主要介紹 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 中啟動資料庫時的命令行參數。
完整代碼:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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