Mapping EnumSet to mysql Set using JPA 2.1

Question

I am looking way to store EnumSet in mysql column with type set:

@Data
@Entity
@Table(name = "ENTITY_TABLE")
public class Entity implements Serializable {

  @Id
  @GeneratedValue
  @Column(nullable = false)
  @NotNull
  private String id;

  @Column(name = "types")
  private EnumSet<Type> types;

}

Enum of type is defined as below:

public enum Type {
  TYPE1,
  TYPE2,
  TYPE3,
  TYPE4,
  TYPE5
}

And table is defined below:

CREATE TABLE `ENTITY_TABLE` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `types` set('TYPE1','TYPE2','TYPE3','TYPE4','TYPE5') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And insert in table:

INSERT INTO ENTITY_TABLE (types) VALUE ( 'TYPE1','TYPE2')

SET in mysql docs

I'm not familiar with mysql set type. Support for @Enumerated types in JPA is for single column values. — garfield, Feb 09 '17 at 14:07
Given you are using JPA2.1, AttributeConverter will be your best bet. However, you need to figure how you can work with mysql set when you are using JDBC, so you know what to be converted to and from in your AtrributeConverter — Adrian Shum, Feb 10 '17 at 04:10

score 3 · Answer 1 · answered Sep 19 '17 at 14:08

Thanks a lot! I had to use a slightly different version what you have here. What worked for me was:

I had an Enum of permissions that needed to be adjustable:

@Convert(converter = SetConverter.class)
@Column(name = "permission")
private EnumSet<Permission> permission;


//in a util and imported
...
@Converter
public static class SetConverter implements AttributeConverter<EnumSet<Permission>, String> {

    public String convertToDatabaseColumn(EnumSet<Permission> attribute) {
        StringBuilder sb = new StringBuilder();
        for (Permission c : attribute) { 
            sb.append(c + ",");
        }
        return sb.toString();
    }

    public EnumSet<Permission> convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            dbData = "";
        }
        EnumSet<Permission> perm = EnumSet.of(Permission.DEFAULT); //default was a value I added.
        String[] persistencePermissions = StringUtils.trimAllWhitespace(dbData).toUpperCase().split(",");
        if (!StringUtils.isEmpty(StringUtils.trimAllWhitespace(dbData))) {
        try {
            for (String str : persistencePermissions) { 
            perm.add(Permission.valueOf(str));
        }}  
        catch (IllegalArgumentException IAE) {
            throw new Exception("INVALID_REQUEST");
        }}

        return perm;

    }

}

@AlessandroDionisi it depends on your set up. For raw MySQL, see [this SO this asnwer](https://stackoverflow.com/a/5033094/7986053). In Spring Data terms, extending the [CRUD interface](https://docs.spring.io/spring-data/data-commons/docs/1.6.1.RELEASE/reference/html/repositories.html): `findByPermissionIn(EnumSet permissions);` Which, will theoretically work fine and **might** even be OK with [Delta Spike](https://deltaspike.apache.org/), too. I say theoretically because I scratched this code and used an alternative approach with a many to many table. — KeaganFouche, Feb 20 '19 at 10:34
I had a very similar problem, which was solved using this solution, thanks for that. Still I had to realise that EclipseLink 2.5.2 did not like streams in the converter code. Once I refactored it to use old good for loops it worked like a charm. — h3f3st0, Aug 14 '19 at 19:52

jklee · Answer 2 · 2017-02-10T06:43:28.023

Default JPA-Solution for Set

@Data
@Entity
@Table(name = "ENTITY_TABLE")
public class Entity implements Serializable {

    @Id
    @GeneratedValue
    private String id;

    @ElementCollection
    @Enumerated(EnumType.STRING)
    @Column(name = "types")
    private Set<Type> types;

}

Another possibility would be with a AttributeConverter, But I have never tried this with MySQL set.

@Data
@Entity
@Table(name = "ENTITY_TABLE")
public class Entity implements Serializable {

    @Id
    @GeneratedValue
    @Column(nullable = false)
    @NotNull
    private String id;

    @Convert(converter = SetConverter.class)
    @Column(name = "types")
    private EnumSet<Type> types;

}


@Converter
public class SetConverter implements AttributeConverter<EnumSet<Type>, String> {

    @Override
    public String convertToDatabaseColumn(EnumSet<Type> attribute) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public EnumSet<Type> convertToEntityAttribute(String dbData) {
        // TODO Auto-generated method stub
        return null;
    }

}

First approach will not work @ ElementCollection is for values different tables but primitive.Also @ ElementCollection is invalid for EnumSet: org.hibernate.AnnotationException: Illegal attempt to map a non collection as a @ OneToMany, @ ManyToMany or @ CollectionOfElements — Shashank, Feb 10 '17 at 02:18
You are right, I forgot to make enumset to a normal set. This is the default solution. — jklee, Feb 10 '17 at 06:42

Mapping EnumSet to mysql Set using JPA 2.1

2 Answers2

Linked