Spring Boot – 测试仓库 – 引用完整性约束违规

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

Spring Boot - testing repository - Referential integrity constraint violation

问题

I see that you're encountering a "Referential integrity constraint violation" error in your Spring Boot app when testing your repositories. This error typically occurs when there's a foreign key constraint violation, meaning that a foreign key value in one table does not exist in the referenced table.

In your case, it seems to be related to the POST_USER_ID foreign key in the POST table, which references the USERS table. Here are a few things to check:

  1. Make sure that you have a user with the ID you're trying to reference (in this case, 11) in your test database. You can double-check your FakeDataSet.getFakeUser() method to ensure it's creating a user with the correct ID.

  2. Verify that the POST_USER_ID in the POST table matches the USER_ID in the USERS table correctly. Ensure that both columns have the same data type.

  3. Check if there are any database constraints or triggers that might affect this relationship.

  4. Review your data initialization and test steps to ensure you're creating and persisting entities in the correct order.

  5. Make sure that your test database is properly configured and set up for running JPA tests.

Without the full context of your application and database schema, it's challenging to pinpoint the exact issue, but these are common areas to investigate when encountering referential integrity constraint violations in JPA-based applications.

英文:

I've got a problem that I was struggling for a while. I'm trying to test repository of my Spring Boot app, but unfortunelly when i was sure how to test repositories I stucked here getting an erorr "Naruszenie więzów integralności" which means "Referential integrity constraint violation". I thing I tried most of solutions that I found on the stack internet, but none of them words for me ;/.
I have got an error like below:

17:42:20.639 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Naruszenie więzów integralności: "FKKKTWEXQ2WLBTEVF2AXEQH8ANX: PUBLIC.POST FOREIGN KEY(POST_USER_ID) REFERENCES PUBLIC.USERS(USER_ID) (11)"
Referential integrity constraint violation: "FKKKTWEXQ2WLBTEVF2AXEQH8ANX: PUBLIC.POST FOREIGN KEY(POST_USER_ID) REFERENCES PUBLIC.USERS(USER_ID) (11)"; SQL statement:
insert into post (data, date, description, imagine_name, imagine_type, post_user_id, tittle, post_id) values (?, ?, ?, ?, ?, ?, ?, ?) [23506-200]
17:42:20.641 [main] DEBUG org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl - JDBC transaction marked for rollback-only (exception provided for stack trace)
java.lang.Exception: exception just for purpose of providing stack trace
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.markRollbackOnly(JdbcResourceLocalTransactionCoordinatorImpl.java:324)
	at org.hibernate.engine.transaction.internal.TransactionImpl.markRollbackOnly(TransactionImpl.java:203)
	at org.hibernate.internal.AbstractSharedSessionContract.markForRollbackOnly(AbstractSharedSessionContract.java:396)
	at org.hibernate.internal.ExceptionConverterImpl.handlePersistenceException(ExceptionConverterImpl.java:297)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1364)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1347)
	at org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager.flush(TestEntityManager.java:171)
	at com.company.art.repository.PostRepositoryTests.findByPostUser_Id_One(PostRepositoryTests.java:45)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	...

And that's my code:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = ArtApplication.class, loader = AnnotationConfigContextLoader.class)
@DataJpaTest
public class PostRepositoryTests {
    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private PostRepository postRepository;

    @Test
    public void findByPostUser_Id_One(){
        //arrange
        Post post = new Post();
        User user = FakeDataSet.getFakeUser();
        post.setPostUser(user);
        Integer expected = 1;
        entityManager.merge(FakeDataSet.getFakeUser());
        entityManager.flush();

        entityManager.merge(post);
        entityManager.flush(); // <----- line with error
        //act
        List<Post> found = postRepository.findByPostUser_Id(FakeDataSet.getFakeUser().getId());
        //assert
        assertThat(found.size()).isEqualTo(expected);
    }
}

Repository:

