hsqldb（转载）

HSQLDB一大特色就是能够在内存中建立数据库，当然它也能将这些内存数据库保存到文件中以便实现真正的持久化。

 

先睹为快！

 

下面是一个In-Process方式访问内存数据库的代码示例：

 

下面代码需要引入hsqldb.jar包 （hsqldb-2.2.8）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

	public static void main(String[] args) {
		try {
			//加载HSQLDB的JDBC驱动
			Class.forName("org.hsqldb.jdbcDriver");
			//在内存中建立数据库memdb,用户名为sa,密码为空
			Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:memdb","username","password");
			System.out.println("connect to memdb OK");
			
			Statement stat = conn.createStatement();
			//新建数据表
			stat.executeUpdate("create table person(NAME VARCHAR(20), AGE INTEGER)");
			System.out.println("create TABLE:person OK");
			
			//插入数据
			stat.executeUpdate("INSERT INTO person VALUES('张三丰',22)");
			stat.executeUpdate("INSERT INTO person VALUES('amos','25')");
			System.out.println("insert data into TABLE:person OK!");

			conn.close();
			
//			stat.execute("SHUTDOWN");
//			System.out.println("SHUTDOWN");
			
			Connection conn2 = DriverManager.getConnection("jdbc:hsqldb:mem:memdb","username","password");
			
			//查询数据
			PreparedStatement pstmt = conn2.prepareStatement("SELECT * FROM person");
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				String s = null;
				s = rs.getString(1) + "," + rs.getString(2);
				System.out.println(s);
			}
			System.out.println("select data OK");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

 

The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs.

 

• HyperSQL RDBMS Engine (HSQLDB)

• HyperSQL JDBC Driver

• Database Manager (GUI database access tool, with Swing and AWT versions)

• Sql Tool (command line database access tool)

 

Running Database Access Tools

 

Cmd命令代码

java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

 

 

When a tool is up and running, you can connect to a database (may be a new database) and use SQL commands to access and modify the data.

 

 

A HyperSQL Database

 

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

 

 

 

 

In-Process Access to Database Catalogs

file：

 

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

 或者

 

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

 

mem：

 

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

 res：

 

Java代码

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

 

 

The first time in-process connection is made to a database, some general data structures are initialised and a few helper threads are started. After this, creation of connections and calls to JDBC methods of the connections execute as if they are part of the Java application that is making the calls. When the SQL command "SHUTDOWN" is executed, the global structures and helper threads for the database are destroyed.

 

 

Note that only one Java process at a time can make in-process connections to a given file: database. However, if the file: database has been made read-only, or if connections are made to a res: database, then it is possible to make inprocess connections from multiple Java processes.

 

HyperSQL HSQL Server

 

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

 

HyperSQL HTTP Server

 

This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.

 

 

org.hsqldb.server.WebServer

 

HyperSQL HTTP Servlet

 

The Servlet class, in the HSQLDB jar, should be installed on the application server to provide the

connection. The database is specified using an application server property. Refer to the source file src/org/hsqldb/server/Servlet.java to see the details.

 

 

Both HTTP Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database.

 

Connecting to a Database Server

 

A common example is connection to the default port (9001) used for the hsql: protocol on the same machine:

 

try {
Class.forName("org.hsqldb.jdbc.JDBCDriver" );
} catch (Exception e) {
System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
e.printStackTrace();
return;
}
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

 

 If the HyperSQL HTTP server is used, the protocol is http: and the URL will be different:

 

Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

 

Security Considerations

the password for the default system user should be changed from the default empty string 

 

 

HyperSQL provides two optional security mechanisms. The encrypted SSL protocol , and Access Control Lists .

Both mechanisms can be specified when running the Server or WebServer. On the client, the URL to connect to an SSL server is slightly different:

 

Java code to connect to the local secure SSL hsql and http Servers

 

Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", "");
Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");

 

Accessing the Data

 

A connection should be reused as much as possible and closed only when it is not going to be used again for a long while.

 

A java.sql.DatabaseMetaData object is used to get metadata for the database.

 

 

A java.sql.CallableStatement object is used to execute an SQL CALL statement. The SQL CALL statement may contain parameters, which should be set to new values before each reuse.

 

A java.sql.Connection object also has some methods for transaction control.

 

Closing the Database

All databases running in different modes can be closed with the SHUTDOWNcommand, issued as an SQL statement.

 

 

A special form of closing the database is via the SHUTDOWN COMPACT command. This command rewrites the .data file that contains the information stored in CACHED tables and compacts it to its minimum size. This command should be issued periodically, especially when lots of inserts, updates or deletes have been performed on the cached tables. Changes to the structure of the database, such as dropping or modifying populated CACHED tables or indexes also create large amounts of unused file space that can be reclaimed using this command.

 

 

Databases are not closed when the last connection to the database is explicitly closed via JDBC. A connection property, shutdown=true, can be specified on the first connection to the database (the connection that opens the database) to force a shutdown when the last connection closes.

 

 

specifying a connection property to shutdown the database when the lastconnection is closed

 

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

 

Creating a New Database

 

When a server instance is started, or when a connection is made to an in-process database,a new, empty database is created if no database exists at the given path.

If no username or password is specified, the default SA user and an empty password are used.

 

specifying a connection property to disallow creating a new database

 

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

 

Creating and Accessing an Encrypted Database

 

First, a key must be created for the desired cipher and configuration. This is done by calling the function CRYPT_KEY(<cipher spec>, <provider>). If the default provider (the built-in JVM ciphers) is used, then NULL should be specified as the provider. The CRYPT_KEY function returns a hexadecimal key. The function call can be made in any HyperSQL database, so long as the provider class is on the classpath. This key can be used to create a new encrypted database. Calls to this function always return different keys, based on a generated random values.

 

As an example, a call to CRYPT_KEY('Blowfish', null) returned the string, '604a6105889da65326bf35790a923932'.

 

To create a new database, the URL below is used:

 

jdbc:hsqldb:file:<database
path>;crypt_key=604a6105889da65326bf35790a923932;crypt_type=blowfish

 

The third property name is crypt_provider. This is specified only when the provider is not the default provider.

 

HyperSQL works with any symmetric cipher that may be available from the JVM.

The files that are encrypted include the .script, .data, .backup and .log files. The .lobs file is not encrypted by default.

The property crypt_lobs=true must be specified to encrypt the .lobs file.

 

 

介绍可以看这里：

http://www.javaeye.com/topic/78887

http://hi.baidu.com/mum0532/blog/item/b08c0c6d88e0faf9431694e5.html

从官网下载的压缩包，解压后的demo目录中，可以运行runManagerSwing，打开可视化管理界面（但没有可视化创建表，创建字段的功能）

 

连接的时候，先选择server模式，然后在下图的红框处输入启动服务的端口和数据库名称。例如jdbc:hsqldb:hsql://localhost:9002/test

这里9002是我程序启动hsqldb的时候使用的端口，test是建立的数据库名称

 

HSQLDB创建数据库和基本的数据库访问：

http://hi.baidu.com/hivemind/blog/item/83873bdf36611c1462279825.html

HSLQDB的sql语法跟MySQL的略有不同，使得很郁闷啊~~开始我都不知道怎么初始化HSQLdb的数据

 

HSQLDB和Hibernate结合的一个小例子：

http://hi.baidu.com/hivemind/blog/item/2c77fb00830e5a16738b6506.html

 

我自己写的一个Java类：（只需要加入hsqldb.jar即可）

/**
 * 方便单机程序使用HSQL的工具类，包括启动，关闭，连接。数据库默认不加密，用户为sa，密码空
 * @author 郑高强
 */
public class HSQL_Util {

    public static final int PORT = 9002;
    public static final String DB_NAME = "kenko";       //数据库文件名，同时也是本类中的数据库名
    public static final String DB_PATH = "./db/";
    public static final String USER_NAME = "sa";
    public static final String PASSWORD = "";
    public static final int SERVER_MODE = 0;
    public static final int STAND_ALONE_MODE = 1;   //In-Process
    public static int mode = SERVER_MODE;          //记录当前用什么模式，开发时用Server，发布时用standalone

    /**
     * 启动数据库服务
     */
    public static boolean startHSQL() {
        if (mode == SERVER_MODE) {
            Server server = new Server();//它可是hsqldb.jar里面的类啊。
            server.setDatabaseName(0, DB_NAME);
            server.setDatabasePath(0, DB_PATH + DB_NAME);
            server.setPort(PORT);
            server.setSilent(true);
            server.start();         //自动多线程运行
            System.out.println("hsqldb started...");
        } else if (mode == STAND_ALONE_MODE) {
            //standalone模式，打开连接就同时启动数据库，所以这里可以什么都不做
        }

        try {
            Thread.sleep(800);        // 等待Server启动
        } catch (InterruptedException e) {
        }
        return true;
    }

    /**
     * 关闭数据库服务
     */
    public static boolean stopHSQL() {
        try {
            Statement statement = getConnection().createStatement();
            statement.executeUpdate("SHUTDOWN;");
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            if (mode == SERVER_MODE) {
                conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:" + PORT + "/" + DB_NAME, USER_NAME, PASSWORD);
            } else if (mode == STAND_ALONE_MODE) {
                conn = DriverManager.getConnection("jdbc:hsqldb:file:" + DB_PATH + DB_NAME, USER_NAME, PASSWORD);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }

    /**
     * 测试
     */
    public static void main(String[] args) {
        HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE;
        HSQL_Util.startHSQL();
        Connection conn = HSQL_Util.getConnection();
        try {
            Statement statement = getConnection().createStatement();
            statement.executeUpdate("create table customer(id integer not null primary key,firstname varchar,lastname varchar)");
            for (int i = 10; i < 20; i++) {
                statement.executeUpdate("insert into customer values(" + i + ",'liu','zhaoyang')");
            }
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
        }
        HSQL_Util.stopHSQL();
    }
}

运行上述的类，在项目目录，会生成一个db文件夹，里边有数据库的一些基本文件：

 

 

HSQL速度测试：

String note = "XXXX";     //这里省略，实际是800个中文字
        System.out.println(note.length());
        HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE;
        HSQL_Util.startHSQL();
        Connection conn = HSQL_Util.getConnection();
        try {
            Statement statement = getConnection().createStatement();
            statement.executeUpdate("create cached table customer(id integer not null primary key,firstname varchar,lastname varchar)");
            for (int i = 1; i < 60000; i++) {                                 //插入6万条数据用了43秒，生成262M的文件
                statement.executeUpdate("insert into customer values(" + i + ",'" + note + "','zhaoyang')");
            }
            statement.executeQuery("select * from customer where id = 40");     //6万条数据用了9秒
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
        }
        HSQL_Util.stopHSQL();
    }