72214

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

<a href="https://dev.mysql.com/doc/refman/5.7/en/set.html" rel="nofollow">SET in mysql docs</a>

Answer1:

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; } }

Answer2:

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 <a href="https://docs.oracle.com/javaee/7/api/javax/persistence/AttributeConverter.html" rel="nofollow">AttributeConverter</a>, 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; } }

Recommend

  • GROK Parsing with regex
  • Simple Injector: How can I skip verification of an object in the container
  • elasticsearch upsert without document id
  • guice assisted inject + multibinding + generics
  • mysql not unique auto increment, primary key two fields
  • 2 count(*)+group by+having+join
  • Checking if URL exists - HTTP Request always returns an exception
  • Get enum value from string
  • How can I use C++ enum types like C#?
  • PostgreSQL primary key auto increment crashes in C++
  • What is a good persistence design for this entity hierarchy?
  • How to find data from last week in MySQL
  • Why Encoding.ASCII != ASCIIEncoding.Default in C#?
  • one Local Olampyad Questions on Informatic in 2011
  • Word Open XML Mail Merge
  • Replace value with Factor in r data.table
  • Database structure design with variable amounts of fields
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Spray.io: When (not) to use non-blocking route handling?
  • Submit form in a displaytag pagination
  • Modifying destination and filename of gulp-svg-sprite
  • Adding custom controls to a full screen movie
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • Unanticipated behavior
  • Comma separated Values
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • KeystoneJS: Relationships in Admin UI not updating
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • LevelDB C iterator
  • Sorting a 2D array using the second column C++
  • How can i traverse a binary tree from right to left in java?
  • java string with new operator and a literal
  • Net Present Value in Excel for Grouped Recurring CF