关于H2使用的那些事

在项目中采用H2作为工作流的数据库，总结一下h2的使用；

前言 ：H2database是一款用java语言编写的开源数据库。 H2服务器下载下载地址： http://www.h2database.com/。
原文地址http://www.h2database.com/html/tutorial.html
下文前提是数据库已被解压安装完毕。

开启和使用H2控制台
H2Database支持通过浏览器可以访问一个SQL数据库，数据库种类包括H2和其他相应的支持JDBC的数据库类型。


         这是一个客户端/服务器端模式，所以运行它需要一个服务器端和一个客户端（浏览器）。根据你的平台和环境，你可以通过多种方式启动H2：
1、Windows     

          Click [Start], [All Programs], [H2], and [H2 Console (Command Line)] 如果你使用的是SUN JKD1.4 或1.5，将会弹出一个标题为“H2 Console”的窗口； 如果你使用的是SUN JDK1.6，将会出现在成功启动的系统上显示图标。
        假如未弹出窗口也未出现图标，原因很可能是你的JAVA没有被正确的安装（在这种情况下，尝试通过其它方法启动服务，如：打开一个浏览器窗口，并使其指向http://localhost:8082）。
2、Windows      

        从文件夹中定位到h2/bin，双击h2.bat。
将会出现一个控制台窗口。如果有问题的话，在控制台窗口中将会出现错误信息。 随后一个浏览器窗口将被打开并指向登录页面（URL: http://localhost:8082）。
3、Any 　      　

      启动一个控制台窗口，定位到目录'h2/bin' ， 输入：

Java代码  

java -cp h2.jar org.h2.tools.Server

防火墙
      当你启动服务时，防火墙可能会显示一个安全警告信息（如果你安装了防火墙）。如果你不打算让位于同一个网络中的其他计算机访问你本机的数据库服务， 你可以设置防火墙
        阻止这些连接。此时来自本机的连接保持仍然可用。相反的，如果你打算允许其他机器访问本机的提供的数据库服务， 你可以设置防火墙允许这些连接。
此外H2本身自带了一个小的防火墙： 默认情况下，其他机器不能连接到本服务。 为了改变默认， 你可以进入'Preferences' ， 选择'Allow connections from other computers'
译者注（Preferences选项位置数据库启动页面顶部）



本地版本
        本地版本不必需JAVA支持， 因为它可以使用GCJ来编译。但是，现在基于Window的GCJ下运行H2仍不稳定。


测试JAVA
打开命令窗口并输入 测试你安装的java的版本
java -version
如果你得到的是错误信息， 此时你可能需要把java目录添加到环境变量中。


错误消息“端口被使用”
同一端口你只能运行一个H2控制台实例，否则你将得到如下错误信息

引用

Port is in use, maybe another ... server already running on....

在同一台电脑上可以开启多个控制台程序（使用不同的端口）， 如果控制台支持并发连接，则无需使用不同端口。


使用其他端口
         如果端口被其它程序使用，你可能会想在其它端口开启H2服务。此时可以通过改变.h2.server.properties中的端口设置来实现。
        该文件被保存在用户目录下（对于Windows 通常是Documents and Settings/<username>）。对应的管理配置项是webPort。


启动成功
如果服务在控制台中启动成功，将会打开一个新的窗口并显示如下：
H2 Server running on port 9092
Webserver running on https://localhost:8082/
不要在此窗口内操作，否则你可能会停止掉H2服务（如果你开启了快速编辑模式）。


借助浏览器连接服务器
          如果H2服务器成功启动的话 你能够使用一个web浏览器来连接H2。 浏览器必须支持javascript、frames和样式层叠表（css）。
         如果你是在本地启动服务， 在浏览器中输入http://localhost:8082。 如果你是从另外一台计算机连接启动服务，你必须提供 H2服务器的ip地址，例如：http://192.168.0.2:8082. 如果你打算在服务器端启用ssl， 浏览器的URL地址必须以https开头。


多个并发的sessions
         h2Database支持多个并发的浏览器session。 因为数据库对象位于服务器， 所以连接的数目受服务器端内存大小的限制。


程序配置
         启动服务时将在本地目录下创建一个配置文件（.h2.server.properties）。 对于windows来说， 该文件位于
c：Document and Settings/[username]。 此文件中包含应用程序的配置信息。


登录
             在登录页面，你必须提供数据库的连接信息。包括设置你的数据库的JDBC驱动、 JDBC URL、 用户名和密码。 如果你做完上面步骤，点击[connect]
你可以保存并重新使用以前的连接信息， 这些信息被自动保存在应用程序的properties文件中。


错误信息
             错误信息以红色字来显示。 点击该信息可以显示或隐藏相应的异常链。


