H2 数据库入门和基本使用

不念不忘少年蓝@ 2022-12-08 04:50 410阅读 0赞

**目录**

一、H2数据库安装

1、H2官方下载

2、安装H2数据库

3、登录H2数据库

4、登录错误解决

5、修改H2数据库密码

二、数据导入导出

1、数据导出

2、数据导入

3、数据插入

三、中文乱码解决

1、H2数据导入导出乱码处理

2、H2函数处理中文乱码

四、空间索引表的使用

1、创建空间索引表

2、几何空间相交查询

五、H2GIS空间扩展

1、H2GIS下载

2、H2GIS安装运行

3、H2GIS空间扩展

六、H2数据库备份

1、启动h2gis

2、数据库备份

3、H2GIS空间扩展

4、删除H2GIS空间扩展的表

5、注释压缩文件重新打包

6、导入数据

--------------------

## 一、H2数据库安装 ##

### **1、H2官方下载** ###

H2官网：[http://h2database.com/html/main.html][http_h2database.com_html_main.html]

H2教程一：[https://www.ctolib.com/docs/sfile/h2-database-doc/Quickstart/index.html][https_www.ctolib.com_docs_sfile_h2-database-doc_Quickstart_index.html]

H2教程二：[http://www.vue5.com/h2\_database/h2\_database.html][http_www.vue5.com_h2_database_h2_database.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70][]

### **2、安装H2数据库**  ###

选择安装目录 -> 点击下一步 -> 安装 -> 完成

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 3][]

### **3、登录H2数据库** ###

选择安装版的好处是安装之后有 H2 Console 图标，点击图标可以直接启动H2并使用默认浏览器访问H2登录页面

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 4][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 5][]

但是 win 2019-10-14 版本的 H2 数据库并没有自动创建一个test数据库，所以这里登录不了

报错信息如下：

> [Database "C:/Users/com/test" not found, either pre-create it or allow remote database creation (not recommended in secure environments) \[90149-200\]][Database _C_Users_com_test_ not found_ either pre-create it or allow remote database creation _not recommended in secure environments_ _90149-200] 90149/90149 [(帮助)][Link 1]

### **4、登录错误解决** ###

1、点击任务栏下的黄色小图标，选择 Create a new database

![20200917194719312.png][]

选择安装路径 C:\\Users\\com

注意：H2数据库默认安装在C:\\Users\\com下（com为系统用户名）

创建成功之后会在目录下新建一个 test.mv.db 文件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 6][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 7][]

再次测试，连接成功

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 9][]

2、./ 读取的路径在%H2%/bin，如果用默认的 ./ 创建数据库，则在%H2%/bin下创建数据库 xx.mv.db 文件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 10][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 11][]

xx.mv.db 为 H2 的数据库文件 ，tcp的URL默认读取的是 C:\\Users\\com下（com为系统用户名）的数据库文件

读取本地目录下的指定文件夹的数据库使用

（1） jdbc:h2:file: + 数据库路径 + 数据库名称

（2） jdbc:h2:tcp://localhost/ + 数据库路径 + 数据库名称

jdbc:h2:file:D:/Program Files (x86)/H2/bin/sea

jdbc:h2:tcp://localhost/D:/Program Files (x86)/H2/bin/test001

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 12][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 13][]

### **5、修改H2数据库密码** ###

h2的用户名密码一般设置为 密码为空的sa用户

ALTER USER SA SET PASSWORD ''

## 二、数据导入导出 ##

### 1、数据导出 ###

CALL CSVWRITE('D:\Hadoop\H2\hello_world.csv', 'SELECT * FROM hello_world');

### 2、数据导入 ###

CREATE TABLE hello_world  AS SELECT * FROM CSVREAD('D:\Hadoop\H2\hello_world.csv')

### 3、数据插入 ###

INSERT INTO hello_world SELECT * FROM CSVREAD('D:\Hadoop\H2\hello_world.csv');

## 三、中文乱码解决 ##

