SpringBoot3集成H2数据库_springboot3 h2-优快云博客

本文链接：https://blog.youkuaiyun.com/orixixi/article/details/134415954

Spring Boot对嵌入式内存数据库有很好的支持，可以自动配置H2、HSQL、Derby数据库，不需要任何链接URL。以H2数据库为例

版本

名称	版本
OpenJDK	17
Gradle	8.2.1
Spring Boot	3.0.9
H2	2.2.224

配置

build.gradle

添加spring boot、H2相关依赖

自动配置嵌入式数据库需要依赖spring-jdbc

plugins {
    id 'java-library'
    id 'org.springframework.boot' version '3.0.9'
    id 'io.spring.dependency-management' version '1.1.3'
}

group 'org.example'
version '1.0-SNAPSHOT'

repositories {
    maven { url 'https://maven.aliyun.com/repository/public' }
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
    implementation 'com.h2database:h2:2.2.224'
    implementation 'org.projectlombok:lombok:1.18.30'
    annotationProcessor 'org.projectlombok:lombok:1.18.30'
}

H2初始化脚本

Spring Boot针对JDBC DataSource可以使用DML脚本自动创建库表，并使用DDL脚本初始化其数据。默认情况下，从classpath*：schema.sql加载模式脚本，从classpath*：data.sql加载数据脚本。即resources目录下的schema.sql、data.sql

schema.sql

create table user_role (id int, role_name varchar);

data.sql

INSERT INTO user_role (id, role_name) VALUES (1, '骑士');
INSERT INTO user_role (id, role_name) VALUES (2, '法师');
INSERT INTO user_role (id, role_name) VALUES (3, '机械师');

代码

实体类

@Data
public class UserRole {

    private Long id;

    private String roleName;
}

控制层

@RestController
@RequestMapping("/userRole")
public class UserRoleController {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/list")
    public List<UserRole> list() {
        return jdbcTemplate.query("SELECT * FROM user_role", new BeanPropertyRowMapper<>(UserRole.class));
    }
}

测试

启动Spring Boot

@Slf4j
@SpringBootApplication
public class Main {
    public static void main(String[] args) {
        SpringApplication.run(Main.class, args);
        log.info("http://localhost:8080/userRole/list");
    }
}

请求list接口

localhost:8080/userRole/list

可以看出，我们使用SpringBoot集成H2数据库，完全不用添加任何额外的spring配置，只需要包含要使用的嵌入式数据库依赖项即可。

H2数据库控制台

另外，H2数据库是基于浏览器的控制台应用程序，我们可以通过添加spirng的配置文件暴露控制台，方便开发阶段使用sql查询数据

在resources目录下创建application.yml文件，添加如下配置

spring.h2.console.enabled：是否启用控制台

spring:
  h2:
    console:
      enabled: true

启动类Main.class打印数据库连接路径、用户名、密码，启动Spring Boot

@Slf4j
@SpringBootApplication
public class Main {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(Main.class, args);
        log.info("http://localhost:8080/userRole/list");
        if (context.getBean(DataSource.class) instanceof HikariDataSource hikariDataSource) {
            log.info("url：{}", hikariDataSource.getJdbcUrl());
            log.info("username：{}", hikariDataSource.getUsername());
            log.info("password：{}", hikariDataSource.getPassword());
        }
    }
}

输出如下：

我们可以得知数据库名是UUID[b58afd55-d3f6-4fac-8d9e-6405865793a5]，用户名为sa，无密码。

查看官网可知在SpringBoot中H2控制台默认路径是/h2-console

打开http://localhost:8080/h2-console

输入日志打印的数据库连接路径、用户名

拓展

集成Hibernate

配置

build.gradle

添加hibernate依赖，将spring-boot-starter-data-jdbc替换成spring-boot-starter-data-jpa

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'com.h2database:h2:2.2.224'
    implementation 'org.hibernate.orm:hibernate-core:6.3.1.Final'
    implementation 'org.projectlombok:lombok:1.18.30'
    annotationProcessor 'org.projectlombok:lombok:1.18.30'
}

