Hibernate @ManyToMany not possible due to invalid types

Question

My target is it to store a recipe entity which can have multiple ingredients whereby multiple ingredients can be used in different recipes. (Step 2: Ingredients should not be saved twice.) But Hibernate cannot even save at least the recipe with ingredients. It always leads to exception based on wrong types and so on. Here is the mapping and the examples:

Database:

            Table "public.ingredient_recipe_rel"
         Column         |         Type          | Modifiers 
------------------------+-----------------------+-----------
 irr_rcp_id             | bigint                | not null
 irr_ing_ingredient     | character varying(40) | not null
 irr_ing_acc_identifier | bigint                | not null
Indexes:
    "ingredient_recipe_rel_pkey" PRIMARY KEY, btree (irr_ing_ingredient, irr_ing_acc_identifier, irr_rcp_id)
    "fk_irr_ing_ingredient_idx" btree (irr_ing_ingredient)
    "fk_irr_rcp_id_idx" btree (irr_rcp_id)
Foreign-key constraints:
    "fk_irr_ing_ingredient" FOREIGN KEY (irr_ing_ingredient, irr_ing_acc_identifier) REFERENCES ingredient(ing_ingredient, ing_acc_identifier)
    "fk_irr_rcp_id" FOREIGN KEY (irr_rcp_id) REFERENCES recipe(rcp_id)

                                           Table "public.recipe"
       Column       |            Type             |                        Modifiers                        
--------------------+-----------------------------+---------------------------------------------------------
 rcp_id             | bigint                      | not null default nextval('recipe_rcp_id_seq'::regclass)
 rcp_title          | character varying(100)      | not null
 rcp_description    | text                        | not null
 rcp_acc_identifier | bigint                      | not null
 rcp_defaultpersons | integer                     | not null
 rcp_difficulty     | integer                     | 
 rcp_rating         | integer                     | 
 rcp_idletime       | integer                     | 
 rcp_cooktime       | integer                     | 
 rcp_calories       | character varying(20)       | 
 rcp_source         | character varying(500)      | 
 rcp_photourl       | character varying(512)      | 
 rcp_updatetime     | timestamp without time zone | not null
 rcp_cookcounter    | integer                     | not null
Indexes:
    "recipe_pkey" PRIMARY KEY, btree (rcp_id)
    "fk_rcp_acc_identifier_idx" btree (rcp_acc_identifier)
Foreign-key constraints:
    "fk_rcp_acc_identifier" FOREIGN KEY (rcp_acc_identifier) REFERENCES accesskey(acc_identifier)
Referenced by:
    TABLE "ingredient_recipe_rel" CONSTRAINT "fk_irr_rcp_id" FOREIGN KEY (irr_rcp_id) REFERENCES recipe(rcp_id)
    TABLE "recipe_basiccategory_rel" CONSTRAINT "fk_rbc_rcp_id" FOREIGN KEY (rbc_rcp_id) REFERENCES recipe(rcp_id)

                                           Table "public.ingredient"
       Column       |          Type          |                            Modifiers                            
--------------------+------------------------+-----------------------------------------------------------------
 ing_ingredient     | character varying(40)  | not null
 ing_acc_identifier | bigint                 | not null
 ing_amount         | real                   | 
 ing_unit           | character varying(20)  | 
 ing_rcp_id         | bigint                 | not null default nextval('ingredient_ing_rcp_id_seq'::regclass)
 ing_group          | character varying(100) | 
Indexes:
    "ingredient_pkey" PRIMARY KEY, btree (ing_ingredient, ing_acc_identifier)
    "fk_ing_identifier_idx" btree (ing_acc_identifier)
Foreign-key constraints:
    "fk_ing_acc_identifier" FOREIGN KEY (ing_acc_identifier) REFERENCES accesskey(acc_identifier)
Referenced by:
    TABLE "ingredient_recipe_rel" CONSTRAINT "fk_irr_ing_ingredient" FOREIGN KEY (irr_ing_ingredient, irr_ing_acc_identifier) REFERENCES ingredient(ing_ingredient, ing_acc_identifier)

RECIPE

@Entity
@Table(name = "RECIPE")
public class Recipe implements Serializable
{
    private static final long serialVersionUID = 1L;

    @SequenceGenerator(allocationSize=1, initialValue=1, sequenceName="recipe_rcp_id_seq", name="recipe_rcp_id_seq")
    @GeneratedValue(generator="recipe_rcp_id_seq", strategy=GenerationType.SEQUENCE)
    @Id
    @Column(name = "rcp_id")
    private Long rcpId;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "INGREDIENT_RECIPE_REL",
            joinColumns = {
                @JoinColumn(name = "irr_rcp_id", nullable = false, updatable = false)
            },
            inverseJoinColumns = {
                @JoinColumn(name = "irr_ing_ingredient", nullable = false, updatable = false)
                @JoinColumn(name = "irr_ing_acc_identifier", nullable = false, updatable = false)
            })
    private List<Ingredient> ingredients = new ArrayList<>();

    //More code, getters, setters
}