> H2数据库导出CSV数据默认字符编码虽然是UTF-8，但是分隔符是 逗号  
> 在导入的时候会中文乱码，解决办法是导出和导入时指定分隔符为 | ，字符编码为UTF-8

### 1、H2数据导入导出乱码处理 ###

（1）新建测试表

DROP TABLE IF EXISTS TEST;
    CREATE TABLE TEST(ID INT PRIMARY KEY,NAME VARCHAR(255));
    INSERT INTO TEST VALUES(1, 'Hello');
    INSERT INTO TEST VALUES(2, 'World');
    INSERT INTO TEST VALUES(3, '乔丹');
    INSERT INTO TEST VALUES(4, '科比');
    INSERT INTO TEST VALUES(5, '艾佛森');
    INSERT INTO TEST VALUES(6, '麦迪');

SELECT * FROM TEST

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 14][]

（2）指定编码导出表数据

CALL CSVWRITE('D:\Hadoop\H2\test.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');

（3）删除测试表重新导入

先删除测试的数据表

DROP TABLE test

测试读取文件是否会发生乱码

CALL CSVREAD('D:\Hadoop\H2\test.csv','id|name', 'charset=UTF-8 fieldSeparator=|');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 15][]

重新指定字符编码和文件分隔符导入表

CREATE TABLE test AS SELECT * FROM CSVREAD('D:\Hadoop\H2\test.csv','id|name', 'charset=UTF-8 fieldSeparator=|');

（4）验证查询

重新导入后中文显示正常

SELECT * FROM TEST

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 16][]

### 2、H2函数处理中文乱码 ###

使用H2自带的UTF-8函数处理

CALL UTF8TOSTRING(STRINGTOUTF8('中文乱码测试！'))

## 四、空间索引表的使用 ##

### 1、创建空间索引表 ###

CREATE TABLE GEO_TABLE(GID SERIAL, THE_GEOM GEOMETRY);
    INSERT INTO GEO_TABLE(THE_GEOM) VALUES
     ('POINT(500 505)'),
     ('LINESTRING(550 551, 525 512, 565 566)'),
     ('POLYGON ((550 521, 580 540, 570 564, 512 566, 550 521))');
    CREATE SPATIAL INDEX GEO_TABLE_SPATIAL_INDEX ON GEO_TABLE(THE_GEOM);

### ![20200924151244515.png][] ###

### 2、几何空间相交查询 ###

SET @POLY='POLYGON ((490 490, 536 490, 536 515, 490 515, 490 490))';
    SELECT * FROM GEO_TABLE WHERE THE_GEOM && @POLY;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 17][]

## 五、H2GIS空间扩展 ##

### 1、H2GIS下载 ###

h2gis官网：[http://www.h2gis.org/][http_www.h2gis.org]

h2gis文档：[http://javadoc.orbisgis.org/][http_javadoc.orbisgis.org]

打开官方，点击 Download 下载

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 18][]

### 2、H2GIS安装运行 ###

**Linux**

wget https://github.com/orbisgis/h2gis/releases/download/v1.5.0/h2gis-dist-1.5.0-bin.zip -O h2gis.zip
    unzip h2gis.zip
    cd h2gis-standalone
    nohup java -cp h2gis-dist-1.5.0.jar org.h2.tools.Server -tcpAllowOthers -tcpPort 9101 -webAllowOthers -webPort 8081 2>&1 &
    ## java -jar h2gis-dist-1.5.0.jar

**Windows**

## 将压缩包解压，cmd打开进入解压目录
    cd h2gis-standalone
    java -jar h2gis-dist-1.5.0.jar

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 19][]

### 3、H2GIS空间扩展 ###

初始化之后就有了H2扩展

CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load";
    CALL H2GIS_SPATIAL();

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 20][]

## 六、H2数据库备份 ##

### 1、启动h2gis ###