添加数据库驱动
             通过将相应驱动的jar文件地址添加进环境变量H2DRIVERS 或CLASSPATH， 可以为H2数据库注册其它的驱动。 例如：（Window） 为了添加数据库驱动包C:\Programs\hsqldb\lib\hsqldb.jar，
        你可以将环境变量的H2DRIVERS 设置成C:\Programs\hsqldb\lib\hsqldb.jar。
              H2支持多种驱动，驱动之间使用“；”（windows）或“：”（其他操作系统）来分割。另外，描述驱动的路径中支持空格， 路径加引用。


使用程序
          程序包含3个面板， 顶部的工具条、 左边的树和右边的查询/结果面板。 数据库实体（例如: tables）在左边的树结构中显示。在查询面板中输入SQL命令
并点击“Run”。命令的执行结果将紧跟在命令下面。


添加数据表名和列名
          通过单击左边的相应树状结构你可以将表名或列名字段插入到查询面板中。 在查询面板中查询语句为空时，如果你单击一个表，‘SELECT * FROM ...’将被添加进去。当在查询面板中输入一个查询语句后，
被使用的表将自动展开在左边的树中。例如： 如果你输入SELECT * FROM TEST T WHERE T， 表TEST将在左边的树中被自动展开。


断开连接和停止服务
         在浏览器中， 点击工具面板上的'Disconnect'断开连接。  但是，此时H2服务器仍然在运行并且可以接受新的session。
          为了停止服务， 你可以通过点击顶部工具面板中的【exit】图标。 如果该图标不存在，（因为你可能是通过其他方式启动的服务）， 你可以在启动该H2服务的控制台窗口中按Ctrl+C来停止H2服务， 或直接关闭该控制台窗口来停止H2服务。


使用JDBC连接到数据库
             为了连接到数据库， 一个java程序首先需要加载数据库驱动，然后获得一个连接。完成上述操作的一个简单方法是使用如下的代码：

Java代码  

	import java.sql.*;
	public class Test {
 		 public static void main(String[] a)
 		 throws Exception {
  	 	 Class.forName("org.h2.Driver");
  	 	 Connection conn = DriverManager.
     	 getConnection("jdbc:h2:~/test", "sa", "");
    	 // add application code here
 		 }
	}

          该代码首先加载驱动（Class.forName()）然后打开一个连接（使用DriverManager.getConnection()）。在所有的场合下，驱动的名字都是'org.h2.Driver'。
           为了能使被该数据库验证通过，数据库的URL通常以jdbc:h2:开头。getConnection()中的第二个参数是用户名（在本例中'sa'是数据库管理员）；第三个参数是密码。
注意： 在本数据库中用户名是不区分大小写的， 密码区分大小写。


新建数据库
         默认情况下，如果URL中指定的数据库不存在将自动创建。创建数据库的用户将成为该数据库的管理员。


使用服务器
          H2目前支持3种服务模式: web服务、TCP服务及ODBC服务模式。这些服务模式以不同的方式启动。

从命令行启动方式
命令行启动将加载默认配置， 运行
java org.h2.tools.Server
将加载默认配置。 为了得到配置选项列表和值， 可以运行
java org.h2.tools.Server -?
通过这些选项可以开启或停止H2服务器部分功能。 为了获得更详细的信息， 可以参考服务器的Api文档。

连接到TCP启动方式
为了借助TCP服务连接远程数据库，需使用如下驱动和数据库URL
JDBC driver class: org.h2.Driver
Database URL: jdbc:h2:tcp://localhost/~/test
关于数据库URL更详细描述，可以参考具体文档。

程序内部启动服务器方式
可以在应用程序内部启动或关闭服务。 例子代码：

Java代码  

import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

从其他进程中关闭一个TCP服务
       TCP服务方式可以从其他进程中关闭， 从命令行中关闭服务器。 运行：
java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
从应用程序中TCP服务，可以运行如下代码：
org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9094");
这种方法将在服务器端调用System.exit。此方法调用的前提是所有的数据库连接已被关闭，以便数据库在下一次被打开时能正确运行。为了关闭远程服务器，应该确保服务器上的远程连接有效。

 

h2导入数据脚本csv

1.启动H2数据库不打开浏览器窗口（默认是打开的）  

2.数据库创建SQL增加了支持BigDecimal类型，h2数据库默认是不支持bigdecimal类型的：

Sql代码

create table test(id int(11),charge BigDecimal(12))  

 

3.通过传参数方式导入数据库脚本

new Console().runTool();   

new RunScript().runCustomTool("jdbc:h2:~/test", "sa","","c:/schema.sql"); 

 

 

H2 数据库导入CSV文件

insert into prescription ( select * from csvread('c:/JV/Demo/prescription2012-05-10 22-26-37.csv')) ;

 

 

 