INGREDIENT

@Entity
@IdClass(IngredientPk.class)
@Table(name = "INGREDIENT")
public class Ingredient implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ing_ingredient")
    private String ingredient;

    @Id
    @Column(name = "ing_acc_identifier")
    private Long identifier;

    @ManyToMany(mappedBy = "ingredients")
    private List<Recipe> recipes;

    //More code, getters, setters
}

This code is not even deployable in Wildfly. I get the following exceptions, but I don't understand why because it's the task of Hibernate to apply the correct types:

Caused by: org.hibernate.HibernateException: Wrong column type in public.ingredient_recipe_rel for column irr_ing_ingredient. Found: varchar, expected: int8

Nevertheless, I tried to change my recipe entity (although I had no need to use this attempt before):

@Entity
@Table(name = "RECIPE")
public class Recipe implements Serializable
{
    private static final long serialVersionUID = 1L;

    @SequenceGenerator(allocationSize=1, initialValue=1, sequenceName="recipe_rcp_id_seq", name="recipe_rcp_id_seq")
    @GeneratedValue(generator="recipe_rcp_id_seq", strategy=GenerationType.SEQUENCE)
    @Id
    @Column(name = "rcp_id")
    private Long rcpId;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "INGREDIENT_RECIPE_REL",
            joinColumns = {
                @JoinColumn(name = "irr_rcp_id", columnDefinition = "INT8 NOT NULL")
            },
            inverseJoinColumns = {
                @JoinColumn(name = "irr_ing_ingredient", columnDefinition = "VARCHAR(40) NOT NULL"),
                @JoinColumn(name = "irr_ing_acc_identifier", columnDefinition = "INT8 NOT NULL")
            })
    private List<Ingredient> ingredients = new ArrayList<>();

    //More code, getters, setters
}

This code is deployable in Wildfly but it looks like that this code is still wrong. When I try to save a recipe which two ingredients, I get the following exception when I try to execute the following test:

@Test
public void persistenceOfRecipeWithIngredientSuccessful()
{
    //given
    createUser(USER_ID1);

    long recipeIdentifier = 1;
    long ingredient1Identifier = 2;
    long ingredient2Identifier = 3;

    //create identifiers to fulfill FK constraint
    createAccessKey(recipeIdentifier);
    createAccessKey(ingredient1Identifier);
    createAccessKey(ingredient2Identifier);

    //setting of mandatory fields are omitted to shorten code
    Recipe recipe = new Recipe();
    recipe.setIdentifier(recipeIdentifier);

    List<Recipe> recipes = new ArrayList<>();
    recipes.add(recipe);
    List<Ingredient> ingredients = new ArrayList<>();

    Ingredient ingredient1 = new Ingredient();
    ingredient1.setIngredient("Flour");
    ingredient1.setIdentifier(ingredient1Identifier);
    ingredient1.setRecipes(recipes);
    ingredients.add(ingredient1);

    Ingredient ingredient2 = new Ingredient();
    ingredient2.setIngredient("Sugar");
    ingredient2.setIdentifier(ingredient2Identifier);
    ingredient2.setRecipes(recipes);
    ingredients.add(ingredient2);

    recipe.setIngredients(ingredients);

    //when
    RecipeService recipeService = new RecipeService();
    recipeService.setEntityManager(getEntityManager());
    getEntityManager().getTransaction().begin();
    //PERSIST CRASHES.....
    recipeService.getEntityManager().persist(recipe);
    getEntityManager().getTransaction().commit();

    //then
    //doing asserts...
}

Caused by: org.postgresql.util.PSQLException: ERROR: column "irr_ing_acc_identifier" is of type bigint but expression is of type character varying Hint: You will need to rewrite or cast the expression.

Any ideas about what is going on here?

@HassamAbdelillah The initial ones, but since then I added some manually. But these mappings are not generated. — Bevor, Apr 16 '16 at 18:50
I got some issues with Eclipse entities generation process when having composite keys once. the @IdClass IngredientPK class have the right types either ? — Hassam Abdelillah, Apr 16 '16 at 18:58
@HassamAbdelillah Yes they have. (Eclipse does not generate IdClasses, it created @EmbeddedIds) — Bevor, Apr 16 '16 at 19:07

score 0 · Accepted Answer · answered Apr 16 '16 at 19:55

Ok, after hours I figured out what's wrong. Obviously I have to define the referenced columns. The solution looks like this now:

@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "INGREDIENT_RECIPE_REL",
        joinColumns = {
            @JoinColumn(name = "irr_rcp_id", referencedColumnName = "rcp_id")
        },
        inverseJoinColumns = {
            @JoinColumn(name = "irr_ing_ingredient", referencedColumnName = "ing_ingredient"),
            @JoinColumn(name = "irr_ing_acc_identifier", referencedColumnName = "ing_acc_identifier")
        })

Hibernate @ManyToMany not possible due to invalid types

1 Answers1