mysql将数据类型列设置为java设置映射 [英] mysql set datatype column to java set mapping

问题描述

在将 mysql SET 类型映射到使用JPA的Java集 为了说明我的问题，我在下面给出一个随机示例

这是一个表，其中的列类型为Set类型(即它将是字符串的集合)

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

下面是用于映射的实体类

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

使用此映射没有例外，但是实体对象中的集合为空，因为JPA/休眠发送的get查询发送了对MusicCD表中所有字段的查询，但是对于流派，它将单独的查询发送到了MusicCD_genre表中

当我看到sql模式时，有一个自动生成的表MusicCD_genre为空. 在MusicCD上发送关于类型的sql选择查询会返回该类型. 那么sql中的Set数据类型是如何工作的，以及映射它的正确注释是什么?

更新: 我也尝试过

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

并用

注释吸气剂

@Type(type = "javaSet")

，但这在反序列化期间不能与EOFException一起使用. 这可以通过将HashSet替换为反序列化的正确类型来实现.

解决方案

我知道这是一个老问题，但是我更喜欢在getters/setter中使用这些"MySQL special type"列，因为它们在Java中使用最多代码.

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

我使用Set的不可变版本，因为这样可以避免在不实际更改数据库的情况下尝试更改设置值.

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.

解决方案

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.

这篇关于mysql将数据类型列设置为java设置映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！