I have a problem when PUTing or PATCHing my Profile entity via Spring Data Rest. The error happens when I PUT or PATCH the same or new data for the "skills" or the "jobs" property.
This is what curl returns:
{"cause":{"cause":{"cause":null,"message":"Unique index or primary key violation: \"PRIMARY_KEY_6E ON PUBLIC.TRAINER_PROFILE_JOBS(TRAINER_PROFILE_ID, JOBS_ID) VALUES (77, 79, 2)\"; SQL statement:\ninsert into trainer_profile_jobs (trainer_profile_id, jobs_id) values (?, ?) [23505-197]"},"message":"could not execute statement"},"message":"could not execute statement; SQL [n/a]; constraint [\"PRIMARY_KEY_6E ON PUBLIC.TRAINER_PROFILE_JOBS(TRAINER_PROFILE_ID, JOBS_ID) VALUES (77, 79, 2)\"; SQL statement:\ninsert into trainer_profile_jobs (trainer_profile_id, jobs_id) values (?, ?) [23505-197]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement"}
The application produces the following output:
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY_KEY_6E ON PUBLIC.TRAINER_PROFILE_JOBS(TRAINER_PROFILE_ID, JOBS_ID) VALUES (77, 79, 2)"; SQL statement:
insert into trainer_profile_jobs (trainer_profile_id, jobs_id) values (?, ?) ...
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY_KEY_6E ON PUBLIC.TRAINER_PROFILE_JOBS(TRAINER_PROFILE_ID, JOBS_ID) VALUES (77, 79, 2)"; SQL statement:
insert into trainer_profile_jobs (trainer_profile_id, jobs_id) values (?, ?) [23505-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) ~[h2-1.4.197.jar:1.4.197]
at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.197.jar:1.4.197]
at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.197.jar:1.4.197]
at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:101) ~[h2-1.4.197.jar:1.4.197]
at org.h2.mvstore.db.MVSecondaryIndex.requireUnique(MVSecondaryIndex.java:236) ~[h2-1.4.197.jar:1.4.197]
at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:202) ~[h2-1.4.197.jar:1.4.197]
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:732) ~[h2-1.4.197.jar:1.4.197]
at org.h2.command.dml.Insert.insertRows(Insert.java:182) ~[h2-1.4.197.jar:1.4.197]
at org.h2.command.dml.Insert.update(Insert.java:134) ~[h2-1.4.197.jar:1.4.197]
at org.h2.command.CommandContainer.update(CommandContainer.java:102) ~[h2-1.4.197.jar:1.4.197]
at org.h2.command.Command.executeUpdate(Command.java:261) ~[h2-1.4.197.jar:1.4.197]
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199) ~[h2-1.4.197.jar:1.4.197]
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153) ~[h2-1.4.197.jar:1.4.197]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.7.9.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
... 149 common frames omitted
There's only one way currently to make it work: 1. PUT or PATCH with an empty array for "jobs" and "skills" 2. Then PUT or PATCH again with the new data
This is what the entity looks like before I try to PUT or PATCH data:
curl -s -XGET -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" http://localhost:2222/trainerProfiles/66
{
...
"jobs" : [ {
"period" : {
"start" : "2015-01-02",
"end" : "2017-01-25"
},
"company" : "Lorem ipsum dolor sit amet",
"sector" : "Lorem ipsum dolor sit amet",
"position" : "Lorem ipsum dolor sit amet",
"description" : "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat."
} ],
"skills" : [ {
"topic" : "java",
"level" : "EXPERT"
}, {
"topic" : "spring",
"level" : "EXPERT"
} ],
"_links" : {
...
}
}%
As you can see, the skills and jobs properties are embedded into the profile entity since I did not create an exported JPA repository for them. This is intended.
To give you the full picture of my entities, I need to introduce some base classes that are inherited from:
public interface BaseEntity {
public Long getId();
public void setId(Long id);
}
@Data
@MappedSuperclass
public class AbstractBaseEntity implements BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
}
@Data
@EqualsAndHashCode(callSuper=true)
@MappedSuperclass
public abstract class AbstractOwnedEntity extends AbstractBaseEntity implements OwnedEntity {
@NotNull
@ManyToOne
private Company owner;
}
The is the entity exposed via Spring Data Rest which give me a headache:
@NoArgsConstructor
@Data
@EqualsAndHashCode(callSuper = true)
@Entity
public class Profile extends AbstractOwnedEntity {
...
@NotNull
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<JobEntry> jobs = new HashSet<JobEntry>();
@NotNull
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<SkillEntry> skills = new HashSet<SkillEntry>();
public Profile(...) {
...
}
public void addJob(JobEntry job) {
this.jobs.add(job);
}
public void addSkill(SkillEntry skill) {
this.skills.add(skill);
}
...
}
The JPE Repository is a very basic CrudRepository with some SPEL in the form of @PreAuthorize, @PostFilter, etc to implement access control (omitted here)
@RepositoryRestResource
public interface TrainerProfileRepository extends CrudRepository<TrainerProfile, Long> {
...
}
