How to associate a parent table with two unrelated Child tables

Question

I want to join an order table to with a different item table (book or food) based on the item_type value. If item_type is 0, item_id should be from the book table. If item_type is 1, item_id should be from the food table.

Below are the sample tables. I hope they can help you understand my question.

create table order{
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  item_type int,
  item_id int
}

create table book{
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  desc varchar(100)
}

create table food{
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  field1 varchar(100)
}

I have tried using the @wherejointable annotaion.

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "item_id",insert="false" update="false")
@WhereJoinTable(clause = "item_type=0")
public Book getBook() {

}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "item_id",insert="false" update="false")
@WhereJoinTable(clause = "item_type=1")
public Food getFood() {

}

However, I get the following error:

Repeated column in mapping for entity: column: item_id (should be mapped with insert="false" update="false")

Is this possible to achieve in hibernate?

I don't know your uses for the `order` table, but I would imagine it would be easier to separate `order` into two tables, `BookOrder` and `FoodOrder`, than try to selectively join to different tables depending on the "type" of the row. — MattLBeck, Feb 15 '15 at 12:42
Are you allowed to change fields or add some tables to your database? If yes, then the best would be to have a base table with fields common to food and books, and then join orders table to this new table, i.e. 'items'. If modifying the schema is not an option, let's see what's best... — fps, Feb 15 '15 at 12:56

score 1 · Accepted Answer · answered Feb 15 '15 at 12:55

1

You need to use the @Any annotation for non-inheritable joins:

@Any(metaColumn = @Column(name = "ITEM_TYPE"))
@AnyMetaDef(idType = "int", metaType = "int", 
        metaValues = { 
         @MetaValue(targetEntity = Book.class, value = "0"),
         @MetaValue(targetEntity = Food.class, value = "1")
   })
@JoinColumn(name="ITEM_ID")
private Object item;

So item can be loaded as a Book or as a Food.

answered Feb 15 '15 at 12:55

Vlad Mihalcea

142,745
71
566
911

Thanks , it should be correct answer. By searching "any" annotation to understand your answer. I found question http://stackoverflow.com/questions/217831/how-to-use-hibernate-any-related-annotations . The uses of "any" will solve my problem. – Gary Li Feb 15 '15 at 13:47

score 0 · Answer 2 · answered Feb 15 '15 at 16:53

I have another solution to solve this problem , ie to use Inheritance for the Order table. In your question you have to put two types of orders and Object oriented approach those types are BookOrder and FoodOrder and they share the same table by using the Single Table Inheritance Strategy.

Book class.

@Entity
public class Book {

    @Id
    @GeneratedValue
    private int    id;

    private String description;
}

Food Class

@Entity
public class Food {
    @Id
    @GeneratedValue
    private int    id;

    private String field1;
}

Let us create an abstract order class

@MappedSuperclass
public abstract class Order {

    @Id
    @GeneratedValue
    private int id;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

}

Now we create two subclasses to this Order table and we use single table inheritance, so we need to provide the discrimination column (this is the column using which the hibernate maps to the object) and we define that to "item_type" column.

BookOrder

@Entity
@Table(name = "orders")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "item_type", discriminatorType = DiscriminatorType.STRING)
@DiscriminatorValue(value = "0")
public class BookOrder extends Order {

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "item_id")
    private Book book;

}

Now we have the FoodOrder table which again extends the Order table

@Entity
@Table(name = "orders")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "item_type", discriminatorType = DiscriminatorType.STRING)
@DiscriminatorValue("1")
@Data
public class FoodOrder extends Order {

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "item_id")
    private Food food;

}

The above mappings creates the tables exactly you wanted.

How to associate a parent table with two unrelated Child tables

2 Answers2