How do I prevent Lists returned from a JPA Repository having null values

Question

I have parent-child object model where Category has a list of Products. This is biderectional, the Product has a parentCategory property.

This is modelled as a OneToMany and ManyToOne JPA Mapping.

@Data
@Entity
public class Category {
    @Id private String id;

    private String displayName;

    @OneToMany(
            mappedBy = "parentCategory",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    @OrderColumn(name = "seqNo")
    private List<Product> products;
}

and

@Data
@Entity
public class Product {
    @Id
    private String id;

    private String displayName;
    
    @ToString.Exclude
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    private Category parentCategory;

    @JsonIgnore
    private int seqNo;
}

Because the order of products is important, and I want the user to manually reorder them, I have added a seqNo property/column and have specified the @OrderColumn.

When moving products up or down, I do

Collections.swap(products, index, index + 1);

or

Collections.swap(products, index, index - 1);

as required, and then repository.save(category)

The Product List gats saved with seqNo in the correct order, but often (but not always) there are gaps in the sequence, giving rise to null elements in the list.

Likewise, when assigning a new Category to a Product, I do

oldParentCategory.getProducts().remove(product);
newParentCategory.addProduct(product); // this sets parentCategory on Product as well as adds Product to Category's products List
categoryRepository.saveAll(Arrays.asList(new Category[]{oldParentCategory, newParentCategory}));

This also leaves a gap in the old Category's sequence numbers.

What am I doing wrong? and how do I fix it?

(P.S.: My JPA implementation is Hibernate [via Spring Boot]. My database is file-based H2 with a pretty basic setup [in development], if that's relevant)

Answer 1

Taking my cue from @Chris (https://stackoverflow.com/users/496099/chris), I made sure that my seqNo values were always reconciled before saving, with

public void reconcileSequenceNumbers() {
        products.stream().forEach(p -> {
            int oldSeqNo = p.getSeqNo();
            int newSeqNo = products.indexOf(p);

            if (newSeqNo != oldSeqNo) {
                p.setSeqNo(newSeqNo);
            }
        });
    }