HSQL DB 在Web 应用下使用方法

1. 简介

HSQL DB 是一款由Java编写的免费数据库软件，相对其他数据库来说，HSQL DB 具有灵活、小巧（仅690KB）等特点。使用时导入hsqldb.jar即可（http://hsqldb.org）。

2. HSQL DB 服务类

该类含启动、停止HSQL DB数据库服务和得到HSQL DB 数据库连接的方法

package com.brofe.hsqldb.core;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hsqldb.Server;

/**
 * HSQLDB 服务提供类 
 * 
 * @author brofe
 * @since Mar 4, 2009
 * @version 1.0
 */
public class HsqldbService {
	
	private static Log logger = LogFactory.getLog(HsqldbService.class);
	
	private static String hsqlUrl = new String("");
	
	private static String hsqlUser = new String("");
	
	private static String hsqlPwd = new String("");
	
	private static String hsqlDBName = new String("");
	
	private static String hsqlDBPath = new String("");
	
	private static String hsqlScriptDir = new String(""); // HSQL 数据库脚本所在目录（最好放置在CLASSPAHT目录下）
	
	private static int hsqlDBPort = 9002;
	
	/**
	 * 初始化默认参数
	 */
	static {
		hsqlScriptDir = "/hsqldb/";
		
		hsqlDBPath = HsqldbService.class.getResource(hsqlScriptDir).getPath();
		hsqlDBName = "default-db";
		
		hsqlUrl = "jdbc:hsqldb:hsql://localhost:" + hsqlDBPort + "/" + hsqlDBName;
		hsqlUser = "sa";
		
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (Exception e) {
			logger.error("加载 Hsqldb JDBC Driver 出错....", e);
		}
	}
	
	/**
	 * 启动Hsqldb的数据库服务
	 */
	public static void start () {
		logger.error("datebase >>>> " + hsqlDBName);
		logger.error("database path >>>> " + hsqlDBPath + hsqlDBName);
		logger.error("databse port >>>> " + hsqlDBPort);
		
		Server server = new Server();
		server.setDatabaseName(0, hsqlDBName);
		server.setDatabasePath(0, hsqlDBPath + hsqlDBName);
		server.setPort(hsqlDBPort);
		server.setSilent(true);
		
		server.start();
		logger.error("Hsqldb 【 " + hsqlDBName + " 】 is starting....");
		
		try {
			Thread.sleep(1000);
		} catch (InterruptedException e) {
			logger.error("Hsqldb 【 " + hsqlDBName + " 】 starting is error...");
		}
	}
	
	/**
	 * 关闭Hsqldb的数据库服务
	 */
	public static void destroy () {
		Connection conn = getConnection();
		try {
			conn.createStatement().executeUpdate("SHUTDOWN");
		} catch (SQLException sqle) {
			logger.error("关闭Hsqldb数据库服务失败...", sqle);
		}
		logger.error("成功关闭Hsqldb数据库服务...");
	}
	
	/**
	 * 得到Hsqldb数据库连接
	 * 
	 * @return Connection
	 */
	public static Connection getConnection () {
		Connection conn = null;
		logger.error("datebase url >>>> " + hsqlUrl);
		logger.error("database user >>>> " + hsqlUser);
		logger.error("databse pwd >>>> " + hsqlPwd);
		
		try {
			conn = DriverManager.getConnection(hsqlUrl, hsqlUser, hsqlPwd);
		} catch (SQLException sqle) {
			logger.error("获取Hsqldb 连接出错...", sqle);
		}
		return conn;
	}

	public static String getHsqlUrl() {
		return hsqlUrl;
	}

	public static void setHsqlUrl(String hsqlUrl) {
		HsqldbService.hsqlUrl = hsqlUrl;
	}

	public static String getHsqlUser() {
		return hsqlUser;
	}

	public static void setHsqlUser(String hsqlUser) {
		HsqldbService.hsqlUser = hsqlUser;
	}

	public static String getHsqlPwd() {
		return hsqlPwd;
	}

	public static void setHsqlPwd(String hsqlPwd) {
		HsqldbService.hsqlPwd = hsqlPwd;
	}

