hsqldb数据库在java web项目（androidpn）中的使用（spring+hibernate+hsqldb）

本文主要介绍spring+hibernate+hsqldb的使用（hsqldb在androidpn中的使用）

一、介绍

hsqldb数据库是一款纯Java编写的免费数据库，许可是BSD-style的协议，如果你是使用Java编程的话，不凡考虑一下使用它，相对其他数据库来说，其体积小，才563kb。仅一个hsqldb.jar文件就包括了数据库引擎，数据库驱动， 还有其他用户界面操作等内容。在Java开源世界里，hsql是极为受欢迎的（就Java本身来说），JBoss应用程序服务器默认也提供了这个数据库引 擎。由于其体积小的原因，又是纯Java设计，又支持SQL99，SQL2003大部分的标准，所以也是作为商业应用程序展示的一种选择。请到以下地址下载hsql：https://sourceforge.net/projects/hsqldb/files/?source=navbar，官网为：http://hsqldb.org/，当前最新版本为2.3.4

官方指导文件中的介绍如下：

HyperSQL Database (HSQLDB ) is a modern relational database system. Version 2.3 is the latest release of the all-newversion 2 code. Written from ground up to follow the international ISO SQL:2011 standard, it supports the completeset of the classic features, together with optional features such as stored procedures and triggers.HyperSQL is used for development, testing and deployment of database applications.

Standard compliance is the most unique characteristic of HyperSQL. There are several other distinctive features.HyperSQL can provide database access within the user's application process, within an application server, or as a separate server process. HyperSQL can run entirely in memory using dedicated fast memory structures as opposedto ram disk. HyperSQL can use disk persistence in a flexible way, with reliable crash-recovery. HyperSQL is theonly open-source relational database management system with a high performance dedicated lob storage system,suitable for gigabytes of lob data. It is also the only relational database that can create and access large commadelimited files as SQL tables. HyperSQL supports three live switchable transaction control models, including fullymulti threaded MVCC, and is suitable for high performance transaction processing applications. HyperSQL is alsosuitable for business intelligence, ETL and other applications that process large data sets. HyperSQL has a wide rangeof enterprise deployment options, such as XA transactions, connection pooling data sources and remote authentication.

    New SQL syntax compatibility modes have been added to HyperSQL. These modes allow a high degree ofcompatibility with several other database systems which use non-standard SQL syntax.  

HyperSQL is written in the Java programming language and runs in a Java virtual machine (JVM). It supports theJDBC interface for database access.

An ODBC driver is also available as a separate download.

This guide covers the database engine features, SQL syntax and different modes of operation. The Server,JDBC interfaces, pooling and XA components are documented in the JavaDoc. Utilities such as SqlTool andDatabaseManager are covered in a separate Utilities Guide.

二、hsqldb数据库运行模式

Hsqldb有四种运行模式：从HSQL Database Manager中可见一班

E:\MyEclipse\workspace\Androidpn\WebRoot\WEB-INF\lib>java -classpath hsqldb-1.8.0.10.jar org.hsqldb.util.DatabaseManagerSwing

1、内存（Memory-Only）模式：所有的数据都将在内存中完成，如果程序退出，则相应的数据也将同时被销毁。连接JDBC的实例为：jdbc:hsqldb:mem:dbname

2、进程（In-Process）模式：此模式从应用程序启动数据库，由于所有的数据都将写到文件中，所以，即使程序退出，数据也不会被销毁。不能通过网络来访问数据库，主要是在一个JVM中使用，那样的话，访问的速度会更加快。In-Process 不需要另外启动，可以通过

DriverManager.getConnection("jdbcUriName","username","password");

方式即可启动数据库。连接 JDBC 的实例为：

jdbc:hsqldb:file:/E:/hsqldb/data/dbname
jdbc:hsqldb:file:/opt/db/dbname
jdbc:hsqldb:file:dbname

注：以上两种模式都可以从应用程序中启动，但是在HSQL Database Manager中不可以访问数据库

3、服务器模式：此模式下 HSQLDB 跟其它数据库服务器一样，需要通过服务器的形式来进行启动，可以通过

java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server –database testdb –dbname testdbName

的命令启动一个守护进程。连接 JDBC 的实例为：

jdbc:hsqldb:hsql://localhost:port/dbname