cd h2gis-standalone
    java -cp h2gis-dist-1.5.0.jar org.h2.tools.Server -tcpAllowOthers -tcpPort 9101 -webAllowOthers -webPort 8081

URL链接：jdbc:h2:tcp://127.0.0.1:9101/~/testdb

### 2、数据库备份 ###

java -cp /usr/local/hadoop/h2gis-standalone/bin/h2-1.4.197.jar org.h2.tools.Script -url jdbc:h2:tcp://127.0.0.1:8081/~/testdb -user sa -script /usr/local/hadoop/h2gis-standalone/table/testdb.zip -options compression zip

### 3、H2GIS空间扩展 ###

CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load";
    CALL H2GIS_SPATIAL();

### 4、删除H2GIS空间扩展的表 ###

DROP TABLE SPATIAL_REF_SYS ;
    DROP TABLE GEOMETRY_COLUMNS ;

### 5、注释压缩文件重新打包 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 21][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 22][]

### 6、导入数据 ###

java -cp /usr/local/hadoop/h2gis-standalone/bin/h2-1.4.197.jar org.h2.tools.RunScript -url jdbc:h2:tcp://127.0.0.1:9101/~/testdb -user sa -script /usr/local/hadoop/h2gis-standalone/table/testdb.zip -options compression zip

###  ###

[http_h2database.com_html_main.html]: http://h2database.com/html/main.html
[https_www.ctolib.com_docs_sfile_h2-database-doc_Quickstart_index.html]: https://www.ctolib.com/docs/sfile/h2-database-doc/Quickstart/index.html
[http_www.vue5.com_h2_database_h2_database.html]: http://www.vue5.com/h2_database/h2_database.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70]: /images/20221123/caa70fecd69d461290679ea116baa8ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200917192644773.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 2]: /images/20221123/6d6158dcb838442cb79f2403c81137cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 3]: /images/20221123/c0e0d7380b7248e2b6782aa62cde3b23.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200917193053517.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 5]: /images/20221123/b38999f6bcd643779889580aad60482b.png
[Database _C_Users_com_test_ not found_ either pre-create it or allow remote database creation _not recommended in secure environments_ _90149-200]: http://169.254.160.254:8082/test.do?jsessionid=b3c874a65c32497993ebef4b4bc9ccd2#
[Link 1]: https://h2database.com/javadoc/org/h2/api/ErrorCode.html#c90149
[20200917194719312.png]: /images/20221123/713ac552f6ea4d6bbe0b00a6afd40dc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200917195326203.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 7]: /images/20221123/367491adb28449508e711fe55d71d258.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 8]: /images/20221123/5b6cc4f5eef74ba08b9e2d3d4fd9c4da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 9]: /images/20221123/97bdbf3e351b42208f1394142060df63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200925115915548.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 11]: /images/20221123/612f03ab47c2487ebd19a61bf51c7b78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200925125044795.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 13]: /images/20221123/82febed64dc14a609fb5d342341c8ddb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 14]: /images/20221123/293671aac9fc4925b16a97e1cc58eb3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 15]: /images/20221123/426ee79918c94204a1e9ef35caa1fcfa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 16]: /images/20221123/4c2bcb858ab34de183382d6528a5b2e3.png
[20200924151244515.png]: /images/20221123/b0ded1375a854cf3b634a702ce00b9a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 17]: /images/20221123/28377e636e534633b4a33f5430c8e5a7.png
[http_www.h2gis.org]: http://www.h2gis.org/
[http_javadoc.orbisgis.org]: http://javadoc.orbisgis.org/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 18]: /images/20221123/3ad58a95ff9e41ad8b990a03f8536d3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 19]: /images/20221123/e898b37c83004b1a9226504f4ecb02c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 20]: /images/20221123/3eca3f9afd34475db0d8e9157733bbd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 21]: /images/20221123/465b662100ed464b8153292a86f8bd4f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx_size_16_color_FFFFFF_t_70 22]: /images/20221123/7b18b1178d4c44e598abbc5f0e92878a.png