登录 注册

H2 数据库使用简要说明（此博客使用 H2 数据库）

本贴最后更新于 2509 天前，其中的信息可能已经水流花落

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

一、引入 Maven 依赖

在 maven 中定义 H2 数据库的版本属性

  <properties>
  <h2.version>1.3.172</h2.version>
  </properties>

添加 H2 依赖

  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>${h2.version}</version>
  <scope>test</scope>
  </dependency>

二、运行方式

1、在内存中运行

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

  jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1

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

2、嵌入式

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

连接字符串：

jdbc:h2:file:~/.h2/DBName;AUTO_SERVER=TRUE

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

3、服务模式

H2 支持三种服务模式：

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

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

  jdbc:h2:tcp://localhost/~/test 使用用户主目录
  jdbc:h2:tcp://localhost//data/test 使用绝对路径

4、连接字符串参数

  • 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

5、启动服务模式，打开 H2 Console web 页面

启动服务，在命令行中执行

  java -cp h2*.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 模式启动

此外，使用 maven 也可以启动 H2 服务

   <version>${h2.version}</version>
   <scope>runtime</scope>
   </dependency>
   </dependencies>
   <build>
   <plugins>
   <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>
   </plugins>
   </build>
   </project>

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

mvn exec:java

或者建立一个 bat 文件

  @echo off
  call mvn exec:java
  pause

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

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

三、应用程序配置

1、Properties 配置

java 应用程序关于数据库的 Properties 配置文件示例如下：

  #h2 database settings
  jdbc.driver=org.h2.Driver
  jdbc.url=jdbc:h2:file:~/.h2/quickstart;AUTO_SERVER=TRUE;DB_CLOSE_DELAY=-1
  jdbc.username=sa
  jdbc.password=
  #connection pool settings
  jdbc.pool.maxIdle=5
  jdbc.pool.maxActive=40

2、初始化数据库

（1）、在 Maven 中初始化数据库

可以创建一个 Profile，专门用于初始化数据库。在 maven 中可以通过 maven-antrun-plugin 执行 ant 任务，在 ant 任务中使用 sql 标签可以执行 sql 脚本文件，配置示例如下：

   <profile>
   <id>refresh-db</id>
   <build>
   <plugins>
   <plugin>
   <groupId>org.apache.maven.plugins</groupId>
   <artifactId>maven-antrun-plugin</artifactId>
   <configuration>
   <target>
   <property file="src/main/resources/application.properties" />
   <sql driver="${jdbc.driver}" url="${jdbc.url}" userid="${jdbc.username}"  password="${jdbc.password}" onerror="continue" encoding="${project.build.sourceEncoding}">
   <classpath refid="maven.test.classpath" />
   <transaction src="src/main/resources/sql/h2/schema.sql"/>
   <transaction src="src/test/resources/data/h2/import-data.sql"/>
   </sql>
   </target>
   </configuration>
   </plugin>
   </plugins>
   </build>
   </profile>

执行如下命令调用该 Profile，初始化数据库

mvn antrun:run -Prefresh-db

（2）、在 Spring 中初始化数据库

Spring Profile 和 maven profile 一样，也可以模拟不同的开发环境。在 Spirng 中可以通过 jdbc:initialize-database 初始化数据库，配置示例如下

  <beans profile="test">
   <context:property-placeholder ignore-resource-not-found="true"
   location="classpath*:/application.properties,
   classpath*:/application.test.properties" />

<jdbc:initialize-database data-source="dataSource" ignore-failures="ALL">
<jdbc:script location="classpath:sql/h2/schema.sql" />
<jdbc:script location="classpath:data/h2/import-data.sql" encoding="UTF-8"/>
/jdbc:initialize-database

四、sql 语法图

1、SELECT

e2e9eb9e8b754a0ca2f9da648b036845-0.9038103049489206.png

2、INSERT

52dabe47497945b5872cf5363358e6f2-0.9908611671932213.png

3、UPDATE

a7d3dc9e78944a6ba1976cb633e72e35-0.7497705770248801.png

4、DELETE

318f94e49f354c6bb5f497d2fd6ee58a-0.3421094038528629.png

5、BACKUP