4、Web服务器模式：此模式以WEB服务器的形式启动，并通过HTTP协议接受客户端命令。从1.7.2版本开始，Web服务器模式的 HSQLDB 开始支持事务处理。可以通过

java -classpath ../lib/hsqldb.jar org.hsqldb.WebServer –database testdb –dbname testdbname

的命令来启动。jdbc:hsqldb:http://localhost:port/dbname

三、使用

主要说下mem、file和server模式下的使用。

1.mem模式

先看配置：spring配置

<span style="font-size:14px;"><span style="font-size:14px;"><bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:jdbc.properties</value>
			</list>
		</property>
	</bean>

	<!-- =============================================================== -->
	<!-- Data Source                                                     -->
	<!-- =============================================================== -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbcDriverClassName}" />
		<property name="url" value="${jdbcUrl}" />
		<property name="username" value="${jdbcUsername}" />
		<property name="password" value="${jdbcPassword}" />
		<property name="maxActive" value="${jdbcMaxActive}" />
		<property name="maxIdle" value="${jdbcMaxIdle}" />
		<property name="maxWait" value="${jdbcMaxWait}" />
		<property name="defaultAutoCommit" value="true" />
	</bean> 
	
	<!-- sessionFactory -->
	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:hibernate.cfg.xml" />
	</bean></span></span>

jdbc.propertites

<span style="font-size:14px;"><span style="font-size:14px;"># JDBC Configuration
jdbcDriverClassName=org.hsqldb.jdbcDriver
jdbcUrl=jdbc:hsqldb:mem:mydb                  //mem模式
jdbcUsername=sa
jdbcPassword=

# DBCP Pool settings
jdbcInitialSize=5
jdbcMaxActive=10
jdbcMaxIdle=5
jdbcMaxWait=30000
jdbcValidationQuery=select 1</span></span>

hibernate.cfg.xml

<span style="font-size:14px;"><span style="font-size:14px;"><?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
	<session-factory>
		<!-- Database connection settings -->
		<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
		<property name="hibernate.connection.url">jdbc:hsqldb:mem:mydb</property>       //mem模式
		<property name="hibernate.connection.username">sa</property>
		<property name="hibernate.connection.password"></property>

		<!-- SQL dialect -->
		<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>

		<!-- Enable Hibernate's automatic session context management -->
		<!--( jta | thread | managed | custom.Class )-->
		<property name="hibernate.current_session_context_class">thread</property>

		<!-- Second-level cache  -->
		<!-- http://ehcache.sourceforge.net/documentation/hibernate.html -->
		<property name="hibernate.cache.use_second_level_cache">true</property>
		<property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.EhCacheProvider</property>

		<!-- UTF-8 -->
		<property name="hibernate.connection.useUnicode">true</property>
		<property name="hibernate.connection.characterEncoding">utf-8</property>

		<!-- Echo all executed SQL to stdout -->
		<property name="hibernate.show_sql">false</property>
		<property name="hibernate.format_sql">true</property>
		<property name="hibernate.use_sql_comments">true</property>

		<!-- Drop and re-create the database schema on startup -->
		<!--( validate | update | create | create-drop )-->
		<property name="hibernate.hbm2ddl.auto">update</property>          //自动创建更新table

		<!-- Mapping Files -->
		<mapping class="org.androidpn.server.model.User" />
		<mapping class="org.androidpn.server.model.NotificationMO" />       //要创建的表对象，采用注解的方式映射表结构
	</session-factory>
</hibernate-configuration></span></span>

NotificationMO.java

<span style="font-size:14px;"><span style="font-size:14px;">@Entity
@Table(name = "apn_notification")
public class NotificationMO implements Serializable {
	
	private static final long serialVersionUID = 1845362556725768545L;
	
	public static final String STATUS_NOT_SEND = "0";
	public static final String STATUS_SEND = "1";
	public static final String STATUS_RECEIVE = "2";
	public static final String STATUS_READ = "3";
	
	public NotificationMO(){
	}
	
	public NotificationMO(String apiKey,String title,String message,String uri){
		this.apiKey = apiKey;
		this.title = title;
		this.message = message;
		this.uri = uri;
		this.createTime = new Timestamp(System.currentTimeMillis());
		this.updateTime = new Timestamp(System.currentTimeMillis());
	}
	
