springboot配置内存数据库h2

springboot , , 评论

H2基本使用

  1. pom
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
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
  1. application.properties
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
# 应用名称
spring.application.name=dynamic-form
# 应用服务 WEB 访问端口
server.port=8080

# h2 内存数据库,内存模式连接配置 库名: dynamic-form
#spring.datasource.url=jdbc:h2:mem:dynamic-form
spring.datasource.url=jdbc:h2:file:./data/dynamic-form
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=123456
# 初始化数据表 DDL
#spring.datasource.schema=classpath:sql/init.sql
# 初始化数据 DML
#spring.datasource.data=classpath:sql/data.sql

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true


# 开启console访问,默认false, http://localhost:8080/h2-console
spring.h2.console.enabled=true
# h2 访问路径上下文
spring.h2.console.path=/h2-console
# 开启h2 console 跟踪,方便调试,默认 false
spring.h2.console.settings.trace=false
# 不允许console远程访问,默认false
spring.h2.console.settings.web-allow-others=false

logging.level.cn.idea360.dynamicform=debug
  1. entity
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.example.odata.model;

import lombok.Data;

import javax.persistence.*;
/**
 * 完整表单也可以直接存储为json
 * @author cuishiying
 */
@Data
@Table(name = "form")
@Entity
public class Form {

    @Id
    @GeneratedValue
    private int id;

    /**
     * 表单名
     */
    @Column
    private String name;
}
  1. orm
1
2
3
4
5
6
7
8
package com.example.odata.repository;

import com.example.odata.model.Form;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface FormRepository extends JpaRepository<Form, Integer> {
}
  1. junit5
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
package com.example.odata;

import com.example.odata.model.Form;
import com.example.odata.repository.FormRepository;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

import static org.junit.jupiter.api.Assertions.assertEquals;

/**
 * @author cuishiying
 */
@Slf4j
@SpringBootTest
@DisplayName("主表单单元测试")
class FormRepositoryTest {

    @Resource
    private FormRepository formRepository;

    @DisplayName("新建表单")
    @Test
    void addForm() throws Exception {
        Form form = new Form();
        form.setName("动态表单");
        Form save = formRepository.save(form);
        assertEquals(1, save.getId());
    }

    @DisplayName("获取表单")
    @Test
    void getData() throws Exception {
        Form form = formRepository.getById(1);
        log.info("form: {}", form);
        assertEquals("动态表单", form.getName());
    }

}

JPA简单说明

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
在JPA规范中，一对多的双向关系由多端来维护

@OneToMany:
       作用：建立一对多的关系映射
    属性：
        targetEntityClass：指定多的多方的类的字节码
        mappedBy：指定从表实体类中引用主表对象的名称。
        cascade：指定要使用的级联操作
        fetch：指定是否采用延迟加载
        orphanRemoval：是否使用孤儿删除

@ManyToOne
    作用：建立多对一的关系
    属性：
        targetEntityClass：指定一的一方实体类字节码
        cascade：指定要使用的级联操作
        fetch：指定是否采用延迟加载
        optional：关联是否可选。如果设置为false，则必须始终存在非空关系。

@JoinColumn
     作用：用于定义主键字段和外键字段的对应关系。
     属性：
        name：指定外键字段的名称
        referencedColumnName：指定引用主表的主键字段名称
        unique：是否唯一。默认值不唯一
        nullable：是否允许为空。默认值允许。
        insertable：是否允许插入。默认值允许。
        updatable：是否允许更新。默认值允许。
        columnDefinition：列的定义信息。
  1. 基类
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
package cn.idea360.erp.entity;

import lombok.Data;
import lombok.experimental.Accessors;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;

/**
 * @author cuishiying
 */
@Data
@Accessors(chain = true)
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class BaseTable implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "Id", nullable = false)
    private Integer id;

    @CreationTimestamp
    @Column(name = "CreateTime")
    private Timestamp createTime;

    @UpdateTimestamp
    @Column(name = "ChangeTime")
    private Timestamp changeTime;

    @Column(name = "DeleteTime")
    private Timestamp deleteTime;

    @Column(name = "IsDeleted", nullable = false)
    private Boolean isDeleted = false;

    @Column(name = "EmployeeId", nullable = false)
    private Integer employeeId;
}
  1. 一对多生成中间表
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
package com.example.odata.entity;

import lombok.Data;

import javax.persistence.*;
import java.util.List;

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name="FATHER")
public class Father {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    /**
     * 1个Father对应多个Child
     */
    @OneToMany(cascade = CascadeType.PERSIST)
    private List<Child> children;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.example.odata.entity;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name="CHILD")
public class Child {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

}
1
2
3
4
5
6
7
8
9
10
11
12
package com.example.odata.repository;

import com.example.odata.entity.Father;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * @author cuishiying
 */
@Repository
public interface FatherRepository extends JpaRepository<Father, Integer> {
}
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
package com.example.odata;

import com.example.odata.entity.Child;
import com.example.odata.entity.Father;
import com.example.odata.repository.FatherRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.util.Arrays;

import static org.junit.jupiter.api.Assertions.assertEquals;

/**
 * @author cuishiying
 */
@Slf4j
@SpringBootTest
@DisplayName("一对多单元测试, 会生成中间表FATHER_CHILDREN")
public class FatherRepositoryTest {

    @Resource
    private FatherRepository fatherRepository;
    @Resource
    private ObjectMapper objectMapper;

    @DisplayName("插入Father")
    @Test
    void addFather() throws Exception {
        Father father = new Father();
        father.setName("父亲");
        Child child1 = new Child();
        child1.setName("大儿子");
        Child child2 = new Child();
        child2.setName("小女儿");
        father.setChildren(Arrays.asList(child1, child2));
        Father save = fatherRepository.save(father);
        log.info("father: {}", objectMapper.writeValueAsString(save));
        assertEquals("父亲", save.getName());
    }

