Hibernate: joining with one of the keys of a multi-keyed table

Question

Hi guys,

I've got a table Category and a table TranslatableText. The category is like this

create table Category (
  id int not null,
  parent_id int default 0,
  TranslatableDescriptionId int default 1,
  primary key(id));

create table TranslatableText (
  id int not null,
  lang enum ('NO','EN','FR'),
  text mediumtext,
  primary key(id, lang));

In my Category entity I've defined a mapping:

@Fetch(FetchMode.SUBSELECT)
@Cache(usage=CacheConcurrencyStrategy.READ_ONLY)
@OneToMany(fetch=FetchType.LAZY)
@JoinColumn(name="TranslatableDescriptionId")
@ForeignKey(name="FK_TranslatableTextId")
private Set<TranslatableText> translatableText;

But when it executes, it tries to access TranslatableDescriptionId, not id. Even if the TranslatableText entity has defined

@Id
@Column(name = "id", nullable = false)
private Integer id;

@Id
@Column(name = "lang", nullable = false)
@Enumerated(EnumType.STRING)
private String lang;

@Column(name = "text", length = 400, nullable = false)
private String text;

The query with the incorrect name selected:

select translatab0_.TranslatableDescriptionId as Translat4_13_1_, translatab0_.id as id1_, translatab0_.lang as Lang1_, translatab0_.id as id22_0_, translatab0_.lang as Lang22_0_, translatab0_.text as Text22_0_ from tblTranslateableText translatab0_ where translatab0_.TranslatableDescriptionId in ('126', '119', '103', '116', '121', '107', '113', '101', '109', '105', '123', '106', '125', '124', '114')

If I change the mappings @JoinColumn to read

@JoinColumn(name="TranslatableDescriptionId", referencedColumnName="id")

I get the following error when loading my app:

org.hibernate.MappingException: Unable to find column with logical name: id in org.hibernate.mapping.Table(Category) and its related supertables and secondary tables

For good measure I also tried:

@JoinColumn(name="id", referencedColumnName="TranslatableDescriptionId")

That gave me the error:

org.hibernate.MappingException: Unable to find column with logical name: TranslatableDescriptionId in org.hibernate.mapping.Table(Category) and its related supertables and secondary tables

Any suggestions to what I should do? I really want Category's translateableText to contain all the translations for its description, so I really want to join Category.TranslatableDescriptionId==TranslatableText.id

UPDATE1: TranslatableText is used by many entities, so putting in a categoryId in it and reversing the relationship is not an option.

UPDATE2: I was able to load it saying @JoinColumn(name="id"), but this led to a ClassCastException in Hibernate where it, instead of having an Integer as a key, has an Array containing a single Integer as a key. This fails to be made into a String and thus proper SQL. So it's probably still not the mapping I want

Cheers

Nik

Answer 1

This kind of mapping is possible, but not very convenient because you'll have to manage identity of TranslatableTexts manually (that's why Hibernate complains about non-mapped column TranslatableDescriptionId):

public class Category implements Serializable {
    ...
    private Long translatableDescriptionId;

    @OneToMany
    @JoinColumn(name="id", referencedColumnName="TranslatableDescriptionId") 
    private Set<TranslatableText> translatableText;
    ...
}

So, you need to manually assign unique translatableDescriptionIds to all "targets" of TranslatableText (categories, items, folders as you say) and manually set this values as id of TranslatableText before persisting it (you can't just add TranslatableText into the Set).

--

However, the more convenient design is to introduce an intermediate entity to keep the identity of all transalations attatched to a specific target:

public class Category {
    ...
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "targetId")
    private TranslationTarget target;
}

public class TranslationTarget {
    @Id @GeneratedValue
    private Long id;

    @OneToMany
    @JoinColumn(name = "targetId")
    private Set<TranslatableText> texts;
}

-

create table Category (              
  targetId int,
  ...);        

create table TranslationTargets (
  id int primary key
);      

create table TranslatableText (              
  targetId int not null,              
  lang enum ('NO','EN','FR'),              
  text mediumtext,              
  primary key(targetId, lang));