Hibernate @OneToOne mapping with a @Where clause

Question

Will this work -

@OneToOne()
@JoinColumn(name = "id", referencedColumnName = "type_id")
@Where(clause = "type_name = OBJECTIVE")
public NoteEntity getObjectiveNote() {
  return objectiveNote;
}

This is what I am trying to do - get the record from table note whose type_id is the id of the current object and type_name is OBJECTIVE.

I can't get the above mapping to work. What am I doing wrong here?

Incontrollable

"OBJECTIVE" is it constant or an attribute? – Brindled

You need to show NoteEntity. Also, try configuring <property name="show_sql">true</property> in order to see what's in the SQL. – Barnyard

Answer 1

This just plain does not work, sorry :( You will need to do it as one to many and live with getting a collection with a single element.

If you really want it to work this way, you can trick hibernate by storing both the foreign key ID and the type_name in a join table and telling it that both columns make up the foreign key.

Freestyle

Ok, how? Option #2 – Glottal

@Freestyle i can confirm that first option by changing the object to a list and doing onetomany(even if the design is onetoone), then getting a single element works fine. Thanks – Fancywork

Answer 2

Actually you can achieve this by specifying @OneToOne without any @Where, but putting @Where on the referenced entity class. I tested this on Hibernate 4.3.11.

This works if you don't care about any entity objects that do not match your @Where.

If you do care about other entities, you can probably create a subclass entity, put @Where on it and join that subclass. But I have not tested this scenario.

Centaury

putting where on the subclass does not seem to work when the join column is specified in the base class – Quamash

Answer 3

You can try using JoinColumnsOrFormulas annotation.

@OneToOne()
@JoinColumnsOrFormulas({
    @JoinColumnOrFormula(column =
        @JoinColumn(name = "id", referencedColumnName = "type_id")),
    @JoinColumnOrFormula(formula =
        @JoinFormula(value = "OBJECTIVE", referencedColumnName = "type_name"))
})
public NoteEntity getObjectiveNote() {
  return objectiveNote;
}

This should produce join condition even with additional filtering for type_name = OBJECTIVE.

Candycandyce