> For the complete documentation index, see [llms.txt](https://hezhiqiang.gitbook.io/about-the-author/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang.gitbook.io/about-the-author/zhong-jian-jian-jiao-cheng-xue-xi/h2-kuai-su-zhi-nan.md).

# H2 教程

## 概述

H2 是一个开源的嵌入式数据库引擎，采用 java 语言编写，不受平台的限制。同时 H2 提供了一个十分方便的 web 控制台用于操作和管理数据库内容。H2 还提供兼容模式，可以兼容一些主流的数据库，因此采用 H2 作为开发期的数据库非常方便。

## 使用说明

### H2 控制台应用

H2 允许用户通过浏览器接口方式访问 SQL 数据库。

1. 进入[官方下载地址](http://www.h2database.com/html/download.html)，选择合适版本，下载并安装到本地。
2. 启动方式：在 bin 目录下，双击 jar 包；执行 `java -jar h2*.jar`；执行脚本：`h2.bat` 或 `h2.sh`。
3. 在浏览器中访问：[http://localhost:8082，应该可以看到下图中的页面：](http://localhost:8082/%EF%BC%8C%E5%BA%94%E8%AF%A5%E5%8F%AF%E4%BB%A5%E7%9C%8B%E5%88%B0%E4%B8%8B%E5%9B%BE%E4%B8%AD%E7%9A%84%E9%A1%B5%E9%9D%A2%EF%BC%9A/)

\
[![](https://camo.githubusercontent.com/fa3fb89a47bf59a6b6d7270cce4a2a0c7f772a9a/687474703a2f2f64756e77752e746573742e757063646e2e6e65742f63732f64617461626173652f68322f68322d636f6e736f6c652e706e67217a70)](https://camo.githubusercontent.com/fa3fb89a47bf59a6b6d7270cce4a2a0c7f772a9a/687474703a2f2f64756e77752e746573742e757063646e2e6e65742f63732f64617461626173652f68322f68322d636f6e736f6c652e706e67217a70)<br>

点击 **Connect** ，可以进入操作界面：

\
[![](https://camo.githubusercontent.com/1ae39f74a9967c7e22c97d3f6c77ced77ee24bb1/687474703a2f2f64756e77752e746573742e757063646e2e6e65742f63732f64617461626173652f68322f68322d636f6e736f6c652d30322e706e67217a70)](https://camo.githubusercontent.com/1ae39f74a9967c7e22c97d3f6c77ced77ee24bb1/687474703a2f2f64756e77752e746573742e757063646e2e6e65742f63732f64617461626173652f68322f68322d636f6e736f6c652d30322e706e67217a70)<br>

操作界面十分简单，不一一细说。

### 嵌入式应用

**JDBC API**

```
Connection conn = DriverManager.
    getConnection("jdbc:h2:~/test");
conn.close();
```

> 详见：[Using the JDBC API](http://www.h2database.com/html/tutorial.html#connecting_using_jdbc)

**连接池**

```
import org.h2.jdbcx.JdbcConnectionPool;
JdbcConnectionPool cp = JdbcConnectionPool.
create("jdbc:h2:~/test", "sa", "sa");
Connection conn = cp.getConnection();
conn.close(); cp.dispose();
```

> 详见：[Connection Pool](http://www.h2database.com/html/tutorial.html#connection_pool)

**Maven**

```
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.197</version>
</dependency>
```

> 详见：[Maven 2](http://www.h2database.com/html/build.html#maven2)

**Hibernate**

hibernate.cfg.xml (or use the HSQLDialect):

```
<property name="dialect">
    org.hibernate.dialect.H2Dialect
</property>
```

> 详见：[Hibernate](http://www.h2database.com/html/tutorial.html#using_hibernate)

**TopLink 和 Glassfish**

Datasource class: `org.h2.jdbcx.JdbcDataSource` `oracle.toplink.essentials.platform.database.H2Platform`

> 详见：[TopLink and Glassfish](http://www.h2database.com/html/tutorial.html#using_toplink)

### 运行方式

**嵌入式**

数据库持久化存储为单个文件。

连接字符串：`\~/.h2/DBName` 表示数据库文件的存储位置，如果第一次连接则会自动创建数据库。

* `jdbc:h2:\~/test` - 'test' 在用户根目录下
* `jdbc:h2:/data/test` - 'test' 在 /data 目录下
* `jdbc:h2:test` - 'test' 在当前工作目录

**内存式**

数据库只在内存中运行，关闭连接后数据库将被清空，适合测试环境

连接字符串：`jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1`

如果不指定 DBName，则以私有方式启动，只允许一个连接。

* `jdbc:h2:mem:test` - 一个进程中有多个连接
* `jdbc:h2:mem:` - 未命名的私有库，一个连接

**服务模式**

H2 支持三种服务模式：

* web server：此种运行方式支持使用浏览器访问 H2 Console
* TCP server：支持客户端/服务器端的连接方式
* PG server：支持 PostgreSQL 客户端

启动 tcp 服务连接字符串示例：

* `jdbc:h2:tcp://localhost/\~/test` - 用户根目录
* `jdbc:h2:tcp://localhost//data/test` - 绝对路径

**启动服务**

执行 `java -cp *.jar org.h2.tools.Server`

执行如下命令，获取选项列表及默认值

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

常见的选项如下：

* -web：启动支持 H2 Console 的服务
* -webPort ：服务启动端口，默认为 8082
* -browser：启动 H2 Console web 管理页面
* -tcp：使用 TCP server 模式启动
* -pg：使用 PG server 模式启动

**设置**

* `jdbc:h2:..;MODE=MySQL` 兼容模式（或 HSQLDB 等）
* `jdbc:h2:..;TRACE_LEVEL_FILE=3` 记录到 `*.trace.db`

**连接字符串参数**

* `DB_CLOSE_DELAY` - 要求最后一个正在连接的连接断开后，不要关闭数据库
* `MODE=MySQL` - 兼容模式，H2 兼容多种数据库，该值可以为：DB2、Derby、HSQLDB、MSSQLServer、MySQL、Oracle、PostgreSQL
* `AUTO_RECONNECT=TRUE` - 连接丢失后自动重新连接
* `AUTO_SERVER=TRUE` - 启动自动混合模式，允许开启多个连接，该参数不支持在内存中运行模式
* `TRACE_LEVEL_SYSTEM_OUT`、`TRACE_LEVEL_FILE` - 输出跟踪日志到控制台或文件， 取值 0 为 OFF，1 为 ERROR（默认值），2 为 INFO，3 为 DEBUG
* `SET TRACE_MAX_FILE_SIZE mb` - 设置跟踪日志文件的大小，默认为 16M

**maven 方式**

此外，使用 maven 也可以启动 H2 服务。添加以下插件

```
<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>exec-maven-plugin</artifactId>
  <executions>
    <execution>
      <goals>
        <goal>java</goal>
      </goals>
    </execution>
  </executions>
  <configuration>
    <mainClass>org.h2.tools.Server</mainClass>
    <arguments>
      <argument>-web</argument>
      <argument>-webPort</argument>
      <argument>8090</argument>
      <argument>-browser</argument>
    </arguments>
  </configuration>
</plugin>
```

在命令行中执行如下命令启动 H2 Console

```
mvn exec:java
```

或者建立一个 bat 文件

```
@echo off
call mvn exec:java
pause
```

此操作相当于执行了如下命令：

```
java -jar h2-1.3.168.jar -web -webPort 8090 -browser
```

## Spring 整合 H2

1. 添加依赖

```
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.194</version>
</dependency>
```

1. spring 配置

```
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
            http://www.springframework.org/schema/jdbc
            http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

  <!--配置数据源-->
  <bean id="dataSource" class="org.h2.jdbcx.JdbcConnectionPool"
        destroy-method="dispose">
    <constructor-arg>
      <bean class="org.h2.jdbcx.JdbcDataSource">
        <!-- 内存模式 -->
        <property name="URL" value="jdbc:h2:mem:test"/>
        <!-- 文件模式 -->
        <!-- <property name="URL" value="jdbc:h2:testRestDB" /> -->
        <property name="user" value="root"/>
        <property name="password" value="root"/>
      </bean>
    </constructor-arg>
  </bean>

  <!-- JDBC模板 -->
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="dataSource"/>
  </bean>
  <bean id="myJdbcTemplate" class="org.zp.notes.spring.jdbc.MyJdbcTemplate">
    <property name="jdbcTemplate" ref="jdbcTemplate"/>
  </bean>

  <!-- 初始化数据表结构 -->
  <jdbc:initialize-database data-source="dataSource" ignore-failures="ALL">
    <jdbc:script location="classpath:sql/h2/create_table_student.sql"/>
  </jdbc:initialize-database>
</beans>
```

## H2 SQL

### SELECT

\
[![](https://camo.githubusercontent.com/dd1bb6d7e8de3127b3ffc66b7e9d90f5e7dc0425/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613366393063306431663166333433372e706e67)](https://camo.githubusercontent.com/dd1bb6d7e8de3127b3ffc66b7e9d90f5e7dc0425/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613366393063306431663166333433372e706e67)<br>

### INSERT

\
[![](https://camo.githubusercontent.com/828b949f2e9dd1b610d54e5c85691b85adab35ae/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d366139326165343336326333343638612e706e67)](https://camo.githubusercontent.com/828b949f2e9dd1b610d54e5c85691b85adab35ae/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d366139326165343336326333343638612e706e67)<br>

### UPDATE

\
[![](https://camo.githubusercontent.com/694dcc97923a11f84c9840cd05d57ab267e056e3/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d646464663065323639393564343663332e706e67)](https://camo.githubusercontent.com/694dcc97923a11f84c9840cd05d57ab267e056e3/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d646464663065323639393564343663332e706e67)<br>

### DELETE

\
[![](https://camo.githubusercontent.com/ddfa568dc01b7aab80207325ab5757ef6b30ec6b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d393665373230323334343561366664362e706e67)](https://camo.githubusercontent.com/ddfa568dc01b7aab80207325ab5757ef6b30ec6b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d393665373230323334343561366664362e706e67)<br>

### BACKUP

\
[![](https://camo.githubusercontent.com/9ba5c4833bde3c92361cbf8c0aaafe645d9d1fb0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d363236373839346432346661623437662e706e67)](https://camo.githubusercontent.com/9ba5c4833bde3c92361cbf8c0aaafe645d9d1fb0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d363236373839346432346661623437662e706e67)<br>

### EXPLAIN

\
[![](https://camo.githubusercontent.com/57108af181713259afea454ade9677facf58a248/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d626265643662623639663939386237612e706e67)](https://camo.githubusercontent.com/57108af181713259afea454ade9677facf58a248/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d626265643662623639663939386237612e706e67)<br>

7、MERGE\
[![](https://camo.githubusercontent.com/03f3308e8d9ac4efe7c560f3da52bc47912f1162/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d626430323136343834333164313261372e706e67)](https://camo.githubusercontent.com/03f3308e8d9ac4efe7c560f3da52bc47912f1162/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d626430323136343834333164313261372e706e67)<br>

### RUNSCRIPT

运行 sql 脚本文件

\
[![](https://camo.githubusercontent.com/fd91ee90f0c24e7eaa2e161d5fab71da3b4497ec/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d643666653033656666303033376531342e706e67)](https://camo.githubusercontent.com/fd91ee90f0c24e7eaa2e161d5fab71da3b4497ec/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d643666653033656666303033376531342e706e67)<br>

#### SCRIPT

根据数据库创建 sql 脚本

\
[![](https://camo.githubusercontent.com/017ef1f8111794d8a3d2879293b1ea6bd22a68fa/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d396261373534376162386263616561622e706e67)](https://camo.githubusercontent.com/017ef1f8111794d8a3d2879293b1ea6bd22a68fa/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d396261373534376162386263616561622e706e67)<br>

#### SHOW

\
[![](https://camo.githubusercontent.com/f7a8c693ce0a39f4d685d38d524fac0a47ce7fc9/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d363734343963366363356362623863312e706e67)](https://camo.githubusercontent.com/f7a8c693ce0a39f4d685d38d524fac0a47ce7fc9/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d363734343963366363356362623863312e706e67)<br>

#### ALTER

**ALTER INDEX RENAME**

\
[![](https://camo.githubusercontent.com/bc2d3b29a71c4e6472758209e243d1abf8bf9496/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d323330626433663937653138356432662e706e67)](https://camo.githubusercontent.com/bc2d3b29a71c4e6472758209e243d1abf8bf9496/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d323330626433663937653138356432662e706e67)<br>

**ALTER SCHEMA RENAME**

\
[![](https://camo.githubusercontent.com/9856f9fd1e85aefb5b87cae23b33c96773e19bb6/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d373937613032383933386534366261332e706e67)](https://camo.githubusercontent.com/9856f9fd1e85aefb5b87cae23b33c96773e19bb6/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d373937613032383933386534366261332e706e67)<br>

**ALTER SEQUENCE**

\
[![](https://camo.githubusercontent.com/d24cd17b55d9712bbeef3edcbceba74ba9992846/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343666333433646131623663366132392e706e67)](https://camo.githubusercontent.com/d24cd17b55d9712bbeef3edcbceba74ba9992846/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343666333433646131623663366132392e706e67)<br>

**ALTER TABLE**

\
[![](https://camo.githubusercontent.com/e03e80ef8e2d956072d0ab2ccf60e35387ab978a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d376531343661343031306632663335372e706e67)](https://camo.githubusercontent.com/e03e80ef8e2d956072d0ab2ccf60e35387ab978a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d376531343661343031306632663335372e706e67)<br>

**增加约束**

\
[![](https://camo.githubusercontent.com/042efac3c29b18d7d72a8d0fc48d72ac9172908b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d346535363035613963383761373963622e706e67)](https://camo.githubusercontent.com/042efac3c29b18d7d72a8d0fc48d72ac9172908b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d346535363035613963383761373963622e706e67)<br>

**修改列**

\
[![](https://camo.githubusercontent.com/4ae2c4bf5243e2b348458320fdd9d1d111a58412/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666263313335386335353365363631342e706e67)](https://camo.githubusercontent.com/4ae2c4bf5243e2b348458320fdd9d1d111a58412/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666263313335386335353365363631342e706e67)<br>

**删除列**

\
[![](https://camo.githubusercontent.com/9744abf1ed4d34b094feb445070a23ac596db07a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d646333623839373431333730303938312e706e67)](https://camo.githubusercontent.com/9744abf1ed4d34b094feb445070a23ac596db07a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d646333623839373431333730303938312e706e67)<br>

**删除序列**

\
[![](https://camo.githubusercontent.com/f8dd36cd14d4328812d4725fcb5210e24a0bdaf0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d656338333839396362383732343936362e706e67)](https://camo.githubusercontent.com/f8dd36cd14d4328812d4725fcb5210e24a0bdaf0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d656338333839396362383732343936362e706e67)<br>

**ALTER USER**

**修改用户名**

\
[![](https://camo.githubusercontent.com/a5bd4d75f8d8271b64fd24bd151852c7bb9452f8/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613165343239633064386563653636632e706e67)](https://camo.githubusercontent.com/a5bd4d75f8d8271b64fd24bd151852c7bb9452f8/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613165343239633064386563653636632e706e67)<br>

**修改用户密码**

\
[![](https://camo.githubusercontent.com/8820917114f84782287188ca86c347b6cff5645a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d356238366639383739363630366535342e706e67)](https://camo.githubusercontent.com/8820917114f84782287188ca86c347b6cff5645a/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d356238366639383739363630366535342e706e67)<br>

**ALTER VIEW**

\
[![](https://camo.githubusercontent.com/7324fccaf9acbda4b1deaadb0a49bb6c0ae9b56c/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d383833326563626332646236336131332e706e67)](https://camo.githubusercontent.com/7324fccaf9acbda4b1deaadb0a49bb6c0ae9b56c/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d383833326563626332646236336131332e706e67)<br>

#### COMMENT

\
[![](https://camo.githubusercontent.com/cfae020b2fcd2ffa544c69b8d284dc56872babad/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343637636530333138383366303032302e706e67)](https://camo.githubusercontent.com/cfae020b2fcd2ffa544c69b8d284dc56872babad/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343637636530333138383366303032302e706e67)<br>

#### CREATE CONSTANT

\
[![](https://camo.githubusercontent.com/9b8ead273463589192d1ebc0d2079893f7d0788f/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d313233316338333536336266656339632e706e67)](https://camo.githubusercontent.com/9b8ead273463589192d1ebc0d2079893f7d0788f/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d313233316338333536336266656339632e706e67)<br>

#### CREATE INDEX

\
[![](https://camo.githubusercontent.com/08348b1eeba63c0e83806c783d1254b711e4b59d/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d643636643539626437383033643563312e706e67)](https://camo.githubusercontent.com/08348b1eeba63c0e83806c783d1254b711e4b59d/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d643636643539626437383033643563312e706e67)<br>

#### CREATE ROLE

\
[![](https://camo.githubusercontent.com/99fd796e9faafa6abf69d7bc3392cb0992707a27/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d376466316465653039386531313237622e706e67)](https://camo.githubusercontent.com/99fd796e9faafa6abf69d7bc3392cb0992707a27/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d376466316465653039386531313237622e706e67)<br>

#### CREATE SCHEMA

\
[![](https://camo.githubusercontent.com/02fa15d4fa9c8de4da3ed921f9259fa7de42ddb6/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d633438353132336336326330383636652e706e67)](https://camo.githubusercontent.com/02fa15d4fa9c8de4da3ed921f9259fa7de42ddb6/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d633438353132336336326330383636652e706e67)<br>

#### CREATE SEQUENCE

\
[![](https://camo.githubusercontent.com/e23da9fc6f02255cc496ef9070a810b79d28094e/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d636332353836303737366433363161652e706e67)](https://camo.githubusercontent.com/e23da9fc6f02255cc496ef9070a810b79d28094e/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d636332353836303737366433363161652e706e67)<br>

#### CREATE TABLE

\
[![](https://camo.githubusercontent.com/0ccd4a2f4318f5769cfd05ac8f15e4ea0d019383/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d333666666336363332376466386235622e706e67)](https://camo.githubusercontent.com/0ccd4a2f4318f5769cfd05ac8f15e4ea0d019383/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d333666666336363332376466386235622e706e67)<br>

#### CREATE TRIGGER

\
[![](https://camo.githubusercontent.com/a3b1b89bc99222a8fb9f6bd8d9219ff7d9058579/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d396137626661343432353238313231332e706e67)](https://camo.githubusercontent.com/a3b1b89bc99222a8fb9f6bd8d9219ff7d9058579/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d396137626661343432353238313231332e706e67)<br>

#### CREATE USER

\
[![](https://camo.githubusercontent.com/a4f6e29bc14208e069953b41bde5966b381d5df3/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613165343565333038626536646163332e706e67)](https://camo.githubusercontent.com/a4f6e29bc14208e069953b41bde5966b381d5df3/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d613165343565333038626536646163332e706e67)<br>

#### CREATE VIEW

\
[![](https://camo.githubusercontent.com/59ff6488fa448670698943c996ea7477967e79e7/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343563346364353136666433363631312e706e67)](https://camo.githubusercontent.com/59ff6488fa448670698943c996ea7477967e79e7/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d343563346364353136666433363631312e706e67)<br>

#### DROP

\
[![](https://camo.githubusercontent.com/1749f823eecbbcb66e749821db681ad1d512fb2d/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d353261333536326437363431313831312e6a7067)](https://camo.githubusercontent.com/1749f823eecbbcb66e749821db681ad1d512fb2d/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d353261333536326437363431313831312e6a7067)<br>

#### GRANT RIGHT

给 schema 授权授权

\
[![](https://camo.githubusercontent.com/271bb626ede8134134c7d4d1ec94c7bd95becaa0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d373530653936636566663030633465652e706e67)](https://camo.githubusercontent.com/271bb626ede8134134c7d4d1ec94c7bd95becaa0/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d373530653936636566663030633465652e706e67)<br>

给 schema 授权给 schema 授权

\
[![](https://camo.githubusercontent.com/4aa7f45c6c8112a5fca4f77b3127f960fff5fd42/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d323263666436356332666631656561352e706e67)](https://camo.githubusercontent.com/4aa7f45c6c8112a5fca4f77b3127f960fff5fd42/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d323263666436356332666631656561352e706e67)<br>

**复制角色的权限**

\
[![](https://camo.githubusercontent.com/952d65c2a0b77a16dfcc734e56b3e6d6df4c1811/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d366362613266313538356664393133622e706e67)](https://camo.githubusercontent.com/952d65c2a0b77a16dfcc734e56b3e6d6df4c1811/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d366362613266313538356664393133622e706e67)<br>

#### REVOKE RIGHT

**移除授权**

\
[![](https://camo.githubusercontent.com/ee7deeacf00e28c4cb797af9d780fe6ce3ca6b05/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d336639303536363963626233333162372e706e67)](https://camo.githubusercontent.com/ee7deeacf00e28c4cb797af9d780fe6ce3ca6b05/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d336639303536363963626233333162372e706e67)<br>

**移除角色具有的权限**

\
[![](https://camo.githubusercontent.com/5ffbfa000529bc1103969b8ab68e818da7385e6f/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d616637376634393532323266316233302e706e67)](https://camo.githubusercontent.com/5ffbfa000529bc1103969b8ab68e818da7385e6f/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d616637376634393532323266316233302e706e67)<br>

#### ROLLBACK

**从某个还原点（savepoint）回滚**

\
[![](https://camo.githubusercontent.com/e77a7a7e0c4ed3cf8bf91f28262d87fa0b11bdde/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d633731613232366163346666663931332e706e67)](https://camo.githubusercontent.com/e77a7a7e0c4ed3cf8bf91f28262d87fa0b11bdde/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d633731613232366163346666663931332e706e67)<br>

**回滚事务**

\
[![](https://camo.githubusercontent.com/edd16cf3f8957afc57ab1da305a3366e6a01afb9/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d656662363563353034633764363963322e706e67)](https://camo.githubusercontent.com/edd16cf3f8957afc57ab1da305a3366e6a01afb9/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d656662363563353034633764363963322e706e67)<br>

**创建 savepoint**

\
[![](https://camo.githubusercontent.com/31b725939e52c0b31b3775fd49f01b02ba54333e/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666565666463323336643462323131642e706e67)](https://camo.githubusercontent.com/31b725939e52c0b31b3775fd49f01b02ba54333e/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666565666463323336643462323131642e706e67)<br>

## 数据类型

\
[![](https://camo.githubusercontent.com/bcb92817648544e46a0f297cf90873f18bfe436b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d353232393664643533323439636461652e706e67)](https://camo.githubusercontent.com/bcb92817648544e46a0f297cf90873f18bfe436b/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d353232393664643533323439636461652e706e67)<br>

#### INT Type

\
[![](https://camo.githubusercontent.com/ecf476a50b7a1deee0893aa821a8fc0b75c8f201/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666536326533643037656239336431312e706e67)](https://camo.githubusercontent.com/ecf476a50b7a1deee0893aa821a8fc0b75c8f201/687474703a2f2f75706c6f61642d696d616765732e6a69616e7368752e696f2f75706c6f61645f696d616765732f333130313137312d666536326533643037656239336431312e706e67)<br>

## 集群

H2 支持两台服务器运行两个数据库成为集群，两个数据库互为备份，如果一个服务器失效，另一个服务器仍然可以工作。另外只有服务模式支持集群配置。

H2 可以通过 CreateCluster 工具创建集群，示例步骤如下（在在一台服务器上模拟两个数据库组成集群）：

* 创建目录
  * 创建两个服务器工作的目录
* 启动 tcp 服务
  * 执行如下命令分别在 9101、9102 端口启动两个使用 tcp 服务模式的数据库
* 使用 CreateCluster 工具创建集群
  * 如果两个数据库不存在，该命令将会自动创建数据库。如果一个数据库失效，可以先删除坏的数据库文件，重新启动数据库，然后重新运行 CreateCluster 工具
* 连接数据库现在可以使用如下连接字符串连接集群数据库
  * 监控集群**运行状态**
  * 可以使用如下命令查看配置的集群服务器是否都在运行
* 限制
  * H2 的集群并不支持针对事务的负载均衡，所以很多操作会使两个数据库产生不一致的结果
* 执行如下操作时请小心：
  * 自动增长列和标识列不支持集群，当插入数据时，序列值需要手动创建不支持 SET AUTOCOMMIT FALSE 语句；
  * 如果需要设置成为不自动提交，可以执行方法 Connection.setAutoCommit(false)

## 参考资料

* [h2database 官网](http://www.h2database.com/html/main.html)
* [Java 嵌入式数据库 H2 学习总结(一)——H2 数据库入门](https://www.cnblogs.com/xdp-gacl/p/4171024.html)