48ecca6c5134483b980410f6f6250d8b-0.1854649811185427.png

6、EXPLAIN

229146627be6493597a7f42b0cf3f8f6-0.5568162383975821.png

7、MERGE

e5be8c380461477bb6e853563552078b-0.267777136633631.png

8、RUNSCRIPT

运行 sql 脚本文件

50e491f7b4f949f295173c0b15656544-18143249957063ce508d4e2fb7b253b9a50913a6.png

9、SCRIPT

根据数据库创建 sql 脚本

365cf93045014aea851dab6f4af64b5d-0.2631504417655244.png

10、SHOW

5c2bbfb842ca44f0bf44fdf84b107f03-0.5079823858097767.png

11、ALTER INDEX RENAME

1dd28b8f7a804eabb97118a6d2b6425d-0.37568450985715707.png

12、ALTER SCHEMA RENAME

80f04f52e17d4dc084ff09811b9da7fa-0.7588178495726674.png

13、ALTER SEQUENCE

cb55184eb04a4129b6df2048119269c1-0.4908600234354752.png

14、ALTER TABLE

增加列

2b2098b9b80640218058143016c0e0f3-0.21304166891173892.png

增加约束

cbd559ac1a8a4f0ba0ab8a03e599b42e-0.04218498513343105.png

修改列

3d3d143d87c64a4da2232000ab9ff915-0.9071437034132126.png

删除列

9747b2150bfe454e946253ba64f73a7e-0.15920391806662604.png

删除序列

e83f3d0e0fe146c5ac51c726c9906da4-0.6709707767609308.png

15、ALTER USER

修改用户名

90d0c96b23e64ef7850e43efe182aa27-18143658fc8f0d3cb69b48ac8a67b121e32a935c.png

修改用户密码

2cd1d5899a784b3c8f365e70e016e970-1.png

16、ALTER VIEW

a5c4acd78eff4b1c9fbb9ecedfee18e9-2.png

17、COMMENT

c7df0fb0fa184cfcbf0ef2719dea6d94-3.png

18、CREATE CONSTANT

db0d577270c345a99e90192b89164b62-4.png

19、CREATE INDEX

3aa569ad51c94e2b9c169b56d810659e-5.png

20、CREATE ROLE

92e1a0ff3ce244b68015b5f715179dbb-6.png

21、CREATE SCHEMA

5aebb8089cd044bf9af604ceee29e447-7.png

22、CREATE SEQUENCE

68acf84e326d40ef9208ca3704719267-8.png

23、CREATE TABLE

f198ea6a31c94e59aceb1dc1f4a14892-9.png

24、CREATE TRIGGER

522c013327d944cb95df4030493b8bef-10.png

25、CREATE USER

cdb45ee71d38406ba7e02ed11fa97a53-11.png

26、CREATE VIEW

85a145312c0f479384117bc85b1cbb18-12.png

27、DROP

8bb5d8a17eff47feb9732c8f75bdc2ba-13.png

28、GRANT RIGHT 授权

6064876e46414224a5c6ddd347d52a26-14.png

给 schema 授权

4e4da07e09cf4df1b7135cdc336ff119-15.png

复制角色的权限

d13cb7f45bf944beafabf99666434544-16.png

29、REVOKE RIGHT 移除授权

8e6e87e86c17421583ec47c1cda3ea4d-17.png

移除角色具有的权限

755e9bd3e3864383a95fbf4326ed931a-18.png

30、ROLLBACK

从某个还原点（savepoint）回滚

35ddcf9a9bc340c1818316f77c7505da-19.png

回滚事务

7a5a2e700ff749c08037e055961ca21d-20.png

创建 savepoint

a9dea9bda6b349b2a1f02a2f9f8a1edd-21.png

五、数据类型

H2 支持如下数据类型

a932dce058a04985926b73bac7ae9026-1.png

1、INT Type

7c8817017ca44f4e811abca193b87b54-2.png

值范围：-2147483648 – 2147483647

2、BOOLEAN Type

可选值：TRUE 和 FALSE

3、TINYINT Type
值范围：-128 -127

4、SMALLINT Type

值范围： -32768 to 32767

5、BIGINT Type

