mysql set datatype column to java set mapping

Question

I having issues in mapping a mysql SET type to Java Set using JPA To illustrate my question i frame a random example below

Here is a table which has a column genre which is of type Set (i.e:it will be a collection of Strings)

CREATE TABLE `MusicCD` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `period` ENUM('Classical', 'Modern','Antique') NOT NULL,
  `genre` SET('horror','thriller','comedy','drama','romance') ,
  PRIMARY KEY (`id`) 
  )

Below is the entity class used for the mapping

@Entity
@Table(name = "MusicCD")
class MusicCD {
private long id;
private Period period;
private Set<String> genre;

//other getter setters // 

@Column(name = "genre")
@ElementCollection(targetClass = String.class, fetch = FetchType.EAGER)
public Set<String> getGenre() {
        return genre;
      }

 public void setGenre(Set<String> genre) {
      this.genre = genre;
    }
}

With this mapping there is no exception but the set is empty in the entity object because the get query sent by JPA/hibernate sents query for all fields in table MusicCD but for the genre it sends a separate query to table MusicCD_genre

When i see the sql schema there is a autogenerated table MusicCD_genre which is empty. Sending a sql select query for genre on MusicCD returns the genres. So how does the Set data type in sql work and what is the correct annotation to map it?

Update: I also tried

 @TypeDefs({@TypeDef(name = "javaSet", typeClass = HashSet.class)})

and annotate the getter with

@Type(type = "javaSet")

but this doesn't work with EOFException during de-serialization. This might work by replacing the HashSet with correct type to deserialize to.

Answer 1

I know it's an old question, but I prefer treat these ´MySQL special type´ columns in the getters/setters when the most use of them would be in java code.

@Entity
@Table(name = "MusicCD")
class MusicCD {
  /*...*/
  @Column(name = "genre")
  private String genreStr;
  /*...*/
  public Set<String> getGenre() {
    if(genreStr == null)
      return Collections.emptySet();
    else
      return Collections.unmodifiableSet(
        new HashSet<String>(Arrays.asList(genreStr.split(",")))
      );
  }

  public void setGenre(Set<String> genre) {
    if(genre == null)
      genreStr = null;
    else
      genreStr = String.join(",", genre);
  }
}

I use the immutable version of Set, because that avoids trying alter the set values without actually alter the DB.