I am using Java 21, Spring Boot 3+ and Spring Data JPA. While making some changes, I am facing a problem with spring data jpa, mapping between entities causing undesired effects when being translated to SQL statements. I have the following database schema:
CREATE TABLE `user`
(
`id` varchar(255),
`username` varchar(40),
`first_name` varchar(40),
`last_name` varchar(40),
PRIMARY KEY (`id`)
);
CREATE TABLE `authority`
(
`authority` varchar(255) NOT NULL,
`user_id` varchar(255) NOT NULL,
PRIMARY KEY (`authority`, `user_id`),
KEY `FK6edqftupukawiexbmveftr` (`user_id`),
CONSTRAINT `FK6edqftupukiexbmveftdyrr` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);
Below is the mapping in java:
@Entity
@IdClass(AuthorityKey.class)
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Authority {
@Id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private User user;
@Id
@Enumerated(EnumType.STRING)
private AuthorityType authority;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Authority)) return false;
Authority authorityObj = (Authority) o;
if (!Objects.equals(user, authorityObj.user)) return false;
return Objects.equals(authority, authorityObj.authority);
}
@Override
public int hashCode() {
return Objects.hash(user, authority);
}
@Override
public String toString() {
return "Authority{" +
"user='" + user + '\'' +
", authority='" + authority + '\'' +
'}';
}
}
@Entity
@Table(name = "User")
@Getter
@Setter
public class User {
@Id
private String id;
private String username;
private String firstName;
private String lastName;
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<Authority> authorities = new HashSet<>();
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof User)) return false;
return id != null && id.equals(((User) o).getId());
}
@Override
public int hashCode() {
return Objects.hashCode(id);
}
@Override
public String toString() {
return "User{" +
"id='" + id + '\'' +
", username='" + username + '\'' +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
'}';
}
}
public class AuthorityKey implements Serializable {
private User user;
private AuthorityType authority;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof AuthorityKey)) return false;
AuthorityKey authorityKey = (AuthorityKey) o;
if (Objects.equals(user,authorityKey.user)) return false;
return authority == authorityKey.authority;
}
@Override
public int hashCode() {
return Objects.hash(user, authority);
}
@Override
public String toString() {
return "AuthorityKey{" +
"user='" + user + '\'' +
", authority='" + authority + '\'' +
'}';
}
}
Below are the repositories for the user and authority:
@Repository
public interface AuthorityRepository extends JpaRepository<Authority, AuthorityKey> {
@Query("SELECT a.authority FROM Authority a WHERE a.user.id = :userId")
Set<AuthorityType> findAuthorityTypesByUserId(@Param("userId") String userId);
}
public interface UserRepository extends JpaRepository<User, String>{
}
Here is the sut that is causing issues:
@Service
public class AuthorityService {
public void assignAuthoritiesToUser(String userId, Set<AuthorityType> authorities) {
User user = userRepository.findById(userId).orElseThrow(() -> new UserNotFoundException("User not found"));
Set<AuthorityType> authoritiesToAssign = new HashSet<>(authorities);
Set<AuthorityType> existingAuthorityTypes = authorityRepository.findAuthorityTypesByUserId(userId);
if (!existingAuthorityTypes.isEmpty()) {
authoritiesToAssign.removeAll(existingAuthorityTypes);
}
transactionTemplate.execute(status -> {
authoritiesToAssign.stream().map(authorityValue -> {
Authority authority = new Authority();
authority.setAuthority(authorityValue);
authority.setUser(user);
return authority;
})
.forEach(authorityRepository::save);
return null;
});
}
}
Below is the test class:
@Test
@DisplayName("Should assign authorities to user")
void shouldAssignAuthoritiesToUser() {
//setup
String userId = "xst3147-68f0-42o8-a444-191478d4c86y7";
Set<AuthorityType> authorities = Set.of(USER_READ, USER_CREATE);
//execute
authorityService.assignAuthoritiesToUser(userId, authorities);
//verify
authorities = authorityRepository.findAuthorityTypesByUserId(userId);
assertThat(authorities).isNotEmpty();
assertThat(authorities).hasSize(2);
}
The error is like below:
: could not execute statement [Duplicate entry 'xst3147-68f0-42o8-a444-191478d4c86y7' for key 'user.PRIMARY'] [insert into user (first_name,last_name,username,id) values (?,?,?,?)]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:62)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:107)
at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:40)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:52)
at org.hibernate.persister.entity.mutation.InsertCoordinator.doStaticInserts(InsertCoordinator.java:175)
at org.hibernate.persister.entity.mutation.InsertCoordinator.coordinateInsert(InsertCoordinator.java:113)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2873)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:363)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1423)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:504)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2339)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1996)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:561)
... 41 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'xst3147-68f0-42o8-a444-191478d4c86y7' for key 'user.PRIMARY'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
Hibernate Logs statements:
Hibernate:
select
u1_0.id,
u1_0.first_name,
u1_0.last_name,
u1_0.username
from
user u1_0
where
u1_0.id=?
Hibernate:
select
a1_0.authority
from
authority a1_0
where
a1_0.user_id=?
Hibernate:
select
a1_0.authority,
a1_0.user_id
from
authority a1_0
where
(
a1_0.authority, a1_0.user_id
) in ((?, ?))
Hibernate:
select
null,
u1_0.first_name,
u1_0.last_name,
u1_0.username
from
user u1_0
where
u1_0.id=?
Hibernate:
insert
into
authority
(authority, user_id)
values
(?, ?)
Hibernate:
insert
into
user
(first_name, last_name, username, id)
values
(?, ?, ?, ?)
It seems when saving an authority entity, it tries to insert a record in the user table. Normally it shouldn't happen since the record is already in the user table.
I will appreciate any help in solving this issue.
Best Regards, Rando.
P.S
The exception is thrown in save method below:
authoritiesToAssign.stream().map(authorityValue -> {
Authority authority = new Authority();
authority.setAuthority(authorityValue);
authority.setUser(user);
return authority;
})
.forEach(authorityRepository::save); // this method throws the exceptions
the complete stack trace
org.springframework.dao.DataIntegrityViolationException: could not execute statement [Duplicate entry 'xst3147-68f0-42o8-a444-191478d4c86y7' for key 'user.PRIMARY'] [insert into user (first_name,last_name,username,id) values (?,?,?,?)]; SQL [insert into user (first_name,last_name,username,id) values (?,?,?,?)]; constraint [user.PRIMARY]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:290)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:565)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:794)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:757)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152)
at com.accnt.app.identityaccesscontrol.authority.service.AuthorityService.assignAuthoritiesToUser(AuthorityService.java:101)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:64)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:58)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.security.authorization.method.AuthorizationManagerBeforeMethodInterceptor.invoke(AuthorizationManagerBeforeMethodInterceptor.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717)
at com.accnt.app.identityaccesscontrol.authority.service.AuthorityService$$SpringCGLIB$$0.assignAuthoritiesToUser(<generated>)
at com.accnt.app.identityaccesscontrol.authority.service.AuthorityServiceIT$UserWithAuthorities.shouldAssignAuthoritiesToUser(AuthorityServiceIT.java:134)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement [Duplicate entry 'xst3147-68f0-42o8-a444-191478d4c86y7' for key 'user.PRIMARY'] [insert into user (first_name,last_name,username,id) values (?,?,?,?)]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:62)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:107)
at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:40)
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:52)
at org.hibernate.persister.entity.mutation.InsertCoordinator.doStaticInserts(InsertCoordinator.java:175)
at org.hibernate.persister.entity.mutation.InsertCoordinator.coordinateInsert(InsertCoordinator.java:113)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2873)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:363)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1423)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:504)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2339)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1996)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:561)
... 41 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'xst3147-68f0-42o8-a444-191478d4c86y7' for key 'user.PRIMARY'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194)
userRepository.findById(userId) should be in the same hibernate session as authorityRepository::save. Otherwise user entity get detached. It seems that hibernate can't understand that that entity already in database and try to save it.