H2数据库配置
1.连接模式介绍 (参照附录)
1.1 内嵌模式
一次只能被一个客户端连接,应用和数据库同在一个JVM中,通过JDBC进行连接。
1.2 服务模式
支持多个客户端连接,支持远程连接。
1.3 内存模式
数据放在内存中,关闭后自动删除。
2.连接字符串
#spring.datasource.driverClassName = org.h2.Driver
#spring.datasource.url = jdbc:h2:/dbfile2/h2Db
#spring.datasource.username =xxx
#spring.datasource.password =xxx
#也可以用自己的
#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
2.1 内嵌模式
例如:
jdbc:h2:~/test //连接位于用户目录下的test数据库
jdbc:h2:file:/data/sample //数据库文件存放的相对地址
jdbc:h2:file:E:/H2/gacl(Windows only)
2.2 服务模式
jdbc:h2:tcp://localhost/~/test使用用户主目录
jdbc:h2:tcp://localhost//data/test使用绝对路径
jdbc:h2:tcp://dbserv:8444/~/sample 指定端口号
2.3 内存模式
jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test_mem
如果不指定DBName,则以私有方式启动,只允许一个连接
2.2 连接字符串参数
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
3.控制台下连接数据库的方式
java -jar jar包位置 -web -webPort 指定端口号(不指定则默认为8082) -browser
java -jar h2-1.3.168.jar -web -webPort 8090 -browser
4.假如要保证多个连接能够连接同一个数据库
(1)启动数据库
java -jar h2-1.3.160.jar -webAllowOthers -tcpAllowOthers
(2)使用服务模式连接数据库
jdbc:h2:tcp://localhost/d:/data/file/park
参考:https://cloud.tencent.com/developer/ask/83328
附录:H2数据库不同模式连接字串
URL Format and Examples
Embedded (local)
connection
jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample
(Windows only)
In-memory (private)
jdbc:h2:mem:
In-memory (named)
jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test_mem
Server mode (remote
connections) using TCP/IP
jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>
jdbc:h2:tcp://localhost/~/test
jdbc:h2:tcp://dbserv84/~/sample
jdbc:h2:tcp://localhost/mem:test
Server mode (remote
connections) using TLS
jdbc:h2:ssl://<server>[:<port>]/<databaseName>
jdbc:h2:ssl://localhost85/~/sample;
Using encrypted
files
jdbc:h2:<url>;CIPHER=AES
jdbc:h2:ssl://localhost/~/test;CIPHER=AES
jdbc:h2:file:~/secure;CIPHER=AES
File locking
methods
jdbc:h2:<url>;FILE_LOCK={FILE|SOCKET|NO}
jdbc:h2:file:~/private;CIPHER=AES;FILE_LOCK=SOCKET
Only open if it
already exists
jdbc:h2:<url>;IFEXISTS=TRUE
jdbc:h2:file:~/sample;IFEXISTS=TRUE
Don't close the
database when the VM exits
jdbc:h2:<url>;DB_CLOSE_ON_EXIT=FALSE
Execute SQL on
connection
jdbc:h2:<url>;INIT=RUNSCRIPT
FROM '~/create.sql'
jdbc:h2:file:~/sample;INIT=RUNSCRIPT
FROM '~/create.sql'\;RUNSCRIPT FROM '~/populate.sql'
User name and/or
password
jdbc:h2:<url>[;USER=<username>][;PASSWORD=<value>]
jdbc:h2:file:~/sample;USER=sa;PASSWORD=123
Debug trace
settings
jdbc:h2:<url>;TRACE_LEVEL_FILE=<level
0..3>
jdbc:h2:file:~/sample;TRACE_LEVEL_FILE=3
Ignore unknown
settings
jdbc:h2:<url>;IGNORE_UNKNOWN_SETTINGS=TRUE
Custom file access
mode
jdbc:h2:<url>;ACCESS_MODE_DATA=rws
Database in a zip
file
jdbc:h2:zip:<zipFileName>!/<databaseName>
jdbc:h2:zip:~/db.zip!/test
Compatibility mode
jdbc:h2:<url>;MODE=<databaseType>
jdbc:h2:~/test;MODE=MYSQL
Auto-reconnect
jdbc:h2:<url>;AUTO_RECONNECT=TRUE
jdbc:h2:tcp://localhost/~/test;AUTO_RECONNECT=TRUE
Automatic mixed
mode
jdbc:h2:<url>;AUTO_SERVER=TRUE
jdbc:h2:~/test;AUTO_SERVER=TRUE
Page size
jdbc:h2:<url>;PAGE_SIZE=512
Changing other
settings
jdbc:h2:<url>;<setting>=<value>[;<setting>=<value>...]
jdbc:h2:file:~/sample;TRACE_LEVEL_SYSTEM_OUT=3