hsqldb的存储方式

Maven 依赖

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

一、内存(mem)存储方式

jdbc:hsqldb:mem:testdb

    @Test
    public void testMem() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
//        String hsqldbMemUrl = "jdbc:hsqldb:mem:.";
        String url = "jdbc:hsqldb:mem:testdb";
        Connection connection = DriverManager.getConnection(url, "SA", "");
        Statement statement = connection.createStatement();
        statement.execute("CREATE TABLE student (id INTEGER, name VARCHAR(10))");
        statement.execute("INSERT INTO student VALUES (1, 'zhang1')");
        statement.execute("INSERT INTO student VALUES (2, 'zhang2')");

        ResultSet rs = statement.executeQuery("SELECT * FROM student");
        while (rs.next()) {
            int col1 = rs.getInt(1);
            String col2 = rs.getString(2);
            System.out.println(String.format("%s %s", col1, col2));
        }

        rs.close();
        statement.close();
        connection.close();
    }

二、文件(file)存储方式

jdbc:hsqldb:file:/home/zxm/hsqldb/testdb

    @Test
    public void testFile() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        String url = "jdbc:hsqldb:file:/home/zxm/hsqldb/testdb";
        Connection connection = DriverManager.getConnection(url, "SA", "");
        Statement statement = connection.createStatement();
        statement.execute("CREATE TABLE student (id INTEGER, name VARCHAR(10))");
        statement.execute("INSERT INTO student VALUES (1, 'zhang1')");
        statement.execute("INSERT INTO student VALUES (2, 'zhang2')");

        ResultSet rs = statement.executeQuery("SELECT * FROM student");
        while (rs.next()) {
            int col1 = rs.getInt(1);
            String col2 = rs.getString(2);
            System.out.println(String.format("%s %s", col1, col2));
        }

        rs.close();
        statement.close();
        connection.close();
    }

会在 file:/home/zxm/hsqldb/testdb 下保存数据

zxm@zxm-pc:~/hsqldb$ ls
testdb.log  testdb.properties  testdb.script  testdb.tmp

三、资源(res)存储方式

jdbc:hsqldb:res:testdb

它 mem 与 file 方式的结合，是从 classpath 下读取数据

可以把 file 方式生成的 testdb.properties 和 testdb.script 放在 classpath 下，用 res 方式可以读取到数据，数据库启动的时候会去这两个文件里读取数据进行初始化，但不会生成 testdb.log 和 testdb.lck ，以后进行的所有操作就都在内存里了，关闭数据库也不会写入 testdb.script

    @Test
    public void testRes() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        String url = "jdbc:hsqldb:res:testdb";
        Connection connection = DriverManager.getConnection(url, "SA", "");
        Statement statement = connection.createStatement();
//        statement.execute("CREATE TABLE student (id INTEGER, name VARCHAR(10))");
//        statement.execute("INSERT INTO student VALUES (1, 'zhang1')");
//        statement.execute("INSERT INTO student VALUES (2, 'zhang2')");

        ResultSet rs = statement.executeQuery("SELECT * FROM student");
        while (rs.next()) {
            int col1 = rs.getInt(1);
            String col2 = rs.getString(2);
            System.out.println(String.format("%s %s", col1, col2));
        }

        rs.close();
        statement.close();
        connection.close();
    }

四、服务器模式

jdbc:hsqldb:hsql://localhost:9001/testdb

    public static void main(String[] args) {
        Server server = new Server();
        server.setDatabaseName(0, "testdb");
        server.setDatabasePath(0, "/home/zxm/hsqldb/testdb");
        server.setPort(9001);
        server.setSilent(true);
        server.setTrace(true);
        server.start();
    }

    @Test
    public void testHSQLConnect() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        String url = "jdbc:hsqldb:hsql://localhost:9001/testdb";
        Connection connection = DriverManager.getConnection(url, "SA", "");
        Statement statement = connection.createStatement();
        statement.execute("CREATE TABLE student (id INTEGER, name VARCHAR(10))");
        statement.execute("INSERT INTO student VALUES (1, 'zhang1')");
        statement.execute("INSERT INTO student VALUES (2, 'zhang2')");

        ResultSet rs = statement.executeQuery("SELECT * FROM student");
        while (rs.next()) {
            int col1 = rs.getInt(1);
            String col2 = rs.getString(2);
            System.out.println(String.format("%s %s", col1, col2));
        }

        rs.close();
        statement.close();
        connection.close();
    }

五、Web服务器模式

jdbc:hsqldb:http://localhost:9001/testdb

    public static void main(String[] args) {
        WebServer webServer = new WebServer();
        webServer.setDatabaseName(0, "testdb");
        webServer.setDatabasePath(0, "/home/zxm/hsqldb/testdb");
        webServer.setPort(9001);
        webServer.setSilent(true);
        webServer.setTrace(true);
        webServer.start();
    }

    @Test
    public void testHSQLConnect() throws ClassNotFoundException, SQLException {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        String url = "jdbc:hsqldb:http://localhost:9001/testdb";
        Connection connection = DriverManager.getConnection(url, "SA", "");
        Statement statement = connection.createStatement();
        statement.execute("CREATE TABLE student (id INTEGER, name VARCHAR(10))");
        statement.execute("INSERT INTO student VALUES (1, 'zhang1')");
        statement.execute("INSERT INTO student VALUES (2, 'zhang2')");

        ResultSet rs = statement.executeQuery("SELECT * FROM student");
        while (rs.next()) {
            int col1 = rs.getInt(1);
            String col2 = rs.getString(2);
            System.out.println(String.format("%s %s", col1, col2));
        }

        rs.close();
        statement.close();
        connection.close();
    }