H2数据库攻略

素颜马尾好姑娘i 2022-05-17 10:49 255阅读 0赞

# [H2数据库攻略][H2] #

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

## 一、引入Maven依赖 ##

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

添加H2依赖

<dependency>
            <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 <port>：服务启动端口，默认为8082
 *  \-browser：启动H2 Console web管理页面
 *  \-tcp：使用TCP server模式启动
 *  \-pg：使用PG server模式启动

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

<?xml version="1.0" encoding="UTF-8"?>
        <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
            <modelVersion>1.0.0</modelVersion>
            <version>1.0.0</version>
            <groupId>groupid</groupId>
            <artifactId>h2-console</artifactId>
            <name>H2 Console</name>
            <packaging>pom</packaging>
            
            <properties>
                <h2.version>1.3.172</h2.version>
            </properties>
        
            <dependencies>
                <dependency>
                    <groupId>com.h2database</groupId>
                    <artifactId>h2</artifactId>
                    <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}" οnerrοr="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初始化数据库，配置示例如下

## 四、sql语法图 ##

### 1、SELECT ###

![18143006-0d15e079d0384facb42610724663b4c4.png][]

### 2、INSERT ###

![18143046-d6abe603092f48cabab8ba5be67f7135.png][]

### 3、UPDATE ###

![18143117-d3b900f87eb14213bb28c4affb77c500.png][]

### 4、DELETE ###

![18143142-7bb1133bb37b4ee78610a7f0be64f64c.png][]

### 5、BACKUP ###

![18143204-c345526f4f454b1b82d8ef73d9accaa7.png][]

### 6、EXPLAIN ###

![18143226-5d6af81f37aa400389dfdc95607fe62e.png][]

### 7、MERGE ###

![18143249-957063ce508d4e2fb7b253b9a50913a6.png][]

### 8、RUNSCRIPT ###

运行sql脚本文件

![18143311-700ef0366d354374baccd25a6f7607b0.png][]

### 9、SCRIPT ###

根据数据库创建sql脚本

![18143330-28b8b8d2296f4e64adf8385bbe93363c.png][]

### 10、SHOW ###

![18143352-a5359cd3cc7049ce9451995392087c93.png][]

### 11、ALTER INDEX RENAME ###

![18143412-f2b7f59b79894948b469b82fc4a83f1d.png][]

### 12、ALTER SCHEMA RENAME ###

![18143432-47c127cc55c447f5850d71462ac3424b.png][]

### 13、ALTER SEQUENCE ###

![18143454-0d3714bf30fd46b595dc663728d91009.png][]

### 14、ALTER TABLE ###

增加列

![18143515-5deeed81b5fa4834bf736412c62c8f12.png][]

增加约束

![18143538-ed2162fdbc864529b698d712db7882db.png][]

修改列

![18143558-4648e08f898c4c85b2121171ddb96417.png][]

删除列

![18143619-53947c1af30342e5a8fc3d9b2c6f85dd.png][]

删除序列

![18143638-6510b8cd9eac489c8f24dbf1ddf75eae.png][]

### 15、ALTER USER ###

修改用户名

![18143658-fc8f0d3cb69b48ac8a67b121e32a935c.png][]

修改用户密码

![18143718-5206d21f17a747d0a82a748c36009ee2.png][]

### 16、ALTER VIEW ###

![18143739-b705c214567841dcb876e6e5eabce801.png][]

### 17、COMMENT ###

![18143756-789f6026a0ca4cb5b2c98c9101f4bcbf.png][]

### 18、CREATE CONSTANT ###

![18143818-8d3fdb88883148d7b8d690c1eaf8d262.png][]

### 19、CREATE INDEX ###

![18143837-973b9a4e234349b599d3d9ae9d76d707.png][]

### 20、CREATE ROLE ###

![18143902-cc41346a902c43e28d8f391e56ec6d81.png][]

### 21、CREATE SCHEMA ###

![18143921-6076c614216b4d39a57c77917f5a7142.png][]

### 22、CREATE SEQUENCE ###