@Repository
public interface PostRepository extends JpaRepository<Post, Integer> {
    List<Post> findByPostUser_Id(Integer id);
}

My fake data class:

public class FakeDataSet {
    public static User getFakeUser(){
        User user = new User();
        user.setId(11);
        user.setActive(true);
        user.setUserName("TheTest");
        user.setPassword("Password");
        user.setEmail("test@test.com");
        user.setName("Test");
        user.setLastName("Testing");
        return user;
    }

    public static Post getFakePost(){
        Post post = new Post();
        post.setTittle("Title");
        post.setId(1);
        post.setImagineType("image/png");
        post.setPostUser(FakeDataSet.getFakeUser());
        post.setData(new byte[5]);
        post.setImagineName("Image");
        post.setDate(Calendar.getInstance());
        post.setDescription("Description");

        return post;
    }
}

Model classes:

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "post")
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "post_id")
    private Integer id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "post_user_id")
    private User postUser;
    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "date")
    private Calendar date;
    @Column(name = "imagine_name")
    private String imagineName;
    @Column(name = "imagine_type")
    private String imagineType;
    @Lob
    private byte[] data;
    @Column(name = "description", length = 500)
    @Length(max = 500)
    private String description;
    @Column(name = "tittle", length = 30)
    @Length(max = 30)
    private String tittle;
    @Nullable
    @OneToMany(mappedBy = "likedPost", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<PostLikes> postLikes;
    @Transient
    private MultipartFile file;
}
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_id")
    private int id;
    @Column(name = "user_name")
    @Length(min = 5, message = "*Your user name must have at least 5 characters")
    @NotEmpty(message = "*Please provide a user name")
    private String userName;
    @Column(name = "email")
    @Email(message = "*Please provide a valid Email")
    @NotEmpty(message = "*Please provide an email")
    private String email;
    @Column(name = "password")
    @Length(min = 5, message = "*Your password must have at least 5 characters")
    @NotEmpty(message = "*Please provide your password")
    private String password;
    @Column(name = "name")
    @NotEmpty(message = "*Please provide your name")
    private String name;
    @Column(name = "last_name")
    @NotEmpty(message = "*Please provide your last name")
    private String lastName;
    @Column(name = "active")
    private Boolean active;
    @ManyToMany(cascade = CascadeType.MERGE)
    @JoinTable(name = "users_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;
    @OneToMany(mappedBy = "postUser", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    private Set<Post> posts;
    @OneToMany(mappedBy = "userThatLike", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<PostLikes> postsLiked;
    @Column(name = "is_banned")
    private boolean isBanned;

}

Do you have any idea what I messed up?
Thank for any tip! ;D

答案1

得分: 1

Your user id is auto generated, change your test to the following

@Test
public void findByPostUser_Id_One(){
//arrange
Post post = new Post();
User user = FakeDataSet.getFakeUser();
post.setPostUser(user);
Integer expected = 1;
entityManager.merge(user);
entityManager.flush();

entityManager.merge(post);
entityManager.flush(); // <----- line with error
//act
List<Post> found = postRepository.findByPostUser_Id(FakeDataSet.getFakeUser().getId());
//assert
assertThat(found.size()).isEqualTo(expected);

}

英文:

Your user id is auto generated, change your test to the following

    @Test
    public void findByPostUser_Id_One(){
        //arrange
        Post post = new Post();
        User user = FakeDataSet.getFakeUser();
        post.setPostUser(user);
        Integer expected = 1;
        entityManager.merge(user);
        entityManager.flush();

        entityManager.merge(post);
        entityManager.flush(); // <----- line with error
        //act
        List<Post> found = postRepository.findByPostUser_Id(FakeDataSet.getFakeUser().getId());
        //assert
        assertThat(found.size()).isEqualTo(expected);
    }

huangapple
  • 本文由 发表于 2020年8月12日 00:22:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/63362359.html
匿名

发表评论

匿名网友

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

确定