值范围：-9223372036854775808 – 9223372036854775807

6、IDENTITY Type
自动增长值

值范围：-9223372036854775808 – 9223372036854775807

7、DECIMAL Type

示例：DECIMAL(20, 2)

8、DOUBLE Type

9、REAL Type
浮点类型

10、TIME Type
时间类型，格式为：hh:mm:ss

11 、DATE Type
日期类型，格式为：yyyy-MM-dd

12、TIMESTAMP Type

时间戳类型，格式为：yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]

可映射为 java 对象：java.sql.Timestamp/java.util.Date

13、BINARY Type

存储字节流，大数据建议使用 BLOB 类型存储

示例：BINARY(1000)

14、OTHER Type
用于存储序列化的 java 对象

15、VARCHAR Type

存储字符串，大文本数据建议使用 CLOB 类型存储

示例：VARCHAR(255)

16、VARCHAR_IGNORECASE Type
与 VARCHAR 类似，但是不区分大小写

17、CHAR Type

存储 unicode 字符串，与 VARCHAR 的区别是，尾部的空格不会被存储

18、BLOB Type

存储大容量二进制流，最大存储 2G 数据

映射 java 对象类型为：java.sql.Blob /java.io.InputStream

19、CLOB Type

用于存储大容量的文本数据

映射 java 对象类型为：java.sql.Clob/java.io.Reader

20、UUID Type
存储通用唯一识别码，占用 128bit

21、ARRAY Type
数组类型，使用一个值列表(1,2)或 PreparedStatement.setObject(.., new Object[] {..})存储值

映射 java 对象类型为：java.lang.Object[]

22、GEOMETRY Type
存储空间几何对象，一般存储 WKT 格式的空间数据

六、集群

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

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

1、创建目录

创建两个服务器工作的目录

  mkdir server1

  mkdir server2

2、启动 tcp 服务

执行如下命令分别在 9101、9102 端口启动两个使用 tcp 服务模式的数据库

  java org.h2.tools.Server-tcp -tcpPort  9101 -baseDir server1

  java org.h2.tools.Server-tcp -tcpPort  9102 -baseDir server2

3、使用 CreateCluster 工具创建集群

  java org.h2.tools.CreateCluster
  -urlSource jdbc:h2:tcp://localhost:9101/~/test
  -urlTarget jdbc:h2:tcp://localhost:9102/~/test
  -user sa
  -serverList localhost:9101,localhost:9102

如果两个数据库不存在，该命令将会自动创建数据库。如果一个数据库失效，可以先删除坏的数据库文件，重新启动数据库，然后重新运行 CreateCluster 工具

4、连接数据库

现在可以使用如下连接字符串连接集群数据库

   jdbc:h2:tcp://localhost:9101,localhost:9102/~/test

5、监控集群运行状态

可以使用如下命令查看配置的集群服务器是否都在运行

  SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CLUSTER'

6、限制

H2 的集群并不支持针对事务的负载均衡，所以很多操作会使两个数据库产生不一致的结果，执行如下操作时请小心：

  RANDOM_UUID(), SECURE_RAND(), SESSION_ID(), MEMORY_FREE(), MEMORY_USED(), CSVREAD(), CSVWRITE(), RAND()

自动增长列和标识列不支持集群，当插入数据时，序列值需要手动创建

不支持 SET AUTOCOMMIT FALSE 语句，如果需要设置成为不自动提交，可以执行方法 Connection.setAutoCommit(false)

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    343 引用 • 723 回帖
  • Solo

    Solo 是一款小而美的开源博客系统，专为程序员设计。Solo 有着非常活跃的社区，可将文章作为帖子推送到社区，来自社区的回帖将作为博客评论进行联动（具体细节请浏览 B3log 构思 - 分布式社区网络）。

    这是一种全新的网络社区体验，让热爱记录和分享的你不再感到孤单！

    1435 引用 • 10056 回帖 • 489 关注
北京 位置
3.7k 30 753 247 181 133 33 72 2.2k

相关帖子

回帖
H2 数据库使用简要说明（此博客使用 H2 数据库）

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
MainTheme
北京
回帖
0
 帖子
5
 积分
704