Mysql set datatype column for java set mapping
I am having trouble mapping mysql SET type to Java Set using JPA To illustrate my question, I am creating a random example below
Here is a table that has a column genre that is of type Set (i.e. it will be a set of rows)
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`)
)
Following is the entity class used to display
@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;
}
}
There is no exception with this mapping, but the set is empty in the entity because the get request sent by JPA / hibernate sends a query for all fields in the MusicCD table, but for the genre it sends a separate query to the MusicCD_genre table
When I see the sql schema, there is an autogenerated MusicCD_genre table that is empty. Sending a sql select query for genre to MusicCD returns genres. So how does the Set datatype work in sql and what is the correct annotation to map it?
Update: I've also tried
@TypeDefs({@TypeDef(name = "javaSet", typeClass = HashSet.class)})
and annotate the getter with
@Type(type = "javaSet")
but that doesn't work with EOFException during de-serialization. This can work by replacing the HashSet with the correct type to deserialize.
source to share
I know this is an old question, but I prefer to treat these special type "MySQL" columns in getters / setters when most of their use will 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 it avoids changing the set values without actually changing the DB.
source to share