[Java知识库]springboot嵌入式数据库H2初探

H2?

H2是一个开源的嵌入式数据库引擎，采用java语言编写，不受平台的限制，同时H2提供了一

个十分方便的web控制台用于操作和管理数据库内容。H2还提供兼容模式，可以兼容一些主

流的数据库，具有比较完备的数据库特性，如支client/server连接，能够支持标准的SQL语

句，支持存储过程等。因此采用H2作为开发期、测试期和演示的数据库非常方便，不太适合

作为大规模生产数据库。

特点

• 非常快，开源，JDBC API
• 嵌入式和服务器模式；基于磁盘或内存中的数据库
• 事务支持，多版本并发
• 基于浏览器的控制台应用程序
• 加密数据库
• 全文搜索
• 占用空间小的纯 Java：大约 2.5 MB 的 jar 文件大小
• ODBC 驱动程序

?好了这边直接开始

? ?

这边采用Springboot + h2+mybatis-plus?+swagger来实现这个例子

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.zkb</groupId>
    <artifactId>h2-test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>h2-test</name>
    <description>h2-test</description>
    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.3.2</mybatis-plus.version>
        <swagger-ui.version>1.5.22</swagger-ui.version>
        <springfox.version>2.9.2</springfox.version>
        <swagger-bootstrap-ui.version>1.9.1</swagger-bootstrap-ui.version>
        <fastjson.version>1.2.47</fastjson.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>


        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>${springfox.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-annotations</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>io.swagger</groupId>
                    <artifactId>swagger-models</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>${swagger-ui.version}</version>
        </dependency>

        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-models</artifactId>
            <version>${swagger-ui.version}</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger-ui -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${springfox.version}</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>${swagger-bootstrap-ui.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

其实与我们使用mysql数据库类似，只不过这边会自带一个h2 server

package com.zkb.conf;

import io.swagger.annotations.ApiOperation;
import io.swagger.models.auth.In;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.ApiKey;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

import java.util.Arrays;
import java.util.List;

@Configuration
@EnableSwagger2
public class SwaggerConfig {

    @Bean
    public Docket createRestApi1() {
        return new Docket(DocumentationType.SWAGGER_2).enable(true).apiInfo(apiInfo()).select()
                .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
                .apis(RequestHandlerSelectors.basePackage("com.zkb.controller"))
                .paths(PathSelectors.any()).build().securitySchemes(apiKeyList()).groupName("系统接口");
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("系统接口文档")
                .description("这是系统接口文档说明")
                .contact(new Contact("h2", "", ""))
                .version("1.0")
                .build();
    }

    private List<ApiKey> apiKeyList() {
        return Arrays.asList(new ApiKey("登录token", "token", In.HEADER.name()),
                new ApiKey("设备类型(android,ios,pc)---必填", "deviceType", In.HEADER.name()));
    }
}

package com.zkb.controller;

import com.zkb.entity.Student;
import com.zkb.mapper.StudentMapper;
import com.zkb.service.StudentService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RequestMapping("/test")
@RestController
@Api(value = "Student", tags = "Student")
public class TestController {

    @Autowired
    StudentService studentService;
    @GetMapping("list")
    @ApiOperation(value = "获取列表")
    public List<Student> getList(){
        return studentService.list();
    }
}

package com.zkb.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("student")
public class Student extends Model<Student> {
    private Integer id;
    private String name;
    private Integer age;
}

package com.zkb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zkb.entity.Student;


public interface StudentMapper extends BaseMapper<Student> {
}

package com.zkb.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zkb.entity.Student;
import com.zkb.mapper.StudentMapper;
import com.zkb.service.StudentService;
import org.springframework.stereotype.Service;

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
}

package com.zkb.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.zkb.entity.Student;

public interface StudentService extends IService<Student> {
}

package com.zkb;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@SpringBootApplication
@MapperScan("com.zkb.mapper")
@EnableSwagger2
public class H2TestApplication {

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

}

?data.sql

INSERT  INTO  student VALUES (1,'张三',10);
INSERT  INTO  student VALUES (2,'李四',16);

schema.sql

CREATE TABLE student(
    id int not null,
    name varchar(20) null,
    age int null
);

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zkb.mapper.StudentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.zkb.entity.Student">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
    </resultMap>

</mapper>

server:
    port: 8888
spring:
    datasource:
        driverClassName: org.h2.Driver
        password: 123456
#        url: jdbc:h2:E:/h2/testdb;MODE=MYSQL
        url: jdbc:h2:mem:mybatis
        username: root
    h2:
        console:
            enabled: true
    jackson:
        date-format: yyyy-MM-dd HH:mm:ss
        time-zone: GMT+8
    sql:
        init:
            schema-locations: classpath:db/schema.sql
            data-locations: classpath:db/data.sql
logging:
    level:
        com:
            fs: debug
mybatis-plus:
    configuration:
        map-underscore-to-camel-case: true
        auto-mapping-behavior: full
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    mapper-locations: classpath:mapping/*.xml
    global-config:
        # 逻辑删除配置
        db-config:
            # 删除前
            logic-not-delete-value: 0
            # 删除后
            logic-delete-value: 1

?看你自己的实际情况选择连接方式?该数据库支持多种连接方式和连接设置。 这是使用不同的数据库 URL 实现的。 URL 中的设置不区分大小写。

以上就是全部代码了，结构什么的，按照第一个图来就好

好了run一下看看

?

可以看到db/文件夹下的两个sql会直接执行

    sql:
        init:
            schema-locations: classpath:db/schema.sql
            data-locations: classpath:db/data.sql

?这里可以对应的初始化sql文件

?

?这里可以看到已经能正常使用H2数据库了，

demo下载地址h2嵌入式数据库例子springboot+h2+mybatisplus+swagger使用例子-Java文档类资源-CSDN下载