HSQLDB 是一个用Java编写的关系数据库管理系统。它有一个JDBC驱动程序并且支持一个SQL-92和SQL:2008标准的较大子集。它提供了一个快速轻量级)数据库引擎，该数据库引擎提供基于磁盘和内存的表。

官方介绍如下：

HSQLDB (HyperSQL DataBase) is the leading SQL relational database software written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command line SQL tool and simple GUI query tools.

HSQLDB supports the widest range of SQL Standard features seen in any open source database engine: SQL:2011 core language features and an extensive list of SQL:2011 optional features. It supports nearly full Advanced ANSI-92 SQL (BNF format). Many extensions to the Standard, including syntax compatibility modes and features of other popular database engines, are also supported.

Version 2.3.4 is fully multithreaded and supports high performance 2PL and MVCC (multiversion concurrency control) transaction control models. See the list of new features in version 2.3.4.

快速入门

maven依赖：

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.3.4</version>
</dependency>

HyperSQL Database

HyperSQL Database 存储有三种，官方介绍如下：

Each HyperSQL database is called a catalog. There are three types of catalog depending on how the data is stored.

Types of catalog data:

• mem: stored entirely in RAM - without any persistence beyond the JVM process's life

• file: stored in filesystem files

• res: stored in a Java resource, such as a Jar and always read-only

All-in-memory, mem: catalogs can be used for test data or as sophisticated caches for an application. These databases do not have any files.

A file: catalog consists of between 2 to 6 files, all named the same but with different extensions, located in the same directory. For example, the database named "test" consists of the following files:

• test.properties

• test.script

• test.log

• test.data

• test.backup

• test.lobs

A res: catalog consists of the files for a small, read-only database that can be stored inside a Java resource such as a ZIP or JAR archive and distributed as part of a Java application program.

应用专属 Database Catalogs

1、文件型数据库

 Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "SA", "");

或者database file 路径为： /opt/db/testdb.

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "SA", "");

2、内存数据库

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");

3、res数据库

Connection c = DriverManager.getConnection("jdbc:hsqldb:res:org.my.path.resdb", "SA", "");

关闭数据库

Connection c = DriverManager.getConnection(
         "jdbc:hsqldb:file:/opt/db/testdb;shutdown=true", "SA", "");

Creating a New Database

This feature has a side effect that can confuse new users. If a mistake is made in specifying the path for connecting to an existing database, a connection is nevertheless established to a new database. For troubleshooting purposes, you can specify a connection property ifexists=true to allow connection to an existing database only and avoid creating a new database. In this case, if the database does not exist, the getConnection() method will throw an exception.

Connection c = DriverManager.getConnection(
         "jdbc:hsqldb:file:/opt/db/testdb;ifexists=true", "SA", "");

创建表

语法如下：

CREATE TABLE table_name (column_name column_type);

例如：

CREATE TABLE t_user (
    id BIGINT NOT NULL, 
    name VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL, 
    age SMALLINT, 
    birthday TIMESTAMP,
    PRIMARY KEY (id)
);

代码如下：

package com.bytebeats.codelab.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Ricky Fung
 * @create 2017-03-05 11:59
 */
public class CreateTableDemo {

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver" );
            conn = DriverManager.getConnection("jdbc:hsqldb:file:/D:db/hsqldb/testdb", "SA", "");
            stmt = conn.createStatement();

            String sql = "CREATE TABLE t_user (" +
                    "id BIGINT NOT NULL, " +
                    "name VARCHAR(20) NOT NULL," +
                    "password VARCHAR(32) NOT NULL, " +
                    "age SMALLINT, " +
                    "birthday TIMESTAMP," +
                    "PRIMARY KEY (id)" +
                    ");";

            System.out.println(sql);

            int result = stmt.executeUpdate(sql);

            System.out.println("result:"+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

插入记录

语法如下：

INSERT INTO table_name (field1, field2,...fieldN)
VALUES (value1, value2,...valueN );

例如：

INSERT INTO t_user(id, name, password, age, birthday)  VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');

代码如下：

package com.bytebeats.codelab.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2017-03-05 13:08
 */
public class InsertQueryDemo {

    public static void main(String[] args) {

        Connection con = null;
        Statement stmt = null;

        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver" );
            con = DriverManager.getConnection("jdbc:hsqldb:file:/D:db/hsqldb/testdb", "SA", "");
            stmt = con.createStatement();
            int result = stmt.executeUpdate("INSERT INTO t_user VALUES (1,'ricky', '12345', 28, '1989-09-15 15:00:00');");
            System.out.println(result);
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

查询

语法如下：

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

例如：

SELECT id, name, password, age, birthday FROM t_user

代码如下：

package com.bytebeats.codelab.hsqldb;

import java.sql.*;

/**
 * ${DESCRIPTION}
 *
 * @author Ricky Fung
 * @create 2017-03-05 13:13
 */
public class SelectQueryDemo {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet result = null;

        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");
            con = DriverManager.getConnection("jdbc:hsqldb:file:/D:db/hsqldb/testdb", "SA", "");
            stmt = con.createStatement();
            result = stmt.executeQuery(
                    "SELECT id, name, password, age, birthday FROM t_user");

            while(result.next()){
                System.out.println(result.getInt("id")+" | "+
                        result.getString("name")+" | "+
                        result.getString("password")+" | "+
                        result.getShort("age")+" | "+
                        result.getTimestamp("birthday"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

HSQLDB支持自增id，使用 IDENTITY 指定，如下：

CREATE TABLE t_user (
    id BIGINT IDENTITY NOT NULL,
    name VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL,
    age SMALLINT,
    birthday TIMESTAMP,
    PRIMARY KEY (id)
);