• SpringBoot 整合 H2 嵌入式关系型数据库

    SpringBoot 整合 H2 嵌入式关系型数据库


    GitHub: link. 欢迎star

    注意：本篇博客风格（不多比比就是撸代码!!!）

    一、maven依赖

            
        <dependency>
            <groupId>com.h2databasegroupId>
            <artifactId>h2artifactId>
            <version>2.1.214version>
        dependency>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-data-jpaartifactId>
        dependency>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    二、application.yml

    spring:
  profiles:
    active: dev
  datasource:
    # 内存模式，进程关闭数据丢失
    # url: jdbc:h2:mem:data_h2
    # 嵌入模式，当前项目目录，进程关闭数据保留
    url: jdbc:h2:./data_h2
    username: root
    password: root
    driver-class-name: org.h2.Driver
  h2:
    console:
      # 开启web控制台
      enabled: true
      # 访问路径url+/h2
      path: /h2
      settings:
        web-allow-others: true
  jpa:
    # 更新表结构，可以在控制台执行修改表结构的SQL语句
    hibernate:
      ddl-auto: update
    show-sql: true

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    三、实体类（映射表）

    - H2Table.java

    import com.andon.springbootutil.constant.DataType;
import lombok.*;
import lombok.experimental.FieldDefaults;
import org.hibernate.annotations.GenericGenerator;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

/**
 * @author Andon
 * 2022/10/26
 */
@Data
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "h2_table")
@FieldDefaults(level = AccessLevel.PRIVATE)
@EntityListeners(AuditingEntityListener.class)
public class H2Table implements Serializable {

    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Column(columnDefinition = "VARCHAR(36) COMMENT 'ID'", updatable = false, nullable = false, unique = true)
    String id;

    @Column(name = "name", columnDefinition = "VARCHAR(255) COMMENT '名称'", nullable = false, unique = true)
    String name;

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "VARCHAR(100) COMMENT '类型'", nullable = false)
    DataType dataType;

    @Column(columnDefinition = "BOOLEAN DEFAULT FALSE COMMENT '是否是调试数据'")
    Boolean isDebug;

    @Column(name = "remark", columnDefinition = "VARCHAR(255) COMMENT '备注'")
    String remark;

    @CreatedDate
    @Column(updatable = false)
    @Temporal(TemporalType.TIMESTAMP)
    Date createTime;

    @LastModifiedDate
    @Temporal(TemporalType.TIMESTAMP)
    Date updateTime;
}

    • 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
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • DataType.java
    /**
 * @author Andon
 * 2022/10/26
 */
public enum DataType {
    S,
    A,
    B,
    C,
    D
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    四、Repository

    import com.andon.springbootutil.entity.H2Table;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author Andon
 * 2022/10/26
 */
public interface H2TableRepository extends JpaRepository<H2Table, String> {
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    五、启动类

    import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;

@EnableJpaAuditing
@SpringBootApplication
public class SpringBootUtilApplication {

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

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    六、接口测试类（Controller、Service）

    - TestH2Controller.java

    import com.andon.springbootutil.constant.DataType;
import com.andon.springbootutil.domain.ResponseStandard;
import com.andon.springbootutil.entity.H2Table;
import com.andon.springbootutil.service.TestH2Service;
import io.swagger.annotations.*;
import lombok.*;
import lombok.experimental.FieldDefaults;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.List;

/**
 * @author Andon
 * 2022/10/26
 */
@Api(tags = "H2")
@RestController
@RequestMapping("/h2-test")
@RequiredArgsConstructor
public class TestH2Controller {

    private final TestH2Service testH2Service;

    @Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public static class H2TableAddVO implements Serializable {
        @NotBlank(message = "名称不能为空")
        @ApiModelProperty(value = "名称", required = true)
        String name;
        @NotNull(message = "数据类型不能为空")
        @ApiModelProperty(value = "数据类型", example = "S", required = true)
        DataType dataType;
    }

    @ApiOperation("新增")
    @PostMapping("/add")
    public ResponseStandard<H2Table> add(@Valid @RequestBody H2TableAddVO h2TableAddVO) {
        return ResponseStandard.successResponse(testH2Service.add(h2TableAddVO));
    }

    @ApiOperation("删除")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id", value = "ID", required = true, example = "asd-uio-zxc"),
    })
    @DeleteMapping("/delete")
    public ResponseStandard<Boolean> delete(@RequestParam(name = "id") String id) {
        testH2Service.delete(id);
        return ResponseStandard.successResponse(true);
    }

    @Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @FieldDefaults(level = AccessLevel.PRIVATE)
    public static class H2TableUpdateVO implements Serializable {
        @NotBlank(message = "ID不能为空")
        @ApiModelProperty(value = "ID", required = true)
        String id;
        @NotBlank(message = "名称不能为空")
        @ApiModelProperty(value = "名称", required = true)
        String name;
        @NotNull(message = "数据类型不能为空")
        @ApiModelProperty(value = "数据类型", example = "S", required = true)
        DataType dataType;
    }

