Cannot create table in test (Hibernate & H2)

Question

I have an image mapping table that maps images to the various items that have images. This works as expected when I make the entries in the database by hand, and am retrieving, however when I try to do tests they fail with the below errors. I am using hibernate with h2 for test, and hibernate with mysql for dev.

Here are the errors the table creation is getting.

ERROR - Unsuccessful: create table ImageMapping (image_mapping_id integer not null, category_id integer, image_id integer, product_id integer, product_item_id integer, product_option_id integer generated by default as identity, primary key (product_option_id, image_id), unique (image_id))
ERROR - Attempt to define a second primary key; SQL statement:
create table ImageMapping (image_mapping_id integer not null, category_id integer, image_id integer, product_id integer, product_item_id integer, product_option_id integer generated by default as identity, primary key (product_option_id, image_id), unique (image_id)) [90017-168]

ImageMapping entity.

@JsonAutoDetect
@JsonIgnoreProperties(ignoreUnknown = true)
@Entity
@Table(name="ImageMapping")
public class ImageMapping {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "image_mapping_id")
    private int imageMappingId;

    @Column(name="product_option_id")
    private int productOptionId;

    @Column(name="product_item_id")
    private int productItemId;

    @Column(name="product_id")
    private int productId;

    @Column(name="image_id")
    private int imageId;

    @Column(name="category_id")
    private int categoryId;

Since the other field the error is mentioning is the product_option_id I am including that entity.

@JsonAutoDetect
@JsonIgnoreProperties(ignoreUnknown = true)
@Entity
@Table(name="ProductOptions")
public class ProductOptions implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_option_id")
    private int productOptionId;

    @JoinTable(
            name="ImageMapping",
            joinColumns = @JoinColumn(name = "product_option_id"),
            inverseJoinColumns = @JoinColumn(name = "image_id")
    )
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private Set<Image> productOptionImageGroup;

    @JoinColumn(name = "image_id")
    @OneToOne
    private Image optionImage;

    @Column(name = "product_option_description")
    private String productOptionDescription;

score 2 · Accepted Answer · edited May 23 '17 at 12:15

It looks like you are using the JoinTable annotation incorrectly. You are actually creating the ImageMapping table twice: once for your entity and once in the JoinTable annotation in ProductOptions.

Check out this post about what the join table annotation means: JPA "@JoinTable" annotation

He gives a nice example of how to use it. I'm not exactly what your domain objects are intended for, there seems to be a lot going on in ImageMapping. But one scheme for using a JoinTable could be

Table: Image
id      integer
data    blob
name    varchar

Table: ProductOption
id      integer
desc    varchar
partnum varchar

Table: Image_Option
image_id     integer foreign key
prod_opt_id  integer foreign key

So in this example, I'd have Hibernate entities for Image and ProductOption, and the table Image_Option would only appear in the JoinTable annotation.

Jeff, this was a pretty awesome answer. I had been struggling with this for over a week and could not find anything, even on SO, guess I need to work on how I search because the post you sent was pretty much the same issue, with different title. Thanks again! — zmanc, Apr 23 '13 at 13:30

Cannot create table in test (Hibernate & H2)

1 Answers1