CODEWITHSUNDEEP
mysqlhibernatemysql-error-1170
Jason Novak
Jason Novak

Reputation: 1091

What are the hibernate annotations used to persist a Map with an enumerated type as a key?

I am having trouble getting the right hibernate annotations to use on a Map with an enumerated class as a key. Here is a simplified (and extremely contrived) example.

public class Thing {
    public String id;
    public Letter startLetter;
    public Map<Letter,Double> letterCounts = new HashMap<Letter, Double>(); 
}


public enum Letter {
    A,
    B,
    C,
    D
}

Here are my current annotations on Thing

@Entity
public class Thing {

    @Id
    public String id;

    @Enumerated(EnumType.STRING)
    public Letter startLetter;

    @CollectionOfElements
    @JoinTable(name = "Thing_letterFrequencies", joinColumns = @JoinColumn(name = "thingId"))
    @MapKey(columns = @Column(name = "letter", nullable = false))
    @Column(name = "count")
    public Map<Letter,Double> letterCounts = new HashMap<Letter, Double>();

}

Hibernate generates the following DDL to create the tables for my MySql database

create table Thing (id varchar(255) not null, startLetter varchar(255), primary key (id)) type=InnoDB;
create table Thing_letterFrequencies (thingId varchar(255) not null, count double precision, letter tinyblob not null, primary key (thingId, letter)) type=InnoDB;

Notice that hibernate tries to define letter (my map key) as a tinyblob, however it defines startLetter as a varchar(255) even though both are of the enumerated type Letter. When I try to create the tables I see the following error

BLOB/TEXT column 'letter' used in key specification without a key length

I googled this error and it appears that MySql has issues when you try to make a tinyblob column part of a primary key, which is what hibernate needs to do with the Thing_letterFrequencies table. So I would rather have letter mapped to a varchar(255) the way startLetter is.

Unfortunately, I've been fussing with the MapKey annotation for a while now and haven't been able to make this work. I've also tried @MapKeyManyToMany(targetEntity=Product.class) without success. Can anyone tell me what are the correct annotations for my letterCounts map so that hibernate will treat the letterCounts map key the same way it does startLetter?

Upvotes: 1

Views: 5014

Answers (2)

Yuri.Bulkin
Yuri.Bulkin

Reputation: 1414

If you use java6, you may try to use @MapKeyEnumerated (javax.persistence) annotation instead of @MapKey

@ElementCollection
    @JoinTable(name = "thing_letter_frequencies", joinColumns = @JoinColumn(name = "thing_id"))
    @MapKeyEnumerated(EnumType.STRING)
    @Column(name = "letter_count")
    public Map<Letter, Double> letterCount = new HashMap<Letter, Double>();

UPDATE:

Whole class code (using Project Lombok)

@Entity
public class Thing {

    @Getter
    @Setter
    @Id
    private String id;

    @Getter
    @Setter
    @Enumerated(EnumType.STRING)
    private Letter startLetter;

    @Getter
    @Setter
    @ElementCollection
    @JoinTable(name = "thing_letter_frequencies", joinColumns = @JoinColumn(name = "thing_id"))
    @MapKeyEnumerated(EnumType.STRING)
    @Column(name = "letter_count")
    public Map<Letter, Double> letterCount = new HashMap<Letter, Double>();
}

What hibernate generates in MySQL (create table statements):

CREATE TABLE `thing` (
    `id` VARCHAR(255) NOT NULL,
    `startLetter` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

CREATE TABLE `thing_letter_frequencies` (
    `thing_id` VARCHAR(255) NOT NULL,
    `letter_count` DOUBLE NULL DEFAULT NULL,
    `letterCount_KEY` VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`thing_id`, `letterCount_KEY`),
    INDEX `FKA0A7775246D72F41` (`thing_id`),
    CONSTRAINT `FKA0A7775246D72F41` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

Upvotes: 0

Jason Novak
Jason Novak

Reputation: 1091

I found something that works on https://forum.hibernate.org/viewtopic.php?f=1&t=999270&start=0 although it is kind of ugly. If you assume that Letter is in the com.myexample package here are the annotations

@CollectionOfElements
@JoinTable(name = "Thing_letterFrequencies", joinColumns = @JoinColumn(name = "thingId"))
@MapKey(columns = @Column(name = "letter"),
    type = @Type(
      type="org.hibernate.type.EnumType",
      parameters = {@Parameter(name = "enumClass", value="com.myexample.Letter"), @Parameter(name="type", value="12")}
    ))
@Column(name = "count")
public Map<Letter,Double> letterCounts = new HashMap<Letter, Double>();

Note the @Parameter(name="type", value="12") Apparently the "value=12" maps the enumerated type to a varchar. Hopefully this helps someone else out but if anyone has a cleaner annotation without the use of magic numbers like 12 I want to hear it.

Upvotes: 0

Related Questions