H2 数据库使用简要说明(此博客使用 H2 数据库)

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" />

    <!-- Spring Simple连接池 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="${jdbc.driver}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- 初始化数据表结构 -->
    <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>
    </beans>

四、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🇲🇲ss

11 、DATE Type
日期类型,格式为:yyyy-MM-dd

12、TIMESTAMP Type

时间戳类型,格式为:yyyy-MM-dd hh🇲🇲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)