    @ApiOperation("修改")
    @PostMapping("/update")
    public ResponseStandard<H2Table> update(@Valid @RequestBody H2TableUpdateVO h2TableUpdateVO) {
        return ResponseStandard.successResponse(testH2Service.update(h2TableUpdateVO));
    }

    @ApiOperation("查询")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id", value = "ID", required = true, example = "asd-uio-zxc"),
    })
    @GetMapping("/query")
    public ResponseStandard<H2Table> query(@RequestParam(name = "id") String id) {
        return ResponseStandard.successResponse(testH2Service.query(id));
    }

    @ApiOperation("查询所有")
    @GetMapping("/queryAll")
    public ResponseStandard<List<H2Table>> queryAll() {
        return testH2Service.queryAll();
    }
}

    • 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
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95

    - TestH2Service.java

    import com.andon.springbootutil.controller.TestH2Controller;
import com.andon.springbootutil.domain.ResponseStandard;
import com.andon.springbootutil.dto.mapstruct.H2TableMapper;
import com.andon.springbootutil.entity.H2Table;
import com.andon.springbootutil.repository.H2TableRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.util.List;
import java.util.Optional;

/**
 * @author Andon
 * 2022/10/26
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class TestH2Service {

    private final H2TableMapper h2TableMapper;
    private final H2TableRepository h2TableRepository;

    public H2Table add(TestH2Controller.H2TableAddVO h2TableAddVO) {
        H2Table h2Table = h2TableMapper.h2TableAddVOToH2Table(h2TableAddVO);
        return h2TableRepository.saveAndFlush(h2Table);
    }

    public void delete(String id) {
        h2TableRepository.deleteById(id);
    }

    public H2Table update(TestH2Controller.H2TableUpdateVO h2TableUpdateVO) {
        H2Table h2Table = h2TableMapper.h2TableUpdateVOToH2Table(h2TableUpdateVO);
        return h2TableRepository.saveAndFlush(h2Table);
    }

    public H2Table query(String id) {
        Optional<H2Table> h2TableOptional = h2TableRepository.findById(id);
        Assert.isTrue(h2TableOptional.isPresent(), "ID不存在");
        return h2TableOptional.get();
    }

    public ResponseStandard<List<H2Table>> queryAll() {
        List<H2Table> h2Tables = h2TableRepository.findAll();
        ResponseStandard<List<H2Table>> response = ResponseStandard.successResponse(h2Tables);
        response.setTotal(h2Tables.size());
        return response;
    }
}

    • 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
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    - H2TableMapper.java

    import com.andon.springbootutil.controller.TestH2Controller;
import com.andon.springbootutil.entity.H2Table;
import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.Mappings;

/**
 * @author Andon
 * 2022/10/26
 */
@Mapper(componentModel = "spring")
public abstract class H2TableMapper {

    @Mappings({
            @Mapping(target = "id", ignore = true),
            @Mapping(target = "isDebug", ignore = true),
            @Mapping(target = "remark", ignore = true),
            @Mapping(target = "createTime", ignore = true),
            @Mapping(target = "updateTime", ignore = true)
    })
    public abstract H2Table h2TableAddVOToH2Table(TestH2Controller.H2TableAddVO h2TableAddVO);

    @Mapping(target = "isDebug", ignore = true)
    @Mapping(target = "createTime", ignore = true)
    @Mapping(target = "remark", ignore = true)
    @Mapping(target = "updateTime", ignore = true)
    public abstract H2Table h2TableUpdateVOToH2Table(TestH2Controller.H2TableUpdateVO h2TableUpdateVO);
}

    • 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

    七、测试

    1.启动项目

    在这里插入图片描述

    2.地址栏输入url+配置文件path，并配置驱动类/地址/用户名/密码

    在这里插入图片描述

    3.进入h2控制台

    在这里插入图片描述

    4.通过写的测试接口，进行增删改查

    在这里插入图片描述

    5.在h2控制台通过sql语句查询

    81c3eac8-f5c7-4d0a-acb2-66f3b025a441

    GitHub: link. 欢迎star

  • 相关阅读:
    04 【计算属性 侦听器】
    2008-2020年全国各省劳动生产率
    黑客自学笔记
    C++ programming: Linux server socket example
    iOS之crash分析篇--符号化
    基于simulink的超级电容，电池及DC motor充放电系统仿真
    C语言面试必看-指针笔试题详解
    lammps已跑完，不想重跑，如何补充新的计算？
    《中国垒球》：棍网球委员会·垒球联盟
    弄懂#if #ifdef #if defined
  • 原文地址：https://blog.csdn.net/weixin_39792935/article/details/127572261