Here is a projects with issues:
@Entity
public class Project {
@Id
@GeneratedValue
private UUID id;
@Transient
@Formula("(select max(Issue.number) from Issue where Issue.project_id = id)")
private int maxIssueNumber;
public int getMaxIssueNumber() {
return maxIssueNumber;
}
}
@Entity
@Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "project_id", "number" }) })
public class Issue {
@Id
@GeneratedValue
private UUID id;
@NotNull
@ManyToOne
private Project project;
@NotNull
private Integer number;
public void setProject(Project project) {
this.project = project;
}
public Integer getNumber() {
return number;
}
@PrePersist
void prePersist() {
number = project.getMaxIssueNumber() 1;
}
}
I need to assign sequential numbers for each issue per project. Like Jira does. The code above doesn't work. If I persist two issues maxIssueNumber
is zero:
@Test
void numberIsIncremented() {
Project project = new Project();
projectRepository.saveAndFlush(project);
Issue issue = new Issue();
issue.setProject(project);
issueRepository.saveAndFlush(issue);
Issue issue2 = new Issue();
issue2.setProject(project);
issueRepository.saveAndFlush(issue2); // fail, unique constraint violated
testEntityManager.clear();
Issue foundIssue = issueRepository.getById(issue.getId());
assertEquals(1, foundIssue.getNumber()); // success
Issue foundIssue2 = issueRepository.getById(issue2.getId());
assertEquals(2, foundIssue2.getNumber()); // fail, number = 1 (if unique constraint is disabled)
}
Could you please suggest how to fix it? Or maybe I should use another approach?
Another approach is to persist maxIssueNumber
. But it's an extra column, and I'll have to update projects on issue insertion. And also it seems non-trivial to implement as well.
Maybe there is a database-level approach like triggers, etc. I use PosgreSQL. Or custom issue insert SQL statement. But it looks too complicated.
One more approach is to query maximum number using an entity repository and set issue number explicitly. But maybe it's possible to implement it on the entity level?
CodePudding user response:
The only working solution I found is to customize save() methods on repository level:
public interface IssueRepository extends JpaRepository<Issue, UUID>, IssueRepositoryExtension {
@Override
default <S extends Issue> S saveAndFlush(S entity) {
return extendedSaveAndFlush(entity);
}
// Similar for save(), saveAll(), saveAllAndFlush()
}
public interface IssueRepositoryExtension {
<S extends Issue> S extendedSaveAndFlush(S entity);
// Similar for save(), saveAll(), saveAllAndFlush()
}
public class IssueRepositoryExtensionImpl implements IssueRepositoryExtension {
@Autowired
private EntityManager entityManager;
@Override
public <S extends Issue> S extendedSaveAndFlush(S entity) {
Query query = entityManager.createQuery(
"select max(number) from Issue issue where issue.project.id = ?1");
query.setParameter(1, entity.getProject().getId());
Object result = query.getSingleResult();
int maxIssueNumber = result != null ? (int) result : 0;
entity.setNumber(maxIssueNumber 1);
entityManager.persist(entity);
entityManager.flush();
return entity;
}
// Similar for save(), saveAll(), saveAllAndFlush()
}