jpa 唯一索引冲突的H2错误的集成测试-加载测试@Sql数据后的旧id序列？

zkure5ic  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(94)

错误消息为：

Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?)

字符串
完整的控制台输出：

:: Spring Boot ::                (v3.0.5)

2023-05-10T11:16:05.732+02:00  INFO 1302958 --- [           main] c.t.s.controller.LayoutControllerTest    : Starting LayoutControllerTest using Java 17.0.1 with PID 1302958 (started by stephane in /home/stephane/dev/java/projects/sql-fetch-all)
2023-05-10T11:16:05.734+02:00  INFO 1302958 --- [           main] c.t.s.controller.LayoutControllerTest    : No active profile set, falling back to 1 default profile: "default"
2023-05-10T11:16:06.576+02:00  INFO 1302958 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-05-10T11:16:06.678+02:00  INFO 1302958 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 84 ms. Found 3 JPA repository interfaces.
2023-05-10T11:16:07.286+02:00  INFO 1302958 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2023-05-10T11:16:07.521+02:00  INFO 1302958 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:9c41a542-2a01-4464-8528-da45f652e5fd user=SA
2023-05-10T11:16:07.523+02:00  INFO 1302958 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2023-05-10T11:16:07.575+02:00  INFO 1302958 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-05-10T11:16:07.622+02:00  INFO 1302958 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.1.7.Final
2023-05-10T11:16:07.970+02:00  INFO 1302958 --- [           main] SQL dialect                              : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2023-05-10T11:16:09.024+02:00  INFO 1302958 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2023-05-10T11:16:09.034+02:00  INFO 1302958 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-05-10T11:16:09.890+02:00  WARN 1302958 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-05-10T11:16:10.320+02:00  INFO 1302958 --- [           main] o.s.b.t.m.w.SpringBootMockServletContext : Initializing Spring TestDispatcherServlet ''
2023-05-10T11:16:10.320+02:00  INFO 1302958 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Initializing Servlet ''
2023-05-10T11:16:10.324+02:00  INFO 1302958 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Completed initialization in 2 ms
2023-05-10T11:16:10.354+02:00  INFO 1302958 --- [           main] c.t.s.controller.LayoutControllerTest    : Started LayoutControllerTest in 4.985 seconds (process running for 6.219)
2023-05-10T11:16:10.773+02:00  WARN 1302958 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23505, SQLState: 23505
2023-05-10T11:16:10.773+02:00 ERROR 1302958 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?) [23505-214]
2023-05-10T11:16:10.775+02:00  INFO 1302958 --- [           main] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
[ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 5.997 s <<< FAILURE! - in com.thalasoft.sqlfetchall.controller.LayoutControllerTest
[ERROR] should_create_one  Time elapsed: 0.435 s  <<< ERROR!
jakarta.servlet.ServletException: 
Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?) [23505-214]]
    at com.thalasoft.sqlfetchall.controller.LayoutControllerTest.should_create_one(LayoutControllerTest.java:74)
Caused by: org.springframework.dao.DataIntegrityViolationException: 
could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?) [23505-214]]
    at com.thalasoft.sqlfetchall.controller.LayoutControllerTest.should_create_one(LayoutControllerTest.java:74)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at com.thalasoft.sqlfetchall.controller.LayoutControllerTest.should_create_one(LayoutControllerTest.java:74)
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 
Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?) [23505-214]
    at com.thalasoft.sqlfetchall.controller.LayoutControllerTest.should_create_one(LayoutControllerTest.java:74)

2023-05-10T11:16:10.855+02:00  INFO 1302958 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2023-05-10T11:16:10.856+02:00  INFO 1302958 --- [ionShutdownHook] .SchemaDropperImpl$DelayedDropActionImpl : HHH000477: Starting delayed evictData of schema as part of SessionFactory shut-down'
2023-05-10T11:16:10.868+02:00  INFO 1302958 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2023-05-10T11:16:10.873+02:00  INFO 1302958 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

型
测试用例为：

@SpringBootTest
@AutoConfigureMockMvc
@DisplayNameGeneration(DisplayNameGenerator.ReplaceUnderscores.class)
@SqlGroup({
  @Sql(value = "classpath:fixture/data.reset.sql", executionPhase = BEFORE_TEST_METHOD),
  @Sql(value = "classpath:fixture/data.init.sql", executionPhase = BEFORE_TEST_METHOD)
})
public class LayoutControllerTest {