![18143941-d0aa266ce850473db02c635eac189fe8.png][]

### 23、CREATE TABLE ###

![18144003-f45983c3b20749dd96595caed365532c.png][]

### 24、CREATE TRIGGER ###

![18144029-de308a619bb346b4980e97c1bfb3540a.png][]

### 25、CREATE USER ###

![18144049-5922e46c4e0b46e3852972ff7fa2b4ee.png][]

### 26、CREATE VIEW ###

![18144115-15b0fee8ee054cb191c66575dc45d8ed.png][]

### 27、DROP ###

![18144213-28b05f3885dd438e9cf48b4e1e328329.jpg][]

### 28、GRANT RIGHT授权 ###

![18144434-e2c74dc9fafc44b19277bcb59b8aedbb.png][]

给schema授权

![18144452-c98405f55c6b4e5c9ed22ffce0e4f01d.png][]

复制角色的权限

![18144513-7684ed55f3f34396aa1684db9497ffa5.png][]

### 29、REVOKE RIGHT移除授权 ###

![18144603-04c89afabf98419daa968e22a43f1cbb.png][]

移除角色具有的权限

![18144632-fb6dbf17e33b4d579e30968de7322daa.png][]

### 30、ROLLBACK ###

从某个还原点（savepoint）回滚

![18144657-83d5961dc7074ae5bf8aa1bac4f70b38.png][]

回滚事务

![18144738-f101dc6ae04a41e290f27a42c605139b.png][]

创建savepoint

![18144802-e73d4f5fce784630b7a5db18b0518792.png][]

## 五、数据类型 ##

H2支持如下数据类型

![18152518-89ead0ca9d3342679ffb9d06e90be01e.png][]

### 1、INT Type ###

![18152540-8284a2904ee945c993b776a10ec407a6.png][]

值范围：-2147483648 - 2147483647

### 2、BOOLEAN Type ###

![18152605-21cdc69d6eef456d9df81bdeae99053d.png][]

可选值：TRUE和FALSE

### 3、TINYINT Type ###

值范围：-128 -127

### 4、SMALLINT Type ###

![18152629-89781ada23bd4544a0183021f419b819.png][]

值范围： -32768 to 32767

### 5、BIGINT Type ###

![18152648-69c51f13270b439eb129b702d907c40e.png][]

值范围：-9223372036854775808 - 9223372036854775807

### 6、IDENTITY Type ###

自动增长值

值范围：-9223372036854775808 - 9223372036854775807

### 7、DECIMAL Type ###

![18152706-bd70fe6a276143f0ab80b351dfbe7cb1.png][]

示例：DECIMAL(20, 2)

### 8、DOUBLE Type ###

![18152726-4573a5e9fac04629ab9f0aeb228e2338.png][]

### 9、REAL Type ###

浮点类型

### 10、TIME Type ###

时间类型，格式为：hh:mm:ss

### 11 、DATE Type ###

日期类型，格式为：yyyy-MM-dd

### 12、TIMESTAMP Type ###

![18152744-6fd9bd35f6a34ca69543110b59128c51.png][]

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

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

### 13、BINARY Type ###

![18152804-6c2b0eea6fda4b4cb190e2fbc8b0125b.png][]

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

示例：BINARY(1000)

### 14、OTHER Type ###

用于存储序列化的java对象

### 15、VARCHAR Type ###

![18152824-55e52227e3354d4eaeaa79e9d0ae0e62.png][]

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

示例：VARCHAR(255)

### 16、VARCHAR\_IGNORECASE Type ###

与VARCHAR类似，但是不区分大小写

### 17、CHAR Type ###

![18152851-766ec170e33b4a3491c50f620fed738e.png][]

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

### 18、BLOB Type ###

![18152917-817eab18238e4bc19f3b4e87bb378cc7.png][]

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

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

### 19、CLOB Type ###

![18152936-d1e09cc4975c4c3d99f4348b73d21afb.png][]

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

映射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)