    @DisplayName("获取Father")
    @Test
    void getFather() throws Exception {
        Father father = fatherRepository.getById(1);
        log.info("father: {}", father);
        assertEquals("父亲", father.getName());
    }
}

sql执行过程

1
2
3
4
5
6
Hibernate: insert into father (name, id) values (?, ?)
Hibernate: insert into child (name, id) values (?, ?)
Hibernate: insert into child (name, id) values (?, ?)
Hibernate: insert into father_children (father_id, children_id) values (?, ?)
Hibernate: insert into father_children (father_id, children_id) values (?, ?)
2023-06-11 00:34:56.892  INFO 26519 --- [           main] com.example.odata.FatherRepositoryTest   : father: {"id":1,"name":"父亲","children":[{"id":2,"name":"大儿子"},{"id":3,"name":"小女儿"}]}

可以看出是先添加一方, 再添加多方, 再建立中间表关联关系

  1. 一对多不生成中间表
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
package com.example.odata.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.Data;

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

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name="tb_user")
@JsonIgnoreProperties(value = {"hibernateLazyInitializer"})
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id")
    private Integer id;

    @Column(name="name")
    private String name;

    /**
     * Todo表新增1列user_id, 内容为User表的id字段
     */
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    private List<Todo> todos;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.example.odata.entity;

import lombok.Data;

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

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name="tb_todo")
public class Todo implements Serializable {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id")
    private Integer id;

    @Column(name="task")
    private String task;

}
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
package com.example.odata;

import com.example.odata.entity.Todo;
import com.example.odata.entity.User;
import com.example.odata.repository.UserRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.util.Arrays;

/**
 * @author cuishiying
 */
@Slf4j
@SpringBootTest
@DisplayName("一对多单元测试, 不会生成中间表")
public class UserRepositoryTest {

    @Resource
    private UserRepository userRepository;
    @Resource
    private ObjectMapper objectMapper;

    @DisplayName("插入User")
    @Test
    void addUser() throws Exception {
        Todo todo = new Todo();
        todo.setTask("完成JPA测试");
        User user = new User();
        user.setName("刘德华");
        user.setTodos(Arrays.asList(todo));
        User save = userRepository.save(user);
        log.info("user: {}", objectMapper.writeValueAsString(save));
    }

    @DisplayName("查询User")
    @Test
    void queryUser() throws Exception {
        User user = userRepository.getById(1);
        log.info("user: {}", objectMapper.writeValueAsString(user));
    }

}

sql执行过程

1
2
3
4
5
6
Hibernate: insert into tb_user (id, name) values (default, ?)
Hibernate: insert into tb_todo (id, task) values (default, ?)
Hibernate: insert into tb_todo (id, task) values (default, ?)
Hibernate: update tb_todo set user_id=? where id=?
Hibernate: update tb_todo set user_id=? where id=?
2023-06-11 02:11:48.230  INFO 29231 --- [           main] com.example.odata.UserRepositoryTest     : user: {"id":1,"name":"刘德华","todos":[{"id":1,"task":"完成JPA测试"},{"id":2,"task":"完成JPA测试2"}]}
  1. 一对多不生成中间表
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
package com.example.odata.entity;

import com.fasterxml.jackson.annotation.JsonManagedReference;
import lombok.Data;
import lombok.EqualsAndHashCode;

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

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name = "books")
public class Book implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String title;

    @EqualsAndHashCode.Exclude
    @JsonManagedReference
    @OneToMany(mappedBy = "book", fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    private Set<Page> pages;
}
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
package com.example.odata.entity;

import com.fasterxml.jackson.annotation.JsonBackReference;
import lombok.Data;

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

/**
 * @author cuishiying
 */
@Data
@Entity
@Table(name = "pages")
public class Page implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private int number;

    private String content;

    @JsonBackReference
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "book_id", nullable = false)
    private Book book;
}
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
package com.example.odata;

import com.example.odata.entity.Book;
import com.example.odata.entity.Page;
import com.example.odata.repository.BookRepository;
import com.example.odata.repository.PageRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

/**
 * @author cuishiying
 */
@Slf4j
@SpringBootTest
@DisplayName("一对多单元测试, 不会生成中间表, 多端维护")
public class BookRepositoryTest {

    @Resource
    private BookRepository bookRepository;
    @Resource
    private PageRepository pageRepository;
    @Resource
    private ObjectMapper objectMapper;

    @DisplayName("插入Book和Page")
    @Test
    void addBook() throws Exception {
        Book book = new Book();
        book.setTitle("JPA双向关联");
        Book save = bookRepository.save(book);
        log.info("book: {}", objectMapper.writeValueAsString(save));

        Page page1 = new Page();
        page1.setNumber(1);
        page1.setContent("第一页");
        page1.setBook(book);
        Page page2 = new Page();
        page2.setNumber(2);
        page2.setContent("第二页");
        page2.setBook(book);
        pageRepository.save(page1);
        pageRepository.save(page2);
        log.info("page1: {}", objectMapper.writeValueAsString(page1));
        log.info("page2: {}", objectMapper.writeValueAsString(page2));
    }

    @DisplayName("查询Book和Page")
    @Test
    void getBook() throws Exception {
        Book book = bookRepository.findById(1).get();
        log.info("book: {}", objectMapper.writeValueAsString(book));
        Page page = pageRepository.findById(2).get();
        log.info("page: {}", objectMapper.writeValueAsString(page));
    }
}

参考

当我遇上你

勿在浮沙筑高楼