  @Test
  void should_create_one() throws Exception {
    final File jsonFile = new ClassPathResource("fixture/data.layout.json").getFile();
    final String content = Files.readString(jsonFile.toPath());

    this.mockMvc.perform(post("/layout/create/{profileId}", 1)
        .contentType(APPLICATION_JSON)
        .content(content))
        .andDo(print())
        .andExpect(status().isCreated())
        .andExpect(jsonPath("$").isMap())
        .andExpect(jsonPath("$", aMapWithSize(4)))
        .andExpect(jsonPath("$.name").value("Another name"));

    assertThat(this.layoutRepository.findAll()).hasSize(1);
  }

型
SQL数据文件包括：

SET REFERENTIAL_INTEGRITY   FALSE;
TRUNCATE TABLE layout RESTART IDENTITY;
TRUNCATE TABLE profile RESTART IDENTITY;
TRUNCATE TABLE profile_type RESTART IDENTITY;
TRUNCATE TABLE product RESTART IDENTITY;
TRUNCATE TABLE product_part RESTART IDENTITY;
TRUNCATE TABLE layout_product RESTART IDENTITY;
SET REFERENTIAL_INTEGRITY   TRUE;

INSERT INTO profile_type (id, profile_type_enum) VALUES (1, 'CAR');

INSERT INTO profile (id, profile_type_id) VALUES (1, 1);

INSERT INTO layout (id, name, profile_id) VALUES (1, 'My simple layout', 1);

INSERT INTO product (id, name, supplier) VALUES (1, 'Lamp', 'Sun');

INSERT INTO product_part (id, name, serial_number, product_id) VALUES (1, 'Shade', 'AA123FR', 1);
INSERT INTO product_part (id, name, serial_number, product_id) VALUES (2, 'Bulb', 'BF43944', 1);
INSERT INTO product_part (id, name, serial_number, product_id) VALUES (3, 'Cable', 'KF84324', 1);

INSERT INTO layout_product (id, layout_id, product_id) VALUES (1, 1, 1);

型
发布内容的json文件：

{ "name": "Another name" }

型
测试性能：

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:demo;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: create-drop
    defer-datasource-initialization: true

spring:
  sql:
    init:
      mode: always

型
该实体是：

@Entity
@Table(name = "layout")
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Layout {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  private Long id;

  @NotNull
  private String name;

  @NotNull
  @ManyToOne(optional = false, fetch = FetchType.LAZY)
  private Profile profile;

  @OneToMany(mappedBy="layout", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
  private Set<LayoutProduct> layoutProducts;

  public void addProduct(LayoutProduct layoutProduct) {
    layoutProducts.add(layoutProduct);
    layoutProduct.setLayout(this);
  }

  public void removeProduct(LayoutProduct layoutProduct) {
    layoutProducts.remove(layoutProduct);
    layoutProduct.setLayout(null);
  }
}

型
仓库是：

@Repository
public interface LayoutRepository extends JpaRepository<Layout, Long> {

  @Query("SELECT new com.thalasoft.sqlfetchall.data.model.domain.projection.LayoutView(l, pf, pt, lp, pd) FROM LayoutProduct lp JOIN lp.layout l JOIN l.profile pf JOIN pf.profileType pt JOIN lp.product pd WHERE l.id = :id")
  List<LayoutView> findByIdFetching(@Param("id") Long id);

}

型
控制器是：

@PostMapping("/create/{profileId}")
  @ResponseStatus(HttpStatus.CREATED)
  public LayoutGetDto create(@PathVariable("profileId") Long profileId, @RequestBody LayoutPostDto layoutDto) {
    layoutDto.setProfileDto(this.layoutMapper.entityToDto(profileService.findById(profileId)));
    LayoutGetDto ldto = this.layoutMapper.entityToDto(this.layoutService.create(this.layoutMapper.dtoToEntity(layoutDto)));
    return ldto;
  }

型
该服务是：

public Layout create(Layout layout) {
    final var newLayout = Layout.builder()
        .name(layout.getName())
        .profile(layout.getProfile())
        .build();
    return this.layoutRepository.save(newLayout);
  }

型
错误是由this.layoutRepository.save(newLayout);调用触发的。
调试器显示newLayout对象的预期json内容。
请注意，未能插入的布局的名称不是在json内容文件中找到的名称，而是在加载的数据中找到的名称。
其他关于查找一个或全部以及删除的测试用例都通过了。
如果删除以下SQL语句：

INSERT INTO layout (id, name, profile_id) VALUES (1, 'My simple layout', 1);
INSERT INTO layout_product (id, layout_id, product_id) VALUES (1, 1, 1);

型
在另一个数据文件的副本中，只有失败的测试使用，那么测试通过罚款。所以测试最初失败，因为它试图添加一个布局时，另一个已经存在相同的id值。
奇怪的是这个输出：

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.LAYOUT(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'My simple layout', CAST(1 AS BIGINT))"; SQL statement:
insert into layout (name, profile_id, id) values (?, ?, ?) [23505-214]]

型
这是否意味着测试正在尝试插入名为My simple layout的布局？或者这是否意味着该布局已经存在于数据库中，并且是冲突的来源？
该项目是在Java 17与spring-boot-starter-parent 3.0.5
我已经看到了这个question，并试图采取行动，但它没有改变任何错误。
从这个question我怀疑问题来自序列ID生成器，它在数据加载后没有更新。所以数据加载插入一个布局，后来在测试中尝试插入一个ID值为1的布局，因为它的序列在数据加载时没有更新。这只是一个猜测。
我还尝试将id生成策略：@GeneratedValue(strategy = GenerationType.SEQUENCE)替换为@GeneratedValue(strategy = GenerationType.IDENTITY)，但在错误中没有任何改变。
更新：我编辑了数据加载文件，将值1的布局ID替换为值2，如下所示：

INSERT INTO layout (id, name, profile_id) VALUES (2, 'My simple layout', 1);
INSERT INTO layout_product (id, layout_id, product_id) VALUES (1, 2, 1);

型
所有的测试都通过了
这告诉我，在使用布局id值1手动插入之后，id序列没有刷新，并且序列仍然为测试的插入提供相同的值1，这导致了约束冲突。

jpa

1条答案

按热度按时间
093gszye

093gszye1#

正如您在最新更新中解释的问题的根本原因，基于此策略GenerationType.IDENTITY for id列。
1.如果你需要id值作为其他脚本的FK列（像你的情况），最好使用你的业务永远不会满足它的大数字，如1000。
1.如果你在其他数据SQL语句中不需要id值，最好跳过此列插入。

赞(0）分享  回复(0）举报  11个月前
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com