springboot与h2database实践

小灰灰 2023-06-06 03:58 33阅读 0赞

一、**h2database**是一个开源的嵌入式数据库引擎，采用java语言编写，不受平台的限制，同时H2提供了一个十分方便的web控制台用于操作和管理数据库内容。H2还提供兼容模式，可以兼容一些主流的数据库，因此采用H2作为开发期的数据库非常方便。  
**二、h2database与springboot结合、首先是目录结构如下：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70]  
**三、pom.xml文件**

<?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.1.9.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.citydo</groupId>
        <artifactId>h2database</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>h2database</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <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>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    </project>

**四、配置文件与sql脚本**

spring:
      datasource:
        url: jdbc:h2:mem:h2test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
        platform: h2
        username: sa
        password:
        driverClassName: org.h2.Driver
      jpa:
        database-platform: org.hibernate.dialect.H2Dialect
        hibernate:
          ddl-auto: update
        properties:
          hibernate:
            show_sql: true
            use_sql_comments: true
            format_sql: true
      h2:
        console:
          enabled: true
          path: /console
          settings:
            trace: false
            web-allow-others: false
    logging:
      level:
        root: INFO

data.sql

insert into user(id,username, name, age, balance) values(1,'user1', '张三', 20, 100.00);
    insert into user(id,username, name, age, balance) values(2,'user2', '李四', 20, 100.00);
    insert into user(id,username, name, age, balance) values(3,'user3', '王五', 20, 100.00);
    insert into user(id,username, name, age, balance) values(4,'user4', '马六', 20, 100.00);

schema.sql

drop table user if exists;
    create table user(
    	id bigint generated by default as identity,
    	username varchar(40),
    	name varchar(20),
    	age int(3),
    	balance decimal(10,2),
    	primary key(id)
    );

**五、代码片段**  
H2databaseController.java

package com.citydo.h2database.controller;
    
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    public class H2databaseController {
    
        @GetMapping("/")
        public String test() {
            return "你好";
        }
    }

User.java

package com.citydo.h2database.domain;
    
    import lombok.Data;
    
    import javax.persistence.*;
    
    
    @Entity
    @Table(name = "user")
    @Data
    public class User {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private String username;
    
        private String name;
    
        private Integer age;
    
        private Double balance;
    
    }

UserRepository.java

package com.citydo.h2database.repository;
    
    import com.citydo.h2database.domain.User;
    import org.springframework.data.jpa.repository.JpaRepository;
    
    /**
     * Created on 2018/3/23.
     *
     * @author zlf
     * @since 1.0
     */
    public interface UserRepository extends JpaRepository<User,Long>{
    }

Springboot启动类

package com.citydo.h2database;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class H2databaseApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(H2databaseApplication.class, args);
        }
    
    }

测试类

package com.citydo.h2database;
    
    import com.citydo.h2database.domain.User;
    import com.citydo.h2database.repository.UserRepository;
    import lombok.extern.slf4j.Slf4j;
    import org.junit.Assert;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    @Slf4j
    public class H2databaseApplicationTests {
    
        @Test
        public void contextLoads() {
        }
    
        @Autowired
        private UserRepository userRepository;
    
        @Test
        public void saveTest() throws Exception {
            User user = new User();
            user.setName("你好");
            user.setUsername("user5");
            user.setAge(10);
            user.setBalance(100.00d);
            User result = userRepository.save(user);
            System.out.println("---------"+result.getId());
            Assert.assertNotNull(user.getId());
        }
    
        @Test
        public void findOneTest() throws Exception{
            List<User> all = userRepository.findAll();
            System.out.println("----------"+all.size());
            for (User user:all){
                System.out.println(user.getName());
            }
            User one = userRepository.getOne(1l);
            Assert.assertNotNull(one);
            Assert.assertTrue(1L==one.getId());
        }
    
    
    }

验证h2数据库是否连接成功

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70 1]  
注意：这是连接数据库界面同时要查看这3个红色标记的地方是否与springboot配置文件一致。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70]: /images/20230601/422e40d5d8c0404084e6c6de0e76d54b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70 1]: /images/20230601/de76c2c10e224d889d179bb53e2266dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ3MzAx_size_16_color_FFFFFF_t_70 2]: /images/20230601/f43d228eb23a4f78ac04b34294a2c0ca.png