hibernate - @ManyToOne(fetch = FetchType.LAZY) doesn't work on non-primary key referenced column

Question

I'm having some troubles to make a @ManyToOne association to be loaded lazilly. I'm using the fetch=LAZY but it doesn't work when join isn't made by the primary key column.

I know this question was already asked but I think it wasn't properly answered, so I provide detailed information to clarify the issue.

This is my model:

DummyB -> DummyA

These are the tables:

create table dummyA  (
  id number(18,0), --pk
  name varchar2(20) -- unique field
);

create table dummyB  (
  id number(18,0),
  dummya_id number(18,0),
  dummya_name varchar2(20)
);

And these are the entities:

@Entity
public class DummyA implements Serializable {

    private Long id;
    private String name;

    @Id
    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

@Entity
public class DummyB implements Serializable {

    private Long id;
    private DummyA dummyA;

    @Id
    public Long getId() {
        return id;
    }

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

    /* Case 1: mapping DummyB -> DummyA by DummyA NON primary key (field name) */
    // @ManyToOne(fetch = FetchType.LAZY)
    // @JoinColumn(name = "dummya_id")
    // public DummyA getDummyA() {
    // return dummyA;
    // }

    /* Case 2: mapping DummyB -> DummyA by DummyA primary key */
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "dummya_name", referencedColumnName = "name")
    @LazyToOne(LazyToOneOption.PROXY)
    public DummyA getDummyA() {
        return dummyA;
    }

    public void setDummyA(DummyA dummyA) {
        this.dummyA = dummyA;
    }

}

Note getDummyA method in entity DummyB is duplicate to try out two cases to join the entities.

Case 1: mapping DummyB -> DummyA by DummyA primary key

@ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "dummya_id")

This works fine, just one query is executed to retrieve DummyB objects.

Case 2: mapping DummyB -> DummyA by DummyA NON primary key (field name)

@ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "dummya_name", referencedColumnName="name")

Same dummyB select is execute, but right after, a dummyA select is executed filtering by name=? to fetch the related A object.

I'm using a really simple jUnit to execute filtering:

public class DummyTest {

    @Autowired
    HibernateTransactionManager transactionManager;

    @Test
    @Transactional
    public void testFindDummyB() throws DAOException {
        Long idDummyB = 2L;

        Session session = getCurrentHibernateSession();

        List lst = session.createCriteria(DummyB.class)
                .add(Restrictions.eq("id", idDummyB)).list();

        assertTrue(lst.size() > 0);
    }

    private Session getCurrentHibernateSession() {
        return this.transactionManager.getSessionFactory().getCurrentSession();
    }

}

My libraries:

• org.hibernate:hibernate-core:jar:4.2.17.Final:compile
• org.hibernate.common:hibernate-commons-annotations:jar:4.0.2.Final:compile
• org.hibernate.javax.persistence:hibernate-jpa-2.0-api:jar:1.0.1.Final:compile
• org.hibernate:hibernate-validator:jar:4.3.2.Final:provided

Other things I've already tried:

• Adding hiberante's @LazyToOne to getDummyA() method doesn't have any effect.

  @LazyToOne(LazyToOneOption.PROXY) @ManyToOne(fetch = FetchType.LAZY, optional = true) @JoinColumn(name = "dummya_name", referencedColumnName = "name") @LazyToOne(LazyToOneOption.PROXY)

• Creating a foreign key from DummyB table to dummyA (and an unique constraint in dummya.name field) has no effect.

• Adding @Column(unique = true) on DummyA getName() method didn't make it.
• Set optional=true or false as suggested here has no effect either.

• Trying to force the lazy loading using the setFetchMode in the criteria didn't work, DummyA select keeps executing.

  List lst = session.createCriteria(DummyB.class) .add(Restrictions.eq("id", idDummyB)).setFetchMode("dummyA", FetchMode.SELECT) .list();

I can't find in Hibernate's docs a point where it refers to this behaviour, so I wonder if there's anything is wrong in my annotations or I came upon a Hibernate bug.

Can anyone tell?

UPDATED by md-dev request: To set it more clear:

Is this the expected behaviour or is a bug? if this the expected behaviour, where is it documented?

Thank you.

Answer 1

Seeing the exact same behaviour with Hibernate 5.0.4. @ManyToOne (with a reciprocal OneToMany) and Lazy fetching works perfectly if the join column is the primary key. If it's not, lazy loading breaks and Hibernate eagerly fetches all ManyToOne's every time an object is instantiated. This can be catastrophically slow if you do a Criteria.list() for, say, 1000 records. What started out as a single query for 1000 records can balloon into 5000 queries to eagerly fetch a variety of @ManyToOne's using individual selects.

Absolutely nothing I've been able to test/change has explained this in any way and I can reproduce it reliably.

The solution I had to implement in my app that uses non-PK's for joins was to just trash @ManyToOne/@OneToMany annotation pairs and write collection fetches manually (caching the results with a transient variable). It's way more work but the performance is substantially higher given that some of my objects have 5 or 6 @ManyToOne objects and all of these were being eagerly fetched with individual selects by Hibernate.

Unfortunately, I can't reorganize my schema to accommodate this quirk in Hibernate. I'm doing a project involving Heroku Connect and the joins between tables when merging data from Salesforce.com are done using a "sfid" column in the table that is not the primary key. The primary key is a separate value unique to the record in the Heroku Postgres database and can't be used to do joins on as no other tables in the database refer to this primary key.

I'm assuming that this is a bug in Hibernate; nothing I've read or been able to modify has affected this behaviour in any way and, as I mentioned, I can make the system work exactly as expected if the join columns are the primary keys.