h2支持本机以外的地址访问

H2 Console
   Sorry, remote connections ('webAllowOthers') are disabled on this server.

Window Console

Starts the H2 Console (web-) server, as well as the TCP and PG server.

Methods

static void

main(String... args)

When running without options, -tcp, -web, -browser and -pg are started.

static void

main(String... args) throws SQLException

When running without options, -tcp, -web, -browser and -pg are started.
Options are case sensitive. Supported options are:

[-help] or [-?]	Print the list of options
[-url]	Start a browser and connect to this URL
[-driver]	Used together with -url: the driver
[-user]	Used together with -url: the user name
[-password]	Used together with -url: the password
[-web]	Start the web server with the H2 Console
[-tool]	Start the icon or window that allows to start a browser
[-browser]	Start a browser connecting to the web server
[-tcp]	Start the TCP server
[-pg]	Start the PG server

For each Server, additional options are available; for details, see the Server tool.
If a service can not be started, the program terminates with an exit code of 1.

Parameters:

args - the command line arguments

 

设置端口
   ./h2.sh -webAllowOthers -webPort 8082 -tcpAllowOthers -tcpPort 8083 &

指定了以本机IP外，其他IP可以浏览器访问，地址：http://192.168.167.21:8082

 

 

 

 

 

 

H2控制台的配置：

      官方说： h2控制台配置文件.h2.server.properties 在用户的主目录.例如window安装用户主目录如下 C:\Documents and Settings\[username]. 配置包含应用的各种设置和h2启动自动调用参数信息。

参数信息如下:

• webAllowOthers: 允许其它电脑连接.
• webPort: h2控制台的端口。
• webSSL: use encrypted (HTTPS) connections.

额外的信息如：

上次登录的连接信息格式：

          <number>=<name>|<driver>|<url>|<user>

例如: 1=Generic H2 (Embedded)|org.h2.Driver|jdbc\:h2\:~/test|sa

 

 

使用JDBC连接H2数据库:

import java.sql.*;
public class Test {
    public static void main(String[] a)
            throws Exception {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.
            getConnection("jdbc:h2:~/test", "sa", "");
        // add application code here
        conn.close();
    }
}

Using the Server

H2 currently supports three server: a web server (for the H2 Console), a TCP server (for client/server connections) and an PG server (for PostgreSQL clients). Please note that only the web server supports browser connections. The servers can be started in different ways, one is using the Server tool. Starting the server doesn't open a database - databases are opened as soon as a client connects.

Starting the Server Tool from Command Line

使用Server 运行程序

java -cp h2*.jar org.h2.tools.Server

 

查看Server运行时需要的参数可以:

java -cp h2*.jar org.h2.tools.Server -?

 

 

  远程连接使用TCP协议的驱动和连接URL如下:

• JDBC driver class: org.h2.Driver
• Database URL: jdbc:h2:tcp://localhost/~/test

 

在应用启动和停止H2的tcp 服务

import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

 

 

停止TCP Server的方式

 命令行:

java org.h2.tools.Server -tcpShutdown tcp://localhost:9092

编码方式:

org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9094");

 

在Web App中使用h2数据库

内嵌模式

1.将数据库放在服务器（tomcat,jboss等）的共享lib目录，server lib目录或者classpath中使用Servlet Listener启动

Server Mode

The server mode is similar, but it allows you to run the server in another process.

3.使用Servlet Listener方式

添加 h2*.jar 在web应用程序中,同时在 web.xml file (between the context-param and the filter section)添加如下配置。

<listener>
    <listener-class>org.h2.server.web.DbStarter</listener-class>
</listener>

 

详细访问数据库可以参考H2中的 DbStarter.java.

 

默认的内嵌连接的连接URL：jdbc:h2:~/test,

用户名：sa

密码 sa

在Servelt中使用h2的Connection如下方式:

Connection conn = getServletContext().getAttribute("connection");

DbStarter 可以启动TCP server,但是默认是禁用的.如果想启动需要在web.xml中添加参数 db.tcpServer 配置.

 

配置如下：

<context-param>
    <param-name>db.url</param-name>
    <param-value>jdbc:h2:~/test</param-value>
</context-param>
<context-param>
    <param-name>db.user</param-name>
    <param-value>sa</param-value>
</context-param>
<context-param>
    <param-name>db.password</param-name>
    <param-value>sa</param-value>
</context-param>
<context-param>
    <param-name>db.tcpServer</param-name>
    <param-value>-tcpAllowOthers</param-value>
</context-param>

  当web app停止时候，h2数据库连接将自动关闭。如果启动使用DbStarter，数据库将自动停止。

H2控制台Servlet的使用

 虽然H2 Console 是个独立的应用，包括自己的web 服务但是它可以作为Servlet使用.如果想作为Servlet使用，应用中需要有h2*.jar 文件, 并在web.xml添加如下配置:

