H2数据库

た入场券 2022-06-16 23:07 252阅读 0赞

**本文转载至  http://www.cnblogs.com/gao241/p/3480472.html**

# [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服务

![复制代码][copycode.gif]

<?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>

![复制代码][copycode.gif]

在命令行中执行如下命令启动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配置文件示例如下：

![复制代码][copycode.gif]

#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

![复制代码][copycode.gif]

### 2、初始化数据库 ###

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

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

mvn antrun:run -Prefresh-db

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

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

![复制代码][copycode.gif]

![复制代码][copycode.gif]

## 四、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]: http://www.cnblogs.com/gao241/p/3480472.html
[H2 1]: http://www.h2database.com/
[copycode.gif]: /images/20220617/be538becfa1346159ec5c1f511dcdda1.png
[18143006-0d15e079d0384facb42610724663b4c4.png]: /images/20220617/a7b334ea6c1f4f55a5b6e7ecb18f71bd.png
[18143046-d6abe603092f48cabab8ba5be67f7135.png]: /images/20220617/69ce11d1cb6a4a868805b6419aa58707.png
[18143117-d3b900f87eb14213bb28c4affb77c500.png]: /images/20220617/34dc6ed6c7404052a8a8a13146389943.png
[18143142-7bb1133bb37b4ee78610a7f0be64f64c.png]: /images/20220617/d0b95b14669d492587028699042fa076.png
[18143204-c345526f4f454b1b82d8ef73d9accaa7.png]: /images/20220617/4c5a8f545190446baa0238a99d5a0679.png
[18143226-5d6af81f37aa400389dfdc95607fe62e.png]: /images/20220617/2f77c39f0b1e43abbc72dd0d2b798b4a.png
[18143249-957063ce508d4e2fb7b253b9a50913a6.png]: /images/20220617/70750f240a6a449e80cf57278487a9e0.png
[18143311-700ef0366d354374baccd25a6f7607b0.png]: /images/20220617/af075f8abf0d4ecf8968c55ab55d8f34.png
[18143330-28b8b8d2296f4e64adf8385bbe93363c.png]: /images/20220617/59f028d03d5c48faa9649ee852d250aa.png
[18143352-a5359cd3cc7049ce9451995392087c93.png]: /images/20220617/7959f86204b748ea9fa6db59f44d67bb.png
[18143412-f2b7f59b79894948b469b82fc4a83f1d.png]: /images/20220617/9b3410ef6b3646f68750d30e8c89e808.png
[18143432-47c127cc55c447f5850d71462ac3424b.png]: /images/20220617/f1cc5fc2ef154ef7b0167a7f3f93a4ba.png
[18143454-0d3714bf30fd46b595dc663728d91009.png]: /images/20220617/e22fb84ea7da48b5b34078d9cfd9e595.png
[18143515-5deeed81b5fa4834bf736412c62c8f12.png]: /images/20220617/5118ddbbbdab45778ebd4c94f394cc90.png
[18143538-ed2162fdbc864529b698d712db7882db.png]: /images/20220617/c7adcf7df324449c8a76f97349bcf942.png
[18143558-4648e08f898c4c85b2121171ddb96417.png]: /images/20220617/a83d34fe6a1e49beb37ee1bb60dfdc71.png
[18143619-53947c1af30342e5a8fc3d9b2c6f85dd.png]: /images/20220617/8f2e46b0aae9466ba7f155e6b23ed61a.png
[18143638-6510b8cd9eac489c8f24dbf1ddf75eae.png]: /images/20220617/d397c9fdd68b44a2941d8a6d69f96c1e.png
[18143658-fc8f0d3cb69b48ac8a67b121e32a935c.png]: /images/20220617/0372aab1b46d4f85a545155afade159e.png
[18143718-5206d21f17a747d0a82a748c36009ee2.png]: /images/20220617/9bdfdbb24242454bb6281590cb5500b3.png
[18143739-b705c214567841dcb876e6e5eabce801.png]: /images/20220617/0c7a6c3915e846bc8be9634a67ddf360.png
[18143756-789f6026a0ca4cb5b2c98c9101f4bcbf.png]: /images/20220617/66218b619c5d4bad92e4bd8eec41aaf1.png
[18143818-8d3fdb88883148d7b8d690c1eaf8d262.png]: /images/20220617/a7d3d108db1847cb8fdf9da21d9a6c84.png
[18143837-973b9a4e234349b599d3d9ae9d76d707.png]: /images/20220617/cab5ff5fa84f4d98a9b05a4853858483.png
[18143902-cc41346a902c43e28d8f391e56ec6d81.png]: /images/20220617/7856907136e04efb99e364c8dbce6635.png
[18143921-6076c614216b4d39a57c77917f5a7142.png]: /images/20220617/4fb319a5248341b39b6191162b626f23.png
[18143941-d0aa266ce850473db02c635eac189fe8.png]: /images/20220617/fd44df317c964e51be16f4da6c70ac6c.png
[18144003-f45983c3b20749dd96595caed365532c.png]: /images/20220617/1fb5b95ff0264159a249be0a0effa49e.png
[18144029-de308a619bb346b4980e97c1bfb3540a.png]: /images/20220617/e19efa7c15c84a95ac07ca47c30911f4.png
[18144049-5922e46c4e0b46e3852972ff7fa2b4ee.png]: /images/20220617/ea55a90ff7fd4bf89e7e5667d4b91bae.png
[18144115-15b0fee8ee054cb191c66575dc45d8ed.png]: /images/20220617/711c02513a66408eae501b427ec9c85f.png
[18144213-28b05f3885dd438e9cf48b4e1e328329.jpg]: /images/20220617/ab6e34153b2443d9812197c80d1811be.png
[18144434-e2c74dc9fafc44b19277bcb59b8aedbb.png]: /images/20220617/4a20c6a9574f47fab8b77349b3f885f7.png
[18144452-c98405f55c6b4e5c9ed22ffce0e4f01d.png]: /images/20220617/163b2047bd1d463e90a0581c5c2873c2.png
[18144513-7684ed55f3f34396aa1684db9497ffa5.png]: /images/20220617/5dc202c698804ef7833f7f66dd9d83dd.png
[18144603-04c89afabf98419daa968e22a43f1cbb.png]: /images/20220617/b35fc375e61642f3a5c1f56b3dd9f6a2.png
[18144632-fb6dbf17e33b4d579e30968de7322daa.png]: /images/20220617/e8331f35f09f450dbc556b820c9a2241.png
[18144657-83d5961dc7074ae5bf8aa1bac4f70b38.png]: /images/20220617/e5f1bb45ca904572b3230a56c1696ff9.png
[18144738-f101dc6ae04a41e290f27a42c605139b.png]: /images/20220617/fb101cd1632c48a1878344163ce8705f.png
[18144802-e73d4f5fce784630b7a5db18b0518792.png]: /images/20220617/4c53c21f54934e77a9554e4b9b016bd6.png
[18152518-89ead0ca9d3342679ffb9d06e90be01e.png]: /images/20220617/9cac81ede31c4ec0b3abeb89496ba6b3.png
[18152540-8284a2904ee945c993b776a10ec407a6.png]: /images/20220617/6802037375b04085a6e6d33357bf0442.png
[18152605-21cdc69d6eef456d9df81bdeae99053d.png]: /images/20220617/c40657ce589642c78b79b50f4715e1e2.png
[18152629-89781ada23bd4544a0183021f419b819.png]: /images/20220617/f2062dedd5dd4670a4240594261b8f44.png
[18152648-69c51f13270b439eb129b702d907c40e.png]: /images/20220617/b33363834b7140fa815c5e39fd969f02.png
[18152706-bd70fe6a276143f0ab80b351dfbe7cb1.png]: /images/20220617/60ca4b19f94b470aa415ceb271a2feb8.png
[18152726-4573a5e9fac04629ab9f0aeb228e2338.png]: /images/20220617/790535c7247243e2929832737fbf7958.png
[18152744-6fd9bd35f6a34ca69543110b59128c51.png]: /images/20220617/757163c7d8ad49e7b7a187d363347961.png
[18152804-6c2b0eea6fda4b4cb190e2fbc8b0125b.png]: /images/20220617/8b20c706c6f4422f9d2c22df19babb1c.png
[18152824-55e52227e3354d4eaeaa79e9d0ae0e62.png]: /images/20220617/2dcd3a4abbe143cb8de3dfa1a12e8276.png
[18152851-766ec170e33b4a3491c50f620fed738e.png]: /images/20220617/47d52573933f43009ee9ed95e6e06234.png
[18152917-817eab18238e4bc19f3b4e87bb378cc7.png]: /images/20220617/3cff1cbdc1cd465f858484616df80196.png
[18152936-d1e09cc4975c4c3d99f4348b73d21afb.png]: /images/20220617/1571671c7b6a454facc7e5702e76227d.png