SpringBoot 集成h2数据库

目录

 

1. h2数据库

2. 在pom.xml中导入相关依赖

3. 修改application.yml文件，加入H2相关配置

4. 编写bean(使用lombok 简化代码) controller

5. 在 Spring Boot 启动类中添加 @MapperScan 注解，扫描 Mapper 文件夹

6. 使用P6Spy监控Spring boot数据库操作

7. 访问 localhost:8080/h2-console 

8. 也可将h2数据库换为mysql,只需更该pom.xml中的相关依赖和application.yml中的数据库配置即可

---

1. h2数据库

h2database为我们提供了十分轻量，十分快捷方便的内嵌式数据库

• H2是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。
• 可以同应用程序打包在一起发布
• 它的另一个用途是用于单元测试。启动速度快，而且可以关闭持久化功能，每一个用例执行完随即还原到初始状态
• 提供JDBC访问接口，提供基于浏览器的控制台，可以执行sql
• 免费，开源，够快
• 还方便了程序刚开始dao层单元测试测试，不需要搭建oracle，不需要加载mysql，快速测试写的dao

2. 在pom.xml中导入相关依赖

<dependencies>
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.7</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.1</version>
        </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>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

3. 修改application.yml文件，加入H2相关配置

logging:
  level:
    com.example.mbp.mapper: debug

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:p6spy:h2:mem:test
    username: root
    password: test
  #在浏览器中开启控制台
  h2:
    console:
      enabled: true

#不使用p6spy
#driver-class-name: org.h2.Driver
#schema: classpath:db/schema-h2.sql
#data: classpath:db/data-h2.sql
#url: jdbc:h2:mem:test
#username: root
#password: test

4. 编写bean(使用lombok 简化代码) controller

package com.example.mbp.bean;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@TableName("user")
@Data
public class User {

    @TableId(value = "id",type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

package com.example.mbp.controller;

import com.example.mbp.bean.User;
import com.example.mbp.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class UserController {

    @Autowired
    UserMapper userMapper;

    HashMap<String,Object> map=new HashMap<>();

    @GetMapping("/user/list")
    public List<User> getUserList(){
        System.out.println(("----- selectAll method test ------"));
        List<User> userList = userMapper.selectList(null);
        //userList.forEach(System.out::println);
        return userList;
    }

    @GetMapping("/user/{id}")
    public User getUser(@PathVariable("id") Integer id){
        return userMapper.selectById(id);
    }

    @GetMapping("/user")
    public List<User> insertUser(User user){
        userMapper.insert(user);
        return userMapper.selectList(null);
    }

    @GetMapping("/user/delete/{id}")
    public Map deleteUser(@PathVariable("id") Integer id){
        System.out.println(userMapper.selectById(id));
        userMapper.deleteById(id);
        map.put("delete",userMapper.selectById(id));
        map.put("list",userMapper.selectList(null));
        return map;
    }

}

 编写Mapper类

package com.example.mbp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mbp.bean.User;

public interface UserMapper extends BaseMapper<User> {
}

5. 在 Spring Boot 启动类中添加 @MapperScan 注解，扫描 Mapper 文件夹

package com.example.mbp;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.mbp.mapper")
@SpringBootApplication
public class MbpApplication {

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

}

6. 使用P6Spy监控Spring boot数据库操作

spy.properties相关配置

modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

控制台会输出相关的数据库操作语句

访问  http://localhost:8080/user/list

http://localhost:8080/user?name=1709&age=9

7. 访问 localhost:8080/h2-console 

8. 也可将h2数据库换为mysql,只需更该pom.xml中的相关依赖和application.yml中的数据库配置即可

<dependencies>
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.7</version>
        </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>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://localhost:3306/june
    username: root
    password: root
logging:
  level:
    com.example.mbp.mapper: debug

#不使用p6spy的配置
#driver-class-name: com.mysql.jdbc.Driver
#url: jdbc:mysql://localhost:3306/june
#username: root
#password: root

注:

参考：

1. SpringBoot集成H2数据库

2. 在springboot中使用h2数据库

3. 使用P6Spy监控你的Spring boot数据库操作

4. MyBatis-Plus 快速开始