HSQLDB 是一個用Java編寫的關(guān)系數(shù)據(jù)庫管理系統(tǒng)旭斥。它有一個JDBC驅(qū)動程序并且支持一個SQL-92和SQL:2008標(biāo)準(zhǔn)的較大子集。它提供了一個快速輕量級)數(shù)據(jù)庫引擎弃酌，該數(shù)據(jù)庫引擎提供基于磁盤和內(nèi)存的表甘穿。

官方介紹如下：

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.

應(yīng)用專屬 Database Catalogs

1、文件型數(shù)據(jù)庫

 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监憎、內(nèi)存數(shù)據(jù)庫

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

3庆揩、res數(shù)據(jù)庫

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

關(guān)閉數(shù)據(jù)庫

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", "");

創(chuàng)建表

語法如下：

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)
);