4

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?

Improve this question

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yes, changing strategy from javax.persistence.GenerationType.AUTO to GenerationType.IDENTITY helped. With AUTO it was saying that id cannot be null and i must insert data with id set. Yeah, '@DataJpaTest' really looks like a better choice. Thanks.
0 
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.

Improve this answer

5 Comments

I believe i did. I mean, hibernate was ought to do it. spring.jpa.hibernate.ddl-auto=create. It should drop and create tables every time, shouldn't it? How can i check if tests run parallel?
oh... then maybe h2 is broken cause it's saying the thing is already in there while it's trying to insert the same thing.
here is a log prior to the point where exception is thrown. Looks like hibernate drops the table. At least, it's trying to. Hibernate: drop table if exists meals CASCADE Hibernate: drop table if exists orders CASCADE Cannot show you full log here, but it drops tables every time. I'll try another database
depend on on what you are trying to do, you can also add DELETE from meals; at the top of your setup script and yeah make sure to check whether surefire/failsafe or whatever test runner you are using is executing tests in parallel, cause if multiple of them are reaching into the same db at the same time, you can never warranty the state of db.
spring.jpa.hibernate.ddl-auto=create-drop will create tables during application context startup and drop tables on application context closing.
0

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.

Improve this answer

Comments

0

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');
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.