Persist a Map with enum as key and list of objects as value

Question

I'm struggling with persisting a map. First of all here is my database design: I've got one sideloader which can have up to 5 different containersizes. ContainerSize is defined as an enum. For each containersize there are multiple scale entries. So it is a 1 to 5 to n relation.

Currently, I'm representing this design with the following entities:

@Entity
@Table(name = "sideloader")
public class SideloaderEntity {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "currency")
    private Currency currency;

    // TODO
    private Map<ContainerSize, SideloaderContainersizeEntity> scalesPerContainerSize;

@Entity
@Table(name = "sideloader_containersize")
public class SideloaderContainersizeEntity {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    //TODO
    private List<SideloaderScaleEntity> scales;

@Entity
@Table(name = "sideloader_containersize_scale")
public class SideloaderScaleEntity {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long scaleId;

    @Column(name = "upper_bound_distance_km")
    private int upperBoundDistanceKm;

    ...

Now, I am stuck. I'm not sure if i could even map this to my existing database layout. And I don't know which annotations I need to use exactly. I've already read some articles about "how to persist a map", but those articles are always describing the mapping without an existing database layout.

Can you help me, please?

Answer 1

With the help of a colleague and @Smutje's post i came up with the following solution:

@Entity
@Table(name = "sideloader")
public class SideloaderEntity {

    @Column(name = "currency")
    private Currency currency;

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "sideloader_id", referencedColumnName = "id")
    @MapKey(name = "containerSize")
    private Map<ContainerSize, SideloaderContainersizeEntity> scalesPerContainerSize;

@Entity
@Table(name = "sideloader_containersize")
public class SideloaderContainersizeEntity {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Enumerated(EnumType.STRING)
    @Column(name = "container_size")
    private ContainerSize containerSize;

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "sideloader_containersize_id", referencedColumnName = "id")
    private List<SideloaderScaleEntity> scales;

@Entity
@Table(name = "sideloader_containersize_scale")
public class SideloaderScaleEntity {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long scaleId;

    @Column(name = "upper_bound_distance_km")
    private int upperBoundDistanceKm;

    ...

I've tested to persist it with the following code and it seems to work:

@Test
void foo() {

    SideloaderPriceComponent sideloader = new SideloaderPriceComponent();

    SideloaderScaleEntity scale = new SideloaderScaleEntity();
    scale.setUpperBoundDistanceKm(10);

    SideloaderContainersizeEntity containersize = new SideloaderContainersizeEntity();
    containersize.setContainerSize(ContainerSize.FORTY);
    containersize.setScales(Collections.singletonList(scale));

    sideloader.setScalesPerContainerSize(Collections.singletonMap(ContainerSize.FORTY, containersize));

    em.persist(sideloader);
    em.flush();
}

Answer 2

I would try to achieve your result in three steps:

1. Try to map the relationship between SideloaderEntity and SideloaderContainersizeEntity using a List and skip the relationship between SideloaderContainersizeEntity and SideloaderScaleEntity for a moment. To achieve this, it should be totally sufficient to replace private Map<ContainerSize, SideloaderContainersizeEntity> scalesPerContainerSize; with @OneToMany(mappedBy = "sideloaderEntity") private List<SideloaderContainersizeEntity> sideloaderContainersizeEntities; where sideloaderEntity is a @ManyToOne private SideloaderEntity sideloaderEntity field of your SideloaderContainersizeEntity.
2. Try to replace the List with a Map: Replace private List<SideloaderContainersizeEntity> sideloaderContainersizeEntities; with @MapKey(name = "containerSize") private Map<ContainerSize, SideloaderContainersizeEntity> scalesPerContainerSize; where containerSize is a private ContainerSize containerSize field of your SideloaderContainersizeEntity.
3. Add the relationship between SideloaderContainersizeEntity and SideloaderScaleEntity as a List.