使用Spring JPA和H2数据库,在应用程序重新启动后,ID不是连续的。

huangapple go评论86阅读模式
英文:

With Spring JPA and H2 database the ID isn't consequent after app restart

问题

我正在使用Spring Boot开发一个简单的Web应用程序。我使用带有内嵌(本地)H2数据库的JPA。项目启动时,Hibernate会自动创建所有表格和连接。这运行得很好。起初,我使用h2内置控制台手动运行SQL查询进行测试。

以下是相关文件:

pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

application.yml(第一次ddl-auto:create,然后是update):

spring:
    jpa:
        open-in-view: false
        hibernate:
            ddl-auto: update
        database-platform: org.hibernate.dialect.H2Dialect
    datasource:
        driverClassName: org.h2.Driver
        url: jdbc:h2:file:~/Documents/database/blog
        username: hazazs
        password: {password}
    h2:
        console:
            enabled: true
            path: /database

Blog.java:

@Entity(name = "BLOGS")
public class Blog implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false)
    private String title;
    @Column(nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date posted;
    @Column(columnDefinition = "TEXT", nullable = false)
    private String content;
    @ManyToOne
    private Blogger blogger;
}

Blogger.java:

@Entity(name = "BLOGGERS")
public class Blogger implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true, nullable = false)
    private String email;
    @Column(nullable = false)
    private String password;
    @Column(nullable = false)
    private boolean gender;
    @Column(unique = true, nullable = false)
    private String username;
    @ManyToMany
    @JoinTable(
        name = "BLOGGERS_ROLES",
        joinColumns = {@JoinColumn(name = "BLOGGER_ID")},
        inverseJoinColumns = {@JoinColumn(name = "ROLE_ID")}
    )
    private Set<Role> roles = new HashSet<>();
    @OneToMany(mappedBy = "blogger", fetch = FetchType.EAGER)
    private Set<Blog> blogs = new HashSet<>();
    @Column(unique = true, nullable = false)
    private String code;
    @Column(nullable = false)
    private boolean enabled;
}

Role.java:

@Entity(name = "ROLES")
public class Role implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true, nullable = false)
    private String name;
    @ManyToMany(mappedBy = "roles", fetch = FetchType.EAGER)
    private Set<Blogger> bloggers = new HashSet<>();
}

在第一次运行应用程序时,ID会自动递增1。但是,当我重新启动应用程序并再次连接数据库时,ID也会递增1,但始终从32开始。因此,如果我首次在h2控制台中手动运行查询INSERT INTO ROLES (NAME) VALUES ('USER'),它会正确地插入ID为1的用户。但是,如果在应用程序重新启动后运行查询INSERT INTO ROLES (NAME) VALUES ('ADMIN'),它会将ID插入为33而不是2。第三次运行从65开始,依此类推。

我已经尝试过以下方法(没有结果):

  • 使用@GeneratedValue的4个GenerationType
  • 在h2依赖中使用<scope>runtime</scope>
  • 数据库URL:url: jdbc:h2:file:~/Documents/database/blog;DB_CLOSE_ON_EXIT=FALSE

我应该如何实现,在上一次应用程序运行结束的地方继续ID递增?

英文:

I'm working on a simple webapplication with Spring Boot. I use JPA with embedded (local) H2 database.
When I start the project, Hibernate makes all the tables and connection automatically. It works fine.
In the beginning I use the h2 built-in console to run SQL queries manually for testing.

Here are my related files:
pom.xml:

&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;com.h2database&lt;/groupId&gt;
    &lt;artifactId&gt;h2&lt;/artifactId&gt;
&lt;/dependency&gt;

application.yml (First time ddl-auto: create, then update):

spring:
    jpa:
        open-in-view: false
        hibernate:
            ddl-auto: update
        database-platform: org.hibernate.dialect.H2Dialect
    datasource:
        driverClassName: org.h2.Driver
        url: jdbc:h2:file:~/Documents/database/blog
        username: hazazs
        password: {password}
    h2:
        console:
            enabled: true
            path: /database

Blog.java

@Entity(name = &quot;BLOGS&quot;)
public class Blog implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false)
    private String title;
    @Column(nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date posted;
    @Column(columnDefinition = &quot;TEXT&quot;, nullable = false)
    private String content;
    @ManyToOne
    private Blogger blogger;

Blogger.java

@Entity(name = &quot;BLOGGERS&quot;)
public class Blogger implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true, nullable = false)
    private String email;
    @Column(nullable = false)
    private String password;
    @Column(nullable = false)
    private boolean gender;
    @Column(unique = true, nullable = false)
    private String username;
    @ManyToMany
    @JoinTable(
        name = &quot;BLOGGERS_ROLES&quot;,
        joinColumns = {@JoinColumn(name = &quot;BLOGGER_ID&quot;)},
        inverseJoinColumns = {@JoinColumn(name = &quot;ROLE_ID&quot;)}
    )
    private Set&lt;Role&gt; roles = new HashSet&lt;&gt;();
    @OneToMany(mappedBy = &quot;blogger&quot;, fetch = FetchType.EAGER)
    private Set&lt;Blog&gt; blogs = new HashSet&lt;&gt;();
    @Column(unique = true, nullable = false)
    private String code;
    @Column(nullable = false)
    private boolean enabled;

Role.java

@Entity(name = &quot;ROLES&quot;)
public class Role implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true, nullable = false)
    private String name;
    @ManyToMany(mappedBy = &quot;roles&quot;, fetch = FetchType.EAGER)
    private Set&lt;Blogger&gt; bloggers = new HashSet&lt;&gt;();

With the first application run the ID increments automatically by 1. But then I restart my application and connect the database again, however it increments by 1 as well, but it starts with 32 all the time.
So if I run the query INSERT INTO ROLES (NAME) VALUES (&#39;USER&#39;) first time manually in the h2 console, it inserts USER with ID 1 correctly.
But if I run the query INSERT INTO ROLES (NAME) VALUES (&#39;ADMIN&#39;) after the application restart, it inserts ADMIN with ID 33 instead of 2.
The third run starts with 65 and so on.

I have tried so far (with no result):
-the 4 GenerationType for @GeneratedValue
-&lt;scope&gt;runtime&lt;/scope&gt; in the h2 dependency
-database url: url: jdbc:h2:file:~/Documents/database/blog;DB_CLOSE_ON_EXIT=FALSE

How could I achieve, that the ID continues from where it finished at the previous application run?

答案1

得分: 3

如何重新启动应用程序?所有的关闭钩子都应该被调用,进程应该进行优雅关闭。

有关优雅关闭,请参阅下面的URL。

https://stackoverflow.com/q/26547532/6572971

数据库对于序列使用了32个条目的缓存,自增是通过序列内部实现的。如果系统在没有关闭数据库的情况下崩溃,最多会丢失这么多个数字。这类似于其他数据库中序列的工作方式。在这种情况下,并不能保证序列值会不间断地生成。

关闭所有连接将会关闭数据库,如果应用程序正常停止(使用关闭钩子),则数据库会被关闭。

英文:

How do you restart the app? All the shutdown hooks should be called and the process should be a graceful shutdown.

Refer below URL for graceful shutdown.

https://stackoverflow.com/q/26547532/6572971

The database uses a cache of 32 entries for sequences, and auto-increment is internally implemented a sequence. If the system crashes without closing the database, at most this many numbers are lost. This is similar to how sequences work in other databases. Sequence values are not guaranteed to be generated without gaps in such cases.

Closing all connections will close the database, and the database is closed if the application is stopped normally (using a shutdown hook).

huangapple
  • 本文由 发表于 2020年9月30日 02:30:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/64125581.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定