springBoot 嵌入 H2 内存数据库

1、创建springBoot项目

pom.xml的maven依赖包

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.4.RELEASE</version>
        <relativePath/>
    </parent>
    <properties>
        <java.version>1.8</java.version>
        <!-- 引入h2的版本号 -->
        <h2.version>1.4.193</h2.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!-- Spring Boot JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MYSQL -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.7</version>
        </dependency>
        <!--热启动 -->
        <!--<dependency>-->
            <!--<groupId>org.springframework.boot</groupId>-->
            <!--<artifactId>spring-boot-devtools</artifactId>-->
        <!--</dependency>-->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <!--maven打包时应用-->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

  先按照正常的流程启动项目，保证可以通过浏览器访问项目  

2、编写测试类

package com.xxx.demo.springBoot;

import org.junit.runner.RunWith;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.sql.CommonDataSource;
import javax.sql.DataSource;
import java.sql.Connection;

@RunWith(SpringRunner.class)
@SpringBootApplication(scanBasePackages = {"com.xxxx.demo.springBoot"})
@SpringBootTest(classes = AppStarterTest.class)
public class AppStarterTest {

    public static void main(String[] args) {
        SpringApplication.run(AppStarterTest.class, args);
    }


}

package com.xxxx.demo.springBoot;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.test.context.TestPropertySource;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@TestPropertySource(value = "classpath:application-test.properties")
@EnableAutoConfiguration
public class springBootJunit extends AppStarterTest {
    private static  Boolean dsInited = false;
    private static Map<String, Integer> globalH2IndexMapper = new HashMap<>();
    private static Pattern keyCreatePattern = Pattern.compile("KEY `([^`]+)` \\([^)]+\\)");
    @Autowired
    DataSource dataSource;

    public DataSource createDataSource() throws Exception{
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");
        comboPooledDataSource.setUser("root");
        comboPooledDataSource.setPassword("123456");
        comboPooledDataSource.setJdbcUrl("");
        return comboPooledDataSource;
    }

    @PostConstruct
    private void init() throws Exception {
        if (!dsInited) {
            dsInited = true;
            Connection devConnection = createDataSource().getConnection();
            Connection connection = dataSource.getConnection();
            Statement statement = devConnection.createStatement();
            ResultSet rs = statement.executeQuery("show tables");
            while (rs.next()) {
                String table = rs.getString(1);
                ResultSet tableStructureRs = devConnection.createStatement().executeQuery(
                        String.format("show create table `%s`", table));
                tableStructureRs.next();
                String createTable = tableStructureRs.getString(2);
                createTable = createTable.replaceAll("COMMENT(\\[\\*\\])?='[^']+'", "");
                createTable = createTable.replaceAll("COMMENT '[^']+'", "");
                createTable = createTable.replaceAll("DEFAULT CHARSET=utf8(mb4)?", "");
                createTable = createTable.replaceAll("ON UPDATE CURRENT_TIMESTAMP?", "");
                createTable = renameKeyCreation(createTable);
                connection.createStatement().executeUpdate(String.format("drop table if exists `%s`", table));
                connection.createStatement().executeUpdate(createTable);
            }
            statement = connection.createStatement();
            // 需要创建一个这样的类 H2DBFunctionExt
            statement.execute("CREATE ALIAS IF NOT EXISTS date_format FOR \"com.aliyun.demo.springBoot.H2DBFunctionExt.date_format\"");
            connection.close();
            devConnection.close();
        }
    }

    @Before
    public void setup() throws Exception {
        reinstateData();
    }

    private String renameKeyCreation(String createTableStr) {
        Matcher matcher = keyCreatePattern.matcher(createTableStr);
        while (matcher.find()) {
            String keyName = matcher.group(1);
            if (!globalH2IndexMapper.containsKey(keyName)) {
                globalH2IndexMapper.put(keyName, 0);
            }
            int curIdx = globalH2IndexMapper.get(keyName);
            globalH2IndexMapper.put(keyName, curIdx + 1);
            createTableStr = createTableStr.replaceAll(keyName, keyName + "_" + curIdx);
        }
        return createTableStr;
    }

    private void reinstateData() throws Exception{
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("show tables");

        while (rs.next()) {
            String table = rs.getString(1);
            connection.createStatement().executeUpdate("truncate table " + table);
        }

        ScriptRunner runner = new ScriptRunner(connection);
        runScript(runner, "src/test/resources/dml.sql");

        connection.close();
    }

    private void runScript(ScriptRunner runner, String filename) throws FileNotFoundException, UnsupportedEncodingException {
        runner.runScript(new InputStreamReader(new FileInputStream(filename), "utf8"));
    }

    @Test
    public void test(){


    }



}

3、测试的application-test.properties文件信息

#server
server.port=7878

#配置数据库h2的参数
########################################################################
#
#     H2配置
#
#########################################################################
#db schema
spring.datasource.schema=classpath:db/schema.sql

#db data
spring.datasource.data=classpath:db/data.sql

#remote visit
spring.h2.console.settings.web-allow-others=true

#console url
spring.h2.console.path=/h2

#default true
spring.h2.console.enabled=true

spring.h2.console.settings.trace=true

spring.datasource.url=jdbc:h2:mem:test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.tyep=H2
spring.datasource.mode=mysql

 

4、H2的两个重要的文件信息schema.sql和data.sql

schema.sql

// 这行不能少 不然总是sql预发错误，坑爹的玩意，闹了一下午 
SET MODE MYSQL; /* another h2 way to set mode */
CREATE SCHEMA IF NOT EXISTS "public"; /* required due to issue with flyway --> https://stackoverflow.com/a/19115417/1224584*/

 

参考：

     http://www.bubuko.com/infodetail-2837848.html