[H2]: https://www.cnblogs.com/gao241/p/3480472.html
[H2 1]: http://www.h2database.com/
[18143006-0d15e079d0384facb42610724663b4c4.png]: /images/20220517/aa8eef2cdd444320b2791088dd1a2695.png
[18143046-d6abe603092f48cabab8ba5be67f7135.png]: /images/20220517/4f13c2af8946420296bed893b78d6865.png
[18143117-d3b900f87eb14213bb28c4affb77c500.png]: /images/20220517/2884902f7b8e429892dd2919fe097724.png
[18143142-7bb1133bb37b4ee78610a7f0be64f64c.png]: /images/20220517/7c50e43d52104e68b62d8ef14b458e8a.png
[18143204-c345526f4f454b1b82d8ef73d9accaa7.png]: /images/20220517/bcd51e63691947408d9d41937c058010.png
[18143226-5d6af81f37aa400389dfdc95607fe62e.png]: /images/20220517/7fb654234fed40089cf97f95a81aa0c5.png
[18143249-957063ce508d4e2fb7b253b9a50913a6.png]: /images/20220517/c98abef24060424997c6f67aae83e529.png
[18143311-700ef0366d354374baccd25a6f7607b0.png]: /images/20220517/2a57e3e9d98c4a60aef43f44b90bee61.png
[18143330-28b8b8d2296f4e64adf8385bbe93363c.png]: /images/20220517/e91f92d2e5754cc49060c2e0ee4f4041.png
[18143352-a5359cd3cc7049ce9451995392087c93.png]: /images/20220517/406f25f9e4e44501ac53888e59df57c9.png
[18143412-f2b7f59b79894948b469b82fc4a83f1d.png]: /images/20220517/0bba722c084349d1b2c1b3f17ae9c96b.png
[18143432-47c127cc55c447f5850d71462ac3424b.png]: /images/20220517/71d1edd7779c42a481f1d70e0563b459.png
[18143454-0d3714bf30fd46b595dc663728d91009.png]: /images/20220517/c2efad8eb5f2410280914acdc27e2af5.png
[18143515-5deeed81b5fa4834bf736412c62c8f12.png]: /images/20220517/7d1cb2c8355943c29c588183ad7089e1.png
[18143538-ed2162fdbc864529b698d712db7882db.png]: /images/20220517/940dcb692c67403d812b47f1a9fb985c.png
[18143558-4648e08f898c4c85b2121171ddb96417.png]: /images/20220517/e2edee2f5c634c1190325486c487d571.png
[18143619-53947c1af30342e5a8fc3d9b2c6f85dd.png]: /images/20220517/7cffe1ab146e43b1ae6368e75d1ce56c.png
[18143638-6510b8cd9eac489c8f24dbf1ddf75eae.png]: /images/20220517/905b0854500547d19da49b9df2a0a66e.png
[18143658-fc8f0d3cb69b48ac8a67b121e32a935c.png]: /images/20220517/b4187a34387948339a029f438c0061de.png
[18143718-5206d21f17a747d0a82a748c36009ee2.png]: /images/20220517/b2d5d051d7954f08b082b53122aca568.png
[18143739-b705c214567841dcb876e6e5eabce801.png]: /images/20220517/5e77c7152dce4fcca2b63e303922149b.png
[18143756-789f6026a0ca4cb5b2c98c9101f4bcbf.png]: /images/20220517/78ed310a211f4b659895e933ca281490.png
[18143818-8d3fdb88883148d7b8d690c1eaf8d262.png]: /images/20220517/e350576f04864b02b38de22ff1b7d456.png
[18143837-973b9a4e234349b599d3d9ae9d76d707.png]: /images/20220517/27a106a8b19447e7a3c43d076f81307a.png
[18143902-cc41346a902c43e28d8f391e56ec6d81.png]: /images/20220517/2a14a721a90146f381a9c973d41decb2.png
[18143921-6076c614216b4d39a57c77917f5a7142.png]: /images/20220517/0193fb3e88a24f7083ebcfaef41d265c.png
[18143941-d0aa266ce850473db02c635eac189fe8.png]: /images/20220517/18baba1ceae441bebf9f4947012bce62.png
[18144003-f45983c3b20749dd96595caed365532c.png]: /images/20220517/cbea4ad54d994783993580aad337de6b.png
[18144029-de308a619bb346b4980e97c1bfb3540a.png]: /images/20220517/87d02e4d18d842558f54d754ae9f60d7.png
[18144049-5922e46c4e0b46e3852972ff7fa2b4ee.png]: /images/20220517/7bde833d778145ca862e7196ee33bc9a.png
[18144115-15b0fee8ee054cb191c66575dc45d8ed.png]: /images/20220517/ee2902a92fc44af78079da89281cea26.png
[18144213-28b05f3885dd438e9cf48b4e1e328329.jpg]: /images/20220517/87ce8d8f3cfe4e3e9ad6421764f8018f.png
[18144434-e2c74dc9fafc44b19277bcb59b8aedbb.png]: /images/20220517/71c5bf0399f0429f92f0813c86c8e950.png
[18144452-c98405f55c6b4e5c9ed22ffce0e4f01d.png]: /images/20220517/19afd7ffb0214ac8b4bfdb5be3fa57db.png
[18144513-7684ed55f3f34396aa1684db9497ffa5.png]: /images/20220517/4e0d795c3d544773ada0905bd8470f2b.png
[18144603-04c89afabf98419daa968e22a43f1cbb.png]: /images/20220517/e6e0b716c44f4d0e9e401e2612aa6467.png
[18144632-fb6dbf17e33b4d579e30968de7322daa.png]: /images/20220517/c6fb5d591b6e49ef85c0b21784694af7.png
[18144657-83d5961dc7074ae5bf8aa1bac4f70b38.png]: /images/20220517/b09eeb3c5cb645a0aaec50c58c29f378.png
[18144738-f101dc6ae04a41e290f27a42c605139b.png]: /images/20220517/45fb78085a28444b84e7946e10ab7814.png
[18144802-e73d4f5fce784630b7a5db18b0518792.png]: /images/20220517/02452f75df294692a84a39062c7f8a5e.png
[18152518-89ead0ca9d3342679ffb9d06e90be01e.png]: /images/20220517/d475d5c45b70414d9eab67a3cdd71565.png
[18152540-8284a2904ee945c993b776a10ec407a6.png]: /images/20220517/fb9375fdff7b463dad5e8766b68cf78b.png
[18152605-21cdc69d6eef456d9df81bdeae99053d.png]: /images/20220517/f9652afa8b3942478f9e9ec3634f1521.png
[18152629-89781ada23bd4544a0183021f419b819.png]: /images/20220517/8fc65c7110774ffb8bdc6a6e9784951f.png
[18152648-69c51f13270b439eb129b702d907c40e.png]: /images/20220517/d98b4e9963d342fab1f4893a4d840399.png
[18152706-bd70fe6a276143f0ab80b351dfbe7cb1.png]: /images/20220517/589903ac71474c14afbe9d669e6de8c0.png
[18152726-4573a5e9fac04629ab9f0aeb228e2338.png]: /images/20220517/40e4a9b3615340328f18a249948a4106.png
[18152744-6fd9bd35f6a34ca69543110b59128c51.png]: /images/20220517/c6c6fbe401724e329f3528c74f087a21.png
[18152804-6c2b0eea6fda4b4cb190e2fbc8b0125b.png]: /images/20220517/7022fc6b55574d2597470bce14ec0de6.png
[18152824-55e52227e3354d4eaeaa79e9d0ae0e62.png]: /images/20220517/bac4985d98e7485d8313ee602df1dd4f.png
[18152851-766ec170e33b4a3491c50f620fed738e.png]: /images/20220517/66774c75181b4e88a74032fa2ea88992.png
[18152917-817eab18238e4bc19f3b4e87bb378cc7.png]: /images/20220517/4936cf7e02ab4b3d92644d2b7f77773b.png
[18152936-d1e09cc4975c4c3d99f4348b73d21afb.png]: /images/20220517/44515e6150d3454fad4b5b8e53e9aa78.png