application.yml

添加配置spring.jpa.defer-datasource-initialization=true

spring:
  h2:
    console:
      enabled: true
  jpa:
    defer-DataSource-initialization: true

schema.sql

添加语句DROP TABLE IF EXISTS `user_role`;。否则会报JdbcSQLSyntaxErrorException异常，Table "USER_ROLE" already exists;

DROP TABLE IF EXISTS `user_role`;
create table user_role (id int, role_name varchar);

hibernate基本使用

@Data
@Entity
@Table(name = "user_role")
public class UserRole {

    @Id
    private Long id;

    @Column(name = "role_name")
    private String roleName;
}


//controller
@RestController
@RequestMapping("/userRole")
public class UserRoleController {

    @Resource
    private UserRoleService userRoleService;

    @GetMapping("/list")
    public List<UserRole> list() {
        return userRoleService.list();
    }
}


//service
public interface UserRoleService {

    List<UserRole> list();
}


//serviceImpl
@Service
public class UserUserRoleServiceImpl implements UserRoleService {

    @Resource
    private UserRoleRepository userRoleRepository;

    @Override
    public List<UserRole> list() {
        return userRoleRepository.findAll();
    }
}


//Repository
@Repository
public interface UserRoleRepository extends JpaRepository<UserRole, Long> {
}

测试

启动Spring Boot

@Slf4j
@SpringBootApplication
public class Main {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(Main.class, args);
        log.info("http://localhost:8080/userRole/list");
        log.info("http://localhost:8080/h2-console");
        if (context.getBean(DataSource.class) instanceof HikariDataSource hikariDataSource) {
            log.info("url：{}", hikariDataSource.getJdbcUrl());
            log.info("username：{}", hikariDataSource.getUsername());
            log.info("password：{}", hikariDataSource.getPassword());
        }
    }
}

集成Mybatis-Plus

配置

build.gradle

添加mybatis-plus依赖

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'com.h2database:h2:2.2.224'
    implementation 'com.baomidou:mybatis-plus-boot-starter:3.5.3.2'
    implementation 'org.projectlombok:lombok:1.18.30'
    annotationProcessor 'org.projectlombok:lombok:1.18.30'
}

application.yml

与hibernate不同，不需要添加额外的spring配置

spring:
  h2:
    console:
      enabled: true

schema.sql

也不需要添加额外的DML语句

create table user_role (id int, role_name varchar);

mybayis-plus基本使用

@Data
@TableName("user_role")
public class UserRole {

    @TableId(type = IdType.AUTO)
    private Long id;

    @TableField("role_name")
    private String roleName;
}


//config
@Configuration
@MapperScan("org.example.mapper")
public class MybatisPlusConfig {
}


//controller
@RestController
@RequestMapping("/userRole")
public class UserRoleController {

    @Resource
    private UserRoleService userRoleService;

    @GetMapping("/list")
    public List<UserRole> list() {
        return userRoleService.list();
    }
}


//service
public interface UserRoleService extends IService<UserRole> {
}


//serviceImpl
@Service
public class UserUserRoleServiceImpl extends ServiceImpl<UserRoleMapper, UserRole> implements UserRoleService {
}


//mapper
public interface UserRoleMapper extends BaseMapper<UserRole> {
}

<?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="org.example.mapper.UserRoleMapper">

</mapper>

测试

启动Spring Boot

@Slf4j
@SpringBootApplication
public class Main {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(Main.class, args);
        log.info("http://localhost:8080/userRole/list");
        log.info("http://localhost:8080/h2-console");
        if (context.getBean(DataSource.class) instanceof HikariDataSource hikariDataSource) {
            log.info("url：{}", hikariDataSource.getJdbcUrl());
            log.info("username：{}", hikariDataSource.getUsername());
            log.info("password：{}", hikariDataSource.getPassword());
        }
    }
}