Hibernate @OneToOne self join with OR in join clause

Question

I have a @OneToOne annotation which I want to join on 2 possible columns. I know how to do it with a plain SQL query, but I have no idea how this could work with hibernate annotations.

Following entity:

@Entity
public class Foo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @OneToOne
    @JoinColumn(name = "cancelRecord")
    private Foo cancelRecord;

    private String externalId;
    private String externalCancellationId;
}

and the corresponding database table:

create table Foo(
    id int identity(1,1) not null primary key, --primary key
    cancelRecord int,
    externalId varchar(100),
    externalCancellationId varchar(100)
)

So far cancelRecord is only joined on that column. Now I want it to join on either cancelRecord or externalCancellationId. A valid SQL query for that would be:

SELECT f.* 
FROM Foo f 
        INNER JOIN Foo fo 
        ON (f.cancelRecord = fo.id 
        OR f.externalCancellationId = fo.externalId)

The crucial part about this query is the OR in the join clause (f.cancelRecord = fo.id **OR** f.externalCancellationId = fo.externalId).

I assume that this is not possible with @JoinColumn and I'd need to rely on @JoinFormula in some way.

Is that assumption right? If so, would I simply need to copy the above query as @JoinFormula?

I tried to use this @JoinFoluma:

@OneToOne
@JoinFormula(value = "SELECT f.* 
        FROM Foo f 
        INNER JOIN Foo fo 
        ON (f.cancelRecord = fo.id 
        OR f.externalCancellationId = fo.externalId)")
private Foo cancelRecord;

This causes an NullPointerException when loading the spring context:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in class path resource [spring/test-database.xml]: Invocation of init method failed; nested exception is java.lang.NullPointerException
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1589)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:554)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:351)
    ... 43 common frames omitted
Caused by: java.lang.NullPointerException: null
    at org.hibernate.cfg.AnnotationBinder.bindOneToOne(AnnotationBinder.java:3185)
    at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1798)
    at org.hibernate.cfg.AnnotationBinder.processIdPropertiesIfNotAlready(AnnotationBinder.java:961)
    at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:788)
    at org.hibernate.boot.model.source.internal.annotations.AnnotationMetadataSourceProcessorImpl.processEntityHierarchies(AnnotationMetadataSourceProcessorImpl.java:250)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess$1.processEntityHierarchies(MetadataBuildingProcess.java:231)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:274)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.build(MetadataBuildingProcess.java:84)
    at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:474)
    at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:85)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:689)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
    at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:511)
    at org.springframework.orm.hibernate5.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:495)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585)
    ... 50 common frames omitted

EDIT:

Exception resolved with this question, but sadly this again ends with an AND instead of an OR.

Thats what I already thought. Do I need to include the whole query as plain SQL query? — XtremeBaumer, Jul 31 '19 at 08:01
Sadly that doesn't help with the exception. I tried to add `referencedColumnName` to the `@JoinFormula`, but that doesn't work either — XtremeBaumer, Jul 31 '19 at 08:41
The whole query has to be wrapped in brackets I think. Not sure the lack of them would be causing the exception seen however. — Alan Hay, Jul 31 '19 at 08:51
Can't get it to work in any way because of that exception. Will ask a follow up question about that exception — XtremeBaumer, Jul 31 '19 at 09:25
Could resolve the issue, but by resolving the issue, I went back to an `AND` instead of the needed `OR` — XtremeBaumer, Jul 31 '19 at 10:18

score 1 · Accepted Answer · answered Jul 31 '19 at 10:57

1

That will not work since the @JoinColumn or @JoinFormula are used to determine Foreign Key column value, so they don't work with multiple columns.

If you define multiple @JoinColumn or @JoinFormula, then the ON relationship will use an AND not an OR.

In your case, since you probably only want to read this association, as opposed to setting it using Hibernate, then you are better off using an SQL query instead.

answered Jul 31 '19 at 10:57

Vlad Mihalcea

142,745
71
566
911

Which approach would you suggest to set keep `@JoinColumn` functionality, but also set the `Foo cancelRecord;` with an SQL query? Would I need to use a `ResultTransformer` to set the `cancelRecord` manually? – XtremeBaumer Jul 31 '19 at 11:18
`ResultTransformer` is for transforming the `Object[]` result into a DTO or Pojo. In your case, you cannot use Hibernate to set the association unless you write a custom Type for that. Otherwise, how would HIbernate know which of those two columns to set and based on what logic? – Vlad Mihalcea Jul 31 '19 at 11:40
Good question. I now am using a HQL to get the base object with `@JoinColumn` and then check if `cancelRecord` is null and if `externalCancellationId` is set. If that is the case, I run a second HQL to get the cancellation record and set that one to the original entry. – XtremeBaumer Jul 31 '19 at 11:43

score -2 · Answer 2 · answered Jul 31 '19 at 07:53

-2

You can use more than one @JoinColumn annotation. See an example below:

   @OneToOne
   @JoinColumns(
     {
       @JoinColumn(updatable=false,insertable=false, name="cancelRecord", referencedColumnName="cancelRecord"),
       @JoinColumn(updatable=false,insertable=false, name="other", referencedColumnName="other")
     }
   )
   private Foo cancelRecord;

answered Jul 31 '19 at 07:53

Jaume Morón i Tarrasa

631
5
9

2

Does that act as an `OR`? I always thought it would act as an `AND` – XtremeBaumer Jul 31 '19 at 07:53
Don't think that is useful here. The questioner wants to join on A **or** B, not A **and** B. – Alan Hay Jul 31 '19 at 07:54

Hibernate @OneToOne self join with OR in join clause

2 Answers2