<servlet>
    <servlet-name>H2Console</servlet-name>
    <servlet-class>org.h2.server.web.WebServlet</servlet-class>
    <!--
    <init-param>
        <param-name>webAllowOthers</param-name>
        <param-value></param-value>
    </init-param>
    <init-param>
        <param-name>trace</param-name>
        <param-value></param-value>
    </init-param>
    -->
    <load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
    <servlet-name>H2Console</servlet-name>
    <url-pattern>/console/*</url-pattern>
</servlet-mapping>

详细可以参考 src/tools/WEB-INF/web.xml.

 

如果想为H2创建一个控制台应用，命令如下:

build warConsole

CSV格式的支持

   在数据库中CSV格式可以被使用，通过使用CSVREAD，CSVWRITE或者外边独立的工具。

使用数据库读取CSV文件 CSVREAD使用:

SELECT * FROM CSVREAD('test.csv');

 

Please note for performance reason, CSVREAD should not be used inside a join. Instead, import the data first (possibly into a temporary table), create the required indexes if necessary, and then query this table.

 

导出文件到CSV格式的文件中

  Another option is to use INSERT INTO ... SELECT.

CREATE TABLE TEST AS SELECT * FROM CSVREAD('test.csv');
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
    AS SELECT * FROM CSVREAD('test.csv');

在h2数据库中生成一个CSV文件

CREATE TABLE TEST(ID INT, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World');
CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');

在应用app写CSV格式的文件使用CSV工具:

import java.sql.*;
import org.h2.tools.Csv;
import org.h2.tools.SimpleResultSet;
public class TestCsv {
    public static void main(String[] args) throws Exception {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("NAME", Types.VARCHAR, 255, 0);
        rs.addColumn("EMAIL", Types.VARCHAR, 255, 0);
        rs.addRow("Bob Meier", "[e-mail]");
        rs.addRow("John Jones", "[e-mail]");
        new Csv().write("data/test.csv", rs, null);
    }
}

读取CSV格式的文件在Java应用中

import java.sql.*;
import org.h2.tools.Csv;
public class TestCsv {
    public static void main(String[] args) throws Exception {
        ResultSet rs = new Csv().read("data/test.csv", null, null);
        ResultSetMetaData meta = rs.getMetaData();
        while (rs.next()) {
            for (int i = 0; i < meta.getColumnCount(); i++) {
                System.out.println(
                    meta.getColumnLabel(i + 1) + ": " +
                    rs.getString(i + 1));
            }
            System.out.println();
        }
        rs.close();
    }
}

H2的升级，备份，恢复

数据库升级

1.备份h2数据库

2.升级h2数据库引擎

3.在升级之后的引擎中执行备份的SQL.

使用Script Tool备份

java org.h2.tools.Script -url jdbc:h2:~/test -user sa -script test.zip -options compression zip

从一个脚本中恢复数据库

使用RunScript 工具运行脚本恢复:

java org.h2.tools.RunScript -url jdbc:h2:~/test -user sa -script test.zip -options compression zip

在线备份(org.h2.tools.Backup)H2

BACKUP TO 'backup.zip'

命令行工具:

java -cp h2*.jar org.h2.tools.Backup -?

The command line tools are:

• Backup creates a backup of a database.
• ChangeFileEncryption allows changing the file encryption password or algorithm of a database.
• Console starts the browser based H2 Console.
• ConvertTraceFile converts a .trace.db file to a Java application and SQL script.
• CreateCluster creates a cluster from a standalone database.
• DeleteDbFiles deletes all files belonging to a database.
• Recover helps recovering a corrupted database.
• Restore restores a backup of a database.
• RunScript runs a SQL script against a database.
• Script allows converting a database to a SQL script for backup or migration.
• Server is used in the server mode to start a H2 server.
• Shell is a command line database tool.

The tools can also be called from an application by calling the main or another public method. For details, see the Javadoc documentation.

H2数据库连接池的使用

使用如下:

import java.sql.*;
import org.h2.jdbcx.JdbcConnectionPool;
public class Test {
    public static void main(String[] args) throws Exception {
        JdbcConnectionPool cp = JdbcConnectionPool.create(
            "jdbc:h2:~/test", "sa", "sa");
        for (int i = 0; i < args.length; i++) {
            Connection conn = cp.getConnection();
            conn.createStatement().execute(args[i]);
            conn.close();
        }
        cp.dispose();
    }
}

 

 Spring中h2 TCP Server 配置

<bean id = "org.h2.tools.Server"
            class="org.h2.tools.Server"
            factory-method="createTcpServer"
            init-method="start"
            destroy-method="stop">
    <constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,8043" />
</bean>