[FIXED] How to generate sequential numbers for child entities per parent entity

Issue

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?

Solution

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()

}

Answered By - Denis
Answer Checked By - Candace Johnson (JavaFixing Volunteer)