H2 数据库的使用

IT►开发
阅读量： ...
文章目录
  1. groovy 中引入依赖
  2. 获取数据库连接
  3. 创建表
  4. 批量写入数据
  5. H2 数据库的相关操作
    1. 查询 session和事物
    2. 查询表大小、数据库路径和内存使用
    3. 关闭数据库并整理
  6. h2 的运行方式
    1. 连接字符串参数
    2. 启动服务
  • 问题
  • 开启大缓存启动参数
  • 减少数据库体积

    • 参考文档：

    groovy 中引入依赖

    1
    2
    3
    4
    @Grapes([
    	@GrabConfig(systemClassLoader=true),
        @Grab(group='com.h2database', module='h2', version='1.4.193', scope='test')
    ])

    如果配置grab获取h2最新版本 1.4.200，访问193的数据库时会造成库结构破坏，数据不可读问题。

    获取数据库连接

    1
    2
    3
    4
    5
    6
    7
    def db = [
        url:'jdbc:h2:E:/database',
        user:'sa',
        password:'',
        driver:'org.h2.Driver'
    ];
    def sql = Sql.newInstance(db.url, db.user, db.password, db.driver);

    创建表

    1
    2
    3
    4
    5
    6
    7
    8
    sql.execute('''
        CREATE TABLE IF NOT EXISTS TABLE_T (
            id bigint auto_increment,
            NAME VARCHAR(30) NOT NULL,
            TIME DATE,
            VALUE BIGINT
        )
    ''');

    批量写入数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    def step=10000
    def current=1
    println "begin synchronize records: $total"
    while (current < total){
    	println "query position $current, step $step, execute query and insert ..."
    	def qry = 'SELECT name,value FROM TABLE_T'
    	sqlNew.withBatch{stmt->
    	    sql.rows(qry,current,step).each {
    			def name=it.name?it.name:''
    			def full_name=it.full_name?it.full_name.replace("'","''"):''
    			def date=it.date?"'${it.date}'":null
    	        stmt.addBatch "INSERT INTO TABLE_T (name,date,value) VALUES ('${name}', value, date)"
    	    }
    	}
    	current=current+step
    }
    print "count new database records ..."
    total=sqlNew.firstRow('SELECT count(1) as count from TABLE_T').count as long
    print total

    1、通过 addBatch 批量提交提升效率
    2、构建 insert 语句时需要用单引号将字符类型字段包裹起来
    3、字符类型字段如果值为空，可以用空字符串表示
    4、但是date类型的字段要注意，有值时用单引号引用，无值时要用null不能用空字符串
    5、所以date类型的字段赋值比较麻烦：”‘${column}’”
    6、特别注意字符型字段中可能存在单引号的值的情况，需要用连续两个单引号来替换，否则写入数据报错

    H2 数据库的相关操作

    1. 问题：在没有写多少数据的情况下数据库文件异常增大（原本300M，没增一条数据只是查询就涨到快1G了）
    2. 问题：如何查看当前的session和未提交的transtaction
    3. 问题：如何从崩溃的数据库中恢复数据

    查询 session和事物

    1
    2
    SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT;
    SELECT * FROM INFORMATION_SCHEMA.IN_DOUBT;

    查询表大小、数据库路径和内存使用

    1
    2
    3
    4
    5
    CALL DISK_SPACE_USED('TABLES_T'); --当前测试无效，返回值始终是0
    CALL DATABASE_PATH();
    CALL MEMORY_FREE();
    CALL MEMORY_USED();
    ANALYZE SAMPLE_SIZE 1000

    关闭数据库并整理

    1
    2
    SHUTDOWN DEFRAG --耗时几秒，从近900M压缩到200M
    SHUTDOWN COMPACT --瞬间完成，无效果

    h2 的运行方式

    • 在内存中运行：数据库只在内存中运行，关闭连接后数据库将被清空，适合测试环境，如果不指定DBName，则以私有方式启动，只允许一个连接

      1
      jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1

    • 嵌入式：数据库持久化存储为单个文件，如果第一次连接则会自动创建数据库

      1
      jdbc:h2:file:~/.h2/DBName;AUTO_SERVER=TRUE

    • 服务模式：H2支持三种服务模式

      • web server：此种运行方式支持使用浏览器访问H2 Console

      • TCP server：支持客户端/服务器端的连接方式

      • PG server：支持PostgreSQL客户端

        1
        2
        jdbc:h2:tcp://localhost/~/test
        jdbc:h2:tcp://localhost//data/test

    连接字符串参数

    参数 说明
    MODE=MySQL 兼容模式，H2兼容多种数据库，该值可以为：DB2、Derby、HSQLDB、MSSQLServer、MySQL、Oracle、PostgreSQL
    DB_CLOSE_DELAY 要求最后一个正在连接的连接断开后，不要关闭数据库
    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

    启动服务

    1
    2
    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模式启动

    问题

    版本bug

    1
    @Grab(group='com.h2database', module='h2', version='1.4.200', scope='test')

    这个版本有bug，当第一次创建库的时候没有问题，但是再次打开会报错：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    Caught: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.IllegalStateException: Unable to read the page at position 107202383718080 [1.4.200/6]" [50000-200]
    org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.IllegalStateException: Unable to read the page at position 107202383718080 [1.4.200/6]" [50000-200]
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)
    	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
    	at org.h2.message.DbException.get(DbException.java:194)
    	at org.h2.message.DbException.convert(DbException.java:347)
    	at org.h2.engine.Database.openDatabase(Database.java:333)
    	at org.h2.engine.Database.<init>(Database.java:301)
    	at org.h2.engine.Engine.openSession(Engine.java:74)
    	at org.h2.engine.Engine.openSession(Engine.java:192)
    	at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)
    	at org.h2.engine.Engine.createSession(Engine.java:166)
    	at org.h2.engine.Engine.createSession(Engine.java:29)
    	at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:340)
    	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:173)
    	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)
    	at org.h2.Driver.connect(Driver.java:69)
    	at ReadCSV.execute(ReadSDBaseExcel.groovy:39)
    	at ReadCSV$execute.call(Unknown Source)
    	at ReadSDBaseExcel.run(ReadSDBaseExcel.groovy:15)
    Caused by: java.lang.IllegalStateException: Unable to read the page at position 107202383718080 [1.4.200/6]
    	at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)
    	at org.h2.mvstore.MVStore.readPage(MVStore.java:2213)
    	at org.h2.mvstore.MVMap.readPage(MVMap.java:672)
    	at org.h2.mvstore.MVMap.readOrCreateRootPage(MVMap.java:688)
    	at org.h2.mvstore.MVMap.setRootPos(MVMap.java:682)
    	at org.h2.mvstore.MVStore.openMap(MVStore.java:576)
    	at org.h2.mvstore.MVStore.openMap(MVStore.java:535)
    	at org.h2.mvstore.MVStore.openMap(MVStore.java:516)
    	at org.h2.mvstore.MVStore.removeMap(MVStore.java:2742)
    	at org.h2.engine.Database.handleUpgradeIssues(Database.java:866)
    	at org.h2.engine.Database.open(Database.java:742)
    	at org.h2.engine.Database.openDatabase(Database.java:307)
    	... 13 more
    Caused by: java.lang.IllegalStateException: Unsupported type 17 [1.4.200/3]
    	at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)
    	at org.h2.mvstore.type.ObjectDataType.newType(ObjectDataType.java:165)
    	at org.h2.mvstore.type.ObjectDataType.read(ObjectDataType.java:229)
    	at org.h2.mvstore.type.ObjectDataType.read(ObjectDataType.java:114)
    	at org.h2.mvstore.Page.read(Page.java:605)
    	at org.h2.mvstore.Page.read(Page.java:239)
    	at org.h2.mvstore.MVStore.readPage(MVStore.java:2211)
    	... 23 more

    切换到version=’1.4.193’就一切正常

    开启大缓存启动参数

    1
    MVCC=TRUE;CACHE_SIZE=3000000;MAX_OPERATION_MEMORY=75000000;PAGE_SIZE=16384;CACHE_TYPE=TQ

    cache size and memory esimates

    减少数据库体积

    1
    2
    MV_STORE=FALSE
    SHUTDOWN DEFRAG

    Why is my H2 database 7x larger on disk than it should be?