Populating database with test data during integration tests with Spring Boot

user_15294843 0
Viewed 2159

I'm trying to build simple CRUD with Spring Boot. Spring Data Jpa is used to deal with persistence. Here are my entities

@Entity(name = "meal")
@Table(name = "meals")
public class Meal extends AbstractBaseEntity {

@Column(name = "name", nullable = false)
private String name;
@Column(name = "cost", nullable = false)
private BigDecimal cost;
@Column(name = "cooking_time", nullable = false)
private Duration cookingTime;

...

getters, setters and constructors omitted

@Entity(name = "order")
@Table(name = "orders")
public class Order extends AbstractBaseEntity {

@Column(name = "total_cooking_time", nullable = false)
private Duration totalCookingTime;
@Column(name = "total_cost", nullable = false)
private BigDecimal totalCost;

...

superclass, which contains generated id looks like this

@MappedSuperclass
public abstract class AbstractBaseEntity {

@Id
@GeneratedValue
protected Long id;

...

Also i'm using JPA 2.1 scheme auto generation with H2 in memory database.

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:restaurant
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.h2.console.enabled=true

And i have data.sql file in my project resources folder to populate db with test data. Here how it looks.

INSERT INTO meals VALUES (1, 900000000000, 122.5, 'Soup');
INSERT INTO meals VALUES (2, 1800000000000, 155.5, 'Stake');

Everything works fine when i run it with SpringApplication class. Tables gets created and populated. But when i'm trying to run tests - all modifying methods, fails with DataIntegrityViolationException due to primary key uniqueness constraint violation. It looks like it is trying to populate database with test data twice. I'm using Spring Data repositories to acces data.

@Repository
public interface MealJpaRepository
        extends JpaRepository<Meal, Long> {

}

My test class looks like this.

@SpringBootTest
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
public class RestaurantApplicationTest {

    @Autowired
    MealJpaRepository repository;

    @Test
    void getAll() {
        assertEquals(repository.findAll().size(), 2);
    }

    @Test
    void getById() {
        assertNotNull(repository.findById(1L));
    }

    @Test
    void create() {
        Meal fish = new Meal("Fish", new BigDecimal("250.0"), Duration.ofMinutes(13));
        repository.save(fish);
        assertTrue(repository.findAll().stream()
                .anyMatch(meal -> meal.getName().equals("Fish") && meal.getCost().equals(new BigDecimal("250.0"))));
    }

}

As i have already mentioned - get methods succeed, speaking of create method - here is my stacktrace.

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.MEALS(ID) [1, 900000000000, 122.50, 'Soup']"; SQL statement:
insert into meals (cooking_time, cost, name, id) values (?, ?, ?, ?) [23505-200]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:276)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy84.save(Unknown Source)
    at com.example.restaurant.RestaurantApplicationTest.create(RestaurantApplicationTest.java:36)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:688)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:210)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:206)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:131)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:65)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:75)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3302)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3829)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
    at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:723)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:345)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:93)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
    ... 80 more
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.MEALS(ID) [1, 900000000000, 122.50, 'Soup']"; SQL statement:
insert into meals (cooking_time, cost, name, id) values (?, ?, ?, ?) [23505-200]

Original idea was to reinitialize and repopulate database before every test method. What am i doing wrong?

spring-data-jpa spring-boot hibernate spring java
4 Answers

If you look at the logs:

Hibernate: create sequence hibernate_sequence start with 1 increment by 1

create table meals (id bigint not null, ...)

Hibernate: call next value for hibernate_sequence
Hibernate: insert into meals (cooking_time, cost, name, id) values (?, ?, ?, ?)

Since the next value for sequence is 1 and the record with id value 1 is already saved ((1, 900000000000, 122.5, 'Soup')), it throws DataIntegrityViolationException

To fix the issue, you don't need to change the sql script and use the id values 100, 200. You need to set the id generation strategy to GenerationType.IDENTITY.

If you use GenerationType.IDENTITY

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected Long id;

the logs generated:

Hibernate: create table meals (id bigint generated by default as identity, ...)

Hibernate: insert into meals (id, cooking_time, cost, name) values (null, ?, ?, ?)

Hibernate will use the automatically incremented value based on the id column. Since the last saved ids are 1 and 2, the next value will be 3.

You can see by simply adding a print statement:

repository.save(fish);
System.out.println("id of fish: " + fish.getId());

Output:

id of fish: 3

If you try with saving ids 100 and 200 on data.sql, then you'll see the output:

id of fish: 201

PS: @SpringBootTest loads the full application context. @DataJpaTest which loads only JPA related components, would be a better choice to test JPA repositories.

user_103674710 
repopulate database before every test method

Did you drop all data, clean up after each test ? Cause it is saying

Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.MEALS(ID) [1, 900000000000, 122.50, 'Soup']"

Also mind if you let the tests run sequential or parallel.

user_7542500

You can annotate your test suites with @Transactional. It means that every test method in your suite is surrounded by an overarching Spring transaction. This transaction will be rolled back at the end of the test method regardless of it's outcome.

user_152953650

Doing some debug i found out the problem was that database id generating sequence knew nothing about data i inserted. So, when hibernate was trying to get next value from the sequence - it was 1. And a row with such id already existed in database.

Hibernate: call next value for hibernate_sequence
Hibernate: insert into meals (cooking_time, cost, name, id) values (?, ?, ?, ?)
2021-02-27 15:17:02.998  WARN 10168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: -104, SQLState: 23505
2021-02-27 15:17:02.999 ERROR 10168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : integrity constraint violation: unique constraint or index violation; SYS_PK_10099 table: MEALS

And this crude temporary solutions proves my guess. I have changed sql script this way and it works like intended now. But, of course, i must investigate further and let database generate id's for test data automatically.

INSERT INTO meals (id, cooking_time, cost, name) VALUES (100, 900000000000, 122.5, 'Soup');
INSERT INTO meals (id, cooking_time, cost, name) VALUES (200, 1800000000000, 155.5, 'Stake');
user_152948430
Related