JPA Entity with DayOfWeek list

Question

Is it possible to add a HashSet<DayOfWeek> to an entity without creating a new table for it? I would like to store it as json.

@Entity
@Table(name="test")
public class Test{

    @Id
    @GeneratedValue
    private Long id;

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "json")
    private HashSet<DayOfWeek> activeDays;
}

Answer 1

A collection of simple values can be mapped with an @ElementCollection, but this requires a separate table.

For the case of the question, i.e. mapping a set of enumerations to a single column, I like using a bitset - just like Java's EnumSet does under the hoods. We map each member of the enum to a boolean with just 1 bit set, e.g.:

Enum member	boolean
MONDAY	0000001
TUESDAY	0000010
WEDNESDAY	0000100
...and so on	0001000

Mapping the enum to the bitset is simply 1 << dayOfWeek.ordinal(). So a value of e.g. 0000101 means EnumSet.of(WEDNESDAY, MONDAY). Now it is easy to use the bitwise operators of the database to query for membership - e.g. if you want all the Test records that contain TUESDAY, do:

-- This is a pseudocode example!!!
-- Various DBs have different ways to express binary constants and bitwise operators
SELECT * FROM Test WHERE activeDays & 0b0000010 <> 0

This requires a JPA AttributeConverter as follows:

import javax.persistence.AttributeConverter;
import java.time.DayOfWeek;
import java.util.AbstractCollection;
import java.util.Arrays;
import java.util.EnumSet;

public class DayOfWeekConverter implements AttributeConverter<EnumSet<DayOfWeek>, Integer> {
    @Override
    public Integer convertToDatabaseColumn(EnumSet<DayOfWeek> value) {
        return value == null ? null : value.stream().mapToInt(this::toBitmap).reduce(0, (aggr, cur) -> aggr | cur);
    }

    private int toBitmap(DayOfWeek d) {
        return 1 << d.ordinal();
    }

    @Override
    public EnumSet<DayOfWeek> convertToEntityAttribute(Integer dbData) {
        return dbData == null ? null :
                Arrays.stream(DayOfWeek.values())
                        .filter(d -> (toBitmap(d) & dbData) != 0)
                        .collect(
                                () -> EnumSet.noneOf(DayOfWeek.class),
                                AbstractCollection::add,
                                AbstractCollection::addAll
                        );
    }
}

...and an annotation on the field as follows - note that I have used the more efficient EnumSet for the data type:

@Convert(converter = DayOfWeekConverter.class)
private EnumSet<DayOfWeek> activeDays;

You can tweak the converter to store a JSON value or any custom format of the EnumSet you can think of (e.g. a string like "|MONDAY|WEDNESDAY|" is perfectly possible and queryable e.g. as activeDays LIKE '%|MONDAY|%'). I think searching through SQL will be less efficient if you use a string representation, but that is up to you.

Actually, the converter to string as above could be implemented as:

public class DayOfWeekConverter implements AttributeConverter<EnumSet<DayOfWeek>, String> {
    @Override
    public String convertToDatabaseColumn(EnumSet<DayOfWeek> value) {
        return value == null ? null : value.stream().map(Enum::name).collect(Collectors.joining("|","|","|"));
    }

    @Override
    public EnumSet<DayOfWeek> convertToEntityAttribute(String dbData) {
        return dbData == null ? null :
                Arrays.stream(DayOfWeek.values())
                        .filter(d -> dbData.contains('|' + d.name() + '|'))
                        .collect(
                                () -> EnumSet.noneOf(DayOfWeek.class),
                                AbstractCollection::add,
                                AbstractCollection::addAll
                        );
    }
}