springboot 集成H2 内存数据库

1. 添加依赖

<dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

2. application.yml文件配置

database: h2

spring:
  datasource:
    #参考官方 http://www.h2database.com/html/features.html#other_logging
    #mem：连接到内存
    #MODE=MySQL：兼容模式，H2兼容多种数据库，该值可以为：DB2、Derby、HSQLDB、MSSQLServer、MySQL、Oracle、PostgreSQL
    #TRACE_LEVEL_SYSTEM_OUT、TRACE_LEVEL_FILE：输出跟踪日志到控制台或文件， 取值0为OFF，1为ERROR（默认值），2为INFO，3为DEBUG
    url: jdbc:h2:mem:数据库名称;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;TRACE_LEVEL_SYSTEM_OUT=2
    driver-class-name: org.h2.Driver
    username: root
    password: ***
    schema: "classpath:db/mysql/schema.sql"
    data: "classpath:db/mysql/data.sql"
    sql-script-encoding: UTF-8
  h2:
    console:
      #数据库控制台 http://localhost:8080/h2-console/login.jsp  
      path: /h2-console
      enabled: true
      setting:
         web-allow-others: true
  jpa:
    database: ${database}
    show-sql: false
    hibernate:
      ddl-auto: none
    open-in-view: false
  resources:
    chain:
      compressed: true
      cache: true
    cache:
      period: 86400

3. web控制台

 

 4. 一些有用的命令

###导入导出
```sql
导出：CALL CSVWRITE(导出文件,导出数据);

示例

　　call CSVWRITE ( 'D:/H2/dbbak/20141013.txt', 'SELECT * FROM MYTABLE');

导入： CREATE TABLE 表名 AS SELECT * FROM CSVREAD('备份');

示例

  CREATE TABLE MYTABLE AS SELECT * FROM CSVREAD('D:/H2/dbbak/20141013.csv');
  
  
  Example:

CALL CSVREAD('test.csv');
-- Read a file containing the columns ID, NAME with
CALL CSVREAD('test2.csv', 'ID|NAME', 'charset=UTF-8 fieldSeparator=|');
SELECT * FROM CSVREAD('data/test.csv', null, 'rowSeparator=;');
-- Read a tab-separated file
SELECT * FROM CSVREAD('data/test.tsv', null, 'rowSeparator=' || CHAR(9));
SELECT "Last Name" FROM CSVREAD('address.csv');
SELECT "Last Name" FROM CSVREAD('classpath:/org/acme/data/address.csv');


CALL CSVWRITE('data/test.csv', 'SELECT * FROM TEST');
CALL CSVWRITE('data/test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|');
-- Write a tab-separated file
CALL CSVWRITE('data/test.tsv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=' || CHAR(9));

```
###快速导入
```sql
Fast Database Import
To speed up large imports, consider using the following options temporarily:

SET LOG 0 (disabling the transaction log)
SET CACHE_SIZE (a large cache is faster)
SET LOCK_MODE 0 (disable locking)
SET UNDO_LOG 0 (disable the session undo log)
These options can be set in the database URL: jdbc:h2:~/test;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0. Most of those options are not recommended for regular use, that means you need to reset them after use.

If you have to import a lot of rows, use a PreparedStatement or use CSV import. Please note that CREATE TABLE(...) ... AS SELECT ... is faster than CREATE TABLE(...); INSERT INTO ... SELECT ....
```