	public static String getHsqlDBName() {
		return hsqlDBName;
	}

	public static void setHsqlDBName(String hsqlDBName) {
		HsqldbService.hsqlDBName = hsqlDBName;
	}

	public static String getHsqlDBPath() {
		return hsqlDBPath;
	}

	public static void setHsqlDBPath(String hsqlDBPath) {
		HsqldbService.hsqlDBPath = hsqlDBPath;
	}

	public static int getHsqlDBPort() {
		return hsqlDBPort;
	}

	public static void setHsqlDBPort(int hsqlDBPort) {
		HsqldbService.hsqlDBPort = hsqlDBPort;
	}

	public static String getHsqlScriptDir() {
		return hsqlScriptDir;
	}

	public static void setHsqlScriptDir(String hsqlScriptDir) {
		HsqldbService.hsqlScriptDir = hsqlScriptDir;
	}
	
	public static void main(String[] args) {
		System.out.println(File.separator + "hsqldb" + File.separator);
	}
}

3. HSQL DB 全局监听器类

该类便于HSQL DB数据库服务随应用服务器起停。

package com.brofe.hsqldb.core;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

/**
 * 启动Hsqldb服务器Application监听器 
 * 
 * @author brofe
 * @since Mar 4, 2009
 * @version 1.0
 */
public class HsqldbListener implements ServletContextListener {
	
	/**
	 * 启动Hsqldb服务器
	 */
	public void contextInitialized(ServletContextEvent event) {
		HsqldbService.start();
	}

	/**
	 * 关闭Hsqldb服务器
	 */
	public void contextDestroyed(ServletContextEvent event) {
		HsqldbService.destroy();
	}
}

4. 在web.xml中配置监听器

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
	http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	
	<listener>
		<display-name>HSQL 数据库服务器监听程序</display-name>
		<listener-class>com.brofe.hsqldb.core.HsqldbListener</listener-class>
	</listener>
	
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
</web-app>

5. 根据定义的数据库名字，创建*.script数据库脚本和HSQL DB 配置脚本*.properties

默认数据库脚本文件为：default-db.script

create memory table tbl_users(id INTEGER, name varchar(80), age INTEGER)

insert into tbl_users values(1, '阿华', 22)
insert into tbl_users values(1, '小明', 23)
insert into tbl_users values(1, '雅思', 24)
insert into tbl_users values(1, '雅言', 25)

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 20
SET SCHEMA PUBLIC

默认HSQL DB配置文件为：default-db.properties

#HSQL Database Engine 1.8.0.10
#Wed Jun 17 11:19:20 CST 2009
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=memory
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

6. 编写测试程序

package com.brofe.hsqldb.core;

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

/**
 * Hsqldb 数据库测试类 
 * 
 * @author brofe
 * @since Mar 9, 2009
 * @version 1.0
 */
public class TestHsqldbService {
	
	public void testSelect () {
		String sql = "select * from tbl_users";
		Connection conn = HsqldbService.getConnection();
		
		if (conn != null) {
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					System.out.print("【" + rs.getObject("id").toString() + " ");
					System.out.print(" " + rs.getString("name") + " ");
					System.out.print(" " + rs.getString("age") + " ");
					System.out.print("】");
				}
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void main(String[] args) {
		TestHsqldbService test = new TestHsqldbService();
		test.testSelect();
	}
	
}

7. 启动应用服务器

我使用的是Tomcat，安装目录为：D:\Program Files\Apache Software Foundation\Tomcat 5.5，故在启动应用之后，会在D盘生成如下目录：D:\Program%20Files\Apache%20Software%20Foundation\Tomcat%205.5\webapps\hsqldbdemo\WEB-INF\classes\hsqldb，此时该目录下只有：default-db.properties、default-db.log两个文件，请把default-db.script文件COPY到该目录下，然后再重启应用服务器执行测试类，即可看到效果。（至于为什么没有自动生成default-db.script， 而要手动COPY， 我也很矛盾， 不知道出了什么问题， 希望各位大虾能提宝贵意见）