	@Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "username", length = 64)
    private String username;
    
    @Column(name = "client_ip", length = 64)
    private String clientIp;
    
    @Column(name = "resource", length = 64)
    private String resource;

    @Column(name = "message_id", length = 64)
    private String messageId;
    
    @Column(name = "apiKey", length = 64)
    private String apiKey;

    @Column(name = "title", length = 512)
    private String title;

    @Column(name = "message", length = 1024)
    private String message;
    
    @Column(name = "uri", length = 512)
    private String uri;
    
    @Column(name = "status", length = 6)
    private String status;
    
    @Column(name = "created_time",updatable = false)
    private Timestamp createTime ;
    
    @Column(name = "updateTime")
    private Timestamp updateTime ;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}
	

	public String getClientIp() {
		return clientIp;
	}

	public void setClientIp(String clientIp) {
		this.clientIp = clientIp;
	}

	public String getResource() {
		return resource;
	}

	public void setResource(String resource) {
		this.resource = resource;
	}

	public String getMessageId() {
		return messageId;
	}

	public void setMessageId(String messageId) {
		this.messageId = messageId;
	}

	public String getApiKey() {
		return apiKey;
	}

	public void setApiKey(String apiKey) {
		this.apiKey = apiKey;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public String getUri() {
		return uri;
	}

	public void setUri(String uri) {
		this.uri = uri;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public Timestamp getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Timestamp createTime) {
		this.createTime = createTime;
	}

	public Timestamp getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(Timestamp updateTime) {
		this.updateTime = updateTime;
	}

}</span></span>

配置完成后启动应用即可，数据库随之启动。

2.file模式，只需将properties和hibernate配置文件中的url改为jdbcUrl=jdbc:hsqldb:file:mydb即可, 其中数据库名字mydb是随便起的，要添加一个数据库名字，否则不能成功

3.server模式稍微有些复杂，该模式下用database Manager可以访问

1）将hsqldb.jar拷贝到工程WEB-INF/lib下

2）开启两个命令行窗口,进入工程 WEB-INF/lib目录

cd E:\MyEclipse\workspace\Androidpn\WebRoot\WEB-INF\lib(假设工程目录为此)

3）分别在命令行中输入命令,启动数据库服务器和客户端:

服务器:java -classpath hsqldb.jar org.hsqldb.Server       //不要关闭，保持运行

此时在lib目录下会生成两个文件

log文件时记录操作日志

<span style="font-size:14px;"><span style="font-size:14px;">CONNECT USER SA
\u000a    create table apn_notification (\u000a        id bigint generated by default as identity (start with 1),\u000a        apiKey varchar(64),\u000a        client_ip varchar(64),\u000a        created_time timestamp,\u000a        message varchar(1024),\u000a        message_id varchar(64),\u000a        resource varchar(64),\u000a        status varchar(6),\u000a        title varchar(512),\u000a        updateTime timestamp,\u000a        uri varchar(512),\u000a        username varchar(64),\u000a        primary key (id)\u000a    )
\u000a    create table apn_user (\u000a        id bigint generated by default as identity (start with 1),\u000a        created_date timestamp,\u000a        email varchar(64),\u000a        name varchar(64),\u000a        password varchar(64),\u000a        updated_date timestamp,\u000a        username varchar(64) not null,\u000a        primary key (id),\u000a        unique (username)\u000a    )
/*C6*/SET SCHEMA PUBLIC
CONNECT USER SA
/*C3*/DISCONNECT
/*C5*/SET AUTOCOMMIT FALSE
INSERT INTO APN_USER VALUES(1,'2016-08-02 16:48:22.577000000',NULL,NULL,'67f6f1a7ef5a4d6ba4012a98de0de90c',NULL,'d7b87b0a43404d00b4da0d4f14c7ce4e')
COMMIT
SET AUTOCOMMIT TRUE</span></span>

properties文件记录配置信息

<span style="font-size:14px;"><span style="font-size:14px;">#HSQL Database Engine 1.8.0.10
#Tue Aug 02 16:43:31 CST 2016
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</span></span>

// 客户端:java -classpath hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

4）在配置文件中设定连接数据库的相关参数

jdbcUrl=jdbc:hsqldb:hsql://localhost

5）运行应用程序即可，此时在databaseManager中就可看到见自动创建的表：用